Visit : Civildatas.blogspot.in

1   

www.universityquestions.in DEPARTMENT OF COMPUTER APPLICATIONS

BRANCH: MCA SEMESTER : I SUBJECT CODE & TITLE: MC7103 DATABASE MANAGEMENT SYSTEM QUESTION BANK

Ci

vil

da

tas

.bl

og

sp o

t.in

UNIT I Part A (2 Marks) 1) What is logical data independence? 2) List out the functionalities of DBA. 3) What is data dictionary? 4) Define Super Key with an example 5) What is a candidate key? Give example. 6) What is the difference between a key and a superkey? 7) Give distinction between primary key, candidate key and super key 8) What is physical, logical and view level abstraction 9) Mention the two basic parts of any database language 10) What is meant by data independence? Explain it. 11) Define schema and subschema. Give examples 12) What do you understand by the term relational system? Distinguish between relational and non-relational systems 13) What are the duties and responsibilities of a DBA? 14) Define Database Management System and Data Model. 15) Mention the significance of super key and candidate key in E-R model. 16) Define aggregation 17) Draw the different mapping cardinality for a binary relationship set R between entity sets A and B 18) What is schema? 19) List the disadvantage of file system over DBMS. 20) Distinguish between strong entity and weak entity set with an example UNIT I Part B (16 Marks) 1) Explain the architecture of a DBMS with diagrammatic illustration 2) Model an E-R diagram for a library management system. State the functional requirements you are taking into consideration 3) Explain in detail about Database Management System Structure 4) Discuss about E-R model with an example 5) Explain the following features of the ER model each with an example i) Participation Constraints (6) ii) Weak Entities (5) iii) Class Hierarchies (5) 6) Explain the architecture of DBMS and its advantages? State the two main disadvantages of DBMS?

www.universityquestions.in Visit : Civildatas.blogspot.in

www.universityquestions.in

Visit : Civildatas.blogspot.in

2 

 

i)Compare file processing systems with database systems (8) ii) Explain the three level architecture proposed by ANSI/SPARC (8) 8) i) Explain the overall system structure for database systems (8) ii) How will you draw E-R diagrams? Explain the method of converting E-R Diagrams into tables (8) 9) An education database contains information about an in-house company education training scheme. For each training course the database contains details of all prerequisite courses for that course and all offerings for that course; for each offering, it contains details of all teachers and all student enrollments for that offering. The database also contains information about employees. The relevant relvars are as follows COURSE {COURSE#, TITLE} PREREQ {SUP_COURSE#, SUB_COURSE#} OFFERING {COURSE#,OFF#,OFFDATE,LOCATION} TEACHER {COURSE#,OFF#, EMP#} ENROLLMENT {COURSE#, OFF#, EMP#, GRADE} EMPLOYEE {EMP#, ENAME, JOB} In PREREQ the superior course ( SUP_COURSE#) has the coordinate course(SUB_COURSE#) as an immediate prerequisite. Draw an E-R diagram for the education database. (16) 10) With the help of a neat schematic diagram, explain the functional components of a database system and give the overall database system structure

sp o

t.in

7)

.bl

og

UNIT II Part A (2 Marks)

Ci

vil

da

tas

1) Define view in SQL. 2) What are the advantages of using PL/SQL? 3) Give one example for the SQL features for testing empty relations 4) What is dynamic SQL? 5) What is the function for Natural join operation? Give an example 6) Define Tuple Relational Calculus. 7) What are the features of variable length record? 8) List the fundamental and additional operators in relational algebra 9) List the four types of file organizations 10) Define Normalization 11) Explain Functional Dependencies. 12) Write short notes on BCNF. 13) What is the difference between 3NF and BCNF? 14) Write the entity integrity and referential integrity rules. Give suitable examples 15) Explain universal and existential quantifiers in relational calculus 16) Let ‘A’ be a relation of degree n. How many different projections of A are there? Justify 17) Differentiate procedural and non-procedural query languages. 18) Define triggers and assertions. 19) Write a domain relational calculus queries to find the loan number, branch, name and amount for loans of over Rs 20,000. 20) What is data striping? UNIT II Part B (16 Marks)

