CS403 Past Old Paper 2010

Vumba2009.blogspot.com

CS403 Database Management System OLD past Paper 2010

Question No: 1 ( Marks: 1 ) - Please choose one
Making a change to the conceptual schema of a database but not affecting the existing external schemas is an example of

► Physical data independence.
► Concurrency control.
► Logical data independence.
► Functional dependency

Question No: 2 ( Marks: 1 ) - Please choose one
GRANT SELECT, UPDATE(Designation) ON Employee TO Amali,Hiruni WITH GRANT OPTION;
What does this SQL statement do?

► Grant permission to Amali,only to retrieve data from Employee table and grant permission to Hiruni, only to update the designation from Employee table.
► Grant permission to Hiruni in order to grant, select and update permission to Amali.
► Grant permission to Amali & Hiruni to update designation of employees in the Employee table.
► Grant permission to Amali and Hiruni to update all data except designation in Employee table.

Question No: 3 ( Marks: 1 ) - Please choose one
Which key word is available in SQL to enforce referential integrity?

► Check
► Primary Key
► Set Default
► Unique

Question No: 4 ( Marks: 1 ) - Please choose one
Which of the following statements are Data Definition Language command?

► INSERT
► UPDATE
► GRANT
► TRUNCATE

Question No: 5 ( Marks: 1 ) - Please choose one
How many clustered index(es) do each database table have?
► 2
► 3
► 5
► 1

Question No: 6 ( Marks: 1 ) - Please choose one
Which of the following is true about DELETE command?
► is DDL Command.
► Resets identity of the table.
► cannot activate a trigger.
► Can be Rolled back.

Question No: 7 ( Marks: 1 ) - Please choose one
Which of the following will be deleted relating to a table if you use TRUNCATE command?
► all rows in a table
► indexes
► table structure and its columns
► constraints

Question No: 8 ( Marks: 1 ) - Please choose one
Which of the following is not true about De-normalization?
► It is the process of attempting to optimize the performance of a database
► De-normalization is a technique to move from lower to higher normal forms of database modeling
► In de-normalization it is required to add redundant data.
► It enhances the performance of DB

Question No: 9 ( Marks: 1 ) - Please choose one
Which of the following is not a feature of TRANSACTION?
► Users should be able to regard the execution of each transaction as atomic.
► Each transaction, run by itself.
► must preserve the consistency of the database
► dependant on other concurrent transactions


Question No: 10 ( Marks: 1 ) - Please choose one
Which of the following gives all the fields from employee table named as EMP?
► select * from EMP;
► select emp* from EMP'
► select emp_id where EMP;
► select * where EMP;

Question No: 11 ( Marks: 1 ) - Please choose one
Suppose there are 4 fields in a table named CUST (customer_id, first_name, last_name, phone). Which of the following gives all the information of the customers in the table whose last name is ALI?
► SELECT * FROM CUST WHERE last_name='ALI';
► SELECT * FROM CUST WHERE last_name=ALI;
► SELECT * FROM CUSTOMER WHERE name=ALI;
► SELECT * FROM CUSTOMER WHERE last_name=ALI;

Question No: 12 ( Marks: 1 ) - Please choose one
Identify the correct statement with respect to normalization.

► Normalization is a formal technique that can be used only at the starting phase of the database design.
► Normalization can be used as a top-down standalone database design technique.
► The process of normalization through decomposition must achieve the lossless join
property at any cost whereas the dependency reservation property is sometimes sacrificed.
► The process of normalization through decomposition must achieve the dependency
reservation property at any cost whereas the lossless join property is sometimes sacrificed.

Question No: 13 ( Marks: 1 ) - Please choose one
Which of the following is not true with respect to denormalization?
► A denormalized data model is not the same as a data model that has not been normalized
► Denormalization takes place before the normalization process
► It is an attempt to optimize the performance of database
► Denormalization process can not be initiated before the Database design

Question No: 14 ( Marks: 1 ) - Please choose one
Which of the following is incorrect with respect to indexed sequential files?
► New records are added to an overflow file
► Record in main file that precedes it is updated to contain a pointer to the new record
► The overflow is merged with the main file during a batch update
► Multiple indexes for the same key field cannot be setup

Question No: 15 ( Marks: 1 ) - Please choose one
Which of the following types of partitioning reduces the chances of unbalanced partitions?
► vertical
► List
► Hash
► Range

Question No: 16 ( Marks: 1 ) - Please choose one
DML commands are used for:
► inserting data into databases
► creating databases
► destroying databases
► creating DB objects

Vumba2009.blogspot.com

Question No: 18 (Marks: 1) - Please choose one
which of the following is correct regarding Dataflow diagram?

► Single DFD is required to represent a system
► The dataflow must be bidirectional
► Created at increasing levels of detail
► Used to represent the relationships among the external entities

Question No: 19 ( Marks: 1 ) - Please choose one
Select the correct statement among the following on proper naming of schema constructs:

