DBMS/CS-402/B. Tech/CSE & IT/4TH/ 2013 Mid-Semester Examination School of Computer Engineering KIIT University, Bhubaneswar-24 Time: 2 Hrs Full Marks: 25 Answer any Four questions, including Question No. 1 which is compulsory 1. a. There are different organizations available for which we are interested to store the unique orgid along with orgname and domain. Each organization is conducting one annual fest (unique festid, festname, budget and theme) in each session. Many people are coming to the annual fest. Each person has unique prid along with prname, age and contact(s). The people can be categorized as either Employee (with salary) or Student (with cgpa) or Staff (with working_hours). Employees are managing Students. Similarly, Staffs are managing Students. Each annual fest contains many events (identified by unique evid along with evname and category). The students are participating in one or more events. Similarly, many students are participating in an event. There are different students treated as event managers for different events. Each event contains different rewards. Each reward has a rewardname and prize. Each reward can be identified by rewardname once the event is known. Draw the entity-relationship diagram for the above system. Make necessary assumption.  b. Convert the above ER diagram into relational schemas.  a. Explain the 3-layered database abstraction with suitable diagram. Highlight the different data independence and mapping in relation to these layers.  b. Discuss the various problems exist in the traditional file systems. Also, explain the various characteristics of a database system.  a. What do you mean by relational data integrity/constraint? Discuss the different types of it.  b. Discuss the steps for converting a ternary relationship set to binary relationship sets in ER modelling with suitable example.  a. Explain the different components of the overall database architecture. b. What do you mean by data dictionary? “Data dictionary is not managed by the user” – Justify. Also discuss its usefulness.  a. Discuss the advantages and disadvantages of Entity-Relationship model, Relational model and Object-Oriented model. Also, justify the statement: “Normally, ER diagram is constructed before the construction of Relational schema and Object-Oriented schema”.  What is the requirement of a key for an entity set? Discuss the various types of keys with suitable examples.  (P. T. O.) 2. 3. 4.  5. b. 6. a. With suitable examples, discuss the different types of database languages. b. Employee (empno, ename, department, age)  Books (ISBN, title, authors, publisher) Borrow (empno, ISBN, date) Solve the following queries by either using SQL or relational algebra.  i. List the employee details who are of 25 years and whose name contains ‘T’ followed by ‘EE’ as part of it.
DBMS/CS-402/B. Tech/CSE & IT/4TH/ 2013 c. ii. Display titles, authors and ISBN of all the books published by either TMH or Pearson or PHI and with title either Database System or Oracle or SQL. Discuss the requirements of ON DELETE CASCADE option in the referential integrity.  ------ ALL THE BEST ------