Advance Database System

Download Full Document From Here


Normalization

Un-normalization form:

({Booking_ref_no, booking_date, course_code, course_name, course_level, start_date,

finish_date, course_cost, status, lecturer_code, lecturer_first_name, lecturer_last_name,

centre_name, centre_phone, class_size}, student_no, student_first_name, student_last_name,

street_address, city, postal_code , country, student_telephone)

1NF

A table is in 1NF if

I. There is no repeating group in the table, thus eliminating repeating group from the

UNF data.

II. If there is no duplicate rows.

III. Entries in the column are of same kind.

IV. Identify each set of related data with a primary key.

V. If all underlying domains contain atomic values only.

Enrolment (Booking_ref_no,Student_no, booking_date, course_code, course_name,

course_level, start_date, finish_date, course_cost, status, lecturer_first_name,

lecturer_last_name, lecturer_code, centre_name, center_phone, class_size,)

Student (student_no, student_first_name, student_last_name, street_address, city,

postal_code, country, student_telephone)

2NF

A table is in 2NF if

I. If it is in 1NF and does not contain any partial dependency

II. All the non-key columns are functionally dependent on the entire primary key.

Student (student_no, student_first_name, student_last_name, street_address, city,

postal_code, country, student_telephone)

Enrolment (Booking_ref_no,Student_no, course_code, course_name, course_level,

start_date, finish_date, course_cost, status, lecturer_first_name, , lecturer_last_name,

lecturer_code, centre_name, center_phone, class_size)

BookingInfo (Booking_ref_no, booking_date)

3NF

A table is in 3NF if

I. Verifying whether the data is in 2NF.

Page | 3

Advance Database Systems

II. Transitive Functional dependencies on non-key fields are eliminated by putting them

in a separate table.

Student (student_no, student_first_name, student_last_name street_address, city, postal_code,

country, student_telephone)

BookingInfo (Booking_ref_no, booking_date)

Enrolment (Booking_ref_no,Student_no, course_code, class_code, lecturer_code)

Course (course_code , course_name, course_level, start_date, finish_date, course_cost,

status)

Lecturer (lecturer_code ,lecturer_first_name, lecturer_last_name)

Class (Class_code, class_size, center_code)

Centre (centre_code ,centre_name,centre_phone)