50.1K

Verified Solution

Link Copied!

Use the following listing to answer questions 1 6. State any assumptions you make, as needed.

OG (OrangeGreen) Consulting is a consulting firm dedicated to serving drugstore clients. OG provides consulting on pharmacy design/decoration approaches but knows very little about databases. Below is a listing that OG keeps of its different consulting assignments (note that OG consultants sometimes work in different cities for the same client and clients sometimes work together). 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 OGs data needs, using database normalization practices. You will achieve your goal by answering the questions below.

AssignmentID	StartDate	ConsultantName	ConsultantSalary	Client	ClientZipCode
123	9/1/2019	Amoxa Cillin	74000	CVS	10504
123	9/5/2019	I.B. Profin	80000	CVS	10504
124	7/1/2020	I.B. Profin	88000	CVS	80301
223	4/1/2020	Amoxa Cillin	74000	Walgreens	94065
224	8/1/2020	Amoxa Cillin	81000	Walgreens	94065
225	7/1/2020	Tor Lipa	88000	Boots	98052
225	7/1/2020	I.B. Profin	88000	CVS	10504
226	10/1/2020	Tor Lipa	88000	RiteAid	94568

1. Assuming that all functional dependencies are apparent in this data, which of the following are true? Highlight the answer row(s)

1. AssignmentIDClient
2. Client->AssignmentID
3. (AssignmentID,ConsultantName)(StartDate, ConsultantSalary, Client, ClientZipCode)
4. (AssignmentID, StartDate)(ConsultantName, ConsultantSalary, Client, ClientZipCode)
5. (StartDate,ConsultantName)(AssignID, ConsultantSalary, Client, ClientZipCode)
6. ClientZipCodeClient
7. Client->ClientZipCode
8. ConsultantSalary->ConsultantName
9. (ConsultantName, Client) (StartDate, ConsultantSalary, ClientZipCode)
10. (StartDate, ConsultantName)->ConsultantSalary
11. (AssignmentID)(StartDate, Client, ClientZipCode)
12. (AssignmentID,StartDate,ConsultantName)(ConsultantSalary, Client, ClientZipCode)

2. Assume the listing above represents the ASSIGNMENT entity in its initial form. List all candidate keys for ASSIGNMENT. Restrict your answer to the row(s) you highlighted in #1.

3. Are any determinants you highlighted in #1 not a candidate key of the ASSIGNMENT entity? If so, list those determinants / functional dependency(ies), in standard notation.

4. Based on what you determined in questions 2 and 3, is it necessary to split the ASSIGNMENT entity into multiple entities? If so, list the themes of all entities.

5. For each theme identified in question #4, use entity/relation structure notation to describe each entity. Use natural keys only. Make sure the PK and FK are properly noted.

6. Keeping future growth of the firm in mind and ease of use/understanding, consider whether it makes sense to modify any of the natural keys used in #5 to incorporate the surrogate key concept. If so, what would the new entity structure(s) look like (in standard notation)? Make sure the PK and FK are properly noted.

Answer & Explanation Solved by verified expert

See Answer