Homework 3 (HW3: Normalization)

ISQA 3310 – Managing the Database Environment

Assignment 3: Normalization Due: Monday, April 7th @ 1:30 pm Points Available: 30 points

Objective This assignment will give you practice in the basics of data normalization, the identification of functional dependencies

and the design of a database based on existing data formats.

Task 1 (15 points): Table 1 shows sample data for student grade reports.

Table 1: Grade Report

st_ID st_name campus_address st_major course_ID course_title inst_name inst_location grade

1683004 Williams 208 Brooks IS IS 350 Database Mgt Parsons B104 A

1683004 Williams 208 Brooks IS IS 465 Systems Analysis Codd B317 B

5432910 Baker 104 Phillips ACCTG IS 350 Database Mgt Parsons B104 C

5432910 Baker 104 Phillips ACCTG ACCT 201 Fund Acctg Miller H310 B

5432910 Baker 104 Phillips ACCTG MKGT 300 Intro. Mktg Bennett B212 A

Assume the following:

 An instructor has a unique location

 A student has a unique major

 A course has a unique title

 A student receives one grade for a course

1) Identify the functional dependencies in the table and convert to a relation in 1NF (i.e. identify the primary key for

the table). Grading note: You may write out your functional dependencies or draw a functional dependency diagram.

2) Using the functional dependencies from Task 1, question 1, as your starting point, decompose the relation into a

set of relations (relational tables) that meet 3NF requirements. Grading note: I want to see the data in the new relational tables.

1) Based on the results from Task 1, question 2, use ERwin to draw an ER model of the relations (use IE (crow’s foot) notation). Grading note: Your ER model should be an internal schema that has primary keys and foreign

keys defined for all entities. Show that referential integrity constraints have been enforced (i.e. in the Entity tab of Model properties, be sure the following properties are checked: Display Logical Primary Key (PK)

Designator; Display Logical Foreign Key (FK) Designator; and Display Migrated Attributes. Don’t

forget to include verb phrases for your relationships.

ISQA 3310 – Managing the Database Environment

Task 2 (10 points): The Public Safety Officer at Millenium College maintains a list of parking tickets issued to vehicles

parked illegally on campus. Figure 1 shows an initial ER model for database. Table 2 provides you with some sample data (attribute names have been abbreviated to conserve space). Your task is to evaluate the initial ER model, to see if you

can improve the design.

Figure 1: Parking Fine Entity

Table 2: Parking Tickets at Millenium College

STID LName FName PhoneNo State Lic

LicNo Ticket_no Date Code Violation Fine

38249 Brown Thomas 111-7804 FL BRY 123 15634 10/17/13 2 No parking permit $25 16017 11/13/13 1 Parking meter expired $15 82453 Green Sally 391-1689 AL TRE 141 14987 10/05/13 3 Disabled parking violation $100 16293 11/18/13 1 Parking meter expired $15 17892 12/13/13 2 No parking permit $25

Assume the following:

 ticket_no specifies all other attributes in the entity (i.e. it is the PK)

 A student has only one first name, last name, and phone number

 A student could receive many tickets but each ticket is issued to only one student

 A student has only one stateLicense (the state in which the vehicle is currently registered) and

licenseNo (the vehicle license number).

 The ticket code specifies the violation and the fine that is issued. For example, an expired parking

meter (code 1) carries a fine of $15; no parking permit (code 2) carries a fine of $25, and disabled parking violation (code 3) carries a fine of $100.

2) Identify all functional dependencies, including any transitive dependencies, for the entity. Grading note: You

may write out your functional dependencies or draw a functional dependency diagram

3) Using the functional dependencies from Task 2, question 1 as your starting point, decompose the ParkingFine

entity into a set of entities that meet 3NF requirements. Use ERwin to draw the E-R model using IE (crow’s foot) notation. Grading note: Your ER model should be an internal schema that has primary keys and foreign keys

defined for all entities. Show that referential integrity constraints have been enforced.

ISQA 3310 – Managing the Database Environment

Task 3 Reflection (5 points):

1) In this portion of the assignment, you will reflect back on your experiences completing tasks 1 and 2. Write at

least a paragraph, answering such questions as: What challenges or problems did you encounter? How did you

resolve them? What general principles did you learn that you can apply in other contexts?

Homework Submission: Post your solution file to our Blackboard Page by 12 noon on the due date under

the Assignments tab link for Assignment 3. If you have multiple files, please zip the files prior to

submitting them to Blackboard.