1.
Using your knowledge of BITS, determine the functional dependencies that exist in the following
table. After determining the functional dependencies, convert this table to an equivalent collection of
tables that are in third normal form:
- Functional Dependencies
Tasks: F(TaskID) > Description, Category, Price
Consultant: F(ConsultNum) > LastName, FirstName
WorkOrders: F(OrderNum) > OrderDate ; F(OrderNum) > ClientNum ; F(OrderNum) >
ConsultNum
Client: F(ClientNum) > ClientName
OrderLine: PK(OrderNum, TaskID) ; F(PK) > ScheduledDate, QuotedPrice
- Converted:
Tasks [TasksID, Description, Category, Price] 3NF
Consultant [ConsultNum, Lastname, FirstName] 3NF
WorkOrders [OrderNum, OrderDate, ConsultNum] 3NF
Client [ClientNum, ClientName] 3NF
OrderLine [TaskID, OrderNum, ScheduledDate, QuotedPrice] 3NF
2. List the functional dependencies in the following table that concern invoicing (an application BITS is
considering adding to its database), subject to the specified conditions. For a given invoice (identified
by the InvoiceNum), there will be a single client. The client’s number, name, and complete address
appear on the invoice, as does the date. Also, there may be several different tasks appearing on the
invoice. For each task that appears, 186 Chapter 5 display the TaskID, description, category, and
price. Assume that each client that requests a particular service task pays the same price. Convert
this table to an equivalent collection of tables that are in third normal form:
- Functional Dependencies
Invoice: F(InvoiceNum) > ClientNum, TaskID
Task: F(TaskID) > Description, Category, Price
Client: F(ClientNum) > LastName, Firstname, Street, City, State, ZipCode, Date
- Converted:
Invoice [InvoiceNum, ClientNum, TaskID] 3NF
Task [TaskID, Description, Category, Price] 3NF
Client [ClientNum, LastName, FirstName, Street, City, State, ZipCode, Date] 3NF
3. BITS wants to store information about the supervisors, including their supervisor number and the
relationship to consultants. Supervisors can work with multiple consultants, but consultants only
have one supervisor. In addition, supervisors specialize in working with clients in specific task
categories. Using this information, convert the following unnormalized relation to fourth normal
form:
- Functional Dependencies:
Tasks: F(TaskID) > Description, Category, Price, ClientNum
Supervisor: F(SupervisorNum) > SupervisorName, TaskID
Consultant: F(ConsltNum) > LastName, FirstName, Street, City, ZipCode, Hours, Rate
- Converted
Tasks [TaskID, Description, Category, Price, ClientNum] 4NF
Supervisor [SupervisorNum, SupervisorName, TaskID] 4NF
Consultant [ConsultNum, LastName, FirstName, Street, City, Zipcode, Hours, Rate] 4NF
4. BITS is considering changing its business model so that many consultants can service one client
and one consultant can represent many clients. Using this information, convert the following
unnormalized relation to fourth normal form:
- Functional Dependencies
Client: F(ClientNum, ClientName, Street, City, State, ZipCode)
Description: F(ClientNum, CreditLimit, Balance)
Consultant: F(ConsltNum, Lastname, FirstName, Street, City, State, ZipCode, Hours, Rate)
- Converted
Client: [ClientNum, ClientName] 4NF
Description: [ClientNum, CreditLimit, Balance] 4NF
Consultant : [ConsltNum, Lastname, FirstName, Hours, Rate] 4NF
5. Convert the following unnormalized relation to fourth normal form using the same requirements
as in Question 4 (many consultants can represent one client, and one consultant can represent
many clients):
- Functional Dependencies
Client: F(ClientNum, ClientName, Street, City, State, ZipCode)
Description: F(ClientNum, CreditLimit, Balance)
Consultant: F(ConsltNum, Lastname, FirstName, Street, City, State, ZipCode, Hours, Rate)
- Converted
Client: [ClientNum, ClientName] 4NF
Description: [ClientNum, CreditLimit, Balance] 4NF
Consultant : [ConsltNum, Lastname, FirstName, Hours, Rate] 4NF
6. Is there any difference between the tables you created in Questions 4 and 5? Why or why not?
and 5? Why or why not?
No, it makes no difference because its practically just vice versa with the tables, they’re
accomplishing the same thing ultimately.
