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

Solution :

a) Functional dependencies are :

  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.

b) The key is superkey where lawyer have different cases information stored in a table.

c) To normalize the table in BCNF :

Step1: Check the table if it is in 1NF :

A table (relation) is in 1NF if

1. There are no duplicated rows in the table.

2. Each cell is single-valued (i.e., there are no repeating groups or arrays).

3. Entries in a column (attribute, field) are of the same kind.

Step 2 : Check table for 2NF :

  A table is in 2NF if it is in 1NF and if all non-key attributes are dependent on all of the key.

Step 3 : Check table for 3NF :

  A table is in 3NF if it is in 2NF and if it has no transitive dependencies.

Step 4 :  Check table for BCNF :

A table is in BCNF if it is in 3NF and if every determinant is a candidate key.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

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