Online Date sheet management
Online date sheet management system lets the students of different universities to create their date sheet online as per their own convenience. System operates in two modes:
Admin mode:
Admin of the system provides the configuration data e.g. the exam starting and ending dates, available centers and subject details. Admin activates the date sheet module for a specified time after which it is deactivated.
Student mode:
Students of the system login through their ID and create date sheet for the selected examination as per their availability. Each Student selects the subject, day and exam center. After clicking the confirm button, the paper of the selected subject is scheduled on that day at the specified exam center. In this way student schedules all of the subjects. After clicking the submit button, the date sheet is locked and email is sent to the student with details of the date sheet. Once the date sheet is locked by the student, he/she cannot change it.
Question:
Consider the following portion of the diagram. Convert it into relational tables up to 3NF.
Note: Just show the tables in 3NF, no need to show tables in 1NF and 2NF
idea Solution :
Third Normal Form
The third normal form requires that all columns in a relational table are dependent only upon the primary key. A more formal definition is:
A relational table is in third normal form (3NF) if it is already in 2NF and every non-key column is non transitively dependent upon its primary key. In other words, all nonkey attributes are functionally dependent only upon the primary key.
Table PARTS is already in 3NF. The non-key column, qty, is fully dependent upon the primary key (s#, p#). SUPPLIER is in 2NF but not in 3NF because it contains a transitive dependency. A transitive dependency is occurs when a non-key column that is a determinant of the primary key is the determinate of other columns. The concept of a transitive dependency can be illustrated by showing the functional dependencies in SUPPLIER:
SUPPLIER.s#
—> SUPPLIER.status
SUPPLIER.s#
—> SUPPLIER.city
SUPPLIER.city
—> SUPPLIER.status
Note that SUPPLIER.status is determined both by the primary key s# and the non-key column city. The process of transforming a table into 3NF is:
Identify any determinants, other the primary key, and the columns they determine.
Create and name a new table for each determinant and the unique columns it determines.
Move the determined columns from the original table to the new table. The determinate becomes the primary key of the new table.
Delete the columns you just moved from the original table except for the determinate which will serve as a foreign key.
The original table may be renamed to maintain semantic meaning.
To transform SUPPLIER into 3NF, we create a new table called CITY_STATUS and move the columns city and status into it. Status is deleted from the original table, city is left behind to serve as a foreign key to CITY_STATUS, and the original table is renamed to SUPPLIER_CITY to reflect its semantic meaning.
The third normal form requires that all columns in a relational table are dependent only upon the primary key. A more formal definition is:
A relational table is in third normal form (3NF) if it is already in 2NF and every non-key column is non transitively dependent upon its primary key. In other words, all nonkey attributes are functionally dependent only upon the primary key.
Table PARTS is already in 3NF. The non-key column, qty, is fully dependent upon the primary key (s#, p#). SUPPLIER is in 2NF but not in 3NF because it contains a transitive dependency. A transitive dependency is occurs when a non-key column that is a determinant of the primary key is the determinate of other columns. The concept of a transitive dependency can be illustrated by showing the functional dependencies in SUPPLIER:
SUPPLIER.s#
—> SUPPLIER.status
SUPPLIER.s#
—> SUPPLIER.city
SUPPLIER.city
—> SUPPLIER.status
Note that SUPPLIER.status is determined both by the primary key s# and the non-key column city. The process of transforming a table into 3NF is:
Identify any determinants, other the primary key, and the columns they determine.
Create and name a new table for each determinant and the unique columns it determines.
Move the determined columns from the original table to the new table. The determinate becomes the primary key of the new table.
Delete the columns you just moved from the original table except for the determinate which will serve as a foreign key.
The original table may be renamed to maintain semantic meaning.
To transform SUPPLIER into 3NF, we create a new table called CITY_STATUS and move the columns city and status into it. Status is deleted from the original table, city is left behind to serve as a foreign key to CITY_STATUS, and the original table is renamed to SUPPLIER_CITY to reflect its semantic meaning.
