Analysis of Entity Relationship Diagram - Assignment Example

CO2060 (Databases) Assignment 12 K Number I declare that the attached work is all my own, and that where I have quoted from, used or referred to the opinions, work or writings of others, these have been fully and clearly acknowledged.  2 I am aware of the consequences of late submission. Contents Task 1: Entity Relationship Diagram 3 Task 2: Data Dictionary 4 Task 3: CREATE TABLE statements and screenshots of the table structure 5 Task 4: Screenshots of table with populated data, sample INSERT INTO tables and discussion 12 Task 5: 19 Query 1: SQL query and Screenshot of the results 19 Query 2: SQL query and Screenshot of the results 20 Query 3: SQL query and Screenshot of the results 21 Task 1: Entity Relationship Diagram Business Rules/Assumptions: i. A clinic must employ one or more staffs and each staff must be employed by one and only one clinic. ii. A clinic can create one or more appointments but each appointment must correspond to one and only one clinic. iii. A patient can fix one or more appointments but each appointment must correspond to one and only one employee. iv. A patient must receive one or more invoices but each invoice must correspond to one and only one patient. v. A patient can get immunized by one or more vaccines. vi. A staff can administer vaccines to one or more patients, but each patient must be administered by a vaccine, by one and only one staff. vii. An invoice must correspond to one and only one patient; however a patient can have more than one invoice. viii. All history records for each employee can be obtained by querying Invoice table. Task 2: Data Dictionary Entity/attributes DataType/size PK/FK Not Null/default values Clinic clinicID Number(8) PK Not Null clinicStreetName Varchar2(40) Not Null clinicCityName Varchar2(40) Not Null clinicCounty Varchar2(40) clinicPostCode Varchar2(7) Not Null clinicPhone Varchar2(11) Not Null clinicFax Varchar2(11) Not Null clinicManager Number(8) Staff staffID Number(8) PK Not Null staffFirstName Varchar2(20) Not Null staffLastName Varchar2(20) Not Null staffStreetName Varchar2(20) Not Null staffCityName Varchar2(12) Not Null staffCounty Varchar2(12) staffPostCode Varchar2(7) Not Null staffPhone Varchar2(11) Not Null staffDOB Date Not Null staffGender Char(1) Not Null staffJD Varchar2(20) Not Null staffClinic Number(8) FK Not Null staffNIN Varchar2(9) staffSalary Float Patient patientID Number(8) PK Not Null patientFirstName Varchar2(20) Not Null patientLastName Varchar2(20) Not Null patientStreetName Varchar2(20) Not Null patientCityName Varchar2(12) Not Null patientCounty Varchar2(12) patientPostCode Varchar2(7) Not Null patientPhone Varchar2(11) Not Null patientDOB Date Not Null patientGender Char(1) Not Null Vaccine vaccineID Number(8) PK Not Null vaccineDetail Varchar2(50) Not Null vaccineCost Float Not Null Invoice invoiceID Number(8) PK Not Null vaccineID Number(8) PK/FK Not Null invoiceDate Date Not Null invoiceCost Float Not Null invoiceStatus Char(1) Not Null patientID Number(8) FK Not Null staffID Number(8) FK Not Null Appointment appointmentID Number(8) PK Not Null clinicID Number(8) FK Not Null patientID Number(8) FK Not Null appointmentDate Date Not Null appointmentTime Varchar(8) Not Null Task 3: CREATE TABLE statements and screenshots of the table structure Table 1: Clinic - SQL Statement: Table Structure: Table 2: Staff -SQL Statement: Table Structure: Table 3: Patient SQL Statement: Table Structure: Table 4: Vaccine SQL Statement: Table Structure: Table 5: Invoice SQL Statement: Table Structure: Table 6: Appointment SQL Statement: Table Structure: Task 4: Screenshots of table with populated data, sample INSERT INTO tables and discussion Table 1: Clinic Clinic table is populated with 2 clinics (rows). Clinic ID is chosen to be a combination of year of establishment of the clinic and a clinic number which is incremented by one each time a new clinic is set up. Thus, the two records inserted for each clinic has ID 20120001 and 20120002. One clinic is assumed to be located at Southampton and the other at London. Clinic Manager Field is not populated currently as manager can be only be added if a corresponding entry is made in the staff table. Hence the field will be updated after the manager is added as an employee in the staff table. The insert statements used for the creation of two rows are shown below along with the screen shot of populated table. Table 2: Staff Four staffs were added, two for each clinic. One staff is assumed to a nurse and the other as manager. ID for each staff is populated as a combination of year of joining, the clinic number and staff number. The city for each staff is populated with the same value as the city in which the clinic (they are employed) is located. Two managers were then updated in the clinic table. The SQL statements and the screen shot of populated table are shown below. Table 3: Patient Records for four patients were created, two for each clinic. Patient ID is chosen as a combination of clinic number of the clinic at which the registration is performed and a patient number incremented by one upon each entry. SQL statements and screenshot of populated table is shown below: Table 4: Vaccine Two rows are created for two vaccines: One for typhoid and another for yellow fever. VaccineID for each vaccine are chosen as an integer which increments by one upon every entry. Invoice cost is populated by querying the vaccine table for vaccine cost based on the vaccine id for which the invoice is created. The SQL statements and screen shot of populated table are shown below: Table 5: Invoice Four invoices were recorded for two registered patients one for each clinic. Invoice ID are chosen as numbers incremented by one upon each entry. SQL statements and screen shot of populated table are shown below: Table 6: Appointment Four rows were inserted, two for each clinic. Appointment ID is chosen as a combination of date on which the appointment is fixed and an appointment number incremented by one upon each entry for the day. The SQL statements and the populated table are shown below: Task 5: Query 1: SQL query and Screenshot of the results Report listing the Clinic address, and telephone number of clinics that were established in the year 2012 along with the Manager’s name and contact number, ordered by clinic number. SQL Query: Output: Query 2: SQL query and Screenshot of the results List of details of unpaid invoices for the month of April 2012 for each patient, ordered by patient ID. SQL Query: Output: Query 3: SQL query and Screenshot of the results The names and staff numbers for all nurses over 35 years old, ordered by staff last name. SQL Query: Output: Read More