January, 2011
A7-R4: INTRODUCTION TO DATABASE MANAGEMENT SYSTEM
PART ONE
(Answer all the questions)
1.1 Given the relation schema:
EMPLOYEE(EMP_NUM,EMP_NAME, JOB_CODE)
JOB(JOB_CODE, JOB_DESCRIPTION)
What is the cardinality of relationship between entities EMPLOYEE and JOB?
A) One to One
B) One to Many
C) Many to One
D) Many to Many
1.2 Given the relation schema R(A,B,C,D)and functional dependencies C-->B and AB-->C,D.
What is the highest normal form of Relation R?
A) 2NF
B) 3NF
C) BCNF
D) 1NF
1.3 Given the statement “an employee may have many degrees”- which kind of attribute
degrees will be?
A) Simple attribute
B) Single attribute
C) Composite attribute
D) Multivalued attribute
1.4 Given the EMPLOYEE and JOB relations in Question 1.1 above, attribute JOB_CODE in
EMPLOYEE relation is a
A) Primary Key
B) Super Key
C) Candidate Key
D) Foreign Key
1.5 In a relational database, a referential integrity constraint is specified with the help of
A) Primary key
B) Consistency key
C) Foreign key
D) None of the above
1.6 Which one of the following is NOT likely to be found in a database dictionary?
A) Names of fields
B) Frequency of back ups
C) Programs to access the data
D) Security of the data
1.7 Which of these queries processes the data in some way?
A) Sort query
B) Select Query
C) Cross tab query
D) Update query
1.8 Which of the following is NOT a DBMS?
A) Oracle
B) Sybase
C) Unify
D) Quatro
1.9 Which of the following is NOT a valid SQL statement?
A) CREATE TABLE
B) CREATE USER
C) CREATE INDEX
D) CREATE DICTIONARY
1.10 Which of the following is the responsibility of the recovery component of DBMS?
A) Maintaining database in a consistent state always
B) Performance Monitoring
C) Enforcing the constraints of the real world
D) Preventing unauthorized access of database
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. (1x10)
2.1 Foreign key can have NULL value.
2.2 Entity Integrity constraint state that Primary Key must have unique value.
2.3 A Database Administrator (DBA) is a person who makes most use of the database.
2.4 Concurrency Control component of a DBMS is responsible for the safety of data losses from
power outages.
2.5 Relational Calculus is a procedural query language.
2.6 Condition mentioned in the Having Clause in SQL is applied to the individual tuples of the
concerned tables.
2.7 Views are provided to maintain the confidentiality of the data in the database.
2.8 Check points are used by the recovery component to reduce the amount of operations to be
UNDONE.
2.9 Hierarchical databases are better than most at minimizing data redundancy.
2.10 In a relational database, two records are linked by cell addresses.
3. Match words and phrases in column X with the closest related meaning/word(s)/phrase(s) in column Y. Enter your selection in the “tear-off” answer sheet attached to the question paper, following instructions therein. (1x10)
X Y3.1 Three level architecture of database system A. Foreign Keys
provides
3.2 ON DELETE and ON UPDATE statements are B. DDL statement
associated with
3.3 CREATE TABLE is a C. Data Abstraction
3.4 This itself is a database with predefined structure D. Self Referencing Relation
3.5 This operation makes all the changes that are E. Data Dictionary
made to the database permanent
3.6 Recursive relationship in the ER diagram result in F. COMMIT
3.7 Concurrency Control is mandatory for G. Multi user DBMS
3.8 ACID property is related to H. Top Down Design approach
3.9 Normalization is a I. ER Model
3.10 Recovery Component of DBMS makes use of J. Transactions
K. System Log
L. Embedded SQL
M. DML statement
4. Each statement below has a blank space to fit one of the word(s) or phrase(s) in the list below. Enter your choice in the “tear-off” answer sheet attached to the question paper, following instructions therein. (1x10)
A. Select Clause B. Aggregate Functions C. Domain Constraint
D. Grant and Revoke E. Aggregation F. Cardinality
G. Weak H. Physical Schema I. Functional Dependency
J. SQL K. Candidate Key L. Participation Constraint
M. Data Dictionary
4.1 ________ of a relationship R between two entity types E1 and E2 defines with how many
instances of E2 one instance of E1 is associated and vice versa.
4.2 ________ entity has no candidate key.
4.3 ________ defines the storage details of a database.
4.4 ________ states that every attribute must have an associated domain.
4.5 ________ of SQL is equivalent to project operation of relation algebra.
4.6 ________ statements of ORACLE provide permissions to the user for accessing and
manipulating databases.
4.7 ________ are applied to a group of tuples.
4.8 ________ is the process of defining higher level entity from lower level entities and their
relationships.
4.9 ________ stores meta data i.e., complete description of the database.
4.10 ________ is a constraint defined between the two sets of attributes in a relation.
PART TWO
(Answer any FOUR questions)
5.
a) Design an ER diagram for a Bank. The bank has many branches with attributesBank_Branch_Name, Address, Phone. Each branch maintains accounts for customers.
Each account has a unique Account_no, Type and Balance attributes. Customers are
represented by their C_ID, F_Name, L_Name, C_Address, C_Phone. Bank also offers loan
to its customers. Each loan has a unique Loan_No, type and Amount attributes. A customer
can have more than one account(of different type). An account can be jointly held by more
than one customer. Similarly a customer can take more than one loan and a loan can be
held jointly by more than one customer. Clearly specify the Primary Keys and Cardinality
and Participation Constraint.
b) Discuss the role of mappings in three level architecture of DBMS.
(10+5)
6.
a) Consider the relations given below:
SALESPERSON(SSN,NAME,START_YEAR,DEPT_NO)
TRIP(Trip_ID, SSN, From_City, TO_City, Departure_Date, Return_Date)
EXPENSE(Trip_ID, Account_NO, Amount)
Specify the Foreign Keys for the above relations.
b) Write the given queries in Relational Algebra.
i) Give the details of the trip that exceeded Rs. 25000 in expense.
ii) Print the SSN of salesman who took the trip to “Singapore”.
iii) Print the total trip expense incurred by salesman with SSN='245-16-9830'.
c) Explain with example the SQL support for specifying various constraints while creating the
database tables.
d) List the features provided by any DBMS package for SECURITY of the database.
(1+7+5+2)
7.
a) Given a relation schema R(A, B, C, D, E, F, G) and functional dependencies B--->D,E;
AB ----> C,D,E,F,G and E-->F,G
i) What is the normal form of relation R? Explain your answer clearly.
ii) Decompose it to bring it to the 3NF. Specify each step of the decomposition clearly.
b) Explain with example the utility of
i) Entity Integrity Constraint
ii) Referential Integrity Constraint.
c) List FOUR main advantages of database approach.
([2+5]+4+4)
8.
a) What is meant by embedded SQL? How is it useful for the programmer?
b) With the help of an example differentiate between Hierarchical and Network model.
c) What is relational calculus? Name one commercially available product which uses relational
calculus.
(5+6+4)
9.
a) List Codd’s rules to qualify a database as relational.
b) Explain, how the object-oriented database model varies from the relational database model.
c) Explain, how cascaded rollback can be avoided.
(6+6+3)
No comments:
Post a Comment