Thapar University 2006 B.E Computer Science DataBase Management System - Question Paper
Thapar Institute of Engineering and Technology
B.Tech Computer Science DataBase Management System
End Semester Examination
Thapar Institute of Engineering & Technology, Patiala Computer Science & Engineering Department B.E Third Year, First Semester End Semester Examination Course Code: CS-009 Date:10/12/2006
Course Name: Database Management System Time Allowed: 3 Hrs.
Instructor: Parteek Bhatia Max. Marks: 36
Note: Attempt any six questions. You can see your evaluated answer sheets on 14.12.06
at 5 PM.
How DBMS achieves data abstraction?
in
Why Relational Model is called as Relational?
VL
What is the need of Upgradation of locks? How it is better than a simple Shared-Exclusive Lock Mechanism? Explain with example.
c)
How data warehouse is helpful for our institute? Justify your answer with futuristic applications. Briefly write the way of its implementation._
2 a)
An agency called Rattan Agencies supplies part-time/temporary staff to hotels with in New Delhi. The table shown below list the time spent by agency staff working at various hotels. The National Insurance Number (NIN) is unique for every member of staff.
2 b)
The table is susceptible to update anomalies. Provide examples of Insertion, Deletion and Update anomalies.
|
Describe anc |
illustrate the |
process of normalization t |
e table shown below. | ||
|
NIN |
ContractNo |
Hours |
EName |
Hno |
HLoc |
|
1135 |
C1024 |
16 |
SI |
H25 |
El |
|
1057 |
Cl 024 |
24 |
HI |
H25 |
El |
|
1068 |
C1025 |
28 |
W1 |
H4 |
Gl |
|
1135 |
C1025 |
15 |
SI |
H4 |
Gl |
Consider a bank database having two types of account saving and current. Attributes of Saving account are accno, balance, rate of interest and attributes of current account are accno, balance and overdraft amount. Create table of Saving Account and Current account with the use of ORDBMS. Take the use of objects. Write the syntax for the creation of object and tables._
3 a)
Consider the following database schema:
b)
Employee(ename, city)
Works(ename,company_name, salary)
Company(company_name,city)
Manages(ename,manager_name)
Give the expression in relational algebra to express:
i)The name of all the employees in this database who do not work for BSNL company.
ii)Name of all employees who live in Amritsar and works for SBOP and earns more than Rs. IOjOOO. uu*,i -
What is the importance of multidimensional view of data? Explain with example.
c)
Write a stored procedure or function (which ever is appropriate for the application) to display the top n highest paid employees from emp table. The value of n is passed to stored function or procedure and it will display the name of n highest paid employees.
Emp(empno,enaroetsal)_
|
b) |
Design a generalization-specialization hierarchy for motor vehicle sales company. The company sells motorcycles, passenger cars, van and buses. |
2 | |||
|
c) |
For the database of question 3 (b), write the SQL query for the following: Assume that the companies may be located in several cities. Find all companies located in every city in which Infosys is located. ___________ |
1 | |||
|
5 a) |
Assume that immediate modification is used in a system. Show, by example how an inconsistent database state could result if log recorded for a transaction are not output to stable storage prior to data updated by the transaction being written to disk. |
2 | |||
|
b) |
During a transaction execution, a transaction passes through several states, until it finally commits or aborts. List all possible sequences of states through which a transaction may pass. Explain each state transition may occur. |
2 | |||
|
c) |
Consider the following database: Person(driver_id, name) Car(license, model, year) Accident(report_numbcr, data, location) Owns(driver_id, license) Participated(driverjd, report_number, damage_amount) Solve the following query in SQL: i) Find the total number of people who owned cars that were involved in accident in 2005. .................... ii) Find the year in which maximum accidents occurs. |
2 | |||
|
6 a) |
Explain the importance of Cluster management in Oracle physical storage system. In what case Cluster is preferred and in what case we prefer nonclustered file organization. |
2 | |||
|
b) |
Write short note on Denormalization. |
2 | |||
|
c) |
Write the code with proper syntax to handle any user-defined exception. |
2 | |||
|
7 a) |
What benefit does strict two-phase locking provide? What disadvantages result? |
2 | |||
|
b) |
How does the frequency of the checkpoint affect: i) System performance when no failure occurs? ii) The time it takes to recover from a system crash? iii) The time it takes to recover from a disk crash? |
1.5 | |||
|
c) |
The table shown below lists members of staff(staffName) working in a given ward (wardName). In this example assume that staff name uniquely identifies each member of staff and that the patient name uniquely identifies each patient. Describe why the relation is in BCNF and not in 4NF Describe and illustrate the process of normalizing the relation shown below in 4NF. |
2.5 | |||
|
WardName |
StaffName |
PatientName | |||
|
Pediatrics |
K2 |
PI | |||
|
Pediatrics |
K2 |
B1 | |||
|
Pediatrics |
SI |
PI | |||
|
Pediatrics |
SI |
B1 | |||
|
Attachment: |
| Earning: Approval pending. |
