| The goal of this assignment is to reinforce the concepts of database design using Entity-Relationship (ER) model and mapping an ER model into a relational model. This assignment has two parts.Part I ModelingYou are building a database for Service Alberta to maintain data for various services offered. Given the database specification below, your job is to turn the specification into an ER diagram. You are using dia (see documentation for dia) to draw your ER diagram. Your notation must be consistent with the notation used in our lecture notes. You can use all constructs and notations discussed in our lecture notes and nothing else (i.e., even notations used in the textbook but not in our lectures cannot be used).You will be working in groups of 2-3; group members must be all registered in the course but they may not be all in the same lecture or lab section. Your ER diagram should capture all the information and constraints in the specification, but at the same time be minimal, meaning redundant entities, relationships, attributes and constraints should be avoided.Database SpecificationThe database keeps detailed information about vehicles, drivers, vital records, etc.Each person (known to Service Alberta) has a first name, a last name, a birth date, a birth place, an address and a phone number. You can assume a subset of the attributes (e.g., first name, last name and birth date) is unique. For each driver, eyes color and hair color are also recorded. Each traffic officer also has a city where the officer operates.Records of births and marriages in the province are maintained. For each birth in Alberta, in addition to the personal details of the person born (as listed above), there is a unique registration number, a registration date, a registration place, a gender at birth, a mother and a father. A birth may be registered without a father but always has a mother. Each marriage in the province is also recorded, and it has a unique registration number, a registration date, a registration place, and the details of the partner persons, referred to as Partner 1 and Partner 2.Drivers can obtain drivers licenses. Each drivers license has a unique license number, a date issued, an expiry date and a license class. Each drivers license must be issued to a driver. There are a set of license classes, and each class has a unique id and a description.Each vehicle has a unique VIN, a make, a model, a year, and a color. Vehicles are registered before they can be operated. Each vehicle registration in Alberta has a unique registration number, a registration date, an expiry date, an Alberta plate and a driver registered as the owner. A vehicle can have multiple registrations and a driver can also have multiple registrations.Registered vehicles can be given tickets. Each ticket given to a registered vehicle has a unique ticket number, an offence date, the offence cited, a fine dollar amount, and sometimes a traffic officer issuing the ticket. Drivers can have demerit point notices in their records. Each notice has a date, a description and the number of demerit points.Part II MappingMap the following ER diagram into relational tables using the rules discussed in class. Give the complete CREATE TABLE commands for each necessary table including attribute names, their domains/types and all possible constraints. Use your common sense to choose a domain for each attribute. |
Those files should be given proper or meaningful names and you should understand that we cannnot those files will be checked.The tar file can be created under Linux (lab machines) and MacOS using the command
tar -czf a1.tgz <all-the-files-to-be-included>
where <all-the-files-to-be-included> is replaced with the names of all files you are including in your submission.
Your ER diagrams should be produced with the dia tool available on the lab machines (here is a link to Windows and Mac versions of dia use it at your own risk) and exported in PDF. You must use the same notation used in the course lectures. If you are making any assumptions in your modeling or mapping, state them clearly in your readme.txt; note that your assumptions cannot violate the specification given here and any possible clarification posted later on top of this page or the course forums.
Reviews
There are no reviews yet.