Category: MySQL

pleasa write all the answers in the file , avoid plagiarism , the work should be in your own woeds , writie references.

pleasa write all the answers in the file , avoid plagiarism , the work should be in your own woeds , writie references.

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

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

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:

In case you missed it in earlier, please take the time to read the attached ‘Clarification’ document which explains the difference between Weeks 1,2,5 SQL Check Assignments (grading and feedback) vs Weeks 6-8 SQL assignments.
This assignment is composed of two deliverables. The directions for both deliverables are in the attached document.
The first deliverable, Week 5 Helpdesk SQL, guides you to produce a Word document with SQL code and screenshots of execution. You will attach that here.
The second deliverable, Week 5 SQL Check Helpdesk, is verification of your SQL results using an automatically graded online. which can be found under Quizzes. You may take the Week 5 SQL Check three times and your highest score will be posted to the Grades area. You are given multiple attempts so you can rework the SQL on the questions you miss. Do NOT attempt the quiz again without spending some time with your SQL queries; this is central to the learning process.
In order to earn full credit, you MUST do them both! Note if the first deliverable, the submitted Word document is incomplete, your posted grade for the Week 5 SQL Check will be modified. You should consider that a complete Word document, with SQL queries and screenshots is 50% of your grade on this assignment.
Attached to this assignment is the Word document containing your instructions, plus scripts (“Helpdesk_setup.sql” and “Helpdesk_insert.sql”) containing instructions your computer will need to make you the database so you can do this week’s homework.
Happy SQL!

Please log in to my Course Dashboard for a better understanding of HW1.
The tutor must know how to use Use Visio to create an ERD of the three entities and SQL Server to implement the relational schema
I have attached the HW3 Instructions, but you will need to use D2L for using the Virtual lab and SQL server.

Instructions
Please take the time to read the attached ‘Clarification’ document which explains the difference between Weeks 1,2,5 SQL Check Assignments (grading and feedback) vs Week 6-8 SQL assignments.
This assignment is composed of two deliverables, a Word document (fill in the attached template) and an SQL Check online quiz to verify your answers. The directions for both deliverables are in the attached document.
The first deliverable guides you to produce a Word document with SQL code and screenshots of execution. You will attach that here.
The second deliverable, Week 1 SQL Check Mythical Creatures I, is verification of your SQL results using our automatically graded online quiz.You may take the Week 1 SQL Check three times and your highest score will be posted to the Grades area. You are given multiple attempts so you can rework the SQL on the questions you miss. Do NOT attempt the quiz again without spending some time with your SQL queries; this is central to the learning process.
In order to earn full credit, you MUST do them both! Note if the first deliverable, the submitted Word document is incomplete, your posted grade for the Week 1 SQL Check will be modified. You should consider that a complete Word document, with SQL queries and screenshots is 50% of your grade on this assignment.
In this Week 1 assignment, we introduce you to a single-table database. It requires you to
download and run an SQL script to set the database up on your work area
write and execute several SQL queries to demonstrate mastery
Attached to this assignment is the Word document containing your instructions, plus a script (“MythicalCreatures.sql”) containing instructions your computer will need to create the database so you can do this week’s assignment.
In other tutorials this week, you use the Khan Academy website to work through tutorials, create databases and queries and test code. In this assignment and in other assignments in the course, we’ll be using a different website called SQLiteonline.com.
Instructions for how to access SQLiteonline.com, create a database, run queries and check output of the queries are found in the attached file Setting_Up_SQLiteonline_com.docx. Those instructions will provide information on how to take snippets of your output to cut and paste into Word for submission.
If you have questions, ASK.
DUE ON MAY 21, 2024 11:59 PM
ATTACHMENTS
MythicalCreatures.sql (19.44 KB)
Setting_Up_SQLiteonline_com_v2.docx (466.81 KB)
Assignment Mythical Creatures I for students.22… (55.63 KB)
Clarification_SQL_checks_Weeks1_2_5_vs_SQL_Week… (14.32 KB)
Download All Files

