Problem: Consider the following set of requirements for a hospital registration system that is used to keep track of doctor's related information and patients can use it to make an appointment in advance.
(a) The hospital keeps track of each doctor's name, doctor ID, social security number, birthdate, language, specialties, and position. Both social security number and doctor ID have unique values for each doctor. One doctor must belong to one department. One department must be managed by one doctor.
(b) Each department is described by a name, department ID, department category, department phone, and established date. Both name and department id have unique values for each department.
(c) Each Diagnosis Time has a start date time, and capacity. One diagnosis time belongs to one doctor and each doctor can have several diagnosis times.
(d) Each patient has patient ID, name, sex, birthday, address, and social security number. Some hospital records need to refer to the city, state, and zip of the patient's address, and to the patient's last name. Both patient number and social security number have unique values for each patient. Patient can choose one diagnosis time of one doctor to register for an appointment. Once the appointment was made, patient will get one registration number and the estimated time.
(e) Each nurse has nurse ID, name, social security number, nursing credential and nursing certification. Both nurse ID and social security number have unique values for each nurse. Each nurse must belong to one department and take care of one or more patients.
Design an ER schema for this application and draw an ER diagram for that schema. Specify key attributes of each entity type and structural constraints on each relationship type using (min, max) notation. Note any unspecified requirements and make appropriate assumptions to make the specification complete.
Task 1. Create a database using PostgreSQL
Consider a small employee database with the following data.
Employees:
| employee_id (PK) | name (Not Null) | hire_date | Salary | email (Unique) | level (Not Null) |
| 1 | Michael Keyes | 06/15/2020 | $30,000 | mkeye001@netw.com | parttime |
| 2 | Ellis Carlisle | 04/24/2017 | $50,000 | ecarl001@vers.com | fulltime |
| 3 | Gregy Garry | 01/09/2018 | $55,000 | ggarr001@netw.com | parttime |
| 4 | Randy North | 12/08/2019 | $28,000 | rnort@vers.com | parttime |
| 5 | Barbara Nunez | 11/08/2010 | $71,000 | bnune@jgreen.com | fulltime |
| 6 | Shirly Reid | 12/05/2007 | $89,000 | sreid001@itconnect.com | fulltime |
| 7 | Braden Robins | 08/22/2009 | $77,000 | brobi@jgreen.com | fulltime |
| 8 | Brendon Moffett | 07/31/2015 | $62,000 | bmoff001@netw.com | parttime |
Companies:
| company_id (PK) | name (Not Null) | industry | address | num_employees (Not Null) | description |
| 1 | Net World | Internet Services | 11345 SW 56 ST | 800 | Internet service provider working in 99.9% areas. |
| 2 | Just Green | Grocers | 4467 NW 8 ST | 10,000 | Sustainable agriculture for all! |
| 3 | Verse | Research | 8754 SW 134 TER | 150 | Research Thinktank specializing in the natural sciences. |
| 4 | IT Connect | Internet Services | 2105 NE 17 ST | 5,400 | Help solve everyones IT problems! |
Work Assignments:
| assignment_id (PK) | due_date (Not Null) | difficulty (Not Null) | Company_id (FK) |
| 1 | 02/18/2022 | easy | 1 |
| 2 | 03/10/2022 | hard | 2 |
| 3 | 01/29/2022 | medium | 4 |
| 4 | 05/30/2022 | hard | 1 |
| 5 | 04/12/2022 | easy | 3 |
| 6 | 09/17/2022 | easy | 3 |
| 7 | 07/20/2022 | medium | 2 |
Performance:
| employee_id (FK/PK) | assignment_id (FK/PK) | score |
| 1 | 1 | 100 |
| 1 | 4 | 75 |
| 2 | 5 | -1 |
| 4 | 5 | 59 |
| 4 | 6 | 29 |
| 5 | 7 | -1 |
| 5 | 2 | 95 |
| 6 | 3 | 88 |
Requirements:
Query1. Create the above-mentioned four tables with proper constraints and make sure both primary keys (PK) and foreign keys (FK) are specified.
Note:
Query2. Insert the data for each table.
Task 2. Manage a database using PostgreSQL
Query1. Insert five more records to the table Performance by using one query. The records are as follows.
| employee_id (FK/PK) | assignment_id (FK/PK) | score |
| 2 | 6 | 77 |
| 7 | 2 | -1 |
| 7 | 7 | 81 |
| 8 | 1 | 48 |
| 8 | 4 | 91 |
Query2. Update the Performance table and set the grade to incomplete (0) where score is -1.
Query3. Add "NOT NULL" constraint to score column in the Performance table.
Query4. Delete the records from the table Performance where score is less than 50.
Query5. Export the Performance table as Performance.csv by using SQL command "COPY". (Note: You need to be a root user to perform this action, so just provide the syntax of this query.)
Query6. Add a new column called "company_id" to the table Employees.
Query7. Rename the column level in the tables Assignments and Employees to worker_type.