You are to analyze whether some of the designs are better than others, and whether there is a difference between them by the time you normalize relations resulting from each of the ERDS To answer the question, you will need to complete the following steps 1 Create relational models (relations) for each of the ERDS (each of the designs) 2 Identify normal form (NF) for each of the relations and convert them to 3NF a Identify functional dependencies b Explain whether it is in 1NF c Explain whether it is in 2NF If it is not, convert it to 2NF d Explain whether it is in 3NF If it is not, convert it to 3NF 3 Compare the results What is different between these sets What is the same What is lost Design 1 it is assumed that only the current employment is being tracked by the database LecturerID Name CollegeName TopicOfExpertis 0 M City Lecturer 0 1 Employ 1 1 State College Sign 0 1 ZIP CarnegieClass Contract 0000 Only keep track of the current employment Every salary change is considered as a different employment If the employment status changed, then the history will disappear i e if the salary of a lecturer changes, the old info will be directly updated but not kept If the lecturer leaves the college, the information will be deleted ContractDate CollegeName LecturerID Salary Design 2 it is assumed that each college keeps track of the history of their employees LecturerID Name CollegeName OpicOfExpertis 0 M Lecturer City Employ 0 1 1 1 State College Sign 1 1 0 M ZIP Write 0 N CarnegieClass Contract 0000 ContractDate CollegeName LecturerID Salary The number of contract a lecturer ever had at the same college is being tracked in this case Salary changes during the period in one college will result in a new contract Therefore, a lecturer can have many contracts The history of contracts are stored in the DB even after a lecturer is fired Design 3 it has the same assumptions with Design 2 but the model uses a ternary relationship CollegeName Lecturer D Name TopicOfExpertis City Lecturer Employ 0 M State College 0 1 0 M ZIP Contract CarnegieClass 0000 ContractDate CollegeName LecturerID Salary The number of contract a lecturer ever had at the same college is being tracked in this case Salary changes during the period in one college will result in a new contract Therefore, a lecturer can have many contracts The history of contracts are stored in the DB even after a lecturer is fired

Question

You are to analyze whether some of the designs are better than others, and whether there is a difference between them by the time you normalize relations resulting from each of the ERDS  To answer the question, you will need to complete the following steps  1  Create relational models (relations) for each of the ERDS (each of the designs)  2  Identify normal form (NF) for each of the relations and convert them to 3NF  a  Identify functional dependencies b  Explain whether it is in 1NF c  Explain whether it is in 2NF  If it is not, convert it to 2NF d  Explain whether it is in 3NF  If it is not, convert it to 3NF 3  Compare the results  What is different between these sets  What is the same  What is lost  Design 1   it is assumed that only the current employment is being tracked by the database  LecturerID Name CollegeName TopicOfExpertis 0  M City Lecturer 0  1 Employ 1  1 State College Sign 0  1 ZIP CarnegieClass  Contract 0000 Only keep track of the current employment  Every salary change is considered as a different employment  If the employment status changed, then the history will disappear  i e  if the salary of a lecturer changes, the old info will be directly updated but not kept  If the lecturer leaves the college, the information will be deleted  ContractDate CollegeName LecturerID Salary Design 2   it is assumed that each college keeps track of the history of their employees  LecturerID Name CollegeName OpicOfExpertis 0  M Lecturer City Employ 0  1 1 1 State College Sign 1 1 0  M ZIP Write 0  N CarnegieClass  Contract 0000 ContractDate CollegeName LecturerID Salary The number of contract a lecturer ever had at the same college is being tracked in this case  Salary changes during the period in one college will result in a new contract  Therefore, a lecturer can have many contracts  The history of contracts are stored in the DB even after a lecturer is fired  Design 3   it has the same assumptions with Design 2 but the model uses a ternary relationship  CollegeName Lecturer D Name TopicOfExpertis City Lecturer Employ 0  M State College 0  1 0  M ZIP Contract CarnegieClass  0000 ContractDate CollegeName LecturerID Salary The number of contract a lecturer ever had at the same college is being tracked in this case  Salary changes during the period in one college will result in a new contract  Therefore, a lecturer can have many contracts  The history of contracts are stored in the DB even after a lecturer is fired

SolutionInn · Accepted Answer

To proceed with the normalization process let us begin by creating the relational models relations for each of the EntityRelationship Diagrams ERDs provided Design 1 Relational Models 1 CollegeCollege...

Question: You are to analyze whether some of the designs are better than others, and whether there is a difference between them by the time

Step by Step Solution

Students Have Also Explored These Related Databases Questions!