HW 1 - Normalization

HW 1 - Normalization
CSCI 3287: Design and Analysis of Data Systems Page 1
Overview
• Assignment points: 10 points or 10% toward your final grade.

Instruction
• Go through below form with all data elements/attributes and organize all attributes to
create 3NF design to remove data redundancy and dependency.
• Your un-normalized, 1st and 2nd normal forms will NOT be scrutinized for grading
purposes. They are the means to the end (3NF). However, you need to display
unnormalized, 1st NF, 2nd NF and 3rd NF, as shown in below sample output.
• Identify each ENTITY names by highlighting them in yellow color and list each attribute.
• Identify the Primary Keys and Foreign Keys, if any, and show them within a bracket next
to the field name, e.g., OrderID (PK), ProductID (FK), etc.
• If an entity does not change from 1st to 2nd normal form, then simply copy over the
data into the 2nd normal form to show that the data is already in second normal form,
and so on.
• Your final 3NF should contain all data in the document organized by entity, listing all
attributes in each entity with primary and foreign keys, if any.
• Ensure that each attribute is functionally dependent on the primary key for that entity.
If an attribute appears multiple times, list it only once in your final normalization design.
HW 1 - Normalization
CSCI 3287: Design and Analysis of Data Systems Page 2
Normalization Sample output example:
HW 1 - Normalization
CSCI 3287: Design and Analysis of Data Systems Page 3
INPUT
Please use below form and normalize into 3 NF.
School of Computing
CourseNo 123
Course Name Database
InstructorNo JF_123
Instructor Name Jack Fox
StudentNo Student Name DOB Gender Grade ProgramNo Program Name
 1 Alex Smith 9/10/1999 F 90 101 CSBS
 2 Ravi Grace 1/25/1998 M 95 102 CSEN
 3 Tom Ford 2/15/1997 M 80 102 CSEN
 4 Lily Mars 3/12/1999 F 85 103 CSBA
Assumptions:
This form represents the School of Computing for all the courses it offers - one for for each
course (e.g., Database) for Student Grade Report. Sample data given for Database course with
Lecturer and list of students with their program.
• Each course has only one instructor; Instructor may teach more than one courses.
• Students may register for multiple courses and each course may be taken by several
students, i.e., different courses may have different numbers of students.
• Student belongs to only one program (e.g., CSBS).
• Student’s grade may be different for different courses.
Sample data are given for illustration purpose only. You just need to use entities/attributes and
normalize into 3NF as shown in the sample output given above. If there is no candidate primary
key, then create appropriate primary key.