In this project, you will use a Database Management System to create and manipulate a very small database system for hospital Your task is to improve it to some extent and implement the design as well as some queries against it. In this project, you will use any database server you want (Oracle, MySQL, etc.)

Project Description: You need to develop a management information system to help users at the administration level of the hospital easilyviewing/tracking various information about doctors how many doctors in department well as information about patines , departments , department and doctors .

• Data Model: We need to store information about hospital management system; The hospital keeps track of doctors each doctors has doc_id dctors ’s name(fname and lname), address, specialization,email and salary and phones .

• Each patient has patient_id,name(fname,lname)birthdate, gender, age , address should have street city, state, zip and phone

• Each departments identifier by dep_no,department_name, and phone. dep_no must be unique for each department.

Other requirements are as follows.

-each departments has many doctors and each doctors work on one departments

- Each doctors manages one departments.

-Each doctors treats many patients. Each patientsassigned in one or more departments and department assigned by many patients we keep track of admission date by assigned.

Implementation requirement: Considering the following queries, first finalize the schema (by adding required relations and/or changing the above relations) and then implement your relations. Next, populate your relations with appropriate data. Make sure each table in the database has enough number of tuples so that each query gets a meaningful and reasonable size output. For this, you should store at least 5 department, 5 patients, 5doctors for each department.

Finally, implement the following queries in SQL.

The queries are as follows.

1- Display all departments the name of departments started by S from departments table

2- For each departments find maximum salary andnumber of doctors when the maximum salary greeter than 5000 sort the result in descending order by maximum salary.

3- Write a query to display the fname of doctors , dep_no and department_name name for all doctors who work in deprment_name surgery

4- Display the manger name and department_name

5- Display the dep_no, lname of doctors , and email for every doctors how the salary greeter then minimum salary .(using subqueries ).

6- Display laname, dep_no and department_name of all the doctors s from the doctors table and departments table .Use set operators to create this quires

7- For each department_ name create a view named doc that contains the maximum salary ,minumum salary average salary , sum salary,count of doctors and department _name for all employees work in department_id 5 you label the view columns maxsal,avgsal,minsal,sumsal countemp and deptname For security purposes, do not allow an employee to be reassigned to another department through the view.

What you should hand in: You should print and submit a report that includes

(1) The E/R model for your database design together with reasonable assumption(s) made,

(2) List of all schemas and their attributes with appropriate data type. Also, identify the primary key for each schema together with the relationship between different schemas.

(3) The print out of tuples in each table, the SQL queries and the output of your queries against your database.

Answer rating: 100% (QA)

Answer and Explanation 1 ER Model Tables doctors patients departments Attributes and relationships b... View the full answer