Question

In: Computer Science

A law firm proposed the following table to keep track the information about cases and the...

A law firm proposed the following table to keep track the information about cases and the lawyers who handle the cases: CASE (caseNumber, caseDescription, lawyerInCharge, caseAssistant, beginningdate, endingDate, 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. NOTE : There may be also several staffs in the firm serve as the caseAssistant working for one LawyerInCharge in a case beginningDate, ending, Date indicate the period of the case lawyerRate, is the hourly payrate a client has to pay the firm’s service for this case. This rate depends on the case. accumulatedHours is the total hours of the client service for a case, clientType can be either individual or cooporate, laywerPhone is the phone number of the lawyerInCharge, caseResultDescription gives the description about the case result. clientCurrentPayment shows the total payment from a client currently paymentMethod can be credit card, cash or check, payment method cannot be changed once a case is on file. salary and bonus are paid to the lawyerInCharge. Bonus depends on the case, salary is not. Answer the following questions carefully a) List Function Dependencies based on above information. Also list the multivalued-depdendecy given by above narrative. b) What is the KEY for table Case? (hint: the caseID cannot be the key for above table because knowing the caseID only cannot identify the values of some other attributes in this table uniquely.) c) What normal form the table CASE is in? Why? Give a clear explanation. d) Normalize this table. Show your result.

Solutions

Expert Solution

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) it is in BCNF form

d) normalization

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

venereology answered 1 year ago

Next > < Previous

Related Solutions

This is a simple list table of a company trying to keep track of parts that...

This is a simple list table of a company trying to keep track of parts that they sell and orders that came in purchasing those parts (in other words, not a database but a flat one table file). You will design a database for this company so that they won’t be relying on a simple 1 table list system to keep track of their data. Looking at the table below, produce the 3NF of the data. OrderNum OrderDate PartNum Description...

The law firm of Topson Law Firm & Co., currently has four cases as of the...

The law firm of Topson Law Firm & Co., currently has four cases as of the end of October 2015: Case #1 Case #2 Case #3 Case #4 Direct Material $480 $8,800 $3,700 $850 Direct Labor ($190 per hour) 40 90 70 15 Estimated court hours 12 65 120 40 The law firm allocates overhead to cases based on a predetermined rate of $150 per estimated court hour. Required: · Determine the total costs assigned in different cases. · Assume Case #3...

Database Design and SQL The following relations keep track of airline flight information: Flights (flno: integer,...

Database Design and SQL The following relations keep track of airline flight information: Flights (flno: integer, from : string, to: string, distance: integer, departs: time, arrive: time, price: integer) Aircraft (aid: integer, aname : string, cruisingrange: integer) Certified (eid: integer, aid: integer) Employees (eid: integer, ename : string, salary: integer) The Employees relation describe pilots and other kinds of employees as well. Every pilot is certified for some aircraft and only pilots are certified to fly. Based on the schemas,...

1. A cosmetic product retailer needs to create a database to keep track of the information...

1. A cosmetic product retailer needs to create a database to keep track of the information for its business operations. The company has a web site that posts all its products. The product information includes product ID, product name, description, and unit price. The company also needs to keep track of customers’ information, including customer names, their shipping addresses, and the email address. The company creates an account for each customer for identification and tracking purpose. A customer can purchase...

As humans, we keep track of large amounts of information more efficiently by putting it into...

As humans, we keep track of large amounts of information more efficiently by putting it into categories. One example of this is to organize the organs of the human body into organ systems – you have 11 listed in chapter 5 of your textbook. Organizing the body in this way is useful but can cause us to view each organ system individually when, in fact, organ systems work together in many ways. Make a convincing argument for viewing the organ...

Maria is a Paralegal at Dewey, Cheatham & Howe, a law firm in Arizona. Information about...

Maria is a Paralegal at Dewey, Cheatham & Howe, a law firm in Arizona. Information about her 2018 income and expenses is as follows: Income received Salary $150,000 Taxes withheld from Salary: Federal Income tax $30,000 State Income Tax 9,000 Social Security Tax 7,961 Medicare Tax 2,175 Interest income from bank 6,000 Dividend income from U.S. Stocks 4,000 Short-Term Capital Gain 2,000 Long-Term Capital Gain 3,000 State income tax refund from last year 500 Expenses Paid: Unreimbursed dental and eye-care costs 1,800 Property taxes on her...

Consider the following set of requirements for a UNIVERSITY database that is used to keep track...

Consider the following set of requirements for a UNIVERSITY database that is used to keep track of students' transcripts. (a) The university keeps track of each student's name, student number, social security number, current address and phone, permanent address and phone, birthdate, sex, class (freshman, sophomore, ..., graduate), major department, minor department (if any), and degree program (B.A., B.S., ..., Ph.D.). Some user applications need to refer to the city, state, and zip of the student's permanent address, and to...

The table below contains information about the production, costs and profits of a firm. The price...

The table below contains information about the production, costs and profits of a firm. The price faced by the firm is $40 per unit. There are 50 empty cells in the table. (a) Fill in all the empty cells. Output Total Fixed Costs Total Variable Costs Marginal Costs Average Variable Costs Average Fixed Costs Average Total Costs Price per unit Profits 0 12 1 12 50 50.0 50.0 -22.0 2 12 75 25.0 37.5 43.5 3 10.0 28.3 4 20.0...

User ADT: Describes and manipulates user information. You must track the following information about a user / provide the following methods:

• User ADT: Describes and manipulates user information. You must track the following information about a user / provide the following methods:o usernameo firstNameo lastNameo a list of the 10 most recently purchased itemso A user can bid on and purchase ItemsThe User class should have a default constructor, as well as one accepting all parameters. It should also provide accessor (getter) and mutator (setter) methods for appropriate methods. By default, a user is able to buy products only (not...

Given the following mission statement: . A group of system administrators must keep track of which...

Given the following mission statement: . A group of system administrators must keep track of which computers are assigned to computer users in the community they support. Currently this is done by hand.. but this is tedious, error prone, and inconvenient. System administrators want to automate this task to ease their workload. Product Vision. The Computer Assignment System (CAS) will keep track of computers, computer users, and assignments of computers to users; answer queries; and produce reports about users, computers,...

Subjects