Question

Database Normalization

Please if you don't know the answer don't comment as "Need More Information."

Introduction:
This lab is designed to help you with practicing normalization concepts implementation.

Submission:
After finishing the task below, convert the word file to a PDF document and submit it to Brightspace.

Task:
Using this file, normalize the following tables to be in the third normal form. Remember to consider having the data when you do the normalization.

Course_Title	Course_Credit_Hours	Professor_ID	Professor_Name	First_day_work	Professor Specialization_ID	Professor Specialization
Accounting, Finance	3,3	234	Patric	14/2/2008	7	Accounting
Marketing, Business administration	3,2	564	Mary	15/4/2010	5	Business Management

Notes: • Every course can be taught by more than one professor and each professor can teach more than one course

Table B

Employee_ID (P.K)	Project_ID (P.K)	E_Name	P_Name	E_Assigned_Hours
100	101	Cedric	Acct.	13
200	110	Natali	Finance	15
300	111	Maria	BD	12

Marking Criteria:
• Entities titles
• Attributes:
• PKs:
• FKs:
• Relationships:
• Handling data:

Answer 1

Answer below:

Task A:
------
Entities: Courses, Professors, Specializations
Relationships: Teaches, SpecializesIn
Attributes: Courses
        CourseID: PK
        Course_Title
        Course_Credit_Hours
Attributes: Specializations
        Specialization_ID: PK
        Specialization_Description
Attributes: Professors
        Professor_ID: PK
        Professor_Name
        First_day_work
        Specialization_ID : FK   # this design supports only one specialization per professor, which is an assumption we are making
Attributes: Teaches
        Professor_ID: FK
        CourseID: FK
                # Primary key here is Professor_ID + Course_ID
Handling Data:
        Load the tables Courses, Specializations first, which have no foreign keys
        Then load the Professors table using the correct specialization id as the foreign key
        Finally load the Teaches tables, using the keys from the Professor and Courses tables to establish the required relationships
        Use equijoins across tables to get details on courses taught by professors and subjects specialized by professors
        
Task B:
------
Entities: Employee, Project
Relationships: WorksOn
Attributes: Employee
        Employee_ID : PK
        E_Name
Attributes: Project
        Project_ID : PK
        P_Name
Relationships: WorksOn
        Employee_ID : FK
        Project_ID : FK
        Assigned_Hours
                # Primary key for this table is Employee_ID + Project_ID
Handling Data:
        Load the Employee and Project tables first since they have no foreign keys
        Using the keys from above tables, load the WorksOn table to establish the required relationships and also track hours assigned
        Use equijoins across tables to get details on project assignments for employees