Question 2. Given our intuitive guideline for 3rd normal form that a non-key att

Question

Question 2. Given our intuitive guideline for 3rd normal form that a non-key attribute is about the key, the whole key and nothing but the key, consider the following schema for a college; the college database is one large table, named Al in Qne: AlwinnOne(Student ID, name, Course ID, Course Section, course name, total credits, grade, professor name, salary, Semester acul ID) Furthermore, the following relationships hold for the data in this large table R1 Student ID name, total credits R2 Student ID, Semester, C ID, C Section grade R3 Semester, C ID C Section Faculty ID R4 Course ID course name RS E Faculty ID 7 prof name, salary In other words, Student ID determines (uniquely identifies name, and total credits Student ID, Semester, Course ID and Course Section determine grade for courses taken Semester, Course ID and Course Section determine the Faculty ID ofthe teacher Course ID determines course name Faculty ID determines professor name, and salary Decompose the college's one large table into a set of tables that are in 3rd normal form

Explanation / Answer

To normalize the given large table, we need to check whether the table satisfies the conditions of various normal forms. If not, convert it to the normal form.

INF:

A relation is said to be in first normal form if:

1) There is no repeating field.

2) Each row and column intersection(cell) has only one value.

3) Each record is uniquely identified by the primary key.

Let us check and bring our relation into 1 NF.

Composite Primary Key= Student ID+Course ID+Course Section+Semester+Faculty ID

non keys :

name -> determined by Student ID (part of primary key)

course name-> determined by Course ID (part of primary key)

total credits-> determined by Student ID (part of primary key)

grade-> determined by Student ID+Semester+Course ID+Course Section (part of primary key)

professor name-> determined by Faculty ID (part of primary key)

salary-> determined by Faculty ID (part of primary key)

Therefore, all the non keys can be determined by the composite primary key.

However, repeating group (Faculty ID, prof name, salary) needs to be moved to another table where Faculty ID becomes primary key.

Similarly, repeating group (Student ID, name, total credits) needs to be moved to another table where Student ID becomes primary key.

Repeating group (Course ID, Course name) needs to be moved to another table where Course ID becomes primary key.

Tables in INF

Course( Course ID, Course name)

Student( Student ID, name, total credits)

Faculty(Faculty ID, prof name, salary)

All in one(Student ID, Course ID, Faculty ID, Course Section, grade,Semester)

2 NF:

For a table to be in 2 NF:

1) It should be in 1 NF.

2) All non keys should be fully dependent on the primary key.

Since the tables Student, Course, and Faculty have non composite primary keys, they are already in 2 NF.

In the all in one table, the non key grade is determined by (Student ID+Semester+Course ID+Course Section). However, it is not dependent on Faculty ID. Therefore, table All in one is not in 2 NF.

After converting to 2 NF, tables are:

Course( Course ID, Course name)

Student( Student ID, name, total credits)

Faculty(Faculty ID, prof name, salary)

Grade(Student ID, Course ID, Course Section, Semester,grade)

All in one(Student ID, Course ID, Course Section, Semester, Faculty ID)

The faculty ID is determined by Semester, Course ID, and Course Section and not by student ID

Therefore

Fac_Course(Course ID, Course Section, Semester, Faculty ID)

This is also in 3NF because there is only one non key.

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.