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

What is database? List the characteristics of database system. (2+1)

What is data independence? How three schema architecture ensures logical and physical data independence? (1+2)

Create a relation of your choice and illustrate the concepts of attribute, tuple, and primary key. (3)

Why aliasing in SQL is needed. Show aliasing of relation in SQL query. (1+2)

What is trigger? Illustrate with example, how can you define before trigger on insert operation. (1+2)

What do you mean by normalization? Discuss 2NF and 3NF with examples. (2+4)

OR

What is functional dependency? Discuss 1NF and 2NF with examples. (2+4)

What is assertion? Mention its use. Given following relations, create an assertion to ensure that there is a person who is student. (1+1+4)

Person(pid, pname, dob, padd) Student(roll, pid)

OR

How assertion differ from trigger? Given following relation, create an assertion to ensure that there is no person with name starting with "An" and dob = 2020. (2+4)

Person(pid, pname, dob, padd)

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 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 containing airline flight information. (6)

Flights(flno, from, to, flight_distance, departs_at, arrives_at, aid) Aircraft(aid, aname, cruising_range) Certified(pid, aid, date, certified_by) Pilot(pid, pname, psalary)

Write the SQL statements for following:

a) Find all aircrafts. b) Find the name of flights that arrive at 1:00 PM. c) Find the names of certified pilots d) Find names of aircrafts that can be used on flights from KTM to DHI. e) Find the average salary of pilots. f) Use left outer join between Aircraft and Certified.

Consider the following relations containing airline flight information. (6)

Flights(flno, from, to, flight_distance, departs_at, arrives_at, aid) Aircraft(aid, aname, cruising_range) Certified(pid, aid, date, certified_by) Pilot(pid, pname, psalary)

Write the Relational Algebra statements for following:

a) Find all aircrafts. b) Find the name of flights that arrive at 1:00 PM. c) Find the names of certified pilots d) Find names of aircrafts that can be used on flights from KTM to DHI. e) Find total number of pilots. f) Use natural join between Aircraft and Certified.