COMP23111 – Fundamentals of Databases Exercise 01 Mapping Data Requirements to an EER Conceptual Model and then to a Relational Schema Goal: The goal for this activity is for you to practice the technique of deriving a conceptual model in the form of an (enhanced) entity-relationship (EER) diagram from a data requirements specification (DRS), such as you have practised deriving from a data flow diagram (DFD) in Example Class 1. This technique is often applied in real-world software development by a data analyst and is typically the first concrete step in the database design process. The outcome of this step is a conceptual model from which a logical model of the data can then be derived. Material: You have been asked to design a database for NASA, below is your DRS. This is all of the information that NASA has provided you with: R1: There are three distinct types of Personnel: Managers, HR and Astronauts. R2: HR personnel must have their job title stored. R3: A manager may manage one department but may not manage any but each department must be managed by one manager. R4: Each Astronaut should have their number of missions stored. R5: Each manager has their military rank stored. R6: For each department we would like to store the departments unique name and its function. R7: There exists three types of Astronaut: Pilots, Commanders and Engineers. Each Astro- naut can be one or more of these types. R8: We would like to know a pilots flying hours, the number of missions a commander has lead and an engineers speciality. R9: An Astronaut may go on multiple missions or may not attend any but every mission must have more than one astronaut on it. We also need to know the number of missions each Astronaut has been on R10: We need to capture the unique name, destination, launch date, mission type and crew size for each mission. R11: Each mission has one target but a target can have multiple or no missions associated with it. R12: Each target has a unique id, name, date of first mission, the type of target and number of missions to it stored. R13: Space, Air and Land vehicles can all be categorised as Vehicles. Each Vehicle must have their unique id and name stored. R14: We are also interested in: a. The wheel type of land vehicles. b. The engineer type of air vehicles. c. The service dates of a space craft broken down into first launch and last launch, the number of missions it has been on and whether it is active based on its last launch date. R15: Each mission must have many vehicles on it and a vehicle may be used on multiple missions or none. R16: We would like to know each Personnel’s unique id number, their name (which consist of a first and last name), email address (of which a personal can have many), dob and their age (derived from their dob). R17: Each Facility has their unique name, its state and main operation stored. R18: Personnel must work in only one Facility but each facility may house multiple Personnel or none. R19: Each facility may have many buildings or none, but each building must belong to one facility as the details of a building alone cannot identify it. R20: A building has a name (identifier), type and opening date. R21: Every facility must store their data on one central server but this server can be accessed by multiple facilities or none, this server also have a unique name. R22: The Server may be used to store mission reports or bank statements. R23: The report id (unique), mission date and details of each mission report must be captured. R24: A unique statement id, statement date, amount and summary must be stored for each bank statement. R25: The unique part number for Parts and unique supplier name for all Suppliers should be recorded. R26: Each vehicle may use many parts from multiple suppliers. Each time something is supplied the quantity is stored. R27: Each personnel reports to one other personnel and some personal can have multiple other personnel report to them. Tasks: 1. Using a modelling software of your choice design the conceptual model in the form of an EER diagram that follows from the DRS above. 2. Now, use the EER diagram you created for Task 1 and convert this into a relational schema using the 9 rules discussed during this course. This can be written in a simplified notation such as the below: Manager (manager_ID, name): pk[manager_ID] Artist (artist_name, genre, managedBy): pk[artist_name], fk[managedBy -> Manager.manager_ID] Submission Procedure: 1. Download and Submit both: – A PDF of your Diagram called; EX01–ER.pdf – A PDF of your Relational Schema called; EX01–RS.pdf To Blackboard. If you fail to submit all files your mark will be reduced by 5%. Deadline: 18:00 on Friday the 8th November (Week 07) Assessment Type: This activity is subject to summative assessments, therefore your submission will be marked and you will receive the associated feedback. The marks you obtain (see below) count for up to 20% of your overall coursework mark for this course unit. Marks: • Marks are awarded for correct, complete entity types (plus attributes) and relationship types (plus attributes), where specialisations/generalisations count as the latter, of course. • Roughly (i.e. not strictly), each correct, complete entity type (plus attributes) is worth one mark, and each correct, complete relationship type (plus attributes) is worth two marks. • If the marker finds that any of your submitted files is unreadable, you lose all the marks, so, test every one of them beforehand.
