plz answer Task 2 bcos Task 1 has already been answered below

plz answer task 2 bcos task 1 has been answered already below

The problem scenario You are asked to design a database for a company that operates driving schools at several cities in UK. It is to store information about the driving schools, clients, instructors, cars and details of bookings for driving lessons and tests. A driving school has a unique ID and address. It employs several driving instructors and some administration staff with various job roles, e.g. manager, secretary, assistant. All members of staff have a unique ID, forename, surname, gender, contact phone number, address. Instructors have a valid driving license number recorded, other members of staff have their role recorded. Each instructor is allocated a specific car by the school. Each car has a numeric identifier, a registration number and model. A client has a unique ID, forename, surname, gender, date of birth, address, contact phone number, and a provisional driving license number. After clients register with a driving school near where they live, they may book individual lessons with a particular instructor in the school. Each lesson lasts for an hour. A client may change to a different instructor at any stage. A client may apply for a driving test at the ncarest test centre. Each centre has a unique ID, name, address and contact phone number. A client may make multiple attempts to pass the test. For each test, the instructor should be available to drop off and pick up the client before and after the test at the centre. If a client fails a test, the reasons for failure must be recorded Task 1. [25 marks] Design a conceptual data model for the given scenario. a) Construct an ER diagram in UML. The diagram should show all entities, attributes of cach entity, relationships between the entities with appropriate multiplicity constraints, and the coverage properties for the hierarchical relationship you have identified. b) State any assumptions necessary to resolve ambiguities in the scenario. Pay special attention to cardinalities and participation constraints. 2. [25 marks) Produce a logical data model for a relational database, a) Map your conceptual data model (Task 1) to a relational schema, indicating clearly the primary keys and any foreign keys introduced to model the relations and constraints. Represent the schema in textual form. b) For each foreign key, specify whether a null value would be allowed. Justify your choice Page 1 of 2 c) State which attributes may be null and explain why. d) State any other required constraints for your logical data model, The problem scenario You are asked to design a database for a company that operates driving schools at several cities in UK. It is to store information about the driving schools, clients, instructors, cars and details of bookings for driving lessons and tests. A driving school has a unique ID and address. It employs several driving instructors and some administration staff with various job roles, e.g. manager, secretary, assistant. All members of staff have a unique ID, forename, surname, gender, contact phone number, address. Instructors have a valid driving license number recorded, other members of staff have their role recorded. Each instructor is allocated a specific car by the school. Each car has a numeric identifier, a registration number and model. A client has a unique ID, forename, surname, gender, date of birth, address, contact phone number, and a provisional driving license number. After clients register with a driving school near where they live, they may book individual lessons with a particular instructor in the school. Each lesson lasts for an hour. A client may change to a different instructor at any stage. A client may apply for a driving test at the ncarest test centre. Each centre has a unique ID, name, address and contact phone number. A client may make multiple attempts to pass the test. For each test, the instructor should be available to drop off and pick up the client before and after the test at the centre. If a client fails a test, the reasons for failure must be recorded Task 1. [25 marks] Design a conceptual data model for the given scenario. a) Construct an ER diagram in UML. The diagram should show all entities, attributes of cach entity, relationships between the entities with appropriate multiplicity constraints, and the coverage properties for the hierarchical relationship you have identified. b) State any assumptions necessary to resolve ambiguities in the scenario. Pay special attention to cardinalities and participation constraints. 2. [25 marks) Produce a logical data model for a relational database, a) Map your conceptual data model (Task 1) to a relational schema, indicating clearly the primary keys and any foreign keys introduced to model the relations and constraints. Represent the schema in textual form. b) For each foreign key, specify whether a null value would be allowed. Justify your choice Page 1 of 2 c) State which attributes may be null and explain why. d) State any other required constraints for your logical data model, EdDiag som Representation - - Stoong Entity Hron entity butel 100 Mult valued sitt bute. - Relationship Ada Task 1 ER Diogram Driving School orinam employy Ornam Onder forenim 08 Bus Iciren gender Staft Chone Phone Gde Croc citiem N BODY role Leaker reason for failure Driving ING skie M Administratie Staff Driving Test Name Ollorared Phone a dove 18 Cor Reg. No mode Relationships?- * 1 Driving school can employ many staff. * Many clients can register for 1 Driving School * 1 Instructor is allocated with 1 car. * 1 client can book 1 Instructor * 1 client can take multiple driving Tests. Entities:- * Irving-school * staff * Instructa Administration staff Car Client * Driving-test