www.universityquestions.in Visit : Civildatas.blogspot.in

www.universityquestions.in

Visit : Civildatas.blogspot.in

3 

 

Ci

vil

da

tas

.bl

og

sp o

t.in

1) Explain Cartesian product, natural join, left outer join, right outer join and full outer join in relational algebra with examples 2) Explain with examples in SQL the following i. Aggregate functions (8) ii. GROUP BY (2) iii. ORDER BY (2) iv. Trigger. (4) 3) Explain the following operations in Relational Algebra i. Select operation (4) ii. Project operation (4) iii. Union operation (4) iv. Cartesion- Product operation (4) 4) What is trigger? Explain in detail with an example 5) Explain 1NF, 2 NF and 3 NF with examples. 6) Explain in detail about Third and Fourth Normal Form with suitable example 7) For the Employee Database with the following entities and attributes Employee : Name, Ssn, Addr,Sal, Supussn, Dno Project : Pname, Pno, Plocation, Dnum Works on : Essn, Pno, Hours Dependent : Essn, Dep_name, Sex, Bdate i) Write a relations algebraic expression to retrieve the names of the employee who work on all projects that “john smith” works on (4) ii) Write a relational algebraic expression to retrieve name of employees who have no dependents. (4) iii) Write SQL query for each project on which more than two employees work, retrieve the project no, project name, no of employees who work on the project. (4) iv) Write a Tuple Relational Calculus to retrieve name and address of all employees who work for the ‘research’ department. (4) 8) i) Write a PL/SQL procedure to list the total hours worked by each employee, arranged in order of department number and within department, alphabetically by employee name (8) ii) Write a PL/SQL function to list the employees if their hours worked goes 10 hours/week (8) 9) Explain the 7 RAID levels in detail i) Define primary key, foreign key, candidate key and alternate key. Explain them using examples (6) ii) List the operators used in relational calculus with examples (10) 10) i)Explain the operators used in relational calculus with examples (8) ii) What are triggers? Explain the method of creating and firing triggers using samples triggers. (8) 11) i)Explain the merge-sort algorithm using an example (6) ii) List the query optimization techniques used in database management systems and explain them. (10) 12) i) Define normalization 1NF, 2NF, 3NF and BCNF. Explain them (8) ii) Explain the techniques used in database tuning (8) 13) i) Explain the Codd’s rule for relational database system

(8)

www.universityquestions.in Visit : Civildatas.blogspot.in

www.universityquestions.in

Visit : Civildatas.blogspot.in

4 

 

tas

.bl

og

sp o

t.in

ii) Explain the sequence of steps involved in retrieving a particular external record occurrence. (8) 14) i) Write a procedure that reads data from a table called bill containing bill number, bill date, bill amount and number of days from bill date within which bill is to be paid. The procedure should find total number of bills, which are to be paid and the total amount to be paid today. (8) ii) What are the different types of triggers and their uses? Write a trigger. Which will disallow user to change the table name PAYROLL in any way on Sunday or Saturday (8) 15) i)What is the highest normal form of each of the following relations? R1(A,B,C) with A->B, A->C, C->B R1(A,B,C,D) with A->BC,CD->B R1(A,B,C,D) with A->BC, E->A, CD->E Find the candidate keys for each of these relations. (8) ii) Find a 3NF decomposition of the following relations scheme: ( Faculty, Dean, Department, Chairperson, Professor, Rank, Student). The relation satisfies the following functional dependencies ( and any others that are logically implied by these): Faculty-> Dean, Dean->Faculty, Department-> Chairperson, Professor -> Bank Chairperson, Department -> Faculty, Student-> DepartmentFacultyDean, ProfessorRank->DepartmentFaculty. (8) 16) Explain the procedure of Normalization in detail. (Take an example and explain up to the level of BCNF). 17) Write in detail about the normalization of a database, upto the BCNF, giving an example of your own. 18) Discuss about the fundamental operations in Relational algebra. Give one example for each. 19) Write a detailed note on domain relational calculus with sample query expression

