$24
Assignment 01 Solution
Introduction
The goalof this assignm ent is to reinforce the concepts of database design using Entity-R elationship (ER ) m odeland m apping an ER m odelinto a relationalm odel. This assignm ent has tw o parts.
Part I- M odeling
You are building a database for Service Alberta to m aintain data for various services offered. G iven the database specification below, your job is to turn the specification into an ER diagram . You are using dia (see docum entation for dia) to draw your ER diagram . Your notation m ust be consistent w ith the notation used in our lecture notes. You can use allconstructs 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 w illbe w orking in groups of 2-3; group m em bers m ust be allregistered in the course but they m ay not be allin the sam e lecture or lab section. Your ER diagram should capture allthe inform ation and constraints in the specification, but at the sam e tim e be m inim al, m eaning redundant entities, relationships, attributes and constraints should be avoided.
D atabase Specification
The database keeps detailed inform ation about vehicles, drivers, vitalrecords, etc.
Each person (know n to Service Alberta) has a first nam e, a last nam e, a birth date, a birth place, an address and a phone num ber. You can assum e a subset of the attributes (e.g., first nam e, last nam e and birth date) is unique. For each driver, eyes color and hair color are also recorded. Each traffi c offi cer also has a city w here the offi cer operates.
R ecords of births and m arriages in the province are m aintained. For each birth in Alberta, in addition to the personaldetails of the person born (as listed above), there is a unique registration num ber, a registration date, a registration place, a gender at birth, a m other and a father. A birth m ay be registered w ithout a father but alw ays has a m other. Each m arriage in the province is also recorded, and it has a unique registration num ber, a registration date, a registration place, and the details of the partner persons, referred to as Partner 1 and Partner 2.
D rivers can obtain drivers'licenses. Each driver's license has a unique license num ber, a date issued, an expiry date and a license class. Each driver's license m ust 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 m ake, a m odel, a year, and a color. Vehicles are registered before they can be operated. Each vehicle registration in Alberta has a unique registration num ber, a registration date, an expiry date, an Alberta plate and a driver registered as the ow ner. A vehicle can have m ultiple registrations and a driver can also have m ultiple registrations.
R egistered vehicles can be given tickets. Each ticket given to a registered vehicle has a unique ticket num ber, an offence date, the offence cited, a fine dollar am ount, and som etim es a traffi c offi cer issuing the ticket. D rivers can have dem erit point notices in their records. Each notice has a date, a description and the num ber of dem erit points.
Part II- M apping
M ap the follow ing ER diagram into relationaltables using the rules discussed in class. G ive the com plete C R EATE TABLE com m ands for each necessary table including attribute nam es, their dom ains/types and allpossible constraints. U se your com m on sense to choose a dom ain for each attribute.
https://eclass.srv.ualberta.ca/m od/page/view .php?id=3659726
1/3
14/09/2019 C M PU T 291 (Fall2019 LEC A1 A2 EA1 EA2): Assignm ent 1 Spec
(The dia file is also here)
D eliverables
Subm it a single tar file for your group nam ed a1.tgz. The subm itted tar file is expected to have the follow ing pieces:
1. A file nam ed C C ID -P 1.pdf for every group m em ber (replace C C ID w ith the m em ber C C ID ). This file is the PD F of the ER diagram prepared by the
m em ber for Part I. If the group includes, for exam ple, three m em bers, there should be three such files.
A file nam ed group-P 1.pdf. This file is the PD F of the ER diagram prepared by the group for Part I, possibly m ore com prehensive or accurate than the individually prepared diagram s. O nly one group solution is subm itted for this part.
A file nam ed group-P 2.txt. This file is a text file that has the group solution for Part II. The solution includes the relations (C R EATE TABLE statem ents) obtained w hen m apping the given ER -m odelin Part II to the R elationalm odel. O nly one group solution is subm itted for this part.
A file nam ed readm e.txt. This is a text file that lists the nam es and ccids of allgroup m em bers. This file m ust also include the nam es of anyone you
collaborated w ith (as m uch as it is allow ed w ithin the course policy) or a line saying that you did not collaborate w ith anyone else. A subm ission w ithout this file or w ith m issing inform ation can lose 5% or m ore of the totalm ark. This is also the place to acknow ledge the use of any source of inform ation besides the course textbook and/or class notes.
A file nam ed com m ents.txt. This is a text file that lists com m ents m ade by each m em ber on the ER diagram of another group m em ber. C learly indicate the ccid of the m em ber com m enting and the ccid of the m em ber w hose ER diagram is being com m ented.
Additionalfiles (e.g., you m ay have the ER for Part 2 and show in draw ing how the elem ents are grouped to form a relation) m ay be subm itted. Those files should be given proper or m eaningfulnam es and you should understand that w e cannnot those files w illbe checked.
The tar file can be created under Linux (lab m achines) and M acO S using the com m and
tar -czf a1.tgz <all-the-files-to-be-included
w here <all-the-files-to-be-included is replaced w ith the nam es of allfiles you are including in your subm ission.
Your ER diagram s should be produced w ith the 'dia'toolavailable on the lab m achines (here is a link to W indow s and M ac versions of dia- use it at your ow n risk) and exported in PD F. You m ust use the sam e notation used in the course lectures. If you are m aking any assum ptions in your m odeling or
apping, state them clearly in your readm e.txt; note that your assum ptions cannot violate the specification given here and any possible clarification posted later on top of this page or the course forum s.
Subm ission
Subm it the tar file of your solution here. O ne group subm ission (m ade by any m em ber) is suffi cient, but allgroup m em bers can subm it and this m ay have the benefit that if one subm ission fails or is corrupt, another subm ission from the sam e group m ay be evaluated. If there are m ultiple subm issions, w e reserve the right to select one arbitrarily for m arking. Also for group solutions in Parts I & II,only one group solution m ust be included in your subm ission. In cases w here m ultiple solutions are included or a group solution is not subm itted, w e reserve the right to pick one solution from your subm ission and only m ark that solution.
O ther D etails
R egister your group here (if not done already).There should be only one registration per group.
Your m ark for Part I is based on your group solution, your individualsolution and your com m ents on the solution of another group m em ber. At least 70% of the m ark w illbe given to the group solution though.
https://eclass.srv.ualberta.ca/m od/page/view .php?id=3659726
2/3
14/09/2019
C M PU T 291 (Fall2019 LEC A1 A2 EA1 EA2): Assignm ent 1 Spec
Last m odified: Friday, 13 Septem ber 2019, 10:48 AM
◄ SQ L Q uery R epository
Jum p to...
Assignm ent 1 groups ►
You are logged in as A run W oosaree (Log out)
C M PU T 291 (Fall2019 LEC A 1 A 2 EA 1 EA 2)
https://eclass.srv.ualberta.ca/m od/page/view .php?id=3659726 3/3
