IGNOU MCA IGNOU BCA IGNOU MBA IGNOU CIC Courses
BCA Assignements : TMA CS - 67 RDBMS Lab
BCA Assignements : TMA CS - 67 RDBMS Lab
Course Code : CS-67
Course Title : RDBMS Lab
Assignment Number : BCA(4)-67/TMA/05
Maximum Marks : 10
Last date of submission : 15th April, 2005
This is a Tutor Marked Assignment. There are two questions in this assignment. Answer all the questions. Each question carries equal weightage. You may use illustrations and diagrams to enhance explanations.
Question 1:
Consider the following requirements for the Consumer Goods (for example, water purifier’s) Maintenance Company’s database system that is used to keep track of it’s clients, pending repairs, advances and bills, stock of equipment components in the store etc.
• The clients for the company are of two kinds: (i) Clients who have the Annual Maintenance Contract (AMC) with the Company and (ii) Others.
• All the visits under AMC are recorded and all repair actions are noted. In addition, the other customers are given warranty of 3 months for the fault repaired.
• The store keeps track of each component with it. It keeps information such as component code, date of last acquisition from the company, quantity in hand, reorder level etc. The store informs the company to issue an order in case the quantity in hand falls below the reorder level.
• The store also keeps track of the issuance of each item (components) to various repairing persons. All issues or returns update the quantity in hand of that component uniquely. Each piece of a component has a unique ID.
• The company also keeps the information about the client details such as client code, name, address of communication, date of AMC etc.
• In addition, all the bills of the clients are duly recorded. In case a bill is paid through a draft or cash, the record of it is directly moved to database, however, in cases where bill is paid by cheque then the record is moved to database only on realisation of cheque.
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 normalized tables with require integrity and security constraints.
Question 2:
(a) Consider a Finance Company that maintains the following tables:
• Clients-List (client-id, name, address, contact-phone, proof-of-residence, maximum-loan-allowed, PAN)
• Deposits (client-id, deposit-no, date-of-deposit, amount)
• Withdrawal (client-id, withdrawal-no, date-of-withdrawal, amount, allowed/denied)
Please Note: A client may withdraw at any time, and deposit any time. Withdrawals are subject to maximum-loan-allowed. A Client is given an iInterest of 10% on the Balance. However, if the client balance is negative, he will be charged 12% interest. (Balance is calculated as = sum of all deposits of the client – sum of all withdrawals). Interest is calculated every month on the Minimum balance of that month.
Create the tables having appropriate referential Integrity constraints. Make and state suitable assumptions, if any.
Write and run the following SQL queries on the tables:
• Find all the client names that have not made any deposits in the last month.
• Find the list of all deposits and withdrawals of last month, in the order of clients and net balance of each.
• Find the list of names of all those clients who have been denied withdrawal more than once.
• List the client’s details and their balances.
• Calculate the interest due for all the clients for the last month.
Make suitable assumptions, if any.
(b) Consider the Relation R={A, B, C, D, E, F} and the set of functional dependencies.
A?D C?B F?D E?F E?C
C?D
What are the various alternate keys in the Relation R? Decompose R into 2NF, 3NF and finally in BCNF relation.