$30
HW 1 - Normalization CSCI 3287: Design and Analysis of Data Systems 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.