Monday, 26 September 2016

July, 2013 A7-R4: INTRODUCTION TO DATABASE MANAGEMENT SYSTEM

July, 2013
A7-R4: INTRODUCTION TO DATABASE MANAGEMENT SYSTEM

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 “OMR” answer sheet supplied with the question paper, following instructions therein. (1x10)

1.1 A view is a
A) Base table
B) Derived table
C) Named Derived table
D) Soft table
1.2 Which of the following do you need to consider when you make a table in SQL?
A) Data types
B) Primary Keys
C) Default Keys
D) All of the above

1.3 If attribute A determines both attributes B and C, then it is also true that:
A) A B.
B) B A.
C) C A.
D) (B,C) A.
1.4 The capacity to change the conceptual schema without having to change the external
schema or application programs
A) Data independence
B) Physical data independence
C) Logical data independence
D) Such changes are non-existent
1.5 Which of the following is an advantage of partitioning?
A) Complexity
B) Extra space
C) Consistent access speed
D) Security

1.6 Which of the following operations can be expressed as a sequence of , x, and –
operations.
A) DIVISION
B) UNION
C) JOIN
D) CARTESIAN PRODUCT
1.7 An advantage of the database management approach is
A) data is dependent on programs.
B) data redundancy increases.
C) data is integrated and can be accessed by multiple programs.
D) none of the above.
1.8 The property / properties of a database is/are:
A) It is an integrated collection of logically related records.
B) It consolidates separate files into a common pool of data records.
C) Data stored in a database is independent of the application programs using it.
D) All of the above.
1.9 Conceptual design
A) is a documentation technique.
B) needs data volume and processing frequencies to determine the size of the database.
C) involves modeling independent of the DBMS.
D) is designing the relational model.
1.10 Transaction processing is associated with everything below except
A) producing detail, summary, or exception reports.
B) recording a business activity.
C) confirming an action or triggering a response.
D) maintaining data

2. Each statement below is either TRUE or FALSE. Choose the most appropriate one and ENTER in the “OMR” answer sheet supplied with the question paper, following instructions therein. (1x10)

2.1 Sequential is a method in which records are physically stored in a specified order
according to a key field in each record.
2.2 Data independence means programs are not dependent on the physical attributes of data
but are dependent on the logical attributes of data.
2.3 An entity set that does not have sufficient attributes to form a primary key is called a simple
entity set.
2.4 The capacity to change the conceptual schema without having to change external schemas
or application programs is called logical data independence.
2.5 NULL is the same as zero for integer and blank for character.
2.6 The PROJECT operation does not remove duplicate tuples.
2.7 The primary key combined with a foreign key creates many to many relationship between
the tables that connect them.
2.8 Triggers are stored procedures created in order to enforce integrity rules in a database.
2.9 A conceptual data model is independent of hardware but dependent on software.
2.10 Backup and recovery facilities are not included in the data administration subsystem.

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 “OMR” answer sheet supplied with the question paper, following instructions therein. (1x10)
                                 X                                                                    Y
3.1 Many to Many relationship in hierarchical data               A. Relational Data Model
model
3.2 Entity belongs to no more than one level entity set         B. Full Functional dependency
3.3 Third Normal Form                                                         C. Dependency preservation
3.4 Component of DBMS that most affects the                    D. Virtual Record
performance
3.5 Primary key of one table that appears in another             E. Weak Entity Type
table
3.6 Logically related two-dimensional tables                        F. Transitive dependency
3.7 Information and logical structure of information            G. Disjoint less constraint
3.8 Information about structure of a database                       H. Database Dictionary
3.9 Functions Performed by SQL                                          I. Database Engine
3.10 Desirable properties of a decomposition                      J. Query Engine
                                                                                              K. Foreign key
                                                                                               L. Alternate Key
                                                                                              M. QBE

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 “OMR” answer sheet supplied with the question paper, following instructions therein. (1x10)

A. SQL                                         B. Retrieval                            C. Reorganization
D. Relational                                E. Concurrency                      F. Network
G. Authorization                          H. Intersection                       I. Recovery
J. Update                                      K. Union                                L. Scalability
M. Database Engine

4.1 The relational algebra operations enable a user to specify basic ________ requests.
4.2 The result of ________ operation is a relation that includes all tuples that are in both R and
S relations.
4.3 The language which has recently become the de-facto standard for interfacing application
programs with relational database system is ________.
4.4 ________ is a process by which the user’s privileges are ascertained.
4.5 ________ database systems tend to be more flexible in their ability to access data than
Hierarchical database systems.
4.6 ________ services is a mechanism for recovering the database in the event that the
database is damaged in any way.
4.7 The ability of a database to grow as the data tracking needs grow is its ________.
4.8 Component of DBMS that accepts logical requests from other DBMS subsystems is called
________.
4.9 ________ control ensure the validity of database updates when multiple users attempt to
access and change the same information.
4.10 ________ facility maintains statistics regarding how the DBMS engine physically accesses
information.

PART TWO
(Answer any FOUR questions)
5.
a) Explain the concepts of relational data model. Also discuss its advantages and
disadvantages.
b) Consider the following relational schema:
Doctor(DName,Reg_no) Patient(Pname, Disease) Assigned_To(Pname,Dname)
Write the expression in Tuple calculus for each of the queries:
i) Get the names of patients who are assigned to more than one doctor.
ii) Get the names of doctors who are treating patients with ‘Polio’.
c) Discuss the characteristics of relations that make them different from ordinary tables and
files.
(5+5+5)
6.
a) Differentiate between various levels of data abstraction.
b) What do you understand by views? Explain how views are different from tables.
c) Given R{ABCD} and a set F of functional dependencies on R given as
F={AB C, AB D, C A, D B}. Find any two candidate keys of R. Show each step.
In what normal form is R? Justify.
(5+5+5)
7.
a) What is a minimal set of functional dependencies? Does every set of dependencies have a
minimal equivalent set?
b) Define Boyce-Codd normal form? How does it differ from 3NF? Why is it considered
stronger form of 3NF?
c) What do you understand by access control? Explain the difference between discretionary
and mandatory access control.
(5+5+5)
8.
a) Discuss the immediate update recovery technique in both single-user and multi-user
environments. What are the advantages and disadvantages of immediate update?
b) What do you understand by safe expression in relational calculus? When a query language
is called relationally complete?
c) Explain the shadow paging recovery technique.
(5+5+5)
9.
a) Explain the data anomalies that are likely to occur as a result of data redundancy. Can data
redundancy be completely eliminated in database approach? Why or Why not?
b) Consider the following relations with underlined primary keys.
Product(Pcode, Description, StockingDate, QtyOnHand, MinQty, Price, Discount, VCode)
Vendor(VCode, Name, Address, Phone)
Here a vendor can supply more than one product but a product is supplied by only one
vendor. Write SQL queries for the following:
i) List the names of all the vendors who supply more than one product.
ii) List the details of the products whose prices exceed the average product price.
iii) List the Name, Address and Phone of the vendors who are currently not supplying
any product.
(6+9)

Next Set