1 Cardiff School of Computer Science and Informatics Coursework Assessment Pro-forma Module Code: CMT207 Module Title: Information Modelling and Database Systems Lecturer: Irena Spasić Assessment Title: Relational data modelling Assessment Number: 1 Date Set: 24 February 2020 Submission Date and Time: 01 May 2020 at 9:30AM Return Date: 22 May 2020 This assignment is worth 50% of the total marks available for this module. If coursework is submitted late (and where there are no extenuating circumstances): 1. If the assessment is submitted no later than 24 hours after the deadline, the mark for the assessment will be capped at the minimum pass mark. 2. If the assessment is submitted more than 24 hours after the deadline, a mark of 0 will be given for the assessment. Your submission must include the official Coursework Submission Cover sheet, which can be found here: https://docs.cs.cf.ac.uk/downloads/coursework/Coversheet.pdf Submission Instructions A submission page will be created on Learning Central in Week 11. You will need to submit two documents: Description Type Name Cover sheet Compulsory One PDF (.pdf) file [student_number].pdf Coursework Compulsory One WORD file (.docx) NOTE: Use the template provided on Learning Central as described in the coursework description below. [student_number].docx Any deviation from the submission instructions above (including the number and types of files submitted) will result in a mark of zero for the assessment. 2 Assignment The coursework is based on the following brief. Please read carefully. A relational database is used to store information about a hospital under the following assumptions: Patients occupy rooms. Each patient is usually placed in a hospital room upon arrival at hospital. Each room can accommodate several patients. Hospital consultants (senior surgeons) are allowed to have their own private patients. Private patients are placed in single private rooms. Information recorded for each patient includes a personal identification number (PIN), last name, first name, home address, etc. Nurses are allocated to the rooms. Each nurse may or may not be allocated to a room in the hospital. However, each nurse may be allocated to at most one room. Also, several nurses may be allocated to the same room. All nurses are uniquely identified by their PIN. Information recorded for nurses includes their full name. Surgical operations performed on patients. Each patient can undergo a surgical operation while in hospital. Some patients may have more than one operation. Information recorded for each surgical operation includes its type, date and time. Surgeons perform operations. Each operation is performed by only one surgeon. Other surgeons may be present, but they are considered to be assisting with the operation. Surgeons are supervised by senior surgeons (i.e. consultants). Each consultant has a specialty. All surgeons can perform or assists surgical operations. Information recorded for each surgeon is their PIN, last name, first name, home address, telephone number, etc. Operations take place in operating theatre. Each operation takes place in one operating theatre only. However, the same theatre can be the scene of many operations. Each theatre has its own identification code. Some theatres are specially equipped with instruments for certain types of operations. Nurses are allocated to the operating theatres. Each nurse may or may not be allocated to a theatre. However, each nurse may be allocated to at most one theatre. Also, several nurses may be allocated to the same theatre. Please answer the questions in the Word submission template, which is provided as a separate document on Learning Central and distributed together with this pro-forma. The questions are divided into six parts. Part 1: Relational data model – entities and attributes [0–24 marks] Identify all entities (a total of 8) and their attributes. Write your answers in the following table, one entity per row. Underline attributes that belong to the primary key. You can introduce essential attributes that were not explicitly mentioned. Part 2: Relational data model – relationships [0–40 marks] Identify all relationships (a total of 10) between the entities from Part 1. Write your answers in the following table, one relationship per row. Please specify the relationship’s name, the two related entities and the cardinality (1:M, M:1 or M:M) of the relationship. 3 Part 3: Relational data model – many-to-many relationships [0–14 marks] There should be exactly one M:M relationship in Part 2. a. Represent this relationship using an ER diagram. b. Split this relationship into two 1:M relationships and represent them using an ER diagram. You must use Chen notation as it was described in the lectures (see example below). No other notation will be accepted. Part 4: ER modelling [0–20 marks] Draw an entity-relationship (ER) diagram for the problem described in the brief above. Provide entities and relationships (including their cardinality) only. Do not include attributes. The diagram has to be neat and readable to get full marks. You must use Chen notation as it was described in the lectures (see example above). No other notation will be accepted. Part 5: Data integrity [0–24 marks] Assume that the relations listed in the following table are used to model information described in the brief. For each relation: a. Underline attributes that belong to its primary key. b. Declare its foreign keys using the SQL syntax. c. Add any other constraints that should support data integrity in accordance with the information given in the brief. Relation ROOM(number, size), where size = (integer) number of beds PATIENT(PIN, first_name, last_name) SURGEON(PIN, first_name, last_name) CONSULTANT(PIN, specialty) PRIVATE_PATIENT(patient, consultant) OCCUPIES(patient, room) NURSE(PIN, first_name, last_name) ALLOCATION(nurse, room) Part 6: Data manipulation [0–28 marks] Using the relations given in Part 5, create SQL queries to: a. Calculate the total number of private patients for each consultant. Make sure to provide the full name for each consultant. b. Identify all room(s) that have the highest number of nurses allocated per bed. This can be only one room or more than one if there is a tie. Make sure not to return any rooms with a lower number of nurses per bed. c. Update the database to allocate the first available room to a newly admitted patient whose other information is already stored in the database using their PIN ‘P314’. Note that the room should be either single or multiple occupancy depending whether the patient is private or not. 4 Please comply with the following restrictions: 1. Use only one SQL query per each question above, i.e. do not store any results in temporary tables. This SQL query may, however, contain nested queries and combine queries using set operations. 2. Do not use explicit JOIN operations including INNER JOIN, LEFT JOIN and RIGHT JOIN. Do not use outer join, i.e. (+). You are, however, allowed to join tables using the WHERE clause. 3. Do not use CASE statement. Do not use IF function. 4. Do not use tables that are unnecessary for the required output. Learning Outcomes Assessed Understand how to create a conceptual model and map it to efficient representation in a database schema. Understand relational database management systems. Criteria for assessment The maximum mark for each task is given in brackets with a total maximum of 150 marks. Marks will be assigned on the basis of how well you achieved the task objective. In general, your work will be assessed according to the following factors: Part 1: Correctness and completeness of answers. Compliance with good practice in conceptual design with respect to data integrity and functional dependencies. Part 2: Correctness and completeness of answers. Compliance with good prac
tice in conceptual design with respect to data integrity and functional dependencies. Part 3: Correctness and completeness of answers. Part 4: Correctness and completeness of the model. Following good practice in conceptual design described in the lecture slides presented in Week 3. Part 5: Correctness and completeness of answers. Part 6: Correctness, simplicity and efficiency of queries. The overall mark will be divided by three and rounded up to the next integer to obtain the final mark that will correspond directly to 50% of the total marks available for this module. Feedback suggestion for future learning Feedback on your coursework will address the above criteria. It will be entered using track changes and comments in the Word submission document. The marked document will be returned to you via Learning Central within three weeks of submission. This will be supplemented with group feedback, which will also be made available on Learning Central. Further individual feedback in person can be arranged upon request.
