This assignment is about using ER model and mapping to relational model for database design. First, design an appropriate ER model for the given question. Next, map the ER model to the relational model. The relational schema is checked using the normalization theory to ensure that all relations are at least in 4NF.
Please present your design using our conventions both ER diagram and the relational model. The relational model, should look like the following (based on the usual example we always use in class):
student(ssn, lName, fName, dob, address, major)
Primary key ={ssn}
course(cNum, cName, description, credits)
Primary key ={cNum}
enrollment(ssn, cNum, grade)
Primary key ={ssn, cNum}
Foreign key 1={ssn} references primary key of student
Foreign key 2={cNum} references primary key of course
Please also note that the order of tables matters. Data types or domains for attributes are optional.
Problem (100 points) The Long Haul Database belongs to a company name Long Haul, which operates a number of passenger vans. Vans are distinguished by registration code, and characterized by the number of passenger seats. The company keep track of the cabin type of its vans. Cabin type differ in their air conditioning capabilities. At present, there are only two types: those with air-conditioning and those without it (yeso). Each van is assigned a quality rank (which is equal to the number of break-down within the last year.) Long Haul operates various lines among the neighboring towns. All lines are express and passenger vans never stop on a line. Each line is assign a unique number, and characterized by its origin and destination towns. Additionally, the management keeps track of the length of distance covered by each line. Information is stored about drivers. For each drivers, the management needs to know ssn, first and last names, and date of birth. If available, driver contact phone numbers are stored. The management keeps a departure plan of lines, which schedules the departure and arrival times of each line on each date. Carefully predicting the passenger interest, the scheduling clerk dispatches one or more vans to each individual scheduled line. For each dispatched van, the dis- patcher records which driver has been assigned to it. Once this assignment is made, the ticket booth is free to book tickets for each dispatched van. Each sold seat is recorded, so that no van should be booked above its capacity. Construct the relational schema for the Long Haul database, based on the above description. Start an ER model then map it to the relational model. Your relational schema should have an adequate normalization up to Fourth Normal Form (4NF). To assure that a relation satisfies a certain normal form, you are required to list functional dependencies and explain the normal form by using supporting functional dependencies. For each relation you construct, clearly indicate the primary key and foreign keys, if any. Problem (100 points) The Long Haul Database belongs to a company name Long Haul, which operates a number of passenger vans. Vans are distinguished by registration code, and characterized by the number of passenger seats. The company keep track of the cabin type of its vans. Cabin type differ in their air conditioning capabilities. At present, there are only two types: those with air-conditioning and those without it (yeso). Each van is assigned a quality rank (which is equal to the number of break-down within the last year.) Long Haul operates various lines among the neighboring towns. All lines are express and passenger vans never stop on a line. Each line is assign a unique number, and characterized by its origin and destination towns. Additionally, the management keeps track of the length of distance covered by each line. Information is stored about drivers. For each drivers, the management needs to know ssn, first and last names, and date of birth. If available, driver contact phone numbers are stored. The management keeps a departure plan of lines, which schedules the departure and arrival times of each line on each date. Carefully predicting the passenger interest, the scheduling clerk dispatches one or more vans to each individual scheduled line. For each dispatched van, the dis- patcher records which driver has been assigned to it. Once this assignment is made, the ticket booth is free to book tickets for each dispatched van. Each sold seat is recorded, so that no van should be booked above its capacity. Construct the relational schema for the Long Haul database, based on the above description. Start an ER model then map it to the relational model. Your relational schema should have an adequate normalization up to Fourth Normal Form (4NF). To assure that a relation satisfies a certain normal form, you are required to list functional dependencies and explain the normal form by using supporting functional dependencies. For each relation you construct, clearly indicate the primary key and foreign keys, if any
