Question

The previous report is the only supporting document a contracting company has provided for getting a new database design. As the company’s owner is very busy, he has requested you to generate a set of suitable relations with the report above.

The process you will follow is itemized below, be brief but provide a good explanation when asked:

1. Considering Employee 112, in how many projects do you find him? What does it tell you in relation to employees? (7 marks)

2. Employee James J. Frommer had a problem with his pay coming from project 18. Why did it happen? What kind of anomaly could have created this situation? Explain           (8 marks)

3. Convert the data contained in the report to 1NF by means of flattening (7 marks)

4. Convert your resulting 1NF to 2NF (10 marks)

5. From the tables generated in 2NF. What kind of attribute do you think Total charged is? Do you recommend keeping it as a column in the resulting table? Explain (10 marks)

6. After reaching third normal form Job class can still be subject to anomalies or redundancy, why? What do you propose to solve the issue? (8 marks)

7. Generate an E-R diagram, in UML notation, that is a constitutes a good conceptual design of your resulting database (12 marks)

8. How would you represent the project leader in your brand-new conceptual design?   (8 marks)

Project Report Project NumberProject Name Employee Number Employee Name Job class June E. Arbough John G. News Alice K. Johnson* William Smithfield David H Charge/Hour Hour billed Total charged $ 84.50 $105.00 $105.00 s 2,011.10 S 2,037.00 3,748.50 450.45 s 2,302.65 Evergreen 103 101 105 Elec. Engineer Database Designer Database Designer Programmer Senior Systems Analyst $96.75 Subtotal Applications Designer General Support Systems Analyst DSS Analyst Subtotal Database Designer Systems Analyst 35.75 102 $10,549.70 Annelise Jones James J. Fromer Anne K. Ramoras Darlene M. Smithson Amber Wave S 1,183.26 118 18.36 S 831,71 $ 96.75 3,134.70 s 2,021.80 $7,171.47 Rolling Tide Alice K. Johnsorn Anne K. Ramoras Delbert K. Joenbrood*Applications Designer $48.10 Geoff B. Wabash William Smithfield $105.00 $96.75 105 S 6,793.50 4,682.70 S 1,135.16 591.14 457.60 113 Clerical Support Programmer Subtotal Programmer Systems Analyst Database Designer Applications Designer $ 48.10 Systems Analyst General Support DSS Analyst Subtotal 106 $35.75 $13,660.10 Starflight Maria D. Alonzo Travis B. Bawangi John G. News* Annelise Jones Ralph B. Washington James J. Frommer Darlene M. Smithson 879.45 S 4.431.15 S 5.911.50 1,592.11 s 2,283.30 559.98 1,902.33 $ 96.75 $105.00 101 108 118 96.75 $18.36 $17,559.82 $48,941.09 Note: A indicates the project leader

Answer 1

1)

Considering Employee 112, he was available in two prrojects named(Amber Wave , Star Flight).

The one thing you can relate from this was :

The name of the Employee 112 was same in both the projects -Amber Wave and Star flight which means that Employee Number was the primary key which is unique for every one .

So from this we can know that the functional dependency will be :

Employee Number -> Employee Name

2)The problem is with the updatation of data which refers to the update anomoly it must be updated at least 2 times or there will be inconsistent data in the database. here the Employee james has also worked in the project Star flight where the another data has been inserted an forgot to update the data twice leads to the inconsistance data in the database.

3)Converting into 1NF:-

Generally the 1st Normal form is about having the data as -the domain and the attributes have the atomic values .

4)converting from 1NF to 2NF:-

2NF is about:if the functional dependency is a partial dependency like-when a non prime attribute depends onthe part of the candidate key or primary key ..this type of scenario or functional dependencies are known as PARTIAL DEPENDENCIES which are not allowed by the 2NF .

Split the table into their requisites as followed.

So from the above given table the relations or the functional dependencies are:

project (project number, project name)

project_number-->project_name

Employee(Employee number, Employee name,job class)

Employee_number-->Employee_name,job_class

Pay(charge/hour,hourbilled , total charged)

charge,hourbilled-->total charged

so here ,

primary keys:project numberr, employee number, chrage ,hourbilled

Since there are no partial dependencies the table eas in 2NF itself

5)the attribute Total charged was an non prime attribute means which is not part of the candidate key ,which will be derived from the candidate key(charge and hourbilled )

Yes, it should be maintained as a coloumn in the derived last table so that the total billed charge will be inserted into that coloumn as per thier requirements.

6)Yes , even after came to 3NF there willl be redundant data for the job class because in every project the job class not only depends onthe employee anme but also depends on the Employee no so for every time that employee name has called the job class shold also be repeated multiple times as employee name has repeated which leads ton the data redundancy and also makes the process slow and system slow and somtimes leads to the big trouble in updating or deleting anamolies..

So inorder to mitigate this type of redundancy problem split the table into parts and make thatv job class depends only on the Employee number not on the Employee name because somtimes Employees names might be same so inorder to avoid those problems nake ..

Employee(Employee number,Employee name , job class)

and the functinal dependencey was

Employee number-->Employee name, job class

empployee name-->job class

so now inorder to reduce that data redundancy split the tables so which leads to the -

Employee1(employee number,Employee name)

Employee2(Employee numbber,job class)

so now the functional dependencies will be

employee number->employee name

employee number->job class

which reduces the data redundancy.

7)ER DIAGRAM:

8)

null

By making a seperate entity "project leader" and including it in the project table be like:

project(project number, project name, project leader)

where the project leader depends on both project number and project name so which can be again divided into two tables to reduce the redundancy be like:

projectx(project number, project name)

projecty(project number, project leader)