You are to analyze whether some of the designs are better than others, and whether there...

   

Transcribed Image Text:

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. 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.

Answer rating: 100% (QA)

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... View the full answer