The purpose of this assignment is to give students an opportunity to demonstrate their competence in applying the basic concepts of data normalization as covered in class and as addressed in Lab Exercises 6 and 7.
Starting from an initial ERD model which is provided, students will on an individual basis work through the process of analyzing the contents of that initial ERD model for possible dependencies that will need to be addressed through data normalization. Each student will be asked to create the corresponding dependency diagram for this initial ERD model. Using below-the-line dependencies in the initial model, students will identify the required data entities. Each proposed data entity will then be further assessed through the creation of a dependency diagram for that entity. 1NF, 2NF, and 3NF normalization will then be applied to each data entity as needed. Finally, from the 3NF normalized dependency diagrams the student will create a final 3NF normalized ERD diagram. The dependency diagram for the initial ERD model, the dependency diagrams for the 3NF normalized data entities, and the final normalized 3NF ERD diagram should then be submitted as a single electronic Word document ( with Visio diagrams as embedded OLE objects) to an eCentennial dropbox, and will then be marked by the course instructor.
Consider the structure of the proposed initial EMPLOYEE table as given below. It describes a situation where employees have jobs and possess certain education credentials. Each employee is assigned to a specific department. Employees have dependents who share their health benefits. This initial table contains unsatisfactory components and characteristics, e.g. multi-valued attributes and non-primary-key dependencies and will need adjustment.
ATTRIBUTE NAME SAMPLE VALUE 1 SAMPLE VALUE 2
Emp_Num 1003 1018
Emp_Lname Willaker Syed
Emp_Fname Thomas Yamina
Emp_Education BA, BSc
Job_Class NS1 DBA
Job_Description Network Specialist 1 Data Base Analyst
Job_PayScale 40-65K 35-55K
Emp_Dependents Geraldine (spouse), Aftak (spouse)
Dept_Code MKTG INFS
Dept_Name Marketing Information Systems
Dept_Manager Jill H. Martin Carloz G. Ortiz
Emp_Title Network Administrator Data Base Specialist
Emp_DOB 23-Dec-1968 20-Jul-1977
Emp_Hire_Date 14-Oct-1997 15-Jul-2009
Emp_Salary $58,255 $47,900
Emp_10%Bonus $5,825.50 $3,790.00
Each student is specifically asked to complete the following:
1. After selecting a suitable primary key, draw the dependency diagram for the initial EMPLOYEE table as given previously. Show all “above-the-line” dependencies associated with your choice of primary key. Also show all “below-the-line” dependencies. Identify all multi-valued attributes. Clearly state any assumptions you had to make that affect the structure of your dependency diagram.
2. Using below-the-line dependencies and multi-valued attributes, identify the required data entities, and allocate all initial attributes to one of those data entities. Identity a suitable primary key for each of the data entities.
3. Create a dependency diagram for each of your initial data entities. Be sure to identify primary keys and all above-the-line and below-the-line dependencies.
4. Apply 1NF, 2NF, and 3NF to each entity dependency diagram, making any needed changes to the data entities, so that when completed your dependency diagrams have only complete above-the-line dependencies.
5. Use these 3NF normalized dependency diagrams, create a 3NF normalized ERD model showing all entities, attributes, primary keys, relationships, and multiplicities. Use an appropriate automated tool (such as MS VISIO or the drawing tools in MS Word) to create your 3NF normalized ERD diagram.
A single electronic document should be produced as your submission for this assignment and it should contain the following:
• The initial dependency diagram from Step 1 above.
• The 3NF normalized dependency diagrams from Step 4 above.
• The 3NF normalized ERD diagram from Step 5 above.
Are you looking for a similar paper or any other quality academic essay? Then look no further. Our research paper writing service is what you require. Our team of experienced writers is on standby to deliver to you an original paper as per your specified instructions with zero plagiarism guaranteed. This is the perfect way you can prepare your own unique academic paper and score the grades you deserve.
Use the order calculator below and get started! Contact our live support team for any assistance or inquiry.