FOURTH SEMESTER DEGREE EXAMINATION, APRIL 2018 

(CUCBCSS—UG)

DATABASE MANAGEMENT SYSTEM & RDBMS

Part A ( Each Question carries 1 Mark )

1. In the three level architecture of a DBMS, the level which describes how data is actually stored is called ___________.
Ans:

2. The person having central control over both data and programs accessing that data is called ___________.
Ans:

3. The person who proposed relational model is ___________.
Ans:

4. ___________ statement is used in SQL to grant privileges to various users
Ans:

5. An expression in tuple relational calculus is of the form ___________
Ans:

6. A ___________ can be thought of as a table in which each tuple is a row, and all rows have the same number of fields
Ans:

7. An ___________ is a condition that is specified on a database scheme, and restricts the data that can be stored in an instance of the database.
Ans:

8. A relation schema R is in ___________ if whenever a nontrivial functional dependency X → A holds in R, then X is a super key of R.
Ans:

9. If all the operations in a transaction submitted to DBMS are completed successfully and their effect is recorded permanently in the database, then the transaction is said to be in ___________ state
Ans:

10. To remove a database schema and all its tables, domains, and other elements, the ___________ option is used along with the DROP command
Ans:

Part B ( Each Question carries 2 Mark )

11. Define logical data independence.
Ans:

12. Explain the syntax to create a view with the help of an example.
Ans:

13. Define BCNF.
Ans:

14. Explain the Two-Phase Locking protocol to ensure serializability.
Ans:

15. When a query language called relationally complete ?
Ans:

Part C ( Each Question carries 4 Mark )

16. Discuss the main categories of data models.
Ans:

17. Define the following:
(a) Entity sets
(b) Relationships
(c) Data Independence
(d) Aggregation
Ans:

18. Consider the following relations:
Emp (eid, ename, age, salary)
Works (eid, did, time)
Department (did, dname, budget, managerid)
Write an SQL DDL statements required to create the above relations with necessary constraints.
Ans:

19. Distinguish between relational algebra and relational calculus.
Ans:

20. Discuss with example various forms of join operations available in relational algebra
Ans:

21. What are the different data types used in SQL ? Explain
Ans:

22. Describe the four important properties of transactions that a DBMS must ensure to maintain the data in a database.
Ans:

23. What is a trigger ? Write an example trigger in SQL to illustrate a real life situation.
Ans:

Part D ( Each Question carries 8 Mark )

24. What are the advantages of using a DBMS ? Explain.
Ans:

25. What are the responsibilities of the DBA and database designers ?
Ans:

26. With the help of examples, discuss about various set operations defined in a relational algebra.
Ans:

27. Explain with example, the three different SQL commands used to modify a database
Ans:

28. Consider the following schema:
Suppliers (sid, sname, address)
Parts (pid, pname, colour)
Catalog (sid, pid, cost)
The Catalog relation lists the prices charged for parts by Suppliers. Write SQL statements for the following queries.
(a) Find the pnames of parts for which there is some suppliers.
(b) Find the pnames of parts supplied by ABC suppliers.
(c) Find the names of suppliers who supply every red part.
(d) Find the sids of suppliers who supply a red part and a green part.
Ans:

29. Consider the following relations:
Employees (ssn, name, Iot)
Departments (did, dname, budget)
Locations (address, budget, capacity)
Works_In (ssn, did, address, since)
Works_In is a ternary relationship connecting entity sets Employee, Departments and Locations. Draw an ER diagram to represent the above scenario. Also write an SQL definition to create a table representing the Works_In relation with all necessary key constraints
Ans:

30. (a) What are the major problems caused by redundancy?
(b) Explain with example, Lossless-join decomposition.
Ans:

31. Write short notes on :
(a) Anomalies associated with interleaved execution of transactions.
(b) Theory of normalization.
(c) Stored procedures in DBMS.
(d) Aggregate functions in SQL.
Ans: