Database Management System (New) (2005 Pattern) question papers for Mca pune university
Consider the following table structure and write SQL statements for the following (any five):
WAREHOUSE (Wh-id, location, No-of-bins, phone) CITIES (city, city-add, state) STORED (Wh-id, item-no, Qty-hcld) ITEMS (item-no, desc, weight) ORDERS (ord-no, ord-date, cust-name) ITEMS-ORDERED (item-no, ord-no, qty-ordered)
CUSTOMERS (cust-name, first-ord-date, lived-in-city)
i) The warehouses located in Mumbai
ii) List of items whose weight exceeds 8
iii) Order date of orders made by Mr. XYZ
iv) The warehouse that hold all the items in order QRDL
v) 1 he total quantity of items held by each warehouse.
vi) The items included in order made by Mr. XYZ and held in KOI.KATA
warehouse.
a) Normalize the following upto 3NF. • 15
Cuslomer-id Customer-Name Customer-Address City
Pin Code
Phone number
E-mail
Arrival Date
Departure Date
Arrival Time
Departure Time
Room-No
Room-Type
Room-Rate
Hotel-branch-id
Hotel-Name
Hotel-Address
Hotel-City
Hotel-pin code
Service tax
Bill-No
Bill-Date
Bill-Amount
b) Explain E.F. Codd rules for RDBMS (any five).
2. Explain the architecture of DBMS.
3. Explain Log-based Recovery Techniques in detail.
4. Explain the resolution of M:N Relation in NDM.
5. Explain:
a) Object Oriented Data Model
b) Data Warehousing
6. Consider the following table structure and solve any 5 SQL queries:
Student (Roll-No, Name, Address. Course-id)
Course (Course-id, Course-name, fees, duration) Faculty (Faculty-id. Faculty-Name, Qualification) Faculty-Course (Course-id, Faculty-id)
a) List total number of students enrolled for "MCA".
b) List the course for which "Mr. ABC" is a teaching faculty.
c) Display coursewise total collection.
d) Name the faculty who is teaching for maximum courses.
e) List names of students enrolled for "MCA" course and having "Mr xyz" as a teaching faculty.
f) Display name(s) of course with minimum students enrolled.
7. Write a short note on (any two):
a) Serializibility.
b) Knowledge Management System.
c) Data Mining.
I. The management of Kartik Hospital has decided to computerize their operations. The following information is provided by the management. There are resident, full lime and consulting doctors, with various specialization consulting doctors visit hospital at a fixed time every day or some days of the week. Which varies from doctor to doctor. The visiting charges too vary from doctor to doctor.
Patients are admitted to hospital and their main cause of admission is recorded. For accident cases, additional information such as Police buckle no., name of the police and accident description is recorded.
A patient is admitted in to a room which has certain catagory and having fixed charge per day.
Normalize the above case upto 3NF. 20
Explain various concurrency control protocols. 10
. Explain the resolution of M : N Relation in NDM. 10
. Explain what is a transaction and it's various states. Also explain the properties of a
transaction. 10
. Explain E.F. Codd rules for RDBMS. 10
Write short notes on (any two) : 10
a) Data Mining
b) Data Independence
c) Referential Integrity
d) Object oriented data model.
7. Consider ihe following (able structures and write SQL statements for the following (any 5):
WAREHOUSE (wh-id, location, no-of-bins, phone)
CITIES (city, city-add, state)
STORED (wh-id, item-no, qty-held)
ITEMS (item-no, discription, weight)
ORDERS (ord-no, ord-date, cust-name)
ITEMS-ORDERED (item-no, ord-no, qty-ordcred)
CUSTOMERS (cust-name, first-ord-datc, lived-in-city) ) The warehouse located in Pune. ) The list of items whose weight is less than 10. ) Dates of all orders made by Mr. Shah.
iv) The warehouse which hold all the items in order ORD 9.
v) The total quantity held by each warehouse.
vi) The location of warehouse holding Electrode item.
1. ABC Telecom Ltd. has launched its Mobile Services in Pune. Subscription for
its services will open for customers form Jan. 2009, the following procedure is
proposed by the authorities.
1) At present there are 3 schemes for subscription.
2) Pune region is divided into 10 area sales offices.
3) Customer can collect subscription forms from any sales office.
4) As per subscription type, payment by DD/cheque can be submitted.
5) Forms are verified, subject to realization of payment, customer is informed about mobile number later by a letter.
6) Customer picks up equipment from sales office.
Represent the above case through an ER diagram and Normalize upto 3 NF.
2. Compare the resolution of M : N relation in NDM and HDM.
3. Explain "dead lock" in detail. How it is detected and prevented ?
4. Explain Lock based protocols in concurrancy control.
5. Explain Architecture of DBMS.
10
6. Write short note on (any 2) :
a) Dataware house
b) Codd's Rule
c) Security and Privacy Mechanism for databases
d) .
7. Consider the following table structure and write SQL statements for the following
(any 5) : 10
EMPLOYEE (cmp.name, street, city)
WORKS (cmp - name, company-name, salary)
COMPANY (company - name, city)
MANAGER (Emp - name. Manager - name)
a) Find the company with smallest pay scale
b) Find the names, street address and cities of residence of all employees who work for ABC Co. and earn more than INR 35000 per month.
c) Find the names of all employees who work for XYZ Co.
d) Find the names of cities of residence of all employees who work for XYZ Co.
e) Count the number of employees staying in Pune but their company is in Bombay.
0 Find employees who do not work for XYZ Co.
1. JET airways is in the process of computerizing its passenger reservation system. During a JAD session, following data items have been identified: reservation code, flight no., flight date, origin, destination, departure time, arrival time, passenger name, seat no., reservation agent no., reservation agent name, total
seats. Normalize the above case upto 3 NF.
2. Explain various recovery techniques.
3. Explain the resolution of M : N relation in NDM.
4. Explain E.F. Codd rules for RDBMS.
5. Explain various locking techniques.
6. Write short note on (any two):
a) Architecture of Datawarehouse
b) Security and Privacy mechanism for Databases
c) ACID Properties d)Data Independence.
Consider the following table structure and write SQL statements for the following (any five):
WAREHOUSE (Wh-id, location, No-of-bins, phone) CITIES (city, city-add, state) STORED (Wh-id, item-no, Qty-hcld) ITEMS (item-no, desc, weight) ORDERS (ord-no, ord-date, cust-name) ITEMS-ORDERED (item-no, ord-no, qty-ordered)
CUSTOMERS (cust-name, first-ord-date, lived-in-city)
i) The warehouses located in Mumbai
ii) List of items whose weight exceeds 8
iii) Order date of orders made by Mr. XYZ
iv) The warehouse that hold all the items in order QRDL
v) 1 he total quantity of items held by each warehouse.
vi) The items included in order made by Mr. XYZ and held in KOI.KATA
warehouse.
a) Normalize the following upto 3NF. • 15
Cuslomer-id Customer-Name Customer-Address City
Pin Code
Phone number
Arrival Date
Departure Date
Arrival Time
Departure Time
Room-No
Room-Type
Room-Rate
Hotel-branch-id
Hotel-Name
Hotel-Address
Hotel-City
Hotel-pin code
Service tax
Bill-No
Bill-Date
Bill-Amount
b) Explain E.F. Codd rules for RDBMS (any five).
2. Explain the architecture of DBMS.
3. Explain Log-based Recovery Techniques in detail.
4. Explain the resolution of M:N Relation in NDM.
5. Explain:
a) Object Oriented Data Model
b) Data Warehousing
6. Consider the following table structure and solve any 5 SQL queries:
Student (Roll-No, Name, Address. Course-id)
Course (Course-id, Course-name, fees, duration) Faculty (Faculty-id. Faculty-Name, Qualification) Faculty-Course (Course-id, Faculty-id)
a) List total number of students enrolled for "MCA".
b) List the course for which "Mr. ABC" is a teaching faculty.
c) Display coursewise total collection.
d) Name the faculty who is teaching for maximum courses.
e) List names of students enrolled for "MCA" course and having "Mr xyz" as a teaching faculty.
f) Display name(s) of course with minimum students enrolled.
7. Write a short note on (any two):
a) Serializibility.
b) Knowledge Management System.
c) Data Mining.
I. The management of Kartik Hospital has decided to computerize their operations. The following information is provided by the management. There are resident, full lime and consulting doctors, with various specialization consulting doctors visit hospital at a fixed time every day or some days of the week. Which varies from doctor to doctor. The visiting charges too vary from doctor to doctor.
Patients are admitted to hospital and their main cause of admission is recorded. For accident cases, additional information such as Police buckle no., name of the police and accident description is recorded.
A patient is admitted in to a room which has certain catagory and having fixed charge per day.
Normalize the above case upto 3NF. 20
Explain various concurrency control protocols. 10
. Explain the resolution of M : N Relation in NDM. 10
. Explain what is a transaction and it's various states. Also explain the properties of a
transaction. 10
. Explain E.F. Codd rules for RDBMS. 10
Write short notes on (any two) : 10
a) Data Mining
b) Data Independence
c) Referential Integrity
d) Object oriented data model.
7. Consider ihe following (able structures and write SQL statements for the following (any 5):
WAREHOUSE (wh-id, location, no-of-bins, phone)
CITIES (city, city-add, state)
STORED (wh-id, item-no, qty-held)
ITEMS (item-no, discription, weight)
ORDERS (ord-no, ord-date, cust-name)
ITEMS-ORDERED (item-no, ord-no, qty-ordcred)
CUSTOMERS (cust-name, first-ord-datc, lived-in-city) ) The warehouse located in Pune. ) The list of items whose weight is less than 10. ) Dates of all orders made by Mr. Shah.
iv) The warehouse which hold all the items in order ORD 9.
v) The total quantity held by each warehouse.
vi) The location of warehouse holding Electrode item.
1. ABC Telecom Ltd. has launched its Mobile Services in Pune. Subscription for
its services will open for customers form Jan. 2009, the following procedure is
proposed by the authorities.
1) At present there are 3 schemes for subscription.
2) Pune region is divided into 10 area sales offices.
3) Customer can collect subscription forms from any sales office.
4) As per subscription type, payment by DD/cheque can be submitted.
5) Forms are verified, subject to realization of payment, customer is informed about mobile number later by a letter.
6) Customer picks up equipment from sales office.
Represent the above case through an ER diagram and Normalize upto 3 NF.
2. Compare the resolution of M : N relation in NDM and HDM.
3. Explain "dead lock" in detail. How it is detected and prevented ?
4. Explain Lock based protocols in concurrancy control.
5. Explain Architecture of DBMS.
10
6. Write short note on (any 2) :
a) Dataware house
b) Codd's Rule
c) Security and Privacy Mechanism for databases
d) .
7. Consider the following table structure and write SQL statements for the following
(any 5) : 10
EMPLOYEE (cmp.name, street, city)
WORKS (cmp - name, company-name, salary)
COMPANY (company - name, city)
MANAGER (Emp - name. Manager - name)
a) Find the company with smallest pay scale
b) Find the names, street address and cities of residence of all employees who work for ABC Co. and earn more than INR 35000 per month.
c) Find the names of all employees who work for XYZ Co.
d) Find the names of cities of residence of all employees who work for XYZ Co.
e) Count the number of employees staying in Pune but their company is in Bombay.
0 Find employees who do not work for XYZ Co.
1. JET airways is in the process of computerizing its passenger reservation system. During a JAD session, following data items have been identified: reservation code, flight no., flight date, origin, destination, departure time, arrival time, passenger name, seat no., reservation agent no., reservation agent name, total
seats. Normalize the above case upto 3 NF.
2. Explain various recovery techniques.
3. Explain the resolution of M : N relation in NDM.
4. Explain E.F. Codd rules for RDBMS.
5. Explain various locking techniques.
6. Write short note on (any two):
a) Architecture of Datawarehouse
b) Security and Privacy mechanism for Databases
c) ACID Properties d)Data Independence.
