DMV database design

Computer Science

Southern Illinois University Edwardsville

Question Description

Please read the DMV part 1 and part 2 attachments, then the DMV requirements to design E/R diagram.

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 ...
Student has agreed that all tutoring, explanations, and answers provided by the tutor will be used to help in the learning process and in accordance with Studypool's honor code & terms of service.

This question has not been answered.

Create a free account to get help with this and any other question!

Similar Questions
Related Tags