Use the following listing to answer questions 1-6. State any assumptions you mak

Question

Use the following listing to answer questions 1-6. State any assumptions you make, as needed Comet Consulting is a consulting firm that provides a wide range of consulting services to its database vendor clients. Comet provides consulting on office interior design/decoration approaches but knows very little about databases. Below is a listing that Comet keeps of its different consulting assignments (note that Comet consultants sometimes work in different cities for the same client). As a budding database architect, you realize this current single list will break down as the firm grows to more consultants and clients. Your task is to determine a better approach to handling Comet's data needs, using database normalization practices. You will achieve your goal by answering the questions below AssignmentID AssignStartDateConsultantName ConsultantSalaryClient ClientZipCode 80301 10504 123 123 124 223 224 225 225 226 9/1/2017 9/1/2017 4/1/2018 4/1/2018 8/1/2018 7/1/2018 7/1/2018 10/1/2018 Bill Burgey Jasmine Martes Jasmine Martes Bill Burgey Bill Burgey Bill Burnes Jasmine Martes Bill Burnes BM IBM IBM Oracle Oracle Microsoft Microsoft 74000 80000 74000 81000 88000 88000 88000 94065 94065 98052 8052 Sybase94568 9 1. List all determinants / functional dependencies you find in this entity, in standard notation form. 2. Assume the listing above represents the ASSIGNMENT entity in its initial form. List all candidate keys Keeping future growth of the firm in mind, would it make sense to modify any of the candidate keys from #2 to incorporate the surrogate key concept? If so, what would the new candidate key(s) look like? 3. 4. Are any determinants not a candidate key of the ASSIGNMENT entity? Which ones? lf, based on what you determined in question #4, it becomes necessary to split this entity into multiple entities, list the themes of all entities 5, For each theme identified in question #5. use structure notation to describe the entity. Make sure PK and FK are properly noted. If any surrogate keys are identified, justify why you made that choice. Keep future growth of the firm in mind. 6.

Explanation / Answer

1. Functional Dependencies

AssignmentID -> AssignmentStartDate

AssignStartDate, ConsultantName -> ConsultantSalary, Client, ZipCode

AssignmentID, ConsultantName -> ConsultantSalary, Client, ZipCode

Client, ZipCode -> ConsultantSalary

2. Candidate keys for Assignment Entity

a) AssignmentID + ConsultantName (a composite key)

b) ConsultantName + AssignStartDate

3. A surrogate key is very much necessary for Assignment Entity since the all the candidate keys are composite keys. A surrogate key like ID which acutally identifies a single entity of Assignment Entity can be introduced. New candidate key is nothing but the additional ID field.

4. Client, ZipCode -> ConsultantSalary cannot be a candidate key for Assignment Entity

5. We need to split this into 2 different entities for the best design. Namely -

a) Assignment (ID, AssignID, StartDate, ConsultantName, ClientID)

b) Client (Id, ClientName, ClientZipCode, SalaryForAConsultant)

5.

a) Surrogate key for Assignment is required to avoid composite key.

b) A surrogate key (Id) for Client Entity is required since a client can be located in multiple locations.

Foreign keys

1. ClientID in Assignment Entity refers to ClientID in Client Entity.

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.