1 (a) What are the activities of the Database Administrator? 7
(b) The TEACHER table has (NAME, DESIGNATION) and TEACHER_ID attributes that distinguish one teacher from another. Which one will be chosen as the Primary Key and why? 5
(c) Explain the difference between external, internal and conceptual schemas. How are these schema layers related to the concepts of logical and physical data independence? 5
(d) What is a transaction? 3
2. A database system has to be created to run the activities of the Departmental library of the Computer Science & Engineering department. The library is used by the students, teachers, research scholars and non-teaching staff of the department, each of whom has a unique Library Card Number. The name of the user is also important in the library. Besides these, for students, class, roll number, registration number and year; for research scholars the guide name (the guide is always a teacher of the same department); for teachers and non-teaching staff designation are also stored in the database. Teachers recommend text and reference books for purchase in the library. It is necessary to keep track of the books that have been recommended but have not yet arrived in the library. A book in the library is characterized by its accession number (different copies of the same book will have different accession number), book number (contains information about the first author of the book), class number (subject-wise classification), author name, book name, category (like DBMS, OS etc.), volume, edition, publisher, year of publication and price. Books are issued to users and the date of issue and the last date of return are stored in the database. When a book is returned the actual date of return is stored along with the fines calculated, if any.
(a) Identify the entity sets present in the above environment along with their attribute sets and primary keys. 7
(b) Identify the relationships present among the entity sets along with their structural constraints. 5
(c) Give the schema of the relational tables designed for the above environment. 8
3. Consider the following tables in the COMPANY database: EMPLOYEE=(first_name, m_initial, last_name, employee_number, birthdate, address, sex, salary, supervisor_empno, depatment_no) DEPARTMENT= (dept_name, dept_no, manager_empno, manager_startdate) PROJECT= (project_name, project_number, project_location, controlling_deptno) DEPT_LOCATIONS= (dept_no, location) WORKS_ON= (employee_no, project_no, hours) DEPENDENT=(employee_no,dependent_name,sex,birthdate, relation_with_employee) The italicized attributes denote the primary key of the table. An employee is assigned to one department but may work on several projects, which are not necessarily controlled by the same department. The number of hours per week that an employee works on each project is also noted.
Write Relational Algebra and SQL statements for the following queries: (2+2)×5
(a) Find the name and address of all employees who work for the HRD department.
(b) Find the names of employees who work on all the projects controlled by Department Number D5.
(c) List the names of all employees with two or more dependents.
(d) List the names of employees who have no dependents.
(e) List the names of managers who have at least one dependent.
(a) Consider a relation schema R = (A,C,D,E,F) with the following set of functional dependencies: C F, E A, BC D, A D Write the algorithm to compute the closure of an attribute set and use it to show that EC is a candidate key for R. 5
(b) Consider the following relation: SUPP_PARTS=(SNO,PNO,QTY,SNAME,SCITY,STATUS) Where, SNO=unique supplier number PNO=unique part number of the item supplied by the supplier (A supplier can supply various parts while a part may be supplied by several suppliers) QTY=quantity of the item (identified by PNO) supplied by the supplier (identified by SNO) pg no(3)
SNAME=unique supplier name SCITY=supplier city STATUS=supplier status (depends on the city of the supplier)
Why the relation is not in 2NF? Decompose the relation so that the decompositions are in 2NF. Are the decomposed relations in 3NF also? If not, why? Generate the final set of relations that are in 3NF. 10
(c) Consider the following table: CTX=(course_id, teacher_id, text_id) A course may be taught by several teachers and may have multiple textbooks. The set of textbooks for a particular course does not depend on the set of teachers who teach that course. Is the relation in 4NF? Justify. 5
5. (a) Define multivalued dependency and 4NF along with examples. 5
(b) What is the lossless join property of a decomposition? 5
(c) Why are transitive and partial dependencies considered bad in a relational database schema? 5
(d) What is template dependency? Discuss with examples. 5
6. (a) Since indices speed query processing, why might they not be kept on several search keys? 3
(b) Why can we have at most one primary or clustering index, but several secondary indices? 3
(c) What are the causes of bucket overflow in a hash file organization? What can be done to reduce the occurrence of bucket overflow? 6
(d) How index files can be created and dropped in SQL? 3
(e) Explain with examples that the LRU strategy is not the optimal strategy for block replacement in Relational Database Systems. 5
7. (a) Consider a disk with block size B=1024 bytes, Block Pointer P=6 bytes long and Record Pointer R=6 bytes long. A file has r=30,000 EMPLOYEE records of fixed length 100 bytes. The size of the primary key EMPNO is 9 bytes long. Consider an unspanned organization. We want to construct a B+ tree access structure (index) on EMPNO.
i) the order p of the tree
ii) the number of leaf level blocks needed if blocks are approximately 69% full
iii) the number of levels needed if internal nodes are also 69% full
iv) the total number of blocks required by the B+ tree, and
v) the number of block accesses needed to search for and retrieve a record from the file-given its EMPNO value- using the B+ tree. 10 (4)
(b) What is a hash structure not the best choice for a search key on which range queries are likely? 3
(c) Why is B*-tree preferred as an access structure to a data file? 4 (d) What are Hash indices? 3
8. Write short notes on any two of the following: 2×10
(a) Division Operation in Relational Algebra
(c) Join operations
(d) Bit-map Indices