CS403- Database Management System Old Past Exam Papers

 

CS403 Database Management System

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?

cs403 mid spring 2010

MIDTERM EXAMINATION

Spring 2010

CS403- Database Management Systems (Session - 6)

Q-  Which of the following constraints enforces entity integrity? PRIMARY KEY

FOREIGN KEY

CHECK

NOT NULL

Q- Which of the following statements is correct? PK can have NULL

PK in a relation is a key in some other relation

PK can be composite

PK must be selected from the list of secondary keys

A binary relationship

A ternary relationship

A recursive relationship

An identifying relationship

Q- A database system allows the following EXCEPT

management and control of data towards an efficient working of an organisation.

more critical functions in organisations to be computerised and the need to keep a large volume of data available in an up to the minute current state increased.

any user to access all its data.

integration of data across multiple applications into a single application.

Q- Which of the following enforces a relation into 1st normal form?

► The domain of attribute must include only atomic values.

► Every non-key attribute is fully functionally dependent on primary key

► non-key attribute is non-transitively dependent on primary key.

► Every non-key attribute is partially dependent on super key

Q- Consider the following set of functional dependencies (FDs) on the following relational schema.

Emp_No -> {Ename, Bdate, Address, Dept_No}

Dept_No -> {Dname, Mgr_No}

The additional FD which can be inferred from the above set of FDs is

Emp_No -> {Dname, Mgr_No} Emp_Name -> Dept_No . Emp_Name -> Dept_Name . Emp_Name, Dept_No -> Mgr_No .

Q- Select the correct statement about the ANSI/SPARC architecture.

► The conceptual level is a level of indication between the internal level and the external level.

► The internal level in a database system will definitely be relational. Tuka uncle lagao…

► Any given database has many conceptual schemas and one physical schema, but it has only one external schemas.

► The external level is not concerned with individual user perceptions, while the conceptual level

is concerned with a community user perception.

Q- Which of the following is a feature of PRIMARY KEY constraint unique identifier for a row within a database table.

allow any actions that would destroy links between tables limit the values that can be placed in a column.

enforces that the column will only accept null values. Q- Structural constraints of a relationship type refer to

► identifying the owner entity type relevant to a given entity type

the

► whether the existence of an entity depends on it being related to another entity via

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.

Q- Which of the following is true about relational schema?

► The sequence of columns is significant

► The sequence of rows is significant.

► Contains only derived attributes.

► Values are atomic.

Q- A unary ternary binary weak

relationship exists when an association is maintained within a single entity.

Q- Which of the following is a correct way to implement one-to-many relationship while designing tables?

► by splitting the data into two tables with primary key and foreign key relationships.

► using a junction table with the keys from both the tables forming the composite primary key of the junction table.

► by splitting each table into three

► as a single table and rarely as two tables with primary and foreign key relationships.

Q- 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.

Q- A software package designed to store and manage databases

Database

DBMS

Data model

Data

Q- Who is responsible for authorizing access to the database, for coordinating and monitoring its use?

Select correct option:

Database Designer

Database Administrator

End User

Application Programmer

Q- Consider the following relation R and its sample data. (Consider that these are the only tuples for the given relation)

Q- Which of the following statements is NOT correct?

► The functional dependency (EmpNo, DeptNo) -> ProjNo holds over R.

► The functional dependency EmpNo -> DeptNo holds over R.

► The functional dependency ProjNo -> DeptNo holds over R.

► The functional dependency (EmpNo, ProjNo) -> DeptNo holds over R.

Q- Which feature of database provides conversion from inconsistent state of DB to a consistent state ensuring minimum data loss?

Select correct option:

User accessible catalog

Data processing

Recovery service

Authorization service

Q- Consider the given relations Student and Instructor as given below. Please note that

Fname and Lname also denote the First Name and Last Name respectively.

Which of the following statements is correct with respect to the two relations given above?

The two relations are not union-compatible since their attribute names differ.

The set operations such as CARTESIAN PRODUCT and DIVISION can be applied on

these

two relations.

To find out those students who work as instructors, it is necessary to perform the operation

Student ∩ Instructor.

To find out the students who are not instructors, it is necessary to perform the operation

Student ÷ Instructor.

Q- A candidate key that does not have a null value and is selected to uniquely identify all other attribute values in any given row is called a     .

Select correct option:

superkey candidate key primary key secondary key

Q- What is the significance of normalization? Q- Define Relationship.

Q- What is the basic function of a DML Compiler? Q- Define primary key and give one example.

Q- What do you know about Insertion anomaly?

Q- Define the first normal form.\

CS403 Fall 2011 old paper

CS403 Fall 2011

Question No: 1 ( Marks: 1 ) - Please choose one
Which one of the following E-R diagrams most correctly represents the relationship between Student and Grade entities?

Question No: 2 ( Marks: 1 ) - Please choose one
Which of the following statements are Data Definition Language commands?
► INSERT
► UPDATE
► GRANT
► TRUNCATE

Question No: 3 ( Marks: 1 ) - Please choose one
Which of the following statements is true about the views?
► view is always a complete set of all the tables in a database
► View can not be used for retrieving data
► The results of using a view are not permanently stored in the database.
► Rows can not be updated or deleted in the view

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

Question No: 5 ( Marks: 1 ) - Please choose one
Suppose there are 4 rows and 3 columns in TABLE1 and 6 rows and 5 coulmns in TABLE2; what is the size of the cartesian product incase of CROSS JOIN between these two tables?
► 24
► 20
► 18
► 15

Question No: 6 ( Marks: 1 ) - Please choose one
Which of the following is not one of the properties of Transaction?
► atomicity
► consistency
► redundancy
► durability

Question No: 7 ( Marks: 1 ) - Please choose one
Which of the following is INCORRECT about VIEWS?
► It is not possible to left out the data which is not required for a specific view.
► A database view displays one or more database records on the same page.
► Views can be used as security mechanisms
► Views are generally used to focus the perception each user
has of the database.

Question No: 8 ( 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: 9 ( Marks: 1 ) - Please choose one
Consider the relation Interview(CandidateNo, InterviewDate, InterviewTime, StaffNo, RoomNo) and the following functional dependencies.
FD1 : CandidateNo, InterviewDate -> InterviewTime, StaffNo, RoomNo
FD2 : RoomNo, InterviewDate, InterviewTime -> StaffNo, CandidateNo
FD3 : StaffNo, InterviewDate -> RoomNo
Which of the following is correct?
► The relation Interview is in 3NF
► The relation Interview is in BCNF.
► The FD3 violates 3NF.
► The FD2 violates 2NF.

Question No: 10 ( Marks: 1 ) - Please choose one
Which of the following types of partitioning reduces the chances of unbalanced partitions?
► Vertical
► List
► Hash
► Range
Hash Partitioning:
It is a type of horizontal partitioning. In this type particular algorithm is applied and
DBMS knows that algorithm. So hash partitioning reduces the chances of unbalanced
partitions to a large extent

Question No: 11 ( Marks: 1 ) - Please choose one
Which of the following is one of the purposes of using DDL commands?
► inserting records into databases
► updating records into databases
► manipulating databases
► creating and destroying databases

Question No: 12 ( Marks: 1 ) - Please choose one
Which of the following statements creates a database named COMPANY.
► CREATE DATABASE company
► CREATE DB company
► ADD DATABASE copmany
► CREATE company DATABASE

Question No: 13 ( 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: 14 ( Marks: 1 ) - Please choose one
Consider a transaction which includes following operations
…
Z= Z + 10
Write Z
...
Suppose that the value of Z after addition and the execution of this operation is 17. Now against the write operation the entry made in the log file will be
What does Z reflects in the above entry?
► Object being updated
► Identity of the transaction
► Object value
► Identity of the next transaction to be executed
In the entry, Tn reflects the identity of the transaction, Z is the object being updated
and 33 is the value that has to be placed in Z.

Question No: 15 ( Marks: 1 ) - Please choose one
Which of the following is not true regarding DB transactions?
► A set of database operations that are processed partly
► A database transaction is a logical unit of database operations
► A database transaction must be atomic
► A database transaction must contains the ACID property

Question No: 16 ( Marks: 1 ) - Please choose one
Which of the following is not true about input forms?
► Provide an easy, effective, efficient way to enter data into a table
► Especially useful when the person entering the data is not familiar with the inner workings
► Provide different controls to add data into the tables
► One input forms can populate one table at a time

Question No: 17 ( Marks: 1 ) - Please choose one
Which of the following is an example of volatile memory?
► RAM
► ROM
► Flash memory
► CPU

Question No: 18 ( 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: 19 ( 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: 20 ( Marks: 1 ) - Please choose one
Which of the following is Advantage of Re-Hashing technique to handle the collisions?
► Collisions don’t use primary table space
► Unlimited number of elements
► Fast access through use of main table space    (page29)
► Overhead of multiple linked lists

Question No: 21 ( Marks: 1 ) - Please choose one
Materialized views and Indexes are NOT similar by the following way(s).
► They consume storage space
► They must be refreshed when the data in their master tables changes.
► They can be accessed directly using a SELECT statement
► All of the Given
Unlike indexes, materialized views can be accessed directly using a SELECT statement.

Question No: 22 ( Marks: 1 ) - Please choose one
Select 'NORTH', CUSTOMER From CUST_DTLS Where REGION = 'N' Order By
CUSTOMER Union Select 'EAST', CUSTOMER From CUST_DTLS Where
REGION = 'E' Order By CUSTOMER
The above statement:
► Has an error - the string should be in double quotes.
► Has an error - ORDER BY clause.
► Does not have an error.
► Has an error - the string in single quotes 'NORTH' and 'SOUTH'.

Question No: 23 ( Marks: 1 ) - Please choose one
A software package designed to store and manage databases
► Database
► DBMS
► Data model
► Data

Question No: 24 ( Marks: 1 ) - Please choose one
Who is responsible for authorizing access to the database, for coordinating and monitoring its use ?
► Database Designers
► Database Administrators
► End Users
► Application Programmers

Question No: 25 ( Marks: 1 ) - Please choose one
A superkey that does not contain a subset of attributes that is itself a superkey is called a ____.
► candidate key
► primary key
► superkey
► secondary key

Question No: 26 ( Marks: 1 ) - Please choose one
A ____ entity has a primary key that is partially or totally derived from the parent entity in the relationship.
► strong
► weak
► business
► relationship

Question No: 27 ( Marks: 2 )
What is the “data type”?
A data type in programming, a classification identifying one of various types of data, as floating-point, integer, or Boolean, stating the possible values for that type, the operations that can be done on that type, and the way the values of that type are stored.

Question No: 28 ( Marks: 2 )
Which DML statement changes the values of one or more columns based on some conditions.
Update command

Question No: 29 ( Marks: 2 )
Name the two types of caching that are commonly used in personal computers?
Two types of caching are commonly used in personal computers: memory
caching and disk caching

Question No: 30 ( Marks: 2 )
What is ‘Serial Execution’?
Serial execution is an execution where transactions are executed in a sequential order,
that is, one after another. A transaction may consist of many operations. Serial
execution means that all the operations of one transaction are executer first, followed
by all the operations of the next transaction and like that.

Question No: 31 ( Marks: 3 )
Write three benefits of using VIEWS.
They work as table and, provide security from unauthorized access.
updated automatically when some changes occurs in original table
views give a group of user’s access to just the information they are allowed to

Question No: 32 ( Marks: 3 )
Shortly explain BYTE data field?
Some of more frequently supported numeric data types include Byte, Integer, and Long Integer. Each of these types supports different range of numeric values and takes 1, 4 or 8 bytes to store. Now, if we declare the age attribute as Long Integer, it will definitely serve the purpose, but we will be allocating unnecessarily large space for each attribute. A Byte type would have been sufficient for this purpose since you won’t find students or employees of
age more than 255, the upper limit supported by Byte data type.

Question No: 33 ( Marks: 3 )
State the main purpose of index in relation with the queries executions.
It will help searching the required data by directly accessing it via index.

Question No: 34 ( Marks: 5 )
Differentiate between the cluster index and non cluster index ?
1 A cluster index is a form of tables which consist of column and rows.
2 Cluster index exists on the physical level
3 It sorts the data at physical level
4 It works for the complete table
5 There is a whole table in form of sorted data
6 A table can contain only one cluster index
Non Cluster Index
1 A non cluster index is in the form of a report about the tables.
2 They are not created on the physical level but at the logical level
3 It does not sort the data at physical level
4 A table has 255 non clustered indexes
5 A table has many non clustered indexes.
6 It work on the order of data

Question No: 35 ( Marks: 5 )
Consider the two relations,
Department (Dept_Code, Dep_Name,Dept_Head) and
Employee(Emp_ID,Emp_Name, Designation, DoB, Dept).
Write SQL statement to drop the primary key of Department relation? The fields in
Employee should reflect the removal in Department table.
Answer:
If you wanted to drop any Primary key column you first have to drop the primary key constraints and then you can drop the primary key column.
ALTER TABLE Department DROP CONSTRAINT Pkzxyx;
ALTER TABLE Department DROP COLUMN Dept_code;

Question No: 36 ( Marks: 5 )
Briefly explain rollback and roll forward.

There are two modes to database recovery, ROLLBACK and ROLLFORWARD. If a transaction amends, the user's transaction on the database is rolled back. ROLLBACK is the most common form of recovery.

The other form of database recovery is ROLLFORWARD. If a hard disk fails, then a ROLLFORWARD is performed to restore the database to the point in time of the disk failure. The logs are kept because they would need to be applied to a full backup of the database to restore the database to the point of disk failure.

CS403- Database Management Systems (Session - 2) Spring 2010

MIDTERM EXAMINATION
Spring 2010
CS403- Database Management Systems (Session - 2)

Question No: 1 ( Marks: 1 ) - Please choose one

Which of the following is NOT a feature of Context DFD?
► one process (which represents the entire system)


► all sources/sinks (external entities)


► data flows linking the process to the sources and sinks (external entities)


► Sub-processes (which explain and decomposed the major process into small processes)



Question No: 2 ( Marks: 1 ) - Please choose one

Which of the following is true for the relational model?
►Degree of a relation is the number of rows in a relation.


►Null value is a blank or zero value given to an attribute value when its value is inapplicable or its value is unknown.


►Complex key is a key consisting of more than one attribute.


►Constraint is a rule that restricts the values in a database.



Question No: 3 ( Marks: 1 ) - Please choose one

Which one of the following four E-R diagrams is the typical result you obtain when you initially start with an E-R diagram containing just two entities, A and C, in a Many-to-Many relationship, and then introduce an associative entity (B).
►
►
►
►







Question No: 4 ( Marks: 1 ) - Please choose one

Which of the following most certainly implies the need for an entire table to implement?


► A binary relationship
► A ternary relationship
► A recursive relationship
► An identifying relationship
Question No: 5 ( Marks: 1 ) - Please choose one

Which of the following constraints enforces entity integrity?
► PRIMARY KEY
► FOREIGN KEY
► CHECK
► NOT NULL

Question No: 6 ( Marks: 1 ) - Please choose one

Which of the following is not true about relational tables?
► Column values are of the same kind.


► Each row is unique.


► Each column must have a unique name.


►The sequence of rows is significant.



Question No: 7 ( Marks: 1 ) - Please choose one

In a conceptual model for a university, what type of relationship exists between Grade and Student entities?
► 1:1
► 1:M
► M:M
► Ternary

Question No: 8 ( Marks: 1 ) - Please choose one
 Controlling redundancy in a database management system DOES NOT help to
► avoid duplication


► avoid unnecessary wastage of storage space


► avoid unauthorized access to data


► avoid inconsistency among data




Question No: 9 ( Marks: 1 ) - Please choose one

Which of the following is INCORRECT with respect to file systems?


► At the physical level, pointer or hashed address scheme may be employed to provide a certain degree of data independence at the user level.
► A logical record is concerned with efficient storage of information in the secondary storage devices.
► Some physical organisations use pointers to record blocks to locate records on disk.
► The efficiency of a file system depends on how efficiently operations such as retrieve, insert, update, delete may be performed on the information stored in the file.



Question No: 10 ( Marks: 1 ) - Please choose one

Which of the following functions are NOT performed by a database administrator?


► Planning, designing and implementing database systems


► Establishing standards and procedures for database systems


► communicating with database users


► Allocation of storage locations and data structures



Question No: 11 ( Marks: 1 ) - Please choose one

Select the correct statement about the ANSI/SPARC architecture.
► The conceptual level is a level of indication between the internal level and the external level.


► The internal level in a database system will definitely be relational.


► Any given database has many conceptual schemas and one physical schema, but it has only one external schemas.


► The external level is not concerned with individual user perceptions, while the conceptual level is concerned with a community user perception.



Question No: 12 ( Marks: 1 ) - Please choose one

Which of the following is a correct way to implement one-to-many relationship while designing tables?
► by splitting the data into two tables with primary key and foreign key relationships.


► using a junction table with the keys from both the tables forming the composite primary key of the junction table.


► by splitting each table into three
► as a single table and rarely as two tables with primary and foreign key relationships.



Question No: 13 ( Marks: 1 ) - Please choose one

Which of the following is not a benefit of normalization?

► Minimize insertion anomolies
► Minimize deletion anomolies
► Minimize updation anomolies

► Maximize redundancy



Question No: 14 ( Marks: 1 ) - Please choose one
 Consider the following relation R and its sample data. (Consider that these are the only tuples for the given relation)


Which of the following statements is NOT correct?
► The functional dependency (EmpNo, DeptNo) -> ProjNo holds over R.
► The functional dependency EmpNo -> DeptNo holds over R.
► The functional dependency ProjNo -> DeptNo holds over R.


► The functional dependency (EmpNo, ProjNo) -> DeptNo holds over R.



Question No: 15 ( Marks: 1 ) - Please choose one

The Entity Relation Model models


► Entities, Relationships and Processes
► Entities and Relationships
► Relationships
► Entities

Question No: 16 ( Marks: 1 ) - Please choose one

As part of database naming conventions, attribute names should use suffixes such as ID, NUMBER or CODE for the _______.
► primary key
► foreign key
► index
► determinant



Question No: 17 ( Marks: 2 )

For which purpose do we use relational data model?

Question No: 18 ( Marks: 2 )

What major problem can occur if we do not normalize a relation into the first normal form?

Question No: 19 ( Marks: 2 )

In which situation do a recursive relationship exist?


Question No: 20 ( Marks: 3 )

What is DML (Data Manipulation Language)?

Question No: 21 ( Marks: 3 )

What are the three important objectives for using data types while specifying attributes?

Question No: 22 ( Marks: 5 )

Briefly explain super key in relation with primary key.

Question No: 23 ( Marks: 5 )

When is a functional dependency F said to be minimal?

MIDTERM EXAMINATION CS403 past old paper

MIDTERM EXAMINATION CS403- Database Management Systems

Question No: 1 ( Marks: 1 ) - Please choose one
User rights information is stored in
► Physical database


► Catalog


► Logical database


► Buffer






Question No: 2 ( 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: 3 ( Marks: 1 ) - Please choose one
Which of the following is NOT a feature of Context DFD?
► one process (which represents the entire system)


► all sources/sinks (external entities)


► data flows linking the process to the sources and sinks (external entities)


► sub-processes (which explain and decomposed the major process into small processes)






Question No: 4 ( Marks: 1 ) - Please choose one
A relation (from the relational database model) consists of a set of tuples, which implies that
º all tuples in a relation must be distinct.




º relational model supports multi-valued attributes whose values can be represented in sets.




º for any two tuples, the values associated with all of their attributes may be the same.




º all tuples in a particular relation may have different attributes.






Question No


Question No: 6 ( Marks: 1 ) - Please choose one
Identify the constraint that limits the values that can be placed in a column.


► NOT NULL


► CHECK No Idea


► FOREIGN KEY


► UNIQUE






Question No: 7 ( Marks: 1 ) - Please choose one

Given are the relations of student and Instructor
































Consider the following table obtained using Student and Instructor relations.








Which relational algebra operation could have been applied on the pair of relations Student and
Instructor to obtain the above data?


► Instructor – Student
► Student ∩ Instructor
► Instructor ÷ Student
► Student – Instructor






Question No: 8 ( 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: 9 ( Marks: 1 ) - Please choose one
Consider the relation Interview(CandidateNo, InterviewDate, InterviewTime, StaffNo, RoomNo)
and the following functional dependencies.
FD1 : CandidateNo, InterviewDate -> InterviewTime, StaffNo, RoomNo
FD2 : RoomNo, InterviewDate, InterviewTime -> StaffNo, CandidateNo
FD3 : StaffNo, InterviewDate -> RoomNo
Which of the following is correct?


► The relation Interview is in 3NF
► The relation Interview is in BCNF.
► The FD3 violates 3NF.
► The FD2 violates 2NF.


Question No: 10 ( Marks: 1 ) - Please choose one
Identify the INCORRECT statement among the given.
► An entity may be an object with a physical existence like a car, a house or an Employee.
► One cannot consider something which has conceptual existence like a course in a degree




program as an entity.


► Age can be considered as a single value attribute of a person.
► An entity type describes the schema or intension for a set of entities which share the same structure.






Question No: 11 ( 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: 12 ( Marks: 1 ) - Please choose one
A collection of concepts that can be used to describe the structure of a database


► Database


► DBMS
► Data model
► Data


Question No: 13 ( Marks: 1 ) - Please choose one
An entity can be logically connected to another by defining a .
► hyperlink
► common attribute
► primary key
► superkey


Question No: 14 ( Marks: 1 ) - Please choose one


The

constraint specifies whether each entity supertype occurrence must also be a


member of at least one subtype.
► specialization
► uniqueness
► inheritance
► completeness


Question No: 15 ( Marks: 1 ) - Please choose one
Database management systems, operating systems, applications and utilities are all
examples of .
► hardware




► software
► computer infrastructure
► input and output


Question No: 16 ( Marks: 1 ) - Please choose one
Which of the following concepts is applicable with respect to 2NF?
► Full functional dependency
► Any kind of dependency
► Transitive dependency
► Non-transitive dependency






Question No: 17 ( Marks: 2 )
What do you know about Insertion anomaly?
Insertion anomaly indicates that we cannot insert a fact about one entity until we have an additional fact about another entity.






Question No: 18 ( Marks: 2 )
Define domain of an attribute.
In computing, the attribute domain is the set of values allowed in an attribute. For the relational model it is a requirement that each part of a tuple be atomic. The consequence is that each value in the tuple must be of some basic type, like a string or an integer. For the elementary type to be atomic it cannot be broken into more pieces.
Question No: 19 ( Marks: 2 )














1...
2...

Label the following diagram




Question No: 20 ( Marks: 3 )
Why do the relational data model considered as simple?
The relational model for database management is a database model based on first-order predicate logic The relational model provide a declarative method for specifying data and queries: we directly state what information the database contains and what information we want from it, and let the database management system software take care of describing data
structures for storing the data and retrieval procedures for getting queries answered


Question No: 21 ( Marks: 3 )
What is the intersection operation in relational algebra?




The intersection operation also has the requirement that both the relations should be union compatible, which means they are of same degree and same domains. It is represented by. If R and S are two relations and we take intersection of these two
relations then the resulting relation would be the set of tuples, which are in both R and
S. Just like union intersection is also commutative.
R _ S = S _ R INTERSECTION Example




The following diagram describes a part of an ER diagram.




Considering the above diagram, which of the given statements are True and which are
False.


i-Entity2 is a weak entity. True



ii-Cardinality ratio for Entity1:Entity2 in Rel1 is 1:N. True
iii-Attrib6 represents an attribute which is having composite nature. False
iv-Attrib3 is a kind of a derived attributes. True
v-Entity2 is participating totally in the Rel1 relationship.






Question No: 23 ( Marks: 5 )
How to implement one-to-many relationship while designing tables?


If the primary key in a parent table matches multiple foreign keys in a child table, then the relationship is one-to-many. This relationship is common in database applications. For example, an application for a sports league might access a team table and a
player table. Each team has multiple players, and each player belongs to a single
team. Every row in the child table (player) has a foreign key identifying the player's team. This foreign key matches the team table's primary key.


When designing such entity beans, you must decide whether both tables are represented by entity beans, or just one.

CS403 old past paper 2010 spring

FINALTERM  EXAMINATION

Spring 2010

CS403- Database Management Systems

Marks: 58

Question No: 2     ( Marks: 1 ) - Please choose one

Which of the following statements are Data Definition Language command?

► INSERT

► UPDATE

► GRANT

       ► TRUNCATE

Question No: 3     ( Marks: 1 ) - Please choose one

Which of the following statements is true about the views?

► view is always a complete set of all the tables in a database

► View can not be used for retrieving data

► The results of using a view are not permanently stored in the database.

► Rows can not be updated or deleted in the view

Question No: 4     ( Marks: 1 ) - Please choose one

How many clustered index(es) do each database table have?

► 2

► 3

► 5

       ► 1

Question No: 5     ( Marks: 1 ) - Please choose one

Suppose there are 4 rows and 3 columns in TABLE1 and 6 rows and 5 coulmns in TABLE2;what is the size of the cartesian product incase of CROSS JOIN between these two tables?

       ► 24

                                ► 20

► 18

► 15

Question No: 6     ( Marks: 1 ) - Please choose one

Which of the following is not one of the properties of Transaction?

► atomicity

► consistency

      ► redundancy

► durability

Question No: 7     ( Marks: 1 ) - Please choose one

Which of the following is INCORRECT about VIEWS?

► It is not possible to left out the data which is not required for a specific view.

► A database view displays one or more database records on the same page.

► Views can be used as security mechanisms

► Views are generally used to focus the perception each user

has of the database.

Question No: 8     ( 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: 9     ( Marks: 1 ) - Please choose one

Consider the relation Interview(CandidateNo, InterviewDate, InterviewTime, StaffNo, RoomNo) and the following functional dependencies.

FD1 : CandidateNo, InterviewDate -> InterviewTime, StaffNo, RoomNo FD2 : RoomNo, InterviewDate, InterviewTime -> StaffNo, CandidateNo FD3 : StaffNo, InterviewDate -> RoomNo

Which of the following is correct?

► The relation Interview is in 3NF

      ► The relation Interview is in BCNF.

► The FD3 violates 3NF.

► The FD2 violates 2NF.

Question No: 10     ( Marks: 1 ) - Please choose one

Which of the following types of partitioning reduces the chances of unbalanced partitions?

► vertical

► List

► Hash

► Range

Hash Partitioning:

It is a type of horizontal partitioning. In this type particular algorithm is applied and

DBMS knows that algorithm. So hash partitioning reduces the chances of unbalanced partitions to a large extent Question No: 11                    ( Marks: 1 ) - Please choose one Which of the following is one of the purposes of using DDL commands?

► inserting records into databases

► updating records into databases

► manipulating databases

► creating and destroying databases

Question No: 12     ( Marks: 1 ) - Please choose one

Which of the following statements creates a database named COMPANY.

► CREATE DATABASE company

► CREATE DB company

► ADD DATABASE copmany

► CREATE company DATABASE

Question No: 13     ( 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

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 can be set up to increase

efficiency

Question No: 14     ( Marks: 1 ) - Please choose one

Consider a transaction which includes following operations

…

Z= Z + 10

Write Z

...

Suppose that the value of Z after addition and the execution of this operation is 17. Now

against the write operation the entry made in the log file will be

What does Z reflects in the above entry?

► Object being updated

► Identity of the transaction

► Object value

► Identity of the next transaction to be executed

In the entry, Tn reflects the identity of the transaction, Z is the object being updated and 33 is the value that has to be placed in Z.

Question No: 15     ( Marks: 1 ) - Please choose one

Which of the following is not true regarding DB transactions?

► A set of database operations that are processed partly

► A database transaction is a logical unit of database operations

► A database transaction must be atomic

► A database transaction must contains the ACID property

The transaction-processing system ensures that either all operations in a transaction are completed without error, or none of them are. If some of the operations are completed but errors occur when the others are attempted, the transaction-processing system “rolls

back” all of the operations of the transaction (including the successful ones), thereby erasing all traces of the transaction and restoring the system to the consistent, known state that it

was in before processing of the transaction began. If all operations of a transaction are completed successfully, the transaction is committed by the system, and all changes to the database are made permanent; the transaction cannot be rolled back once this is done 

Question No: 16          ( Marks: 1 ) - Please choose one

Which of the following is not true about input forms?

► Provide an easy, effective, efficient way to enter data into a table

► Especially useful when the person entering the data is not familiar with the inner workings

► Provide different controls to add data into the tables

       ► One input forms can populate one table at a time Question No: 17     ( Marks: 1 ) - Please choose one Which of the following is an example of volatile memory?

       ► RAM

► ROM

► Flash memory

► CPU

Question No: 18     ( 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: 19     ( 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: 20     ( Marks: 1 ) - Please choose one

Which of the following is Advantage of Re-Hashing technique to handle the collisions?

► Collisions don’t use primary table space

► Unlimited number of elements

       ► Fast access through use of main table space (page29 vuzs)

► Overhead of multiple linked lists

Question No: 21     ( Marks: 1 ) - Please choose one

Materialized views and Indexes are NOT similar by the following way(s).

► They consume storage space

                                ► They must be refreshed when the data in their master tables changes.

       ► They can be accessed directly using a SELECT statement

► All of the Given

Unlike indexes, materialized views can be accessed directly using a SELECT statement.

Question No: 22     ( Marks: 1 ) - Please choose one

Select 'NORTH', CUSTOMER From CUST_DTLS Where REGION = 'N' Order

By

CUSTOMER Union Select 'EAST', CUSTOMER From CUST_DTLS Where

REGION = 'E' Order By CUSTOMER

The above statement:

► Has an error - the string should be in double quotes.

► Has an error - ORDER BY clause.

► Does not have an error.

► Has an error - the string in single quotes 'NORTH' and 'SOUTH'.

Question No: 23     ( Marks: 1 ) - Please choose one

A software package designed to store and manage databases

► Database

       ► DBMS

► Data model

► Data

A Database Management System (DBMS) is a software package designed to store and manage databases. Database Management Systems

Question No: 24     ( Marks: 1 ) - Please choose one

Who is responsible for authorizing access to the database, for coordinating and monitoring its use ?

► Database Designers

       ► Database Administrators

► End Users

► Application Programmers

Question No: 25     ( Marks: 1 ) - Please choose one

A superkey that does not contain a subset of attributes that is itself a superkey is called a

► candidate key

► primary key

► superkey

► secondary key

Question No: 26     ( Marks: 1 ) - Please choose one

A       _ entity has a primary key that is partially or totally derived from the parent entity in the relationship.

► strong

► weak

► business

► relationship

A weak entity has a primary key that is partially or totally derived from the parent entity in the relationship

Question No: 27     ( Marks: 2 )

What is the “data type”?

A data type (or datatype) In programming, a classification identifying one of various types of data, as floating-point, integer, or Boolean, stating the possible values for that type, the operations that can be done on that type, and the way the values of that type are stored. Question No: 28       ( Marks: 2 )

Which DML statement changes the values of one or more columns based on some conditions.

Update command

Question No: 29     ( Marks: 2 )

Name the two types of caching that are commonly used in personal computers? Two types of caching are commonly used in personal computers: memory caching and disk caching

Question No: 30     ( Marks: 2 )

What is ‘Serial Execution’?

Serial execution is an execution where transactions are executed in a sequential order,

that is, one after another. A transaction may consist of many operations. Serial execution means that all the operations of one transaction are executer first, followed by all the operations of the next transaction and like that.

Question No: 31     ( Marks: 3 )

Write three benefits of using VIEWS.

1.       They work as table and, provide security from unauthorized access.

2.       updated automatically when some changes occurs in original table

3.       views give a group of user’s access to just the information they are allowed to

Question No: 32     ( Marks: 3 )

Shortly explain BYTE data field?

Some of more frequently supported numeric data types include Byte, Integer, and Long

Integer. Each of these types supports different range of numeric values and takes 1, 4 or 8 bytes to store. Now, if we declare the age attribute as Long Integer, it will definitely serve the purpose, but we will be allocating unnecessarily large space for each attribute. A Byte type would have been sufficient for this purpose since you won’t find students or employees of age more than 255, the upper limit supported by Byte data type.

Question No: 33     ( Marks: 3 )

State the main purpose of index in relation with the queries executions.

It will help searching the required data by directly accessing it via index.

Question No: 34     ( Marks: 5 )

Differentiate between the cluster index and non cluster index ?

1 A cluster index is a form of tables which consist of column and rows.

2 Cluster index exists on the physical level

3 It sorts the data at physical level

4 It works for the complete table

5 There is a whole table in form of sorted data

6 A table can contain only one cluster index

Non Cluster Index

1 A non cluster index is in the form of a report about the tables.

2 They are not created on the physical level but at the logical level

3 It does not sort the data at physical level

4 A table has 255 non clustered indexes

5 A table has many non clustered indexes.

6 It work on the order of data

Question No: 35     ( Marks: 5 )

Consider the two relations,

Department (Dept_Code, Dep_Name,Dept_Head) and

Employee(Emp_ID,Emp_Name, Designation, DoB, Dept).

Write SQL statement to drop the primary key of Department relation? The fields in

Employee should reflect the removal in Department table. Answer:

If you wanted to drop any Primary key column you first have to drop the primary key

constraints and then you can drop the primary key column. (vuzs) ALTER TABLE Department DROP CONSTRAINT Pkzxyx; ALTER TABLE Department DROP COLUMN Dept_code; Question No: 36 ( Marks: 5 )

Briefly explain rollback and rollforward