MCS-23 SOLVED ASSIGNMENT
Course Code : MCS-023Course Title : Introduction to Database Management Systems
Assignment Number : BCA(III)/023/Assign /14-15
Maximum Marks : 100
Weightage : 25%
Last Dates for Submission : 15th October, 2014 (For July 2014 Session)
15th April, 2015 (For January 2015 Session)
This assignment has SIX questions. Answer all questions of total 80 marks. Rest 20 marks are for viva voce. You may use illustrations and diagrams to enhance your explanations. Please go through the guidelines regarding assignments given in the Programme Guide for the format of presentation. Answer to each part of the question should be confined to about 300 words.
DOWNLOAD
Question 1: Construct an ER diagram for a Banking System. Clearly indicate the entities, relationships, cardinality and the key constraints. Also, derive the un normalized relational database tables with the help of this diagram.
Question 2: Explain decomposition of a database in database design. Why consolidation of a database after normalization is considered while designing a database. Explain with the help of a suitable example.
Question 3: (5X2=10 Marks)
(a) What is non-loss decomposition in database? How it is useful in database?
(b) Explain evaluation of expression process in query optimization.
Question 4:
We have following relations:
Supplier(S#,sname,status,city)
Parts(P#,pname,color,weight,city)
SP(S#,P#,quantity)
Answer the following queries in SQL.
(i) Find name of supplier for city = „Delhi‟.
(ii) Find suppliers whose name start with „AB‟
(iii) Find all suppliers whose status is 10, 20 or 30.
(iv) Find total number of city of all suppliers.
(v) Find s# of supplier who supplies „red‟ part.
(vi) Count number of supplier who supplies „red‟ part.
(vii) Sort the supplier table by sname.
(viii) Find name of parts whose color is „red‟
(ix) Find parts name whose weight less than 10 kg.
(x) Find all parts whose weight from 10 to 20 kg.
(xi) Find average weight of all parts.
(xii) Find S# of supplier who supply part „p2‟
(xiii) Find name of supplier who supply maximum parts.
(xiv) Sort the parts table by pname.
(xv) Find the parts which weigh 10kg or above and are in „red‟ colour
Question 4: (10 Marks)
(a) Explain ACID properties of Transaction with suitable example.
(b) Explain TWO phase locking.
Question 5:
(a) Consider table R(A,B,C,D,E) with FDs as A->B, BC->E and ED-> A. The table is in which normal form? Justify your answer.
(b) Explain system recovery procedure with check point record.
Author Name
Author Description!
Get Free Email Updates to your Inbox!