Instructions…….
Please take the time to read the attached ‘Clarification’ document which explains the difference between Weeks 1,2,5 SQL Check Assignments (grading and feedback) vs Week 6-8 SQL assignments.
This assignment is composed of two deliverables, a Word document (fill in the attached template) and an SQL Check online quiz to verify your answers. The directions for both deliverables are in the attached document.
The first deliverable guides you to produce a Word document with SQL code and screenshots of execution. You will attach that here.
The second deliverable, Week 1 SQL Check Mythical Creatures I, is verification of your SQL results using our automatically graded online quiz.You may take the Week 1 SQL Check three times and your highest score will be posted to the Grades area. You are given multiple attempts so you can rework the SQL on the questions you miss. Do NOT attempt the quiz again without spending some time with your SQL queries; this is central to the learning process.
In order to earn full credit, you MUST do them both! Note if the first deliverable, the submitted Word document is incomplete, your posted grade for the Week 1 SQL Check will be modified. You should consider that a complete Word document, with SQL queries and screenshots is 50% of your grade on this assignment.
In this Week 1 assignment, we introduce you to a single-table database. It requires you to
download and run an SQL script to set the database up on your work area
write and execute several SQL queries to demonstrate mastery
Attached to this assignment is the Word document containing your instructions, plus a script (“MythicalCreatures.sql”) containing instructions your computer will need to create the database so you can do this week’s assignment.
In other tutorials this week, you use the Khan Academy website to work through tutorials, create databases and queries and test code. In this assignment and in other assignments in the course, we’ll be using a different website called SQLiteonline.com.
Instructions for how to access SQLiteonline.com, create a database, run queries and check output of the queries are found in the attached file Setting_Up_SQLiteonline_com.docx. Those instructions will provide information on how to take snippets of your output to cut and paste into Word for submission.
If you have questions, ASK.
DUE ON MAY 21, 2024 11:59 PM
ATTACHMENTS
MythicalCreatures.sql (19.44 KB)
Setting_Up_SQLiteonline_com_v2.docx (466.81 KB)
Assignment Mythical Creatures I for students.22… (55.63 KB)
Clarification_SQL_checks_Weeks1_2_5_vs_SQL_Week… (14.32 KB)
Download All Files

Instructions
Please take the time to read the attached ‘Clarification’ document which explains the difference between Weeks 1,2,5 SQL Check Assignments (grading and feedback) vs Week 6-8 SQL assignments.
This assignment is composed of two deliverables, a Word document (fill in the attached template) and an SQL Check online quiz to verify your answers. The directions for both deliverables are in the attached document.
The first deliverable guides you to produce a Word document with SQL code and screenshots of execution. You will attach that here.
The second deliverable, Week 1 SQL Check Mythical Creatures I, is verification of your SQL results using our automatically graded online quiz. You may take the Week 1 SQL Check three times and your highest score will be posted to the Grades area. You are given multiple attempts so you can rework the SQL on the questions you miss. Do NOT attempt the quiz again without spending some time with your SQL queries; this is central to the learning process.
In order to earn full credit, you MUST do them both! Note if the first deliverable, the submitted Word document is incomplete, your posted grade for the Week 1 SQL Check will be modified. You should consider that a complete Word document, with SQL queries and screenshots is 50% of your grade on this assignment.
In this Week 1 assignment, we introduce you to a single-table database. It requires you to
download and run an SQL script to set the database up on your work area
write and execute several SQL queries to demonstrate mastery
Attached to this assignment is the Word document containing your instructions, plus a script (“MythicalCreatures.sql”) containing instructions your computer will need to create the database so you can do this week’s assignment.
In other tutorials this week, you use the Khan Academy website to work through tutorials, create databases and queries and test code. In this assignment and in other assignments in the course, we’ll be using a different website called SQLiteonline.com.
Instructions for how to access SQLiteonline.com, create a database, run queries and check output of the queries are found in the attached file Setting_Up_SQLiteonline_com.docx. Those instructions will provide information on how to take snippets of your output to cut and paste into Word for submission.
If you have questions, ASK.
Due on May 21, 2024 11:59 PM
Attachments
MythicalCreatures.sql (19.44 KB)
Setting_Up_SQLiteonline_com_v2.docx (466.81 KB)
Assignment Mythical Creatures I for students.22… (55.63 KB)
Clarification_SQL_checks_Weeks1_2_5_vs_SQL_Week… (14.32 KB)
Download All Files