Course Code : MCS-043
Course Title : Advanced Database Management Systems
Assignment Number : MCA (4)/043/Assign/12
Maximum Marks : 100
Weightage : 25%
Last Dates for Submission : 15th October, 2012 (For July 2012 Session)
15th April, 2013 (For January 2013 Session)
This assignment has ten questions, which carries 80 marks. Answer all the questions. Rest 20 marks are for viva voce. You may use illustrations and diagrams to enhance 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.
Q 1: The organization called ABC undertakes several kinds of software project related to HR finance, manufacturing projects. Each employee can move on one or more projects. Each project is undertaken on the request of a client. A client can request for several projects. Each project has only one client. A project can use a number of items from different manufacturers and items may be used by several projects. Before delivery of items to a client, it is
tested by testing group in the organization. (5 marks)
(a) Draw the EER diagram for the organization showing all entities, relationship, aggregation, generalisation and specialisation for
the description given above. (5 marks)
(b) Create the normalised tables till 5 NF for the EER diagram created as answer to part (a) of this question, highlighting the different types of dependencies including Multi-Valued and Join dependencies (if your design does not have any such dependency, then you must explain the MVD and JD with the
help of an example situation). (5 marks)
(c) Draw the class diagram for the organization as described above
and relate it to database design as done in part (b). (5 marks)
Q 2: Consider the following relations: (6 marks)
Hotelstaff (S_code, S_name, S_desig, S_pay)
Client (C_id, C_name, C_address, C_phone, C_city)
Room (R_id, R_type, R_rent, R_booked)
Booking (B_id, C_id, R_id, S_code, B_date)
Write the appropriate SQL commands for the following:
i. Find the details of all the staff whose designation is “Dy. Manager” and who have made booking of executive type room to the client from Pune.
7
ii. List the details of all the rooms which have not been allocated to any client.
iii. Find the names of all the clients who are from “Goa and Delhi” for whom the booking was done in the last 15 days.
Q 3: Explain the significance and usage of views in a relational database
system, with the help of suitable example. Explain any four constraints
of updating a tuple in a view. (8 marks)
Q 4: (a) Define indices and their advantages in RDBMS. Explain the concepts
of clustering and hashing indices, using an example of each. (10 marks)
(b) Consider the relations:
GRADE (stud_id, subject_id, grade)
SUBJECT (subject_id, s_name, teacher)
(i) Write the relational algebraic query for the following:
List the student number, subject names and grades of the student whose id is 100.
List the grades of all the students in the subjects taught by
teacher “XYZ”. (5 marks)
Q5: Explain the role of checkpoints in log-based recovery with the help
of an example. (5 marks)
Q 6: Discuss the shadow paging recovery scheme. Compare this scheme
with the log based recovery scheme. (6 marks)
Q 7: Discuss how the two-phase locking protocol ensures serial ability but
does not ensure deadlock free situation. (5 marks)
Q 8: How does embedded SQL differ from Dynamic SQL with the help of
an example describe the implementation of cursor and triggers. (5 marks)
Q 9: How does Postgre SQL perform storage and indexing of tables? (5 marks)
Q 10: How OLAP supports query processing in dataware house? (5 marks)
IGNOU Coaching for BCA, MCA, MBA in Jaipur
IGNOU JAIPUR
Regional Director,
IGNOU Regional Centre,
70/79,
Sector - 7,
Patel Marg,
Mansarovar
Rajasthan - 302020
India
Ph :+91-0141-2785763 / 2785750
Fax :+91-0141-2784043
