A law firm designed the following table to keep track the information about case

Question

A law firm designed the following table to keep track the information about cases and the lawyers who handle the cases:

Case (caseNumber, caseDescription, lawyerInCharge, caseAssistant, beginningdate, ending, Date, lawyerRate, accumulatedHours, clientsName, clientPhone, clientAdress, clientType, laywerPhone, caseResultDescription, clientCurrentPayment, paymentMethod, salary, bonus)

Among above attributes,

caseNumber, is the ID of the case

lawyerInCharge is the name of the lawyer (a single person) who in charge of the case.

There may be also several other people in the firm serve in the same case as the caseAssistant working for one lawyerInCharge

beginningDate, ending,Date indicate the period of the case

lawyerRate, is the hourly payrate of the client have to pay for the firm’s service in this case. This rate depends on the case.

accumulatedHours is the total hours of the client service,  

clientType can be either individual or cooperate,

laywerPhone is the phone number of the lawyerInCharge,

caseResultDescription gives the description about the case result.

clientCurrentPayment shows the total payment from client

paymentMethod can be credit card, cash or check

Salary and bonus belongs to the lawyerInCharge

Type your answer

a. List all functional dependencies based on the given information

b. What is the key if you have all these attributes in just one table?

c. How do you normalize the table into BCNF. Show your result. (Don’t forget the table name and the key for each table. Also pay attention to the foreign keys).

Explanation / Answer

a)

Functional dependencies:

caseNumbercaseDescription

caseNumberlawyerInCharge, clientsName, clientCurrentPayment, paymentMethod, accumulatedHours

caseDescription lawyerRate

caseNumber caseResultDescription

caseNumber beginningdate, endingDate

lawyerInCharge caseAssistant, laywerPhone, salary, bonus

clientsName clientType, clientPhone, clientAdress.

b)

The key in the relation is caseNumber lawyerInCharge clientsName

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

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