CIS 401 Optional Assignment 9

Scenario

Campus POLICEMEN issue CITATIONS for various OFFENSES committed by STUDENTS. It is their policy to refer non-student offenders to local City police officers for citations. PENALTIES for offenses can range from warnings to university disciplinary action, to actual fines (which are sent to County Judge for prosecution.)

Design ALL Tables

needed to build a database solution for the scenario described above. Be sure to follow principles of geed design (normalize) as you go. Describe the tables in TEXT format (identify foreign keys in some way that is clearly visible if you don’t like italics): TABLE(PrimaryKey, attribute, attribute)

Assumptions

Make and state assumptions as needed. Be sure your design reflects these assumptions.

Draw the ERD

Go ahead and SHOW foreign keys in your entities on the ERD even though they are not technically supposed to be there. LABEL relationship lines with words, be sure to mark cardinality (min and max on both ends of each line).

Describe the Table Structure

of each table

Show sample data

for three (or more) rows of each table.