Normalization Example

Normalization Example

After the Interview A corporation wants to develop a database about its employees, the information they have after a rough interview expresses the following reality: An employee is assigned to a given project within a given department. Each project is assigned and managed by one department and the department is managed by one manager with unique employee number. For each employee the corporation has his/her salary history consisting of job title, salary and date starting. Each department also has information on the offices it uses and the project that is being performed in each office. For each office the information consists of the size of it and the telephones assigned to it. For each employee the corporation knows the telephone to contact him and the current job title. Each department has a budget and each project has its own budget.

Guide 1 Define the Domains Atomize as less as possible Define the internal Format Use the one that will cover for all views Write the initial semantic assumptions Draw the dependencies diagram Connect all domains Determine direction of the arrows Using Functional Dependencies 6 Eliminate transitive dependencies Obtain the entities Underline the independent domains as PK’s Write down additional semantic assumptions Present the the domains and the entities to the user 10 Get your designed approved and SIGNED

Problem 1 Domains • Dept # Integer • D Budget Float • Mgr # Integer • Emp # Integer • Project Integer • Telephone Integer • Date date • Job Title String(10) • Salary Float • P Budget Float • Office # Integer • Area Float

Assumptions • No employee is the Manager of more than one department at a time • No employee works in more than one department at a time • No employee works in more than one project at a time • No employee has more than one office at a time • No employee has more than one phone at a time • No employee has more than one job at a time • No project is assigned to more than one department at a time • No office is assigned to more than one department at a time

FD Diagram D Budget Mgr # Office # Dept # Project Telephone Emp # Area Date Job Title P Budget Salary

Entities 1. Departments:(Dept #, D Budget, Mgr# ) 2. Employees : (Emp#, Project, Telephone) 3. Salary Histories: ( Emp#, Date, Job Title, Salary) 4. Projects :(Project,Dept #, P Budget) 5. Phones : (Telephone, Office # ) 6. Offices : (Office #,Dept #, Area ) 12 domains with 6 entities

FD DiagramChanging an assumption D Budget Mgr # Office # Dept # Project Telephone Emp # Area Date Job Title P Budget Salary

Entities 1. Departments:(Dept #, D Budget, Mgr# ) 2. Employees : (Emp#, Project, Telephone) 3. Salary Histories: ( Emp#, Date, Job Title) 3b. Salary Level: (Job Title, Salary) 4. Projects :(Project,Dept #, P Budget) 5. Phones : (Telephone, Office # ) 6. Offices : (Office #,Dept #, Area ) 12 domains with 7 entities

Entities and SQL

Entities and Views

Entities and QBE -Print -DX >50k -DX

Entities and QBE(Appendix C) Print Manager# that manage a department with projects With a budget > $50,000 >50k -Print -DX -DX Note that working on the SQL is simpler after you use QBE

Entities and QBE Print the of Office# and Area in square foot as well the department # where employee= 1234 is currently working =1234 -TX -Print -DZ -OY -DZ Print -DX -OY,Print

Normalization Example