da

UNIT III Part A (2 Marks)

Ci

vil

1) What is concurrency control? 2) What is meant by Equivalence Rules? 3) Define Two Phase Locking Protocol. 4) Write a short note on Cascading Roll back. 5) What is Query optimization? What are the different techniques used in it. 6) Mention the properties of transaction. 7) What is a recovery scheme? What is meant by log based recovery? 8) How will you estimate the cost of a query? 9) Define role based access control and explain it. 10) What is meant by serializability in a transaction? 11) What are the principal methods used for dealing the deadlock problems? 12) Can deadlock occur in a serializable schedule? If yes, explain with example 13) Write the properties of a good encryption technique to secure the database 14) State the ACID properties 15) Specify the two modes of locking a data item in the database. 16) List down the fields of a database log. 17) List the steps involved in processing a Query. 18) What is the need for evaluation plans? List. 19) Write a Transaction that transfers Rs100 from account B to account C

www.universityquestions.in Visit : Civildatas.blogspot.in

www.universityquestions.in

Visit : Civildatas.blogspot.in

5 

 

20) List the states of a transaction.

vil

da

tas

.bl

og

sp o

t.in

UNIT III Part B (16 Marks) 1) Explain with an example the steps involved in processing a query 2) i)What is meant by the concurrent execution of database transactions in a multi-user system? Discuss why concurrency control is needed? Give suitable example. (8) ii) Explain two phase locking protocol with an example (8) 3) Draw a state diagram and discuss the typical states that a transaction goes through during execution. 4) Explain with examples read only transaction and read write transaction. 5) What is crash recovery? Discuss. 6) Discuss the evaluation plan in detail 7) Explain the following terms i) Serializability (10) ii) Recoverability (6) 8) Explain the following terms i) Graph based Protocols (8) ii) Time Stamp Protocols (8) 9) Given R(A, B, C, D, E) with the set of FD’s F(AB->CD, ABC->E,C->A}. Find any two candidate keys of R and What is the normal form of R? Justify. 10) Given R(A, B, C, D, E) with the set of FD’s F{AB->CD, A->E,C->D} Is the decomposition of R into R1{A, B,C}, R2(B,C,D) and R3(C,D,E) lossless? Prove. 11) Explain Query Evaluation and Query Optimization involved in query processing 12) Explain in detail the two types of phase locking 13) Discuss about log based recovery in detail 14) i) State and explain the two-phase locking protocol for concurrency control (8) ii) Explain the time-stamp ordering protocol used for concurrency control (8) 15) i) Explain the log based recovery techniques (8) ii) Explain the shadow paging technique used for database recovery (8)

Ci

16) A relation R has attributes A, B, C, D, E, F, G, H, I, J and satisfies the following functional dependencies ABD->E, AB->G, B->F, C->J, CJ->I, G->H i) What are the candidate keys? Is this an irreducible set of functional dependencies? ii) Compare the two log-based recorvery scheme in terms of ease of implementation and overhead cost iii) What properties a transaction must have? Define each briefly. (5+5+6) 17) i) What is concurrent control? How is it implemented in DBMS? Explain. (8) ii) Explain various recovery techniques during transaction in detail. (8) 18) Explain the security measures to be considered at the levels of physical, human, operating system and database system. 19) Discuss about deadlock detection and recovery. 20) Write in detail about the shadow paging technique of crash recovery

UNIT IV Part A (2 Marks)

www.universityquestions.in Visit : Civildatas.blogspot.in

www.universityquestions.in

Visit : Civildatas.blogspot.in

6 

 

sp o

t.in

