Database design and technology
As a database developer, you have been asked to design a new database the operation of the new project within the Liverpool John Moores University (LJMU). LJMU is planning to deliver a number of professional training courses in Al. Each course is delivered by one member of staff at LJMU's Training Centre; each staff is allocated a staff number. The policy of LJMU is to record the start working date of each staff, the first and last name, the date of birth, home address, telephone number for (home and mobile), email, type of contracts (permanent, sessional), sex, National Insurance Number and the specified salary. There are different training courses offered by LJMU. The fees vary for each course and on the number of delegates a company sends. For example if a company sends one delegate, the charge may be 2500. If the company sends two people, the first may be charged 2500, but the second may be charged 2000 The Al professional courses can be attended by a number of delegates, subject to a limit for each course (Maximum 10 for each of the planned professional courses). A start and end date is assigned for each course with the start and end time. An invoice is sent to the delegate's company. The invoice clarifies to the client orland the company the date that the fee is paid, method of payment (one full payment, or a deposit then completing the payment). (a) Identify the main entities (list all attributes of these entities) for the above proposed database. Determine primary key, Candidate keys and foreign keys attributes for each entity (b) Create a single Entity-Relationship (ER) diagram to represent the data requirements of the database Conceptual Database Design). To simplify the diagram, only show entities, relationships, primary and foreign key attributes. State any assumptions (if necessary) to support your design. (c) Identify the main relationships between entities described in part (a); represent each relationship in your ER diagram, clearly labelling each relationship. State the degree of each of the relationships described in part (b). Decompose any many to many relationships replacing them with new entity types and relationships. The LIVER DATABASE Company in Liverpool wishes to allocate staff to a new development project that has been proposed by external companies. As a result, a new relation was created for this proposal as follows SaffPro (StaffNo, ProjNo, ProjName ProjLocation, StaffAddress, StaffEmail, StaffMobileNo) Assuming that, StaffHours, StaffSurName, StaffNo stands for staff number of the employee; ProjNo identifies one of the projects that the staff works on; StaffHours is the number of hours per week that the staff works on the project; ProjName and Project location are the name and location of the project; StaffSurName is the staff surname; StaffAddress is the postcode of the address of the staff StaffEmail is the staff university email address StaffMobileNo is the staff Mobile telephone number (a) Using appropriate diagrams to aid in your answer briefly describe the following concepts: Normalisation including First, Second and Third Normal forms Primary, Forign and Candidate keys Functional dependences. (b) Based on the given primary key (StaffNo, Projld). Show, comprehensively, how you would normalise the StaffProject relation to first, second and third normal forms, indicating the functional dependencies you use in each stage of normalisation with justifications. State any assumptions you make about the data and time (if any) The logical phase of the database design process has provided the following relations for a database that was proposed to support the administration of a Private Surgery in Liverpool Patient (P Code, P Surname, P Firstname, P Sex, PDoBirth, P Occupation, P Height, P Weight, P Address, P MobileNo Doctor (D Code, D SurName, D FirstName, D PrivateAddress, DMobileNo, Function) Operation (OCode, P Code, D Code, Date, Time, Result, O Type) Is_Examined By (P_Code, D Code, Date, Time Location). Write the following queries in "RELATIONAL ALGEBRA", for the above relations 1. Find the Operation Types that have been done on the 22d of December of 2018 and have been successful. 2. Find the name, gender and age of all patients that have been seen by "Mr. Alex" and have been operated on by doctor "Dr. Henson", assuming that there are no doctors with the same surname 3. Find the first Name, Last Name, Address and Gender of the patients that have been operated on by doctor "D7679" and results have been successful. 4. Find the Full details of the patients that have had at least an operation with broken arm, with full details of the doctor which has been assigned to look after this patient. 5. Find the details of all patients that have shared any doctors with the patient that has the code "P898". This should include the name of the doctor and his contact detail Part four (Physical Database Design) (a) Write SQL statements to create and populate (with suitable suggested test data) the database tables for the relations considered in part two (b) Write SQL statements to implement each of the following queries 1. Find how many patients have been examined on the 4" of November 2017 by doctor Number "D989" 2. Find the full name, gender, address and age of patients that had an operation on the 20th of November 2018. List the details of all doctors that were involved with this operation. 3. Find the surname of the patients (with their code) that have been seen by at least one of the doctors that have examined patient "P999" 4. List the details of all patients and doctors (who are assigned to examine them or operate on them) for the following dates, 21/11/2016, 23/12/2018 and 24/12/2018 5. Find the full details of the patients that have been operated on by doctor "D909". 6. Find the contact details of all patients and their doctors that have had at least one operation with broken arm As a database developer, you have been asked to design a new database the operation of the new project within the Liverpool John Moores University (LJMU). LJMU is planning to deliver a number of professional training courses in Al. Each course is delivered by one member of staff at LJMU's Training Centre; each staff is allocated a staff number. The policy of LJMU is to record the start working date of each staff, the first and last name, the date of birth, home address, telephone number for (home and mobile), email, type of contracts (permanent, sessional), sex, National Insurance Number and the specified salary. There are different training courses offered by LJMU. The fees vary for each course and on the number of delegates a company sends. For example if a company sends one delegate, the charge may be 2500. If the company sends two people, the first may be charged 2500, but the second may be charged 2000 The Al professional courses can be attended by a number of delegates, subject to a limit for each course (Maximum 10 for each of the planned professional courses). A start and end date is assigned for each course with the start and end time. An invoice is sent to the delegate's company. The invoice clarifies to the client orland the company the date that the fee is paid, method of payment (one full payment, or a deposit then completing the payment). (a) Identify the main entities (list all attributes of these entities) for the above proposed database. Determine primary key, Candidate keys and foreign keys attributes for each entity (b) Create a single Entity-Relationship (ER) diagram to represent the data requirements of the database Conceptual Database Design). To simplify the diagram, only show entities, relationships, primary and foreign key attributes. State any assumptions (if necessary) to support your design. (c) Identify the main relationships between entities described in part (a); represent each relationship in your ER diagram, clearly labelling each relationship. State the degree of each of the relationships described in part (b). Decompose any many to many relationships replacing them with new entity types and relationships. The LIVER DATABASE Company in Liverpool wishes to allocate staff to a new development project that has been proposed by external companies. As a result, a new relation was created for this proposal as follows SaffPro (StaffNo, ProjNo, ProjName ProjLocation, StaffAddress, StaffEmail, StaffMobileNo) Assuming that, StaffHours, StaffSurName, StaffNo stands for staff number of the employee; ProjNo identifies one of the projects that the staff works on; StaffHours is the number of hours per week that the staff works on the project; ProjName and Project location are the name and location of the project; StaffSurName is the staff surname; StaffAddress is the postcode of the address of the staff StaffEmail is the staff university email address StaffMobileNo is the staff Mobile telephone number (a) Using appropriate diagrams to aid in your answer briefly describe the following concepts: Normalisation including First, Second and Third Normal forms Primary, Forign and Candidate keys Functional dependences. (b) Based on the given primary key (StaffNo, Projld). Show, comprehensively, how you would normalise the StaffProject relation to first, second and third normal forms, indicating the functional dependencies you use in each stage of normalisation with justifications. State any assumptions you make about the data and time (if any) The logical phase of the database design process has provided the following relations for a database that was proposed to support the administration of a Private Surgery in Liverpool Patient (P Code, P Surname, P Firstname, P Sex, PDoBirth, P Occupation, P Height, P Weight, P Address, P MobileNo Doctor (D Code, D SurName, D FirstName, D PrivateAddress, DMobileNo, Function) Operation (OCode, P Code, D Code, Date, Time, Result, O Type) Is_Examined By (P_Code, D Code, Date, Time Location). Write the following queries in "RELATIONAL ALGEBRA", for the above relations 1. Find the Operation Types that have been done on the 22d of December of 2018 and have been successful. 2. Find the name, gender and age of all patients that have been seen by "Mr. Alex" and have been operated on by doctor "Dr. Henson", assuming that there are no doctors with the same surname 3. Find the first Name, Last Name, Address and Gender of the patients that have been operated on by doctor "D7679" and results have been successful. 4. Find the Full details of the patients that have had at least an operation with broken arm, with full details of the doctor which has been assigned to look after this patient. 5. Find the details of all patients that have shared any doctors with the patient that has the code "P898". This should include the name of the doctor and his contact detail Part four (Physical Database Design) (a) Write SQL statements to create and populate (with suitable suggested test data) the database tables for the relations considered in part two (b) Write SQL statements to implement each of the following queries 1. Find how many patients have been examined on the 4" of November 2017 by doctor Number "D989" 2. Find the full name, gender, address and age of patients that had an operation on the 20th of November 2018. List the details of all doctors that were involved with this operation. 3. Find the surname of the patients (with their code) that have been seen by at least one of the doctors that have examined patient "P999" 4. List the details of all patients and doctors (who are assigned to examine them or operate on them) for the following dates, 21/11/2016, 23/12/2018 and 24/12/2018 5. Find the full details of the patients that have been operated on by doctor "D909". 6. Find the contact details of all patients and their doctors that have had at least one operation with broken arm
