NFS2201 Database Management Systems Assignment 2-15% 1. Case Study: - 50% Q-Limo provides taxi service to the VIP customers throughout Qatar. Consider the following entities and relational model for the Q-Limo. Qlimo (qlimo_id, name, address, phone, email) Vehicle ( vehicle_id, make, v_model, v_description, garage_id, qlimo_id) Garage (garage_id, name, address, phone, 8_description) QLIMO QLIMO_ID HUMBER (3) NAME VARCHAR2(50 BYTE) ADDRESS VARCHAR2(100 BYTE) PHONE VARCHAR2(16 BYTE) EMAIL VARCHAR 150 BYTE) - QLIMO_ID_PK (OLIMO_10) VEHICLE - VEHICLE_ID HULDER MAKE VARCHAR200 BYTE) V_NODEL VARCHAR CO BYTE) V DESCRIPTION VARCHAR2(100 BYTE) GARAGE_ID HUMBER (5) QUIOM_ID HUMBER) > VEH_ID_PK (VEHICLE_ID) 3 VEH_BAR_ID_FK (GARAGE_ID) VEH_QUIMO_FK (OLIOM_ID) GARAGE P. GARAGE ID NUMBER (5) NAME VARCHAR2(50 BYTE) ADDRESS VARCHAR2(100 BYTE) PHONE VARCHAR2 (15 BYTE) G_DESCRIPTION VARCHAR2(50 BYTE) > GARAGE_ID_PK (GARAGE_ID) Q-Limo also tracks information about vehicle servicing, service garage, and vehicle registration. Q-Limo requires a system that will integrate all requirements of the system. You are hired to develop a fully functional logical model (ERD) for Q-Limo that can be used to develop the physical model of the system. Task: Not all entity requirements are determined at this point, therefore Q-Limo is looking for your expertise in assisting them with the design. It is important to look at the different areas to be considered in the design and based on your knowledge to make reasonable assumptions regarding each area. You will have to make assumptions in determining the entities, attributes/columns, the datatypes, relationships, primary/foreign key constraints, and cardinality. Your Entity Relationship Diagram (ERD) has to follow the rules of Normalization an adhere to 3rd Norm Form in your design. 1 1 2. Q-Limo Physical Model (Tables) - 25% Map your logical model (ERD) from question 1 into a set of tables. Use the Create Table SQL to create all tables, and create a Data Dictionary for each table. The Data Dictionary should include: . . Table Name Column Name (Choose appropriate column to describe each Entity) Primary keys, foreign keys, alternate keys Data types and size Allowable values Description of attribute and entities Derived data Assumptionsotes . Use the following template for your answer: Table Name: XXX Column Primary Foreign Alternate Data Allowable Description Derived Notes/ Name Key Key type value Data assumptions & size Note: All the entities should be mapped to tables and should include bridge entities for N:M relationships. Ensure that the tables are in 3rd Normal Form (no need to show normalization). 2 Page NFS2201 Database Management Systems Assignment 2-15% 1. Case Study: - 50% Q-Limo provides taxi service to the VIP customers throughout Qatar. Consider the following entities and relational model for the Q-Limo. Qlimo (qlimo_id, name, address, phone, email) Vehicle ( vehicle_id, make, v_model, v_description, garage_id, qlimo_id) Garage (garage_id, name, address, phone, 8_description) QLIMO QLIMO_ID HUMBER (3) NAME VARCHAR2(50 BYTE) ADDRESS VARCHAR2(100 BYTE) PHONE VARCHAR2(16 BYTE) EMAIL VARCHAR 150 BYTE) - QLIMO_ID_PK (OLIMO_10) VEHICLE - VEHICLE_ID HULDER MAKE VARCHAR200 BYTE) V_NODEL VARCHAR CO BYTE) V DESCRIPTION VARCHAR2(100 BYTE) GARAGE_ID HUMBER (5) QUIOM_ID HUMBER) > VEH_ID_PK (VEHICLE_ID) 3 VEH_BAR_ID_FK (GARAGE_ID) VEH_QUIMO_FK (OLIOM_ID) GARAGE P. GARAGE ID NUMBER (5) NAME VARCHAR2(50 BYTE) ADDRESS VARCHAR2(100 BYTE) PHONE VARCHAR2 (15 BYTE) G_DESCRIPTION VARCHAR2(50 BYTE) > GARAGE_ID_PK (GARAGE_ID) Q-Limo also tracks information about vehicle servicing, service garage, and vehicle registration. Q-Limo requires a system that will integrate all requirements of the system. You are hired to develop a fully functional logical model (ERD) for Q-Limo that can be used to develop the physical model of the system. Task: Not all entity requirements are determined at this point, therefore Q-Limo is looking for your expertise in assisting them with the design. It is important to look at the different areas to be considered in the design and based on your knowledge to make reasonable assumptions regarding each area. You will have to make assumptions in determining the entities, attributes/columns, the datatypes, relationships, primary/foreign key constraints, and cardinality. Your Entity Relationship Diagram (ERD) has to follow the rules of Normalization an adhere to 3rd Norm Form in your design. 1 1 2. Q-Limo Physical Model (Tables) - 25% Map your logical model (ERD) from question 1 into a set of tables. Use the Create Table SQL to create all tables, and create a Data Dictionary for each table. The Data Dictionary should include: . . Table Name Column Name (Choose appropriate column to describe each Entity) Primary keys, foreign keys, alternate keys Data types and size Allowable values Description of attribute and entities Derived data Assumptionsotes . Use the following template for your answer: Table Name: XXX Column Primary Foreign Alternate Data Allowable Description Derived Notes/ Name Key Key type value Data assumptions & size Note: All the entities should be mapped to tables and should include bridge entities for N:M relationships. Ensure that the tables are in 3rd Normal Form (no need to show normalization). 2 Page