DBMS/CS-402/CSE & IT/4TH/ 2014 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. Discuss the role & responsibilities of DBA.  b. Explain Aggregation feature of EER diagram with suitable example.  c. Explain two DML commands and two DDL commands.  d. Differentiate between Strong entity set & weak entity set.  2. a. Draw the entity-relationship diagram for the Soccer game as given below:  There are many teams playing matches among themselves. In each match, there is a host team and a guest team. The match takes place in the stadium of host team. Each team has an unique TID along with Tname, Home_stadium and Home_city as attributes. Each team has many players and each player belongs to only one team. Each player is uniquely identified by PID and has Pname, Birth_date, Debut_year and Jersy_no. For each match, we are interested to record the date of play, game result, players participate, number of red cards issued, number of yellow cards issued, substitute players’ list and the times of substitutions. Each match has exactly three referees. Out of these referees, one referee is the main referee and the other two are assistant referees. Each referee has as RID (unique), Rname, Birth_date, Years_of_experience. Make necessary assumptions. b. Convert the above ER diagram into relational schemas.  3. a. Solve the following queries using the relational algebra: [1x4] Doctor (SSN, FirstName, LastName, Speciality, YearOfExperience, PhoneNum) Patient (SSN, FirstName, LastName, Address, DOB, PrimaryDoctor_SSN) Medicine (TradeName, UnitPrice, GenericFlag) Prescription (PId, Date, Doctor_SSN, Patient_SSN) Prescription_Medicine (PId, TradeName, NumOfUnits) i. List the trade name of generic medicine with unit price less than $50. ii. List the first and last name of patients whose primary doctor named ‘John Smith’. iii. List the first and last name of doctors who are not primary doctors to any patient. iv. List the SSN of patients who have ‘Aspirin’ and ‘Vitamin’ trade names in one prescription. b. Discuss the different types of relational data integrity constraints available.
DBMS/CS-402/CSE & IT/4TH/ 2014 4. a. Discuss the various problems exist in the traditional file systems. Also, explain the various characteristics of a database system.  b. Discuss the advantages and disadvantages of hierarchical model and network model. Also, differentiate between two data models.  5. Write Short notes: [3 X 2] a. Extended ER Model b. 3-Layered data abstraction ~~~~~ ALL THE BEST ~~~~~