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 – Modeling
You are building a database for Service Alberta to maintain data for various services oﬀered. Given the database speciﬁcation below, your job is to turn
the speciﬁcation 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 speciﬁcation, but at the same time be minimal, meaning redundant entities,
relationships, attributes and constraints should be avoided.
The database keeps detailed information about vehicles, drivers, vital records, etc.
Each person (known to Service Alberta) has a ﬁrst 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., ﬁrst name, last name and birth date) is unique. For each driver, eyes color and hair color are also recorded. Each traﬃc
oﬃcer also has a city where the oﬃcer 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 driver’s license has a unique license number, a date issued, an expiry date and a license class. Each driver’s
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 oﬀence date, the oﬀence cited, a ﬁne
dollar amount, and sometimes a traﬃc oﬃcer 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 – Mapping
Map 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.
CMPUT 291 (Fall 2019 LEC A1 A2 EA1 EA2): Assignment 1 Spec
(The dia ﬁle is also here)
Submit a single tar ﬁle for your group named a1.tgz. The submitted tar ﬁle is expected to have the following pieces:
1. A ﬁle named CCID-P1.pdf for every group member (replace CCID with the member CCID). This ﬁle is the PDF of the ER diagram prepared by the
member for Part I. If the group includes, for example, three members, there should be three such ﬁles.
2. A ﬁle named group-P1.pdf. This ﬁle is the PDF of the ER diagram prepared by the group for Part I, possibly more comprehensive or accurate than
the individually prepared diagrams. Only one group solution is submitted for this part.
3. A ﬁle named group-P2.txt. This ﬁle is a text ﬁle that has the group solution for Part II. The solution includes the relations (CREATE TABLE
statements) obtained when mapping the given ER-model in Part II to the Relational model. Only one group solution is submitted for this part.
4. A ﬁle named readme.txt. This is a text ﬁle that lists the names and ccids of all group members. This ﬁle must also include the names of anyone you
collaborated with (as much as it is allowed within the course policy) or a line saying that you did not collaborate with anyone else. A submission
without this ﬁle or with missing information can lose 5% or more of the total mark. This is also the place to acknowledge the use of any source of
information besides the course textbook and/or class notes.
5. A ﬁle named comments.txt. This is a text ﬁle that lists comments made by each member on the ER diagram of another group member. Clearly
indicate the ccid of the member commenting and the ccid of the member whose ER diagram is being commented.
6. Additional ﬁles (e.g., you may have the ER for Part 2 and show in drawing how the elements are grouped to form a relation) may be submitted.
Those ﬁles should be given proper or meaningful names and you should understand that we cannnot those ﬁles will be checked.
The tar ﬁle can be created under Linux (lab machines) and MacOS using the command
tar -czf a1.tgz
where <all-the-ﬁles-to-be-included> is replaced with the names of all ﬁles 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 speciﬁcation given here and any possible clariﬁcation posted
later on top of this page or the course forums.
Submit the tar ﬁle of your solution here. One group submission (made by any member) is suﬃcient, but all group members can submit and this may have
the beneﬁt that if one submission fails or is corrupt, another submission from the same group may be evaluated. If there are multiple submissions, we
reserve the right to select one arbitrarily for marking. Also for group solutions in Parts I & II, only one group solution must be included in your submission.
In cases where multiple solutions are included or a group solution is not submitted, we reserve the right to pick one solution from your submission and
only mark that solution.
Register your group here (if not done already). There should be only one registration per group.
Your mark for Part I is based on your group solution, your individual solution and your comments on the solution of another group member. At least 70% of
the mark will be given to the group solution though.