USN 22MCA15
First Semester MCA Degree (Autonomous) Semester End Examination 2023-24
RDBMS
Model Question Paper
[Time: 3 Hours ] [ Maximum Marks: 100]
Instructions to students:
1. Answer FIVE FULL questions as per the choice.
2. Use black ball point pen only for text, figures, tables etc.
RBT
Q No. Questions Marks CO Cognitiv
e Level
Unit – 1
1. a) Discuss on the characteristics of database approach. [10 Marks] CO1 L2
b) Justify the significance of the role played by the actors and workers in a [10 Marks] CO1 L5
DBMS environment.
OR
2. a) Show that the 3-schema architecture helps to achieve data independence. [10 Marks] CO1 L5
b) Demonstrate the various architectures of DBMS. [10 Marks] CO1 L2
Unit – 2
3. a) Illustrate the various types of attributes. [05 Marks] CO2 L2
b) Demonstrate the representation of [05 Marks] CO2 L2
Cardinality ratio
Participation constraints
c) Consider a multi-speciality hospital with a number of departments, rooms, [10 Marks] CO2 L6
doctors, nurses, and other staff working in the hospital. Patients with
different kinds of ailments come to the hospital and are treated by multiple
doctors. Some patients are admitted as in-patients if required. Design and
develop a database for the hospital to maintain the records of various
departments, rooms, and doctors in the hospital. Also maintain records of
the regular patients, patients admitted in the hospital, the treatment
provided, and the concerned doctors.
OR
4. a) Demonstrate the implementation of a weak entity. [05 Marks] CO2 L2
b) Illustrate the degree of relationship. [05 Marks] CO2 L3
b) Design and develop a database to maintain the details of different trains, [10 Marks] CO2 L6
train status, and passengers. The record of train includes its number, name,
source, destination, and days on which it is available, whereas record of
train status includes dates for which tickets can be booked, total number of
seats available, and number of seats already booked.
Unit – 3
5 a) Consider the following schemas: [10 Marks] CO2 L6
Doctor(docid,docname,specialization,experience,dob)
Patient(pid,pname,docid,roomno)
Staff(staffid,sname,designation,basicpay)
Room(roomno,incharge,wardname)
Complete the following task
i. Create the tables with primary, unique and referential keys
ii. Insert atleast 2 tuples for the above schemas.
iii. Display the details of the staff with an increase in the basic pay
by 15%
iii. Add a field name doctor_id to the table ‘Room’
iv. Remove the field dob from doctor
v. Create a table called visiting doctors as a replica of doctor table
and add fields ‘visiting days’, ‘visiting time’,’visiting fees’.
b) Consider the schema of [Link]. 5.a. and [10 Marks] CO2 L6
i. Find the average experience of doctors under each specialization.
ii. Display the name of the doctor, specialization, room incharge, ward
name of each patient.
[Link] the total number of doctors whose specialization starts with “E”
and with an experience of more than 10 years.
[Link] the maximum experience of the doctors treating the patient named
“Deepak”
v. Delete the patients who do not have any assigned doctor.
OR
6 a) With illustrations, show the implementation of various constraints in a [10 Marks] CO2 L6
relation.
b) Consider the following schemas: [10 Marks] CO2 L6
Company(companyid,companyname,location,no_of_employees,date_
of_establishment)
Employee(empid,empname,companyid,designation,basic_pay,total_a
llowances,total_deductions,date_of_birth, date_of_joining)
i. Calculate the average experience of the employees in the company.
ii. Display the age and total salary of each employee.
iii. Display the employees who had been employed from the year of
establishment of the company.
iv. Calculate the average age of employees of each company who had
joined during the year 2000.
v. Find the average number of employees of each company which pays a
basic salary of more than 50000.
Unit – 4
7 a) With illustrations, prove the significance of Data control language CO2 L2
commands. [10 Marks]
b) Consider the schema of Question No. 5a and build queries to illustrate CO2 L1,6
i. Self Join
ii. Equijoin
[10 Marks]
iii. Left Outer Join
iv. Right Outer Join
v. Outer Union
OR
a) For the schema discussed in question 5a and implement the following CO2 L6
operations
i. Create a view to store all the female employees working Account
department in “ABC” location with an average salary of 8000.
ii. From the above view find the minimum and maximum basicpay of
8 female employees. [10 Marks]
iii. From the above view find the average age of employees working in
Accounts department at location “XYZ”.
iv. Display an ordered list of employees based on age of each account and
finance department.
v. Illustrate the implementation of altering and dropping a view
b) Consider the schema of Question No. 5a and build queries to illustrate CO2 L6
i. AND operation
ii. Union
[10 Marks]
iii. Intersect
iv. Minus
v. Like
Unit - 5
a Illustrate the informal guidelines of a relation. Construct a normalized CO3 L3,6
9 relational database model for a hospital management system. [10 Marks]
b Define serializability. Analyse the various types of serializability. CO4 L1,4
[10 Marks ]
OR
a Define a normal form. Apply 3NF to build a database model for a HR CO3 L1,6
10 Management System. [10 Marks]
b Define ACID properties. Demonstrate a transaction model and its states. CO4 L1,2
[10 Marks]