Database Management Systems - Dec 2014

Computer Science Engg. (Semester 5)

TOTAL MARKS: 100
TOTAL TIME: 3 HOURS (1) Question 1 is compulsory.
(2) Attempt any four from the remaining questions.
(3) Assume data wherever required.
(4) Figures to the right indicate full marks. 1(a) Explain the typical components module of a DBMS,with a neat diagram(10 marks) 1(b) Define the following with examples :
i) value set ii) Complex attribute iii) Data model iv) Schema construct v) Meta data.(10 marks) 2(a) What are structural constraints on a relation type? Examples.(5 marks) 2(b) what is a weak entity type? Explain the role of partial key in design of weak entity type.(5 marks) 2(c) Design an ER diagram for the mail order Database considering the following requirements. Here employee takes order for parts form customers.
i) The mail order company has employees each identified by a unique employee ID, first and last name, Address, Gender, Zip code.
ii) Each customer of the company is identified by unique customer ID, first and last name, Address, Location & zip code.
iii) Each order placed by a customer taken by an employee and is given a unique part number, part name, price & quantity in stock.
iv) Each order placed by a customer taken by an employee and is given a unique order number. Each order contains specified quantities of one or more parts. Each order has a data of receipt as well as an expected ship date. The actual ship date is also recorded.
v) Each customer can place number of orders & each order placed by one customer only.
iv) Each Employees can take any number of orders but each order belongs to only one employee.
vii) Each part placed by number of customers and each customer can place order for number of parts.
viii) Write assumptions made.(10 marks) 3(a) Discuss the entity integrity and referential integrity constraints. Why is each considered important?(5 marks) 3(b) Discuss the various types of JOIN operations. Why is Theta Join required?(5 marks) 3(c) Give the schema :
student (USN, NAME, BRANCH, PERCENTAGE)
Faculty (FID, FNAME, DEPT, DESIGNATION, SALARY)
Course (CID,CNAME,FID)
Enroll (CID,USN,GRADE)
Give the relation algebra expression for the following :
i) retrieve the name and percentage of all students for the course 10CS54.
ii) List the Departments having a average salary of the faculties above Rs. 30,000.
iii) List name of the course having students grade 'A' maximum(10 marks) 4(a) Explain the different constraints that can be applied during table creation in SQL, with an example.(4 marks) 4(b) Write the SQL query for the following Database Schema :
Works (Pname, Cname, salary)
Lives (Pname,Street,City)
located_in (Cname, Mgrname)
i) Find the names of all persons who live in the city Bangalore:.
ii) Retrieve the names of all person of "Infosys" whose salary is between Rs 50(12 marks) 5(a) Explain the syntax of SELCT statement in SQL.(4 marks) 5(b) How is view created and dropped? What problems are associated with updating views?(6 marks) 5(c) Explain the following i) Embedded SQL ii) Database stored procedure.(10 marks) 6(a) What is a functional dependency and who specifies th functional dependency that hold among the attributes of a relation chem?(5 marks) 6(b) Consider R - $$\left \{ A,B,C,D,E,F \right \}.\left \{ A\rightarrow BC,C\rightarrow E,CD\rightarrow EF \right \}.\\Show \ that \ AD\rightarrow F.$$(5 marks) 6(c) Find the key and normalize
book tittle | Auth_name | Book _type | List _price | Affiliation | publication.
Fds are { {\left { Book \ tittle \rightarrow Book \ type,\ Publication\right }
$$Auth_\ name\rightarrow \ Affiliation$$
$$Book \ type \rightarrow \ List \ Price$$}(10 marks) 7(a) Which normal form is based on the concept of multi value functional dependency? Explain the same with example.(10 marks) 7(b) Given relation R with 4 attributes R = (A, B, C, D,) and following Fds. Identify the candidate keys for R and highest normal form.
$$i) C\rightarrow D,C\rightarrow A,B\rightarrow C, II) B\rightarrow C,D\rightarrow A.$$(10 marks) 8(a) Write short notes on the following :
a) Two phase locking protocol.
b) Transaction support in SQL.
c) Time stamp ordering algorithms.
d) Acid properties.(20 marks)