Scribd
Upload
5 views3 pages

StudentTranscriptsSecondNormal Inclass

The document outlines the process of normalizing a student transcript data table into Second Normal Form (2NF). It includes steps such as flattening the table, identifying a primary key, determining functional dependencies, recognizing partial dependencies, and finally decomposing the table into two separate entities: Student and Course. The primary key is defined as a combination of StudentID, Dept, CourseNo, and Semester.

Uploaded by

vkelechi2007
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
5 views3 pages

StudentTranscriptsSecondNormal Inclass

The document outlines the process of normalizing a student transcript data table into Second Normal Form (2NF). It includes steps such as flattening the table, identifying a primary key, determining functional dependencies, recognizing partial dependencies, and finally decomposing the table into two separate entities: Student and Course. The primary key is defined as a combination of StudentID, Dept, CourseNo, and Semester.

Uploaded by

vkelechi2007
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Name:

Consider the student transcript data provided in the table below. Our goal is to place this data into Second Normal Form.

Major
StudentID LastName FirstName MiddleName 1 Major2 Dept CourseNo CourseName Semester Grade
9874923 McDermot Mary Margaret ENG ENG 108 Writing II Fall 2002 A-
t PHL 151 Critical Thinking Fall 2002 B+
MTH 150 Thinking Math Fall 2002 B
ENG 230 American Literature Spring 2003 B+
REL 150 Religion in America Spring 2003 B-
9840495 Jameson John James CSC INFT CSC 230 Programming Fall 2002 A-
MTH 160 Discrete Math Fall 2002 B-
ENG 107 Writing I Fall 2002 B+
PHL 151 Critical Thinking Spring 2003 B
REL 150 Religion in America Spring 2003 B+

1. Flatten the table.

Major
StudentID LastName FirstName MiddleName 1 Major2 Dept CourseNo CourseName Semester Grade
McDermot
9874923 t Mary Margaret ENG ENG 108 Writing II Fall 2002 A-
McDermot
9874923 t Mary Margaret ENG PHL 151 Critical Thinking Fall 2002 B+
McDermot
9874923 t Mary Margaret ENG MTH 150 Thinking Math Fall 2002 B
McDermot
9874923 t Mary Margaret ENG ENG 230 American Literature Spring 2003 B+
McDermot
9874923 t Mary Margaret ENG REL 150 Religion in America Spring 2003 B-
9840495 Jameson John James CSC INFT CSC 230 Programming Fall 2002 A-
9840495 Jameson John James CSC INFT MTH 160 Discrete Math Fall 2002 B-
9840495 Jameson John James CSC INFT ENG 107 Writing I Fall 2002 B+
9840495 Jameson John James CSC INFT PHL 151 Critical Thinking Spring 2003 B
9840495 Jameson John James CSC INFT REL 150 Religion in America Spring 2003 B+

2. Identify a primary key for the flattened table. When identifying a primary key, you cannot simply depend on the data in front
of you but you should ask some what if questions, such as what if a student retakes a course?

StudentID, Dept, CourseNo, Semester

3. Identify any functional dependencies. If Field B can be determined uniquely (including looking it up) from Field A then Field
B is functionally dependent on Field A. For example, an ISBN Number can be used to look up information about a book, such
as its title, author, publisher and so on. We can denote this as ISBN  BookAuthor, BookTitle, BookPublisher. There can also
be dependence on a composite field. For example, Season, EpisodeNumber  EpisodeTitle, AirDate, GuestStar.

Dept and CourseNo  CourseName


Student ID  StudentFName, LastName, MiddleName
StudentID  Current Major
StudentID, Semester  Major History (Major 1 & 2)
StudentID, Dept, CourseNo, Semester  Grade

4. Identify any partial dependencies on the primary key.

Student ID  StudentFName, LastName, MiddleName


StudentID, Semester  Major History (Major 1 & 2)
StudentID, Dept, CourseNo, Semester  Grade

5. Decompose (normalize) the table to 2NF (second normal form).

Student(StudentID(Pk), FirstName, LastName, MiddleName)


Course(CourseID(Pk), Dept, CourseNo, CourseName)

You might also like