1) Define Sparse indices. 2) Give the difference between Dense index and Sparse index. 3) What do you mean by dense indices? 4) Define hash indices 5) What are Bitmap indices? 6) What do you mean by hashing? 7) Differentiate open hashing and closed hashing 8) Distinguish between sequential files and indexed files. 9) Explain the division remainder method used for hashing 10) Explain why the allocation of records to blocks affects database-system performance significantly 11) Discuss the techniques for allocating file blocks on disk 12) List the various physical storage media and mention their hierarchy. 13) Narrate the parameters used for evaluation of indexing and hashing techniques 14) List two factors to determine the reliability of storage media. 15) What are the factors to be considered for dynamic hashing of files? 16) List the issues in modifying the fields of variable length records. 17) List the major disadvantages of keeping organizations information in a file processing system

Ci

vil

da

tas

.bl

og

UNIT IV Part B (16 Marks) 1) Describe in detail about sequential file organization and random file organization 2) Explain B+ Tree Index File and B Tree index files with examples 3) Explain the following terms (4) i) Byte String Representation ii) Fixed Length Representation (4) iii) Sequential File Organization (4) iv) Clustering File Organization (4) 4) Describe about B+ Tree Index files 5) Construct a B+ Tree for {1, 4, 7, 10, 17, 21, 31, 25, 19, 20, 28, 42} with n = 4. 6) i) Explain the file representation techniques (8) ii) Explain the working of magnetic tapes and magnetic disks (8) 7) i) Explain the B-tree indexing method (8) (8) ii) Explain dynamic hashing with an example 8) i) Explain the different properties of indexes in detail (8) (8) ii) Explain various hashing techniques 9) Explain Tree Structured indexing with an example (16) 10) Explain the file organization with fixed-length records and variable-length records 11) Discuss the algorithm for the creation of a B+ tree index file 12) Discuss about the various data models and drawbacks in file processing system

1) 2) 3) 4) 5)

UNIT V Part A (2 Marks) List out the elements associated with object Give example for Object-identity and reference types in SQL What is object relational databases? Give example for Type inheritance Give example for Table inheritance

www.universityquestions.in Visit : Civildatas.blogspot.in

www.universityquestions.in

Visit : Civildatas.blogspot.in

7 

 

Ci

vil

da

tas

.bl

og

sp o

t.in

6) What is persistent programming language? 7) How Persistent Programming Languages distinguished from Languages with embedded SQL 8) What is the need for Persistence of Objects? 9) Explain persistence by class 10) Explain persistence by creation 11) Explain persistence by marking 12) Explain persistence by reachability 13) What is Intra procedures? 14) What is Intra program? 15) What is Inter program? 16) What is Persistent? 17) Expand XML. 18) What is a markup Language? 19) Give the structure of XML data 20) Expand DTD 21) What is database schema 22) Explain XML Document Schema 23) What is database tuning? 24) State need for database tuning. 25) 3) List some of the useful statistical information about database relations as maintained by the DBMS catalog. UNIT V Part B (16 Marks) 1) Explain the following with example i) Object-identity and reference types in SQL (8) (8) ii) Implementation of O-R features 2) Write short notes on i) Persistence of Object (8) (8) ii) Object-identity and pointers 3) Write short notes on i) Object-identity and pointers (8) (8) ii) Storage and Process of persistent objects (8) 4) i) Compare Object Oriented databases and Object Relational databases. (8) ii) Explain structure of XML data with example 5) Explain i) Temporal Query Languages (4) ii) Spatial and Geographical Database (12) 6) Explain indexing of spatial data i) K-d trees (6) (5) ii) Quad trees iii) R-Trees (5) 7) Write short notes on Mobile and Personal Data bases 8) Design a database for a Mobile application 9) Design a database to store the details of agriculture area in tamilnadu 10) Design a spatial database for storing the visible surface of an object 11) i)Explain about the “Decision Support System” (8) ii) Distinguish between data warehouses and data mart. (8)

www.universityquestions.in Visit : Civildatas.blogspot.in