Relation structure notation to describe each entity

Assignment Help Database Management System

Reference no: EM132615524

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 drug store 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 OGconsultants 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 OG's 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	AmoxaCillin	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	AmoxaCillin	74000	Walgreens	94065
224	8/1/2020	AmoxaCillin	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)
a. AssignmentID→Client
b. Client->AssignmentID
c. (AssignmentID,ConsultantName)→(StartDate,ConsultantSalary, Client, ClientZipCode)
d. (AssignmentID, StartDate)→(ConsultantName, ConsultantSalary, Client, ClientZipCode)
e. (StartDate,ConsultantName)→(AssignID, ConsultantSalary, Client, ClientZipCode)
f. ClientZipCode→Client
g. Client->ClientZipCode
h. ConsultantSalary->ConsultantName
i. (ConsultantName, Client) → (StartDate, ConsultantSalary, ClientZipCode)
j. (StartDate, ConsultantName)->ConsultantSalary
k. (AssignmentID)→(StartDate, Client, ClientZipCode)
l. (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 #1not a candidate key of the ASSIGNMENT entity? If so, listthose 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.

Reference no: EM132615524

Questions Cloud

Compute effective interest rate or effective cost of bond : Compute the effective interest rate or effective cost of bond. March 1 and September 1, 2017 to pay interest and amortize the bond premium

Expressions for heat q associated with a constant pressure : Derive expressions for the heat Q associated with a constant pressure process involving the system in part a. Repeat for a constant volume process.

What does an increase in the interest expense mean : Is interest expense considered an account or or total? What does an increase in the interest expense mean on the income statement?

Calculate the gravitational acceleration of a falling object : Calculate the gravitational acceleration of a falling object near the Moon's surface. (The Moon's mass is 7.36×10²²kg)

Relation structure notation to describe each entity : Relation structure notation to describe each entity. Use natural keys only.Make sure the PK and FK are properly noted.

Find how the will impact the statutory remittance schedule : Find how the will impact the statutory remittance schedule. Using the Current Year calendar in the course material, provide specifics of when the remittances

Two reasons for a measurement to have low precision : What are two reasons for a measurement to have low precision. What are two reasons for a measurement to have low accuracy.

What programs are available to treat criminal behavior : Consider the various perspectives and theories of crime that we have discussed in class and reflect on which theory or theories you find most valid.

Calculate the entropy change when 5 moles of an ideal gas : Calculate the entropy change when 5 moles of an ideal gas expands from 10 liters to 100 liters isothermally and reversibly at 300 K.

User Account

All Pages