Scribd
Upload
3 views12 pages

NF Train

The document outlines the process of normalizing a college's lecturer data to Third Normal Form (3NF) by removing repeating groups, partial dependencies, and transitive dependencies. It provides a detailed example of the normalization process and presents a series of relational algebra questions related to the lecturer data, focusing on filtering and aggregating information based on various criteria. The questions aim to extract specific details about professors, their salaries, and the courses they teach.

Uploaded by

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

NF Train

The document outlines the process of normalizing a college's lecturer data to Third Normal Form (3NF) by removing repeating groups, partial dependencies, and transitive dependencies. It provides a detailed example of the normalization process and presents a series of relational algebra questions related to the lecturer data, focusing on filtering and aggregating information based on various criteria. The questions aim to extract specific details about professors, their salaries, and the courses they teach.

Uploaded by

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

NF Train

Remove repeating groups (1NF)


Remove partial dependencies (2NF)
Remove Transitive Dependencies
Example
• A college maintains details of its lecturers' subject area skills. These details comprise:
• Lecturer Number
• Lecturer Name
• Lecturer Grade
• Department Code
• Department Name
• Subject Code
• Subject Name
• Subject Level
• Subject Code, Subject Name and Subject Level are repeating fields.
• Normalize this data to Third Normal Form.
Solution
• UNF
• Lecturer Number ,Lecturer Name, Lecturer Grade, Department Code, Department Name, Subject Code, Subject Name, Subject Level
• 1NF
• Lecturer Number, Lecturer Name, Lecturer Grade, Department Code, Department Name
• Lecturer Number , Subject Code, Subject Name, Subject Level
• 2NF
• Lecturer Number, Lecturer Name, Lecturer Grade, Department Code, Department Name
• Lecturer Number, Subject Code
• Subject Code, Subject Name, Subject Level
• 3NF
• Lecturer Number, Lecturer Name, Lecturer Grade
• Department Code, Department Name
• Lecturer Number, Subject Code
• Subject Code, Subject Name, Subject Level
Relational Algebra Example
Use the following relational model to answer
the coming questions
Questions
• Question (1) display a list of all “Egyptian” professor data.
• Question (2) display a list of all “Egyptian” professor data who get
salary more than 10000 pounds.
• Question (3) display a list of professor names and nationalities who
get salary more than or equal to 20000 pounds.
• Question (4) display a list of professor name and code of courses he
taught
• Question (5) display a list of professor name and code of courses he
taught in summer semester.
Questions
• Question (6) display a list of “Egyptian” professor names and name of
courses taught in summer semester.
• Question (7) display a list of “Egyptian” professor names and salary
who taught courses with 3 credit hours in the summer semester.
• Question (8) display a list of professors who taught the course IS2510
but never taught IS3610.
• Question (9) display a list of professors who taught the course IS2510
and also taught IS3610.
• Question (10) display a list of professors who taught all the course
that professor walid taught.
Questions
• Question (11) display the sum of professor salaries
• Question (12) for each nationality, display the count of professors and
average of professors salaries.

You might also like