► Entity type name applies to all the entities belonging to that entity type and therefore a plural name is selected for entity type.
► In the narrative description of the database requirements, verbs tend to indicate the names of
relationship types.
► The nouns arising from a database requirement description can be considered as names of attributes.
► Additional nouns which are appearing in the narrative description of the database requirements represent the weak entity type names.

Question No: 20 ( Marks: 1 ) - Please choose one
Structural constraints of a relationship type refer to

► identifying the owner entity type relevant to a given entity type
► whether the existence of an entity depends on it being related to another entity via the relationship type.
► the role that a participating entity from the entity type plays in each relationship instance.
► the constraints applicable in granting access to tables, columns and views in a database schema.

Question No: 21 ( Marks: 1 ) - Please choose one
Identify the correct statement.
► Entity integrity constraints specify that primary key values can be composite.
► Entity integrity constraints are specified on individual relations.
► Entity integrity constraints are specified between weak entities.
► When entity integrity rules are enforced, a tuple in one relation that refers to another relation must refer to an existing tuple.

Question No: 22 ( Marks: 1 ) - Please choose one
What is the impact of setting multiple indexes for the same key, in index sequential files?
► Multiple indexes for the same key can not be set
► It increases efficiency
► It decreases efficiency
► It will increase complexity as the access time will be increased

Question No: 23 ( Marks: 1 ) - Please choose one
Which of the following is not true regarding Indexes?
► Index can be defined even when there is no data in the table
► It support Range selections
► It can be created using ‘Create Index’ statement
► It can not be created on composite attributes

Question No: 24 ( Marks: 1 ) - Please choose one
While recovering data, which of the following files does a recovery manager examines at first?

► A system file
► Log file
► Data dictionary
► Metadata

Question No: 25 ( Marks: 1 ) - Please choose one
The main memory of a computer system is also known as

► ROM
► RAM
► PROM
► Hard disk

Question No: 26 ( Marks: 1 ) - Please choose one
______is a control that enables users to select one option from an associated list; users can also type an option.

► Combo box
► Button
► Text box
► Static area

Question No: 27 ( Marks: 1 ) - Please choose one
_______ records data by burning microscopic holes in the surface of the disk with a laser.

► Hard disk
► RAM
► Optical disk
► Floppy disk

Question No: 28 ( Marks: 1 ) - Please choose one
Which of following is NOT generally the aim of data partitioning and placement of data?
► Reduce Workload
► Balance Workload
► Merging different relations
► Speed up rate of useful works.

Question No: 29 ( Marks: 1 ) - Please choose one
ALTER TABLE exams
RENAME COLUMN Q_description TO Question_Descp, Std_ID to Student_ID.
Syntax of ALTER TABLE is NOT correct.

► True
► False

Question No: 30 ( Marks: 1 ) - Please choose one
Which of the following is true regarding Index?
► Index can only be created for a single table in database
► Index can maximum be created for two tables in database
► Index should be created for every table in the database
► Index can be created for every table in the database

Question No: 31 ( Marks: 1 )
What is procedural DML?
Procedural DML or Low level: DML requires a user to specify what data are needed and how to get those data. Procedural as the name specifies, it not only requires a user to specify what data is needed but also the procedure how to retrieve it.

Question No: 32 ( Marks: 1 )
What does RAM stand for?

Ram stands for random access memory. It is the main memory of the computer.

Question No: 33 ( Marks: 2 )
Which DML statement changes the values of one or more columns based on some conditions.
The UPDATE statement changes the values of one or more columns based on some condition. This updated value can also be the result of an expression or calculation.

Question No: 34 ( Marks: 2 )
Name the two primary modes for taking Locks.

There are two primary modes for taking locks: optimistic and pessimistic.

Vumba2009.blogspot.com

Question No: 35 ( Marks: 3 )
Give three reasons of partitioning in the process of denormalization.

The three reasons of partitioning is

Ø  reduce workload,

Ø  balance workload,

Ø  speed up the rate of useful work

Question No: 36 ( Marks: 3 )
Write any three factors which we consider while defining key in designing an indexed sequential file?

When  you  design  an  indexed  sequential  file,  you  must  define  each  key  in  the

following terms: 

•  Position and size 

•  Data type 

•  Index number 

•  Options selected

Question No: 37 ( Marks: 3 )
Create a unique index named ‘ IndexNum’ on the ‘CustName’ column of the table ‘Customer’.

Create unique index index_num on customer(custname);

Question No: 38 ( Marks: 5 )
Write a query to change the "status" field in the table "name_table" against the phone 34657. Make the status as enable.

Select status from name_table where phone=34657 and status=’enable’;

Question No: 39 ( Marks: 5 )
Describe ATOMICITY as one of the properties of TRANSACTION.
All transactions share these properties:  atomicity, consistency, isolation, and durability (represented by the acronym ACID). 

• Atomicity:  This implies indivisibility; any indivisible operation (one which will either complete fully or not at all) is said to be atomic.  Users  should  be  able  to  regard  the  execution  of  each  transaction  as  atomic: either all actions are carried out or none are. Users should not have to worry about the effect of incomplete transactions (say, when a system crash occurs).

Question No: 40 ( Marks: 10 )
Explain the main components of an Index. Explain the basic features of the two types of indexes (Ordered Index and Hash Index).

Question No: 41 ( Marks: 10 )
Make the following comparison among the indexes
• Clustered vs. Un-clustered Indexes
• Single Key vs. Composite Indexes with examples

Indexing on a single column of a table is the simplest and most common manifestation of an index. A single column index is one that is created based on only one table column.

CREATE INDEX NAME_IDX

ON EMPLOYEE_TBL (LAST_NAME);

A composite index is an index on two or more columns of a table. You should consider performances when creating a composite index. Composite index are most effective on table columns that are used together frequently as conditions in a query’s where clause.

Example:

CREATE INDEX ORD_IDX

ON ORDERS_TBL(CUST_ID, PROD_ID);

When a one column is used in  the where clause then single column index is used and when more than one column is used then composite index is used.

A composite index is likely to be larger than a single-attribute search key index because the size of entries is larger.

Clustered Indexes

A clustered index determines the storage order of data in a table. A clustered index is analogous to a telephone directory, which arranges data by last name. Because the clustered index dictates the physical storage order of the data in the table, a table can contain only one clustered index. However, the index can comprise multiple columns (a composite index), like the way a telephone directory is organized by last name and first name.

A clustered index is particularly efficient on columns often searched for ranges of values. Once the row with the first value is found using the clustered index, rows with subsequent indexed values are guaranteed to be physically adjacent. For example, if an application frequently executes a query to retrieve records between a range of dates, a clustered index can quickly locate the row containing the beginning date, and then retrieve all adjacent rows in the table until the last date is reached. This can help increase the performance of this type of query. Also, if there is a column(s) which is used frequently to sort the data retrieved from a table, it can be advantageous to cluster (physically sort) the table on that column(s) to save the cost of a sort each time the column(s) is queried.

Clustered indexes are also efficient for finding a specific row when the indexed value is unique. For example, the fastest way to find a particular employee using the unique employee ID column emp_id would be to create a clustered index or PRIMARY KEY constraint on the emp_id column. Note PRIMARY KEY constraints create clustered indexes automatically if no clustered index already exists on the table and a nonclustered index is not specified when you create the PRIMARY KEY constraint.

Non-clustered Indexes

Nonclustered indexes have the same B-tree structure as clustered indexes, with two significant differences: 

The data rows are not sorted and stored in order based on their nonclustered keys.  The leaf layer of a nonclustered index does not consist of the data pages.  Instead, the leaf nodes contain index rows. Each index row contains the nonclustered key value and one or more row locators that point to the data row (or rows if the index is not unique) having the key value.  Nonclustered indexes can be defined on either a table with a clustered index or a heap. In Microsoft® SQL Server™ version 7.0, the row locators in nonclustered index rows have two forms: 

If the table is a heap (does not have a clustered index), the row locator is a pointer to the row. The pointer is built from the file ID, page number, and number of the row on the page. The entire pointer is known as a Row ID. 

If the table does have a clustered index, the row locator is the clustered index key for the row. If the clustered index is not a unique index, SQL Server 7.0 adds an internal value to duplicate keys to make them unique. This value is not visible to users; it is used to make the key unique for use in nonclustered indexes. SQL Server retrieves the data row by searching the clustered index using the clustered index key stored in the leaf row of the nonclustered index.  Because nonclustered indexes store clustered index keys as their row locators, it is important to keep clustered index keys as small as possible. Do not choose large columns as the keys to clustered indexes if a table also has nonclustered indexes.

Vumba2009.blogspot.com

--Question (Marks: 2 )
How the normal join is different from equi join?

--Question ( Marks: 3 )
What is set difference in relational algebra? explain.


--Question ( Marks: 5 )
Table A represents E-R Model and table B represents Relational Model, match
the entries from Table A to corresponding entries in Table B

TABLE A TABLE B

A. Entity Type A. Primary (Secondary) Key
B. key Attribute B. Dmain
C. Composite Attribute C. Relation and foreign key
D. Multivalued Attribute D. Set of simple component attributes
E. Value Set E. Relation


--Question ( Marks: 10 )
Explain the significance of Data Model. Why do we have to know the specific
data model of a DBMS

*Next PAPER:*

--in data dictionary, what does CRM stand for? (1 marks)

--How many entities does a unary relationship involve? (1 marks)

-- if one super type have three subtypes, then how many relations should be
created? (2 marks)


--Write three steps for constructing a Context DFD? (3 marks)

--Briefly explain rollback and roll forward. (5 marks)

--Explain difference between Derived attribute and Multivalued attribute with the of an example. (10 marks)

Vumba2009.blogspot.com