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 corporate,

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

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

All functional dependencies based on the given information:

1)Case = Case Number ,Beginning Date, Ending Date Case Description, Case Result Description

2)Clients = ClientsName, ClientPhone, Client Address

3)Client Type   = Individual or Corporate, CurrentPayment Mode

4) Lawyer = Lawyer In Charge, Case Assistant, LaywerPhone

5) Lawyer Salary=lawyerRate, AccumulatedHours, Current payment and Method, Salary , Bonus

________________________________________________________________________

b) Case ID is the key have all these attributes in just one table

_____________________________________________________________________

c)   Case                                         Lawyer

Individual or Corporate     :::::                  Case ID

Case Description and Result   ::::              Lawyer In Charge and Assistant

Client Current Payment    ::::       Total Hours Worked and Rate per case

Payment Method                   :::::                   Credit card, Cash or Check Salary and bonus

__________________________________________________________________________________

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

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