Punjab Technical University 2010-2nd Sem B.Sc BioInformatics M.Sc(BI) (nd) DATABASE SYSTEMS - Question Paper
Total No. of Questions : 13] [Total No. of Pages : 04
Roll No.......................
J-3262[S-1118] [2037] M.Sc. (BI) (Semester - 2nd) DATABASE SYSTEMS (M.Sc. (BI) - 204)
Time : 03 Hours Maximum Marks : 75
Instruction to Candidates:
1) Section - A is compulsory.
2) Attempt any Nine questions from Section - B.
Section - A
a) Design an ER diagram for all the following relationships. Clearly indicate
the entities, relationships and the key constraints. [6]
(i) Each company operates 4 departments, and each department does one functionality.
(ii) Each department in part (i) employs one or more employees, and each employee is employed by one department.
(iii) Each of the employees in part (ii) may or may not have one or more dependents, and each dependent belongs to one employee.
(iv) Each employee in part (iii) may or may not have an employment history.
|
b) Examine the table shown below : [6] | ||||||||||||
|
(i) Why is the table above not in 1NF?
(ii) Describe the process of normalizing the data shown in the table above to the second normal form (2NF).
(iii) Identify the primary, foreign keys in your 2NF relations.
c) Explain a B-tree with the help of an example. [2]
d) How is a key inserted in a B-tree? [2]
e) Use three reasons for maintaining a data dictionary. [2]
f) Explain the significance of the following. [2]
(i) Application Programming Interface
(ii) Remote Procedure Calls.
g) List three functions of a Database Administrator(DBA). [2]
h) What is the difference between a subquery and a join? Under what circumstances would you not be able to use a subquery? [4]
i) What is the purpose of Null? Is a component of a primary key allowed to accept null? Why? [2]
j) What is a view? Explain what happens when a user accesses a database through a view? [2]
(9 x 5 = 45)
Q2) Suppose you have been designated as a database administrator of an organization. Describe the responsibilities that may be generally assigned to you in order to maintain the centralised database efficiently. Also, include at least 2 responsibilities with respect to the Database security.
Q3) Create an ER diagram for the loan management system of a finance company. Loans are given on the purchase of various items with different interest rates. The company keeps track of defaulters and takes appropriate steps against them. Make, and state, suitable assumptions (if any).
Q4) Explain the three level architecture of DBMS, including the diagram. How is the mapping between these levels achieved?
Q5) Explain the evolution of Client/Server Architecture. List the components and the usage in Client/Server computing. Mention any two applications of this model.
Q6) What are the problems caused by data redundancies? Can data redundancies be completely eliminated when a database approach is used? Explain this with the help of an example.
Q7) Write the significance of the evaluation of a database management system. List the technical and administrative issues respectively, required to be taken care of by the evaluation team.
Q8) Draw a diagram to illustrate the relationship of different file organizations based on an access key. What is direct file organization? Explain the role of hashing in direct file organization with the help of an example.
Q9) Consider the following schema :
employee (e_no, e_name, address, city, basic_sal, job_status) projects (p_no, p_name, p_category) work_in (p_no, e_no, p_duration)
Write appropriate SQL queries for the following :
(a) Display the names of employees who are working in a project on DBMS.
(b) Find the employer number of all employees who are working on at least one project.
(c) Find the average salary of all employees working in a project based in Delhi.
Q10) Describe any three differences between the object oriented data model and the relational data model. In what ways is the object oriented data model similar to the hierarchical and network data models? Mention any two examples in which we can use the object oriented databases and relational databases, with the proper justification.
Q11) Explain the trade-offs in distributing the database. List the issues involved in storing the relations in the distributed databases.
Q12) What are the different relational operators used in relational algebra? Consider the following relations A and B, and perform A U B, A f| B and A - B.
|
A-> |
Flight No. |
Airways |
|
IC 2715 |
Indian Airlines | |
|
IC 1007 |
Jet Airways | |
|
9119 |
Sahara | |
|
IC 3849 |
Indian Airlines | |
|
B-> |
Flight No. |
Airways |
|
IC 1007 |
Jet Airways | |
|
IC 5187 |
Cathay Pacific | |
|
IC 1003 |
Singapore Airlines |
Q13) Discuss the differences between the following file organizations :
(a) Serial
(b) Index-Sequential
(c) Hashed
(d) Inverted
J-3262[S-I118] 4
|
Attachment: |
| Earning: Approval pending. |
