A7-R3: INTRODUCTION TO DATABASE MANAGEMENT SYSTEMS • •
JANUARY-2006 JULY-2004
JANUARY- 2006
TOTAL TIME: 3 HOURS
TOTAL MARKS: 100 (PART ONE-40; PART TWO – 60) PART ONE (Answer all the questions)
1.
Each question below gives a multiple choice of answers. Choose the most appropriate one and enter in the “tear-off” answer sheet attached to the question paper, following instructions therein. (1 x 10)
1.1 A) B) C) D)
Pick the odd one out Primary key Super key Candidate key Foreign key
1.2 A) B) C) D)
Relational Algebra is Data Definition Language Meta Language Procedure query language Non procedural language
1.3 A) B) C) D)
One of the following is a valid record-based data models Object-oriented model Relational model Entity-relationship model None of the above
1.4 A) B) C) D)
One of the following steps is not involved in processing a query Parsing and translation Optimization Evaluation Distribution
1.5 A) B) C) D)
Which one of the following describes the timestamp-based protocols correctly? This protocol requires that each transaction issue lock and unlock requests in two phases. This protocol employs only exclusive locks. This protocol selects an ordering among transaction in advance. None of the above
1.6 A) B) C) D)
Which one of the following is not a valid relational database? SYBASE ORACLE IMS UNIFY
1.7 A) B) C) D)
4NF is designed to cope with transitive dependency join dependency multi valued dependency none of these
1.8 A) B) C) D)
Which one of the following is a valid join type? natural full outer join on using
1.9 A) B) C) D)
Which one of the following is not a valid aggregation function in SQL? avg min where sum
1.10 A) B) C) D)
Which of the following is not a valid unary operation in the relational algebra? select min project rename
2.
Each statement below is either TRUE or FALSE. Choose the most appropriate one and ENTER in the “tear-off” sheet attached to the question paper, following instructions therein. (1 x 10)
2.1 2.2 2.3 2.4 2.5 2.6 2.7
A relationship is an association among several entities. Physical data models are used to describe data at the highest level. QBE is based on the tuple relational calculus. The database schema and the database instance are the same thing. Functional dependencies are constraints on the set of legal relations. Integrity constraint guard against accidental damage to the database. One-way to ensure serializability is to require that access to data items be done in a mutually | exclusive manner. The cost of processing a query is not dependent on disk access. The recovery scheme does not depend on the concurrency control scheme. Deadlocks can be described precisely in terms of a directed graph.
2.8 2.9 2.10 3.
Match words and phrases in column X with the closest related meaning/ word(s)/phrases in column Y. Enter your selection in the “tear-off” answer sheet attached to the question paper, following instructions therein. (1 x 10)
3.1
X Dense index
A.
3.2
Transaction
B.
Query language based on both the relational algebra and the tuple relational calculus
3.3
Shadow Paging
C.
The index structure is the most widely used to several index structures that maintain their efficiency despite insertion and deletion of data
3.4
Referential integrity constraint
D.
A record appears for every search key value in the file
3.5
Committed
E.
A recovery technique
3.6
B+ tree index
F.
This ensures that a value that appears in one relation for a given set of attributes also appears for a certain set of attribute in another relation
3.7
Network Model
G.
The successful completion of a transaction
3.8
Entity
H.
3.9
DMI
I.
A unit of program execution that accesses and possibly updates various data items A powerful declarative query language
3.10 Embedded SQL
Y data are represented by collection of records and relationship among data are represented by links
J.
An object in the real world that is distinguishable from all other objects
K.
The number of entities to which another entity can be associated via a relationship set BCNF
L.
4.
A. D. G. J.
Each statement below has blank space to fit one of the word(s) or phrases in the list below. Enter your choice in the “tear-off” answer sheet attached to the question paper, following instructions therein. (1 x 10) merge-join rollback cartesian product ordered
M. hash
B. E. H. K.
natural join from relational algebra transaction
N. trigger
C. F. I. L.
starvation replicate fragmented division
O. super key
4.1
The ___________________ operation allows to combine information from any tow relations.
4.2
A(n) ____________________ is a statement that is executed automatically be the system as a side effect of a modification to the database.
4.3
The __________________ algorithm can be used to compute natural joins and equi-joins.
4.4
If a relation is ___________________ a copy of that stored in two or more sites.
4.5
A(n) ________________________ is a set of one or more attributes that taken collectively allows us to identify uniquely an entity in the entity set.
4.6
A(n) ________________________ is a collection of operations that performs a single logical function in a database application.
4.7
The _________________________ clause by itself defines a Cartesian product of the relations in the clause.
4.8
___________________________ indices are based on the values being distributed informally across a range of buckets.
4.9
The ___________________ is a situation where a transaction never completes its designated task.
4.10
The ____________________ operation is suited to queries that include the phase “for all”.
PART TWO (Answer any FOUR questions) 5. a) b) 6. a) b)
Construct an E-R diagram for a hospital with a set of patients and a set of medical doctors. Associate with each patient a log of the various tests and examinations conducted. Explain the advantages and disadvantages of Database Processing? (10+5) List and explain with suitable example five primary relational algebra operators. What is meant by Heuristic Optimization? Discuss the main heuristics that are applied to query optimization?
(10+5) 7. a)
Consider the insurance database given below: person (driver-id, name, address) car (license, model, year) accident (report-number, date, location) owns (driver-id, license) participated (driver-id, car, report-number, damage-amount)
b) 8. a) b) c) 9. a) b) c)
Construct the following SQL queries for this relational database. i) Find the total number of people who owned cars that were involved in accidents in 2004. ii) Find the number of accidents in which the cars belonging to “Thakre” were involved. iii) Delete the Mazda belonging to “S Khan”. How does SQL allow implementation of entity and integrity constraints? (9+6) List and explain Armstrong’s Axioms. Explain the purpose and utility of different normal forms. Specifically define and differentiate between third normal form and BCNF. What is referential integrity? Explain with suitable examples. (5+5+5) Explain ACID property of transactions. What do you understand by lock granularity? Explain Explain in brief working of two-phase locking protocol.
JULY-2004 A7-R3: INTRODUCTION TO DATABASE MANAGEMENT SYSTEMS NOTE: 1. There are TWO PARTS in this Module/paper. PART ONE contains FOUR questions and PART TWO contains FOUR questions. 2.
PART ONE is to be answered in the TEAR-OFFANSWER SHEET only, attached to the question paper, as per the instructions contained therein. PART ONE is NOT to be answered in the answer book.
3.
Maximum time allotted for PART ONE is ONE HOUR. Answer book for PART TWO will be supplied at the table when the answer sheet for PART ONE is returned. However, candidates who complete PART ONE earlier than one hour, can collect the answer book for PART TWO immediately after handing over the answer sheet for PART ONE. TOTAL TIME: 3 HOURS TOTAL MARKS: 100 (PART ONE-40; PART TWO – 60) PART ONE (Answer all the questions) 1.
Each question below gives a multiple choice of answers. Choose the most appropriate one and enter in the “tear-off” answer sheet attached to the question paper, following instructions therein. (1 x 10)
1.1
In AN E-R, Y is the dominant entity and X is a subordinate entity. Then which of the following is incorrect operationally, if Y is deleted, so it X. X existence is dependent on Y. operationally, if X is deleted, so is Y. operationally, if X is deleted, Y remains the same.
A) B) C) D) 1.2 A) B) C) D)
Assume transaction A holds a share lock R. If a transaction B also requests for a shared lock on R, It will result in deadlock situation. It will immediately be granted. It will immediately be rejected. It will be granted as soon as it is releases by A.
1.3 A) B) C) D)
Which of the following is correct a SQL query automatically eliminates duplicates. SQL permits attribute names to be repeated in the same relation. a SQL query will not work if there are no indexes on the relations. none of the above.
1.4 A) B) C) D)
Data security threats include private invasion hardware protection fraudulent manipulation of data. all of the above
1.5 A) B) C) D)
A race condition occurs when two concurrent activities interact to cause a processing error. two users of the DBMS are interacting with different files at the same time. both A and B none of the above.
1.6 A) B) C) D) 1.7 A) B) C) D) 1.8 A) B) C) D) 1.9 A) B) C) D) 1.10 A) B) C) D)
Consider the join of a relation R with a relation S. If R has m tuples and S has n tuples. Then the maximum and minimum size of the join respectively are m+n and 0 m+n and |m-n| mn and 0 mn and m+n In a multiuser database, if two users wish to update the same record at the same time, they are prevented by doing so by jamming password documentation record-lock If every non-key attribute is functionally dependent on the primary key, then the relation will be in 1NF 2NF 3NF 4NF R(A,B,C,D) is a relation, Which of the following does not have a lossless join dependency preserving BCNF decomposition A→B, B→CD A→B, B→C, C→D AB→C, C→AD A→BCD Consider the schema R{S,T,U,V} and the dependencies S→T, T→U, U→V, V→S. Let R={R1, R2} such that R1∩R2=Φ. Then the decomposition is not 2NF but not in 3NF in 2NF but not in 3NF in 3NF but not in 2NF in both 2NF and 3NF
2.
Each statement below is either TRUE or FALSE. Choose the most appropriate one and ENTER in the “tear-off” sheet attached to the question paper, following instructions therein. (1 x 10)
2.1 2.2 2.3 2.4
Data items are composed of data elements. A record in 3NF is better than a record to 1NF from a relational mathematics point of view. Generalized DBMS do not retrieve data to meet routine requests. A relation R with schema (x, y) satisfies the FDs x→y, the tuples <1, 2> and <2, 2> can both be in R simultaneously. A relational database which is 3NF may still have undesirable data redundancy because there may exists non trival FDs involving prime attributes only on left side. Transferring data in blocks from the main memory to the cache memory enables an interleaved main memory unit to operate at its maximum speed. There is always decomposition into BCNF that is lossless and dependency preserving. The step verb is used to specify the increment in a loop in PL/SQL Commit can be fired upto a savepoint. B-Tree are for storing data on disk and B+-Tree are for main memory.
2.5 2.6 2.7 2.8 2.9 2.10 3.
Match words and phrases in column X with the closest related meaning/ word(s)/phrases in column Y. Enter your selection in the “tear-off” answer sheet attached to the question paper, following instructions therein. (1 x 10)
3.1 3.2 3.3
X hierarchical database model record database administrator
A. B. C.
3.4
schema
D.
3.5
transaction log
E.
3.6 3.7
database data redundancy
F. G.
3.8
data dictionary
H.
3.9 database management system 3.10 network database model
I. J. K. L. M. N. O.
Y Organization of a database in its entirety Major limitation of file management systems Contains all the information about elements of data stored in a database In this database model, each record can have more than one parent Record of all activity that has affected a database during a period of time Hierarchical database model Using this approach means that applications software programs can access data from only a single file at a time Specifies exactly how data will be arranged and stored on a storage device for use by the DBMS Collection of related files A major advantage in using a database management system This person or group coordinates all related activities to control a database in an organization Group of related fields This model is composed of parent and child records (one parent to more than one child) Software tool that facilitates creation and maintenance of database The most popular type of database model used on microcomputers
4.
A. D. G. J. M. P. 4.1
Each statement below has blank space to fit one of the word(s) or phrases in the list below. Enter your choice in the “tear-off” answer sheet attached to the question paper, following instructions therein. (1 x 10) Larger Database Inconsistent data Smaller Users (U,-)
B. E. H. K. N. Q.
INSERT Database Trigger 60% Operating System R-(R-S) Base register addressing
C. F. I. L. O. R.
Parallel search Lost update Record 3NF Active data set Tree
Suppose the domain set of an attribute consists of signed 4-digit numbers. There will be _________ of reduction in the storage of this attribute, if it is stored as an integer rather then character form.
4.2
Data items grouped together for storage purposes are called _________.
4.3
In a virtual memory system the address space specified by the address lines of the CPU must be _________ then the physical memory size and smaller than the secondary size.
4.4
The DBA is, in effect, the co-ordinator between _________ and the users.
4.5
Give a relational algebra expression using only the minimum number of operators from (U,-) which is equivalent to R^S _________.
4.6
The _________ operation can not be performed using views when the defination includes more than one table.
4.7
The REVOKE command can not be used to revoke the privileges granted through the _________.
4.8
The data stored in cursor is called _________.
4.9
_________ are PL/SQL blocks that fire when an insert, update or delete performed on database.
4.10
_________ mode permits relocation without any change whatsoever the code.
PART TWO
(Answer any FOUR questions) 5. a) b)
6. a) b)
7.
8. a)
b)
Explain the recovery procedure that needs to take place after disk crash. Compare the two log-based recovery scheme in terms of ease of implementation and overhead cost. (7+8)
How does SQL allow implementation of entity, integrity and referential integrity constraints? Define the following terms i) association ii) relationship iii) aggregation iv) specialization v) generalization vi) logical and physical independence (9+6) Consider a relation TENANT (NAME, CITY, STREET#, APT#, APT-TYPE, RENT, LANDLORD, ADDRESS), Where following functional dependencies hold APT# STREET# CITY ADDRESS ADDRESS APT-TYPE NAME ADDRESS RENT LAND-LORD APT-TYPE RENT i) Are the following relation scheme in 3NF? APARTMENT (APT-TYPE, ADDRESS, RENT) DWELLER (NAME, ADDRESS, RENT) ii) What updating and insertion anomalies do you foresee in TENANT, APARTMENT & DWELLER relations. iii) Do the APARTMENT & DWELLER relations have lossless join? iv) Find a key of this relation. How many keys does TENANT have? v) Find the decomposition of TENANT into 3NF having lossless join and preserving dependencies. (3x5)
What is the highest normal form of each of the following relations? i) R1 (A, B, C) with A→B, A→C, C→B ii) R1 (A, B, C, D) with A→BC, CD→B iii) R1 (A, B, C, D, E) with A→BC, E→A, CD→E Find the candidate keys for each of these relations.
Find a 3NF decomposition of the following relation 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 → RankChairperson Department → Faculty Student → DepartmentFacultyDean ProfessorRank → DepartmentFaculty (8+7) 9. a) b) c)
Define the BCNF? How does it differ from 3NF? Why is it considered a stronger form of 3NF? What is meant by the term heuristic optimization? Discuss the main heuristic that are applied during query optimization. Can a transaction be aborted? Explain in brief. (5+5+5)
A7-R3: INTRODUCTION TO DATABASE MANAGEMENT SYSTEMS NOTE: 1. There are TWO PARTS in this Module/paper. PART ONE contains FOUR questions and PART TWO contains FOUR questions. 2.
PART ONE is to be answered in the TEAR-OFFANSWER SHEET only, attached to the question paper, as per the instructions contained therein. PART ONE is NOT to be answered in the answer book.
3.
Maximum time allotted for PART ONE is ONE HOUR. Answer book for PART TWO will be supplied at the table when the answer sheet for PART ONE is returned. However, candidates who complete PART ONE earlier than one hour, can collect the answer book for PART TWO immediately after handing over the answer sheet for PART ONE. TOTAL TIME: 3 HOURS TOTAL MARKS: 100 (PART ONE-40; PART TWO – 60) PART ONE (Answer all the questions) 1.
Each question below gives a multiple choice of answers. Choose the most appropriate one and enter in the “tear-off” answer sheet attached to the question paper, following instructions therein. (1 x 10)
1.1 A) B) C) D)
The DMLs which require a user to specify what data is needed and how to get it, is called DDL DMLs Procedural DMLs Nonprocedural DMLs None of the above
1.2 A) B) C) D)
One of the following is not an object-based logical model. The binary model The entity-relational model The infological model None of the above.
1.3 A) B) C) D)
A super key is a set of one or more attributes that, taken collectively, allow us to identify uniquely an entity in the entity set. to make the key most powerful for faster retrieval. to increase effectiveness of database access. None of the above.
1.4 A) B) C) D)
Which one of the following is binary operation? project select rename None of the above
1.5 A) B) C) D)
Consider P1 and P2 are formulae. Which one of the following is correct ? P1 ^ P2 is equivalent to ¬ (P1 V ¬ (¬P2)) P1 ^ P2 is equivalent to ¬ (¬(P1) V ¬ (P2)) P1 ^ P2 is equivalent to ¬ (¬(P1) V ¬ (¬P2)) None of the above.
1.6 A) B) C) D)
Which clause from the following corresponds to the projection operation of the relational algebra. from select where None of the above.
1.7 A) B) C) D)
Which one of the clause is useful in defining the notion of tuple variables? as by from None of the above.
1.8 A) B) C) D)
The intersect operation automatically eliminates duplicates. automatically eliminates duplicates, if we provide all clause with intersect. never eliminates duplicates. None of the above.
1.9 A)
A table is in 2NF if it is in 1NF and if no column that is not a part of the primary key is dependent on only a portion of the alternate key. no column that is not a part of the primary key is dependent on only a portion of the primary key. no column that is not a part of the primary key is dependent on only a portion of the foreign key. None of the above.
B) C) D) 1.10 A) B) C) D)
The JOIN command in the relational algebra combines data from two tables based on common rows. common columns. Predefine rules None of the above.
2.
Each statement below is either TRUE or FALSE. Choose the most appropriate one and ENTER in the “tear-off” sheet attached to the question paper, following instructions therein. (1 x 10)
2.1
A conceptual level is a lowest level of data abstraction and describes how the data are actually stored. Aggregate functions take a collection of values and returns one or more than one value as a result. DECLARE is an executable statement. Column constraints are specific to a column in a base table. The order by clause lists items in descending order. Unique index can occur only one per table in database. The DELETE statement is used to delete an existing column or columns from a table. ODBC means Open Database Connectivity. Database design is the process of determining an appropriate database structure to satisfy a given set of requirements. Firewall can be considered as a single point control on a network.
2.2 2.3 2.4 2.5 2.6 2.7 2.8 2.9 2.10 3.
Match words and phrases in column X with the closest related meaning/ word(s)/phrases in column Y. Enter your selection in the “tear-off” answer sheet attached to the question paper, following instructions therein. (1 x 10)
3.1
X File manager
A.
3.2
Query processor
B.
Y graphical means of modeling various software components host language
3.3
UML
C.
(¬ (P1) V P2)
3.4
Outer join
D.
encoding data
3.5
P1 ⇒ P2
E.
¬ (P1) V P2)
3.6
Aggregate functions
F.
correctness and completeness of the data in a database
3.7
Embedded SQL
G.
decoding data
3.8
OPEN
H.
allocation of space on disk storage
3.9
Encryption
I.
allocation of time for disk storage
J.
deal with voluminous information
K.
deal with missing information
L.
return a single function
M.
return multiple functions
N.
low-level instructions for database manager
O.
Makes the cursor ready for row retrieval
3.10 Data integrity
4.
A. D. G. J.
Each statement below has blank space to fit one of the word(s) or phrases in the list below. Enter your choice in the “tear-off” answer sheet attached to the question paper, following instructions therein. (1 x 10) with recovery manager attributes uncommitted
M. dynamic P. database manager
4.1
B. E. H. K.
form static password authorization
N. class Q. committed
C. F. I. L.
discriminator object shadow association
O. aggregation R. entity
An entity is an object that is distinguishable from other objects by a specific set of __________.
4.2
The primary key of a weak entity set is formed by the primary key of the identifying entity set, plus the weak entity set’s __________.
4.3
A(n) __________ is an abstraction through which relationships are treated as higher level entities.
4.4
The __________ clause permits a view definition to be used in multiple places within a query.
4.5
The __________ SQL component of SQL allows programs to construct and submit SQL queries at run time.
4.6
A cursor is an SQL __________ that is associated with a specific table expression.
4.7
In __________ paging, transaction logs are not required.
4.8
The __________, restores the database to a consistent state.
4.9
The undo logs rollback __________.
4.10
A(n) __________ granting of a privilege to a user.
PART TWO (Answer ALL questions) 5. a) b)
c)
6. a) b)
List advantages and disadvantages of database processing. Explain one of the advantages and one of the disadvantages. Explain the following terms with reference to DBMS i) relation ii) relational database iii) a normalized relation What is DBA (Data Base Analysis)? What is the role of DBA in regard to security? (5+6+4)
List the rules that an OLAP system should follow. Explain the following in brief: i) What are stored procedures? ii) What is data fragmentation? iii) What is the difference between file server and a client/server system? iv) What are triggers? What purpose do they serve? (7+8)
7. a)
b)
c)
8. a) b) c)
9.
While selecting DBMS, what are the checklist points one has to study for each of the following? i) Security ii) Integrity iii) Data Reconstructing iv) Data Definition Assuming that you are using DBMS that provides the locking facilities. How programs be written to i) avoid deadlock ii) guarantee correct results. What is deadlock? How does it occur? (8+4+3)
Describe the function of each of the following types of keys: primary, alternative, secondary and foreign. Explain third normal form with example. Describe the GRANT function and explain, how it relates to security. What types of privileges may be granted? How are they revoked? (6+4+5)
Assume the following tables: degree (degcode, name, subject) candidate (seatno, degcode, name, semester, month, year, result) marks (seatno, degcode, semester, month, year, papcode, marks) In the degree table, degcode is the degree code, name is the name of the degree (e.g. M.Sc., M.Com. etc.) and subject is the subject of the course (e.g. Physics, Math etc.). In the marks table papcode is the paper code (e.g. A-1, A-2 etc.) Solve the following queries using SQL: i) Write a SELECT statement to display all the degree codes which are there in the candidate table but not present in the degree table in the order of degcode. ii) Write a SELECT statement to display the names of all candidates who have appeared for their M.Sc. (Phys) examination in the order of name. iii) Write a SELECT statement to display the name, subject and number of candidates for all degrees in which there are less than 5 candidates.
Iv) v)
Write a SELECT statement to display the names of all the candidates who have got less than 40 marks in exactly two subjects. Write a SELECT statement to display the names of all the candidates who have got highest total marks in M.Sc. (Math). (5x3)