NIELIT A Level Introduction to Database Management System Assignment

NIELIT A Level

Introduction to Database Management System

Assignment 5

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)

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:

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

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

c) 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.

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

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

                                                                         Next Assignment