Questions
Syllabus
Qns. By Syllabus

Semester

Subject

Year

Bachelors Level/Second Year/Third Semester/Science bit/third semester/database management system/syllabus wise questions

Bachelors In Information Technology

Institute of Science and Technology, TU

Database Management System (BIT202)

Year Asked: Model, syllabus wise question

Data Modeling Using the Entity-Relational Model
1.
What do you mean by weak entity and partial key? Design an ER diagram for following scenario; In film industry, producers produce movies. Producers have their name, age and budget as attributes. They are uniquely identified by prod_id. All the movies have their title, year, and release date. No movies can have same title. Every movies must be played by actor. An actor can play many movies. Actors have Fname and Lname to uniquely identify them. The actors have charge_rate as well. A single movie can have many producers and a producer can produce many movies. [3+7]
2.
What do you mean by generalization specialization in EER? Illustrate with examples. [5]
3.
During ER-to-Relational Mapping, show how you map 1:N and N:M relationship into a relation? [5]
Database Concepts and Architecture
1.
Consider a database system with following schemas; Hospital(hname, haddress, hspecilaity) Doctor(did, dname, dspecilization, ) Worksat(did, hname, workinghrs) Pharmacy(phname, hname, no_of_sales, total_revenue) Now write SQL statements and relational algebra statements for following queries: a. Select name of all doctors having specialization 'gyno', b. Select the name and address of hospital where working hours is 'day', c. Using natural join select the name of doctors whose working hours are 'night', d. Find the average salary of the doctors, e. Find names of hospital and their pharmacy which generate revenue more than 10000. Sort the result in descending order on the basis of pharmacy name. [10]
2.
What is data independence? How three schema architecture ensures logical and physical data independence? [5]
Database Recovery Techniques
1.
What is shadow paging? How it is used for database recovery? [5]
NoSQL
1.
Differentiate structured and unstructured data. List the advantages of NoSQL. [5]
Relational Database Design
1.
Why normalization is needed in database design? Describe third normal form with an example. [5]
SQL
1.
Define Outer Join in SQL. Given following relations, show the results of left and right out Joins.

$\begin{array}{|c|c|c|c|}\hline \text{Eid} & \text{Ename} & \text{Address} & \text{Dno} \\ \hline 1 & \text{Ram} & \text{KTM} & 111 \\ 2 & \text{Rita} & \text{PKR} & 222 \\ 3 & \text{Hari} & \text{KTM} & 333 \\ \hline \end{array}$

$\begin{array}{|c|c|}\hline \text{Dno} & \text{Dname} \\ \hline 111 & \text{HRM} \\ 222 & \text{Admin} \\ 444 & \text{Account} \\ \hline \end{array}$
[5]
The Relational Data Model and Relational Database Constraints
1.
Define domain constraint and referential integrity constraint. [5]
Transaction Processing and Concurrency Control, and Recovery
1.
Define serial, non-serial and serilizable schedules with example. How can you test serializability in a schedule, illustrate with an example? [5]
2.
Show how lost update and incorrect summary problem might occur in concurrent execution of transactions. [5]