Master in Data Science (SMS, TU) Database Management Systems Question Paper 2081 Nepal

Differentiate network data model from hierarchical data model. [3]

Define relationship set and relationship instance. [3]

Why aliasing in SQL is needed. Show aliasing of a relation in SQL. [1+2]

Describe attribute, relation and tuple with examples. [3]

While converting ER model into relational schema, how the mapping of binary M:N relationship types is done? Illustrate with an example. [2+1]

How tuple calculus is different from domain calculus? For following relation, write tuple calculus statement to retrieve name and roll number of all students who studies at school named MDS. [3+3]

Student(<u>Roll</u>, Name, Address, Sid) School(<u>Sid</u>, Sname, Affiliated_to)

OR

How relational algebra is different from tuple calculus? For following relation, write domain calculus statement to retrieve name and roll number of all students who studies at school named MDS. [3+3]

Student(<u>Roll</u>, Name, Address, Sid) School(<u>Sid</u>, Sname, Affiliated_to)

What is stored procedure? Mention its use. Given following relations, create a stored procedure named checkperson() to ensure that there is a person who is not a student. [1+1+4]

Person(<u>Pid</u>, Pname, Page, Paddress) Student(<u>Roll</u>, Sname, Sage, Pid)

OR

What is trigger? Mention its use. Given following relations, create a before insert trigger on person that will call checkperson() procedure when age of person is greater than age of student. [1+1+4]

Person(<u>Pid</u>, Pname, Page, Paddress) Student(<u>Roll</u>, Sname, Sage, Pid)

Design an ER diagram for following scenario: [6]

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 an 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 zero or many movies. There is an identifying relationship between actor and vanity van. Vanity van has partial attribute van_id.

Consider the following relations for hospital management system: [6]

Doctor(<u>Did</u>, Dname, Dspecialization) Patient(<u>Pid</u>, Pname, Paddress, Mname) Appointment(<u>Did</u>, <u>Pid</u>, Date, Time) Medicine(<u>Mname</u>, Expiry_date, Composition)

Write the SQL and relational algebra statements for following; a) Find name and address of all patients. b) Find name of doctors who have appointment on 2024/04/08. c) Find names of patients and doctors with whom patients have appointments. d) Find the names of patients and the names of medicines taken by them. Also list the number of medicine taken by the patients.

What is DBMS? Describe the characteristics that makes DBMS better than flat file system. [1+5]