MySQL - punditwritersbay.com

Write a SQL query to get the doctor_name where the city they live in is not the

Write a SQL query to get the doctor_name where the city they live in is not the same as the city their hospital is in
Table Name: Doctor
doctor_id
doctor_name
lives_in_city
specialization
hospital_id
3001
Dr. John Smith
New York
Cardiology
2001
3002
Dr. Emily Davis
Chicago
Neurology
2002
3003
Dr. Sarah Lee
Los Angeles
Pediatrics
2003
3004
Dr. Mark Brown
Miami
Oncology
2004
3005
Dr. Anna White
San Diego
Orthopedics
2005
3006
Dr. David Clark
Houston
General Surgery
2006
3007Dr. Hanna HostMiamiRadiologist2005
table Name: Hospital
hospital_id
name
city
patients_admitted
patients_discharged
2001
City Hospital
New York
150
120
2002
Green Valley Clinic
Chicago
200
180
2003
Sunshine Medical Center
Los Angeles
100
90
2004
Lakeside Health Facility
Miami
300
250
2005
Hilltop General Hospital
San Diego
400
350
2006
Oceanview Medical Center
Houston
250
220

Write a SQL query to get the doctor_name where the city they live in is not the

Subject – 2024 Summer – Database Systems (ITS-538-M80) – Full Term Course Projec

Subject – 2024 Summer – Database Systems (ITS-538-M80) – Full Term
Course Projec

Subject – 2024 Summer – Database Systems (ITS-538-M80) – Full Term
Course Project Phase
Summary:
To apply all acquired knowledge in the last Project Assignments to create a Database Scheme. For this project, the ERD is provided to you.
Deliverables:
SQL Script with your name as follows: YourLastName_Project_Ph3.sql.
MS Word Document with your OUTPUT, named as follows: YourLastName_Project_Ph3_Output.doc.
ZIP file to include the two files mentioned above.
Required Software:
MySQL DBMS by Oracle. MySQL Community Server and MySQL Workbench.
Follow Step 1-3 for your Course Project Phase phase 1, to install the required software components.
Lab Steps:
Step 1: Analyze the ERD for a student information system provided below. Be alert for the specifications provided for: 1) Entities, 2) Attributes, 3) Primary Key, and 4) Relationships. You will need to have a clear understanding for these components to create your database using MySQL.

Step 2: Create a New Database in MySQL, Produce SQL File, Drop Table
Create a new Database, as you did in your Course Project Phase 1 deliverable and you already created a database using the MySQL DBMS. Reference the attached video (Course Project Ph1 Video.mp4), for a demonstration to create a new Database using MySQL. This video was initially presented to you for phase 2 of your project. Also, reference the supplemental text: Available for Free download as part of our UC Library. MySQL Database Usage & Administration. (2010). By Vaswani, Vikram. McGraw Hill. ISBN: 978-0-07-160550-2. Link to UC Library: http://search.ebscohost.com/login.aspx?direct=true&AuthType=shib&db=nlebk&AN=291311&site=eds-live&ebv=EB&ppid=pp_ii
Utilize the SQL dialect you learned so far in this course for MySQL. Use the file supplemental textbook: MySQL Database Usage & Administration. (2010). By Vaswani, Vikram. McGraw Hill, as supplement your knowledge of the MySQL dialect.
Create your SCRIPT file to be named: YourLastName_Project_Ph3.sql.
Step 3: Include the following commands at the tops of your scripts:
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS STUDENT;
DROP TABLE IF EXISTS CAMPUS;
DROP TABLE IF EXISTS ROOM;
DROP TABLE IF EXISTS COURSE;
DROP TABLE IF EXISTS INSTRUCTOR;
DROP TABLE IF EXISTS APPROVED_INSTRUCTOR;
DROP TABLE IF EXISTS CLASS;
DROP TABLE IF EXISTS STUDENT_GRADE;
SET FOREIGN_KEY_CHECKS=1;
Step 4: Create Tables in your database
Create a new Table for each of the entities provided to you in the ERD diagram in Step 1.
Add a column to represent each attribute addressed in the ERD diagram in Step 1.
Designate Primary Key, as noted in the ERD diagram in Step 1.
Designate a Foreign Key relationship between the tables, as noted in the ERD diagram in Step 1.
Enable referential integrity on the relationships, as needed.
Enable cascade updates as needed on the relationships.
Step 5: Designate Data Types. Update the data type as needed to enforce the domain constrain of the data. This needs to be completed for every column for all tables.
Dates: they should have a date data designation type.
Surrogate keys: shall be auto-numbered
Character type: shall have a character data designation type.
Step 6: Column Constraints Designation.
Grade designation must be of one of these values as follows: A, B, C, D, E, F, W, E (E=enrolled, and W = withdrawn).
Student’s first and last names are not to be designated as NULL.
Course Credit hours shall be BETWEEN one and four.
The instructor first and last name must NOT be NULL
Course name designation has to be UNIQUE and must not be NULL type.
Step 7: Data Table Addition
Use the INSERT operator to add minimum 2-3 rows of data per each database.
You are free to use any values you might like for each of the columns.
NOTE/ Reminder: you are required to add data to the parent table prior to adding any data to child tables, as referential integrity is enabled.
Step 8: Executing your SCRIPT
Must incorporate the COMMIT command at the end of your Script
Must incorporate the SHOW TABLES command, to display the table you created. At the end of the script created.
Must incorporate the SELECT statement to show data allocated for each table. This would be added at the end of the script.
EXECUTE your SCRIPT.
Copy and paste your OUTPUT into your MS Word file as follows: YourLastName_Project_Ph3_Output.doc
Step 9: Upload your work
Upload ZIP file to include the two files as follows: 1) SQL Script with your name as follows: YourLastName_Project_Ph3.sql, and 2) MS Word Document with your OUTPUT, named as follows: YourLastName_Project_Ph3_Output.doc.
Rubric:
Tables Created: create a table for each entity as noted in the ERD diagram in Step 1. 5 points
Columns Created: create a column for each attribute as noted the ERD diagram in Step 1. 5 points
Primary Key designation: primary key addressed for all tables with unique constraints specified as column properties. 5 points
Data Types: Addressed for each attribute. Should include: 1) Date data type incorporated, 2) Surrogate Key Automated, 3) Numeric data shall be numeric type, 4) Character data shall have a character type. 5 points
Relationships Created, as noted in the ERD diagram in STEP 1. Relationships shall be enabled for referential integrity and cascade updates. 10 points.
Data Added per row: 5 rows of data for each table. 10 points
Column Constraints Added: As noted in the description for the project. Checked by adding rows with invalid values. Should take into consideration constraints as follows: 1) student name cannot be NULL, 2) course credit hours are restricted to allocations between 1-4, 3) course name is unique and NOT NULL, 4) instructor last name and first name can NOT be NULL, and 5) grade restrictions to the following values: A,B,C,D,F,I,W, E. 10 points
Note; My First name – Prashanth , my Last Name – Pandu

Rubric for Easy read attached below My personal info is in the word doc below Ad

Rubric for Easy read attached below
My personal info is in the word doc below
Ad

Rubric for Easy read attached below
My personal info is in the word doc below
Advanced Database Management Project: Embracing the Challenge
This project is designed to mirror the real-world experience you’ll encounter in your database management career. Here’s what to expect:
Independent Troubleshooting: You’ll be tasked with troubleshooting, researching, and finding solutions independently. This is a crucial skill in the field, as you’ll often face unforeseen challenges requiring independent problem-solving.
Simulated Real-World Issues: Expect difficulties during installation, similar to what you might face on the job. For instance, installing PHP can sometimes be tricky, but this is a realistic scenario that will help you develop your troubleshooting skills.
Database Connectivity: You must connect your PHP application to a MySQL database. This may involve using a web browser, command line (terminal), or IDE. Be prepared to troubleshoot any connection issues that arise.
Unclear Instructions: Just like in real-world projects, instructions may only sometimes be crystal clear. This is an opportunity to hone your research and critical thinking skills to find the necessary information and solutions.
Embrace the Learning Curve: This project is not meant to be a smooth ride. It’s designed to challenge and equip you with the skills necessary to thrive in real-world database management scenarios. View this as a valuable learning experience that will prepare you for the exciting challenges and rewarding opportunities in the job market.
Good Luck!
Project Requirements
Create a Word document with the doc or docx extension.
Add the following information to the first page of the Word document:

Project: PHP MySQL
Florida International University Student Name: (word doc with private info)
Department of Knight Foundation Student ID: (Word doc below with this private info)
School of Computing and Information Sciences
COP 4751: Advanced Database Management Systems
Summer 2024: Total Marks 16.66 points
Course Teacher: Dr. Kiavash Bahreini
Add the following fourteen steps as the titles of your sections in the Word document.
Open the following link on w3schools.com and follow the instructions:
1. Open the PHP MySQL Database page: PHP: MySQL Database (w3schools.com)Links to an external site. and follow all the instructions on this page from the top to the bottom. After doing step 1, you will open the following URLs and complete steps 2 to 14.
2. Open PHP MySQL Connect to the database (w3schools.com)Links to an external site. and follow all the instructions on this page from the top to the bottom. You need to install MySQL 8.0.34 or other versions from https://dev.mysql.com/downloads/installer/
Also, install MySQL Workbench 8.0.34 or other versions from https://dev.mysql.com/downloads/workbench/
3. Open PHP MySQL Create Database (w3schools.com)Links to an external site. and follow all the instructions on this page from the top to the bottom. Please figure out which version of the PHP you require to install. That is part of your project.
4. Open PHP MySQL Create Table (w3schools.com)Links to an external site. and follow all the instructions on this page from the top to the bottom.
5. Open PHP MySQL Insert Data (w3schools.com)Links to an external site. and follow all the instructions on this page from the top to the bottom.
6. Open PHP MySQL, Get the Last Inserted ID (w3schools.com)Links to an external site., and follow all the instructions on this page from the top to the bottom.
7. Open PHP MySQL Insert Multiple Records (w3schools.com)Links to an external site. and follow all the instructions on this page from the top to the bottom.
8. Open PHP MySQL Prepared Statements (w3schools.com)Links to an external site. and follow all the instructions on this page from the top to the bottom.
9. Open PHP MySQL Select Data (w3schools.com)Links to an external site. and follow all the instructions on this page from the top to the bottom.
10. Open PHP MySQL Select Data With WHERE Clause (w3schools.com)Links to an external site. and follow all the instructions on this page from the top to the bottom.
11. Open PHP MySQL. Select Data With ORDER BY Clause (w3schools.com)Links to an external site. and follow all the instructions on this page from the top to the bottom.
12. Open PHP MySQL Delete Data (w3schools.com)Links to an external site. and follow all the instructions on this page from the top to the bottom.
13. Open PHP MySQL Update Data (w3schools.com)Links to an external site. and follow all the instructions on this page from the top to the bottom.
14. Open PHP MySQL Limit Data Selections (w3schools.com)Links to an external site. and follow all the instructions on this page from the top to the bottom.
15. After doing every step mentioned above, you capture the screen of your database or the execution of your application and add the photo into the related section in your Word document.
16. Save your Word document.
17. Submit your Word document to Canvas before the deadline.
18. Never send me, your TA, or your grader your document via emails or Canvas messages. Your emails or the Canvas messages will not be considered, and you won’t be graded for your project.

Description The project is based on deploying a Human Resource Management System

Description
The project is based on deploying a Human Resource Management System

Description
The project is based on deploying a Human Resource Management System (HRMS) for a large company. The goal is to protect employees’ data and avoid errors in the payroll system along with a safe working environment. It ensures security from data breaches. Tasks like building a secure network, configuring PCs and servers for HR data, and building databases to hold personnel data and performance indicators are just a few of the planning, organizing, carrying out, and troubleshooting involved in this project. Cloud services will also be made available for employees to utilize securely accessing remotely. The infrastructure will be protected from possible dangers like hackers by configuring security policies to restrict access to only authorized employees. In addition to this, 24-hour monitoring and helpdesk assistance will be provided to solve any issues for the continuation of work. The system’s main goals are to safeguard employee data and improve HR support. It results in a secure and safe workplace for the staff. The creation of a safe HR data management system, the configuration of servers, workstations, and self-service portals, firewall and security policy setup; and continuing support are all included in the project deliverables. Key players in the project include the team, the project manager, the employees, the HR and IT departments, and outside experts who provide technical services.
Requirements: | .doc file
Requirements:

Instructions: You must submit two separate copies (one Word file and one PDF fil

Instructions:
You must submit two separate copies (one Word file and one PDF fil

Instructions:
You must submit two separate copies (one Word file and one PDF file). These files must not be in compressed format.
It is your responsibility to check and make sure that you have uploaded both the correct files.
Zero mark will be given if you try to bypass the SafeAssign (e.g. misspell words, remove spaces between words, hide characters, use different character sets, convert text into image or languages other than English or any kind of manipulation)
You are advised to make your work clear and well-presented. This includes filling your information on the cover page.
You must use this template, failing which will result in zero mark.
You MUST show all your work, and text must not be converted into an image, unless specified otherwise by the question.
Late submission will result in ZERO mark.
The work should be your own, copying from students or other resources will result in ZERO mark.
Use Times New Roman font for all your answers.

Instructions: You must submit two separate copies (one Word file and one PDF fil

Instructions:
You must submit two separate copies (one Word file and one PDF fil

You must submit two separate copies (one Word file and one PDF file) using the A

You must submit two separate copies (one Word file and one PDF file) using the Assignment Template on Blackboard via the allocated folder. These files must not be in compressed format.
It is your responsibility to check and make sure that you have uploaded both the correct files.
Zero mark will be given if you try to bypass the SafeAssign (e.g. misspell words, remove spaces between words, hide characters, use different character sets, convert text into image or languages other than English or any kind of manipulation).
Email submission will not be accepted.
You are advised to make your work clear and well-presented. This includes filling your information on the cover page.
You must use this template, failing which will result in zero mark.
You MUST show all your work, and text must not be converted into an image, unless specified otherwise by the question.
Late submission will result in ZERO mark.
The work should be your own, copying from students or other resources will result in ZERO mark.
Use Times New Roman font for all your answers.
2 Marks
Learning Outcome (1):
Explain the concepts and architectures involved in the database development.
Question One
List the three main types of constraints in the relational model and explain each in your own words. List the Basic operations for changing the database and explain why the DELETE operation may violate only the referential integrity and not the entity constraint.
Answer:
2 Marks
Learning Outcome(4):
Instructors: Create Entity-Relationship model, Relational model, and write SQL queries.
Question Two
Your client, a retail company, has approached you for assistance in optimizing their inventory management system. They need a database schema to track product inventory, suppliers, and orders efficiently. Design an Entity-Relationship (ER) diagram to represent this system, considering the following entities and their attributes:
Product: Attributes may include ProductID, Name, Description, Price, and Quantity.
Supplier: Attributes may include SupplierID, Name, Contact, Address.
Order: Attributes may include OrderID, Date, and TotalAmount.
OrderDetails: This is a relationship entity representing the details of each order, such as the quantity of each product ordered.
Consider the relationships between these entities and ensure that your ER diagram reflects these connections accurately. Your diagram should illustrate how products are supplied by suppliers, how orders are placed, and the details associated with each order.
Answer
2 Marks
Learning Outcome(1):
Instructors: Explain the concepts and architectures involved in the database development.
Question Three
Identify and explain the various types of database users and their roles in a database system. Choose one type of user (e.g., end-users, database administrators, application programmers) and elaborate on the specific tasks and responsibilities associated with that user role. Discuss how the interaction of different user types contributes to a database system’s overall functionality and efficiency. Finally, provide a real-world example or case study where the collaboration of different types of database users played a crucial role in the success or failure of a database project. Ensure your response reflects a deep understanding of the roles and responsibilities of database users.
Answer:
2 Marks
Learning Outcome(5):
Instructors: Design a database starting from the conceptual design to the implementation of database schemas.
Question Four
You are tasked with designing a conceptual database schema for the King Fahad General Hospital. The system needs to store information about Patients, Doctors, Departments, and Medicines. Explain in detail the fundamental database concepts you would consider during the design phase. Discuss the importance of entities, attributes, and relationships. Additionally, outline your proposed database architecture’s potential challenges and benefits, considering factors such as scalability and data integrity.
Answer:

You must submit two separate copies (one Word file and one PDF file) using the A

Your boss has a question for you. “We think this is about the right level of gra

Your boss has a question for you. “We think this is about the right level of granularity for our data mart. What do you think? Should we extract more detailed information, and if so, what? Or would you recommend going to a coarser level of granularity, and if so, what fields would you recommend we drop?” Give your rationale. Think critically, and demonstrate a good understanding of data management.
Attached are the finished files for the assignment and the instruction. Please unzip and load the codes in MySql. My part is Question 4 in Memo Management. Thanks