Unformatted Attachment Preview
CMIS 564: Database Design
DMV Project Part 1
1a)
Read through the entire requirements statement. As you read, make a list of
ENTITIES reflecting the requirements.
1b)
Review the requirements statement a second time. Make a list of
RELATIONSHIPS and specify which entities they relate.
2)
Draw an E/R Diagram following the modeling conventions discussed in class.
The following are required:
• Entities (named with nouns): from 1a above. Each one of these becomes a
box.
• Relationships (named with verbs): from 1b above. Each one of these
becomes a line that connects two entities
• Primary keys: the unique identifier for each of the entities
• Attributes: fields that are not the primary key, but which store data about
each of the entities. The number per entity will vary depending on what
you are given in the requirements document.
• Cardinality: maximum and minimum for both ends of each relationship line
• Modality: optional or mandatory for both ends of each relationship line
Special instructions to note:
• If, after you have included all of the entities and attributes, the diagram will
not fit on one page in print preview, be sure that only relationship lines cut
across a page. Do not have entity boxes dissected by page breaks.
• Avoid ternary or higher-degree relationships.
• You can complete this using modeling software such as Microsoft Visio,
Lucidchart, or draw.io. Assignments completed in Word will not be
accepted and will receive a grade of zero.
• Your ERD must be exported/saved to pdf and submitted in that file format.
CMIS 564: Database Design
DMV Project Part 2
For this assignment you will transform the E/R model you created in Part 1into relational
model. To successfully complete this assignment, you will complete two methods of
expressing your relational schema: a text description and a graphical representation.
First, review pages 122-126 in the textbook. Then create a text description of your
relational schema. This should included:
• Entities, formatted in all capital letters, listed on separate lines
• Attributes, formatted in title case, in parenthesis following the appropriate entity
name
• Primary keys for each entity, indicated by solid underline
• Foreign keys as needed, indicated by dashed underline
Next, review the steps of transformation in pages 131-144 of the textbook. Then create a
graphical representation of your relational schema. This should include:
• Entity names in all caps
• Attributes in a single row table beneath the appropriate entity name
• Primary keys for each entity, indicated by solid underline
• Foreign keys as needed, indicated by dashed underline
• Referential integrity constraint: arrow indicating direction of relationship.
Special instructions:
• This can be completed in Word.
• Save it as a pdf file and submit the file to Blackboard
CMIS564: DATABASE DESIGN
CASE STUDY: Department of Motor Vehicle (DMV)
The Department of Motor Vehicles (DMV) for the State of Illinois is developing a new database
to support three related functional areas:
•
•
•
Vehicle Registration
Tracking of Accidents and Violations
Driver Licensing
The following requirements have been specified based on interviews with key staff and a review
of DMV documents.
Vehicle Registration
The vehicle registration unit of the DMV tracks data on automobiles, busses, motorcycles,
trucks, aircraft, and boats. Every vehicle has a DMV vehicle identification number, year, make,
model, and manufacturer’s ID (the number stamped on the engine block). Most vehicles have
license plates, with the exception of aircraft and boats. For taxis, commercial airplanes,
municipal busses, and ferryboats, the DMV also issues and records an eight-character
‘medallion’ number, the date of most recent inspection, and vehicle capacity (maximum number
of passengers).
In Illinois, vehicles are registered annually; the DMV must record the most recent registration
date, full name and mailing address of owner(s), and sales price of the vehicle. Annual
registration fees are calculated from the sales price.
Specific kinds of vehicles have special data requirements. The DMV must record the
displacement, engine class (diesel, gas, sail) and keel length of boats. Manufacturer’s
horsepower rating and date of last emissions check are recorded for all busses, trucks, autos, and
motorcycles. The number of axles of busses and trucks must be maintained for calculation of the
annual commercial highway tax. Finally, the DMV must store the name of the home airport and
Federal Aviation Administration code of each airplane.
B. Bordoloi
02/20/12
Page 1 of 2
Tracking of Accidents and Violations
The Records Bureau of the DMV maintains historic lists of all accidents and most violations
reported in Illinois.
An accident report is written by an officer at the scene, and includes names and license numbers
of all involved drivers, and the license plate numbers of their vehicles. Officers also describe
any damage to each vehicle or injury to an involved person. If a vehicle or driver is out-of-state,
the state of registry is documented. Names of passengers of vehicles are recorded, as are names
of any pedestrians involved in the incident. If the officer suspects drunk driving, he may
administer a sobriety test and note the level of alcohol in the driver’s bloodstream. The accident
report also notes date, time, location, weather conditions (visibility, precipitation, road
conditions), and a narrative description of the circumstances of the accident.
In addition, the DMV records all violations of State of Illinois Traffic Code, including speeding
tickets, parking tickets, drunk driving, and the like.
DMV clerks enter information on violations from tickets, which list the officer, date, time,
location, license plate number, and state of registration. If a driver is present (for example, a
speeding ticket), the policeman notes driver’s license number, name, and occasionally the blood
alcohol level. Occasionally a violation involves damage to the vehicle, which is described on the
ticket and transferred to the DMV database. If a violation causes a vehicle to be towed, the name
of the towing company is recorded for the benefit of the owner. For some violations, ‘points’ are
recorded in the database and charged against the driver’s record. All violations are cited against
sections of the Illinois State Traffic Code.
The DMV maintains a list of all Illinois police officers by badge number, police department,
department branch, and rank. Badge number, department, and branch are use together to identify
officers.
The DMV does not record accidents or violations for boats or aircraft.
Driver Licensing
The DMV administers vehicle operator exams, and maintains license records. Each exam
consists of five parts – parallel parking, U-turn, Y-turn, use of turn signals, and speed control. A
rating of 0 to 100 is assigned by the administering officer, except for the speed control portion,
which is rated on a scale of 30.
Vehicle operators occasionally fail the exam several times before passing. Full historic records
of exams, both passed and failed, must be stored. The DMV tracks the name of the DMV center
administering each exam, as well as the badge number and department of the responsible police
officer, for internal statistical analysis.
When an operator passes an exam, his full name, address, date of birth, and hair and eye color
are entered into the system. The operator is issued an operating license, and an expiration date
(usually four years from the date of issue) is recorded. Special authorization is required to
operate commercial vehicles as taxis and busses. The DMV also notes if a person is an organ
donor and if they wear corrective lenses.
B. Bordoloi
02/20/12
Page 2 of 2