Course Code : CS-67

Assignment Number : BCA(4)-67/TMA/06

Maximum Marks: 10

Last Date of Submission : 15^th April, 2006

This is a Tutor Marked Assignment. There are two questions in this assignment. Answer both the questions. All questions carry equal weightage. You may use illustrations and diagrams to enhance explanations.

Question 1: Consider the following requirements for a construction company that uses database system to keep track of its projects, workers and material requirements of various projects.

• The projects for the company are of two kinds: (i) Turn key projects and (ii) Others.

• All the projects have a life cycle (Please note that the turn key projects have a maintenance phase in addition.) and workers are allotted as per the phase of the project. Each project has its own plan of completion that is drawn at the start of the project. The worker and material requirement of project is calculated at the start of the project.

• The store manages the materials. One of the major constraints for the store is that it requires 15 days to acquire a product. Thus, the inventory should fulfil the requirements of the next 15 days.

• The store also keeps track of the issue of materials and return of defective materials to various projects.

• The company keeps the information of all the clients including the information about the projects that are being maintained by the company (turn key projects).

Draw an ER diagram for the above company. Specify key attributes and constraints of each entity type and on each relationship type. Note any unspecified requirements and make appropriate assumptions to make the specification complete. Also, design the normalised tables with required integrity and security constraints. (5 Marks)

Question 2: (a)Assume that a Consumer item lease Company which leases various household items to its clients for their use for a specific period of time, maintains the following tables:

• Clients (clientID, name, address, contact Phone)

• Itemlist (itemID, itemName, itemCost, purchase Date )

• Leaselist (clientID, transactionNO, itemID, startDate, returnDate, amountTObeCharged)

Please Note: A client may lease an item many times. Amount to be charged is calculated as per a fixed rate multiplied by number of days item is leased. All items have unique itemID. However, two or more items may have same name.

Create the tables having appropriate referential integrity constraints. Make and state assumptions, if any.

Write and run the following SQL queries on the tables:

• Find all the client names that have not got any item leased during the last month and no leased item is pending with them.

• Find the list of all the items that were leased or returned last month.

• Find the names of all those clients who have given the business to the company in the decreasing order of total amount paid by a client.

• List the client's details and the items leased to them at present.

• Find the client who has been leased at least two items.

Make suitable assumptions, if any. (3 Marks)

(b) Consider the Relation R = {A, B, C, D, E, F} and the set of functional dependencies.

A - B, C - B, A - D, C - F, F - C, C - A

What are the various alternative keys in the Relation R? Decompose R into 2NF, 3NF and finally into BCNF relation. (2 Marks)