CS403 Complete Solved Final Term Subjective

www.allvupastpapers.blogspot.com

 Physical Record and Denormalization

Denormalization is a technique to move from higher to lower normal forms of

database modeling in order to speed up database access. Denormalization process is

applied for deriving a physical data model from a logical form. In logical data base

design we group things logically related through same primary key. In physical

database design fields are grouped, as they are stored physically and accessed by

DBMS. In general it may decompose one logical relation into separate physical

records, combine some or do both. There is a valid reason for denormalization that is

to enhance the performance.

 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

Indexed Sequential Summary:

Following are salient features of Indexed sequential file structure:

Records are stored in sequence and index is maintained.

Dense and nondense types of indexes are maintained.

Track overflows and file overflow areas are ensured.

Cylinder index increases the efficiency .

 Optical disks record data by burning microscopic holes in the surface of the disk with

a laser. To read the disk, another laser beam shines on the disk and detects the holes

by changes in the reflection pattern.

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

www.allvupastpapers.blogspot.com

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

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

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

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

www.allvupastpapers.blogspot.com

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.

www.allvupastpapers.blogspot.com

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.

www.allvupastpapers.blogspot.com

    1.      TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.

2.      TRUNCATE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.

3.      TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. The counter used by an identity for new rows is reset to the seed for the column.

4.      You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

5.      TRUNCATE cannot be rolled back.

6.      TRUNCATE is DDL Command.

7.      TRUNCATE Resets identity of the table

    Checkpoint:

Checkpoint is also a record or an entry in the log file. It serves as a milestone or

www.allvupastpapers.blogspot.com

reference point in the log file. At certain point in time the DBMS enters a log

entry/record in the log file and also performs certain operations listed below:

Question No: 28    ( Marks: 2 )

 Define domain of an attribute.

Ans:

Domain is the set of possible values that an attribute can have, that is, we specify a set of values either in the form of a range or some discrete values, and then attribute can have value out of those values. Domain is a form of a check or a constraint on attribute that it cannot have a value outside this set.

www.allvupastpapers.blogspot.com

   

Question No: 29    ( Marks: 2 )

 Write the main feature of volatile storage media?

    Computer storage that is lost when the power is turned off is called as volatile storage. For example RAM

Question No: 30    ( Marks: 2 )

 Suppose you want to delete a table row by row and record an entry in the transaction log for each deleted row. Which DML command will you use?

   DELETE  FROM ;

Question No: 31    ( Marks: 3 )

 Write three benefits of using VIEWS.

Views are generally used to focus, simplify, and customize the perception

each user has of the database. Views can be used as security mechanisms

by allowing users to access data through the view, without granting the users

permissions to directly access the underlying base tables of the view.Views allow users to focus on specific data that interests them and on the specific tasks for which they are responsible. Unnecessary data can be left out of the view. This also increases the security of the data because users

www.allvupastpapers.blogspot.com

   

Question No: 32    ( Marks: 3 )

 SELECT * FROM Persons

WHERE FirstName LIKE '%da%';

what does the above statement return?

    Ans: It will select all fields from the table from persons table where in the first name carry character da at any position

Question No: 33    ( Marks: 3 )

 What is the difference between a primary key and a unique key with reference to clustered and nonclustered indexes?

1. Primary key does not allow null value but unique key allows null values
2. We can declare only one primary key in a table but a table can have multiple unique key
3. Primary key has a cluster index so one table can have only one cluster index
4. non-clustered index can be create on any key so a table can have multiple non-clusted key

   www.allvupastpapers.blogspot.com

Question No: 34    ( Marks: 5 )

 Consider a table named COMPANY with fields COMPANY_NAME,

DESCRIPTION, ORDER_NUMBER. Write an SQL statement to display company names in reverse alphabetical order.

    SELECT COMPANY_NAME FROM COMPANY ORDER BY COMPANY_NAME DESC;

www.allvupastpapers.blogspot.com

Question No: 35    ( Marks: 5 )

 Name the five main components of Database management systems software.

§                     DBMS Engine accepts logical request from the various other DBMS subsystems, converts them into physical equivalents, and actually accesses the database and data dictionary as they exist on a storage device.

§                     Data Definition Subsystem helps user to create and maintain the data dictionary and define the structure of the files in a database.

§                     Data Manipulation Subsystem helps user to add, change, and delete information in a database and query it for valuable information. Software tools within the data manipulation subsystem are most often the primary interface between user and the information contained in a database. It allows user to specify its logical information requirements.

§                     Application Generation Subsystem contains facilities to help users to develop transaction-intensive applications. It usually requires that user perform a detailed series of tasks to process a transaction. It facilitates easy-to-use data entry screens, programming languages, and interfaces.

§                     Data Administration Subsystem helps users to manage the overall database environment by providing facilities for backup and recovery, security management, query optimization, concurrency control, and change management.

 www.allvupastpapers.blogspot.com

 

Question No: 36    ( Marks: 5 )

 Give 4 similarities between Materialized views and indexes.

1. They consume storage space.
2. They must be refreshed when the data in their master tables changes.
3. They improve the performance of SQL execution when they are used for query rewrites.
4. Their existence is transparent to SQL applications and users.

Logical data independence

Logical data independence provides the independence in a way that changes in conceptual model do not affect the external views. Or simply it can be stated at the Immunity of external level from changes at conceptual level.

Commands of DDL

1.      Create

2.      Alter

3.      Truncate

4.      Drop

www.allvupastpapers.blogspot.com

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.

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

www.allvupastpapers.blogspot.com

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 1:
What is database?

ANSWER:
A database is a logically coherent collection of data with some inherent meaning, representing some aspect of real world and which is designed, built and populated with data for a specific purpose.

QUESTION 2:
What is DBMS?

ANSWER:
? Redundancy is controlled.
? Unauthorised access is restricted.
? Providing multiple user interfaces.
? Enforcing integrity constraints.
? Providing backup and recovery.

QUESTION 4:
www.allvupastpapers.blogspot.com

What is a Database system?

ANSWER:
The database and DBMS software together is called as Database system.

QUESTION 5:
Disadvantage in File Processing System?

ANSWER:
? Data redundancy & inconsistency.
? Difficult in accessing data.
? Data isolation.
? Data integrity.
? Concurrent access is not possible.
? Security Problems. .

QUESTION 6:
Describe the three levels of data abstraction?

ANSWER:
The are three levels of abstraction:

? Physical level: The lowest level of abstraction describes how data are stored.

? Logical level: The next higher level of abstraction, describes what data are stored in database and what relationship among those data. www.allvupastpapers.blogspot.com

? View level: The highest level of abstraction describes only part of entire database.

QUESTION 7:
Define the "integrity rules"

ANSWER:
www.allvupastpapers.blogspot.com

There are two Integrity rules.

? Entity Integrity: States that ?Primary key cannot have NULL value?

? Referential Integrity: States that ?Foreign Key can be either a NULL value or should be Primary Key value of other relation.

QUESTION 8:
What is extension and intension?

ANSWER:
Extension  It is the number of tuples present in a table at any instance. This is time dependent.

Intension   It is a constant value that gives the name, structure of table and the constraints laid on it.

QUESTION 9:
What is System R? What are its two major subsystems?

ANSWER:
System R was designed and developed over a period of 1974 79 at IBM San Jose Research Center . It is a prototype and its purpose was to demonstrate that it is possible to build a Relational System that can be used in a real life environment to solve real life problems, with performance at least comparable to that of existing system.

Its two subsystems are

? Research Storage

? System Relational Data System.

QUESTION 10:
How is the data structure of System R different from the relational structure?

ANSWER:
Unlike Relational systems in System R

? Domains are not supported

? Enforcement of candidate key uniqueness is optional

? Enforcement of entity integrity is optional

? Referential integrity is not enforced

QUESTION 11:
What is Data Independence?

ANSWER:
Data independence means that ?the application is independent of the storage structure and access strategy of data?. In other words, The ability to modify the schema definition in one level should not affect the schema definition in the next higher level.

Two types of Data Independence: www.allvupastpapers.blogspot.com
? Physical Data Independence : Modification in physical level should not affect the logical level.
? Logical Data Independence : Modification in logical level should affect the view level.

NOTE: Logical Data Independence is more difficult to achieve

www.allvupastpapers.blogspot.com

QUESTION 12:
What is a view? How it is related to data independence?

ANSWER:
A view may be thought of as a virtual table, that is, a table that does not really exist in its own right but is instead derived from one or more underlying base table. In other words, there is no stored file that direct represents the view instead a definition of view is stored in data dictionary.
Growth and restructuring of base tables is not reflected in views. Thus the view can insulate users from the effects of restructuring and growth in the database. Hence accounts for logical data independence. .

QUESTION 13:
What is Data Model?

ANSWER:
A collection of conceptual tools for describing data, data relationships data semantics and constraints. www.allvupastpapers.blogspot.com

QUESTION 14:
What is E R model?

ANSWER:
This data model is based on real world that consists of basic objects called entities and of relationship among these objects. Entities are described in a database by a set of attributes.

QUESTION 15:
What is Object Oriented model?

ANSWER:
This model is based on collection of objects. An object contains values stored in instance variables with in the object. An object also contains bodies of code that operate on the object. These bodies of code are called methods. Objects that contain same types of values and the same methods are grouped together into classes.

QUESTION 16:
What is an Entity?

ANSWER:
It is a 'thing' in the real world with an independent existence.

QUESTION 17:
What is an Entity type?

ANSWER:
It is a collection (set) of entities that have same attributes.

QUESTION 18:
What is an Entity set?

www.allvupastpapers.blogspot.com

ANSWER:
It is a collection of all entities of particular entity type in the database.

QUESTION 19:
What is an Extension of entity type?

ANSWER:
The collections of entities of a particular entity type are grouped together into an entity set.

QUESTION 20:
What is Weak Entity set?

ANSWER:
An entity set may not have sufficient attributes to form a primary key, and its primary key compromises of its partial key and primary key of its parent entity, then it is said to be Weak Entity set.

QUESTION 21:
What is an attribute?

ANSWER:
It is a particular property, which describes the entity.

QUESTION 22:
What is a Relation Schema and a Relation?

ANSWER:
A relation Schema denoted by R(A1, A2, ?, An) is made up of the relation name R and the list of attributes Ai that it contains. A relation is defined as a set of tuples. Let r be the relation which contains set tuples (t1, t2, t3, ..., tn). Each tuple is an ordered list of n values t=(v1,v2, ..., vn).

QUESTION 23:
What is degree of a Relation?

ANSWER:
It is the number of attribute of its relation schema.

QUESTION 24:
What is Relationship?

ANSWER:
It is an association among two or more entities.

QUESTION 25:
What is Relationship set?

ANSWER:
The collection (or set) of similar relationships.

QUESTION 26:
What is Relationship type?

www.allvupastpapers.blogspot.com

ANSWER:
Relationship type defines a set of associations or a relationship set among a given set of entity types.

QUESTION 27:
What is degree of Relationship type?

ANSWER:
It is the number of entity type participating.

QUESTION 28:
What is Data Storage   Definition Language?

ANSWER:
The storage structures and access methods used by database system are specified by a set of definition in a special type of DDL called data storage definition language.

QUESTION 29: www.allvupastpapers.blogspot.com
What is DML (Data Manipulation Language)?

ANSWER:
This language that enable user to access or manipulate data as organised by appropriate data model.

? Procedural DML or Low level: DML requires a user to specify what data are needed and how to get those data.

? Non Procedural DML or High level: DML requires a user to specify what data are needed without specifying how to get those data.

QUESTION 30:
What is VDL (View Definition Language)?

ANSWER:
It specifies user views and their mappings to the conceptual schema.

QUESTION 31:
What is DML Compiler?

www.allvupastpapers.blogspot.com

ANSWER:
It translates DML statements in a query language into low level instruction that the query evaluation engine can understand.

QUESTION 32:
What is Query evaluation engine?

ANSWER:
It executes low level instruction generated by compiler.

QUESTION 33:
What is DDL Interpreter?

ANSWER:
It interprets DDL statements and record them in tables containing metadata.

QUESTION 34:
What is Record at a time?

ANSWER:
The Low level or Procedural DML can specify and retrieve each record from a set of records. This retrieve of a record is said to be Record at a time.

QUESTION 35:
What is Set at a time or Set oriented?

ANSWER:
The High level or Non procedural DML can specify and retrieve many records in a single DML statement. This retrieve of a record is said to be Set at a time or Set oriented.

QUESTION 36:
What is Relational Algebra?

ANSWER:
It is procedural query language. It consists of a set of operations that take one or two relations as input and produce a new relation.

QUESTION 37:
What is Relational Calculus?

www.allvupastpapers.blogspot.com

ANSWER:
It is an applied predicate calculus specifically tailored for relational databases proposed by E.F. Codd. E.g. of languages based on it are DSL ALPHA, QUEL.

QUESTION 38:
How does Tuple oriented relational calculus differ from domain oriented relational calculus

ANSWER:
The tuple oriented calculus uses a tuple variables i.e., variable whose only permitted values are tuples of that relation. E.g. QUEL

The domain oriented calculus has domain variables i.e., variables that range over the underlying domains instead of over relation. E.g. ILL, DEDUCE.

QUESTION 39:
What is normalization?

ANSWER:
It is a process of analysing the given relation schemas based on their Functional Dependencies (FDs) and primary key to achieve the properties

? Minimizing redundancy

? Minimizing insertion, deletion and update anomalies.

QUESTION 40:
What is Functional Dependency?

ANSWER:
A Functional dependency is denoted by X Y between two sets of attributes X and Y that are subsets of R specifies a constraint on the possible tuple that can form a relation state r of R. The constraint is for any two tuples t1 and t2 in r if t1[X] = t2[X] then they have t1[Y] = t2[Y]. This means the value of X component of a tuple uniquely determines the value of component Y.

QUESTION 41:
When is a functional dependency F said to be minimal?

ANSWER:
? Every dependency in F has a single attribute for its right hand side.

? We cannot replace any dependency X A in F with a dependency Y A where Y is a proper subset of X and still have a set of dependency that is equivalent to F.

? We cannot remove any dependency from F and still have set of dependency that is equivalent to F.

QUESTION 42:
What is Multivalued dependency?

www.allvupastpapers.blogspot.com

ANSWER:
Multivalued dependency denoted by X Y specified on relation schema R, where X and Y are both subsets of R, specifies the following constraint on any relation r of R: if two tuples t1 and t2 exist in r such that t1[X] = t2[X] then t3 and t4 should also exist in r with the following properties

? t3[x] = t4[X] = t1[X] = t2[X]

? t3[Y] = t1[Y] and t4[Y] = t2[Y]

? t3[Z] = t2[Z] and t4[Z] = t1[Z]

where [Z = (R (X U Y)) ]

QUESTION 43:
What is Lossless join property?

ANSWER:
It guarantees that the spurious tuple generation does not occur with respect to relation schemas after decomposition.

QUESTION 44:
What is 1 NF (Normal Form)?

ANSWER:
The domain of attribute must include only atomic (simple, indivisible) values.

QUESTION 45:
What is Fully Functional dependency?

ANSWER:
It is based on concept of full functional dependency. A functional dependency X Y is full functional dependency if removal of any attribute A from X means that the dependency does not hold any more. www.allvupastpapers.blogspot.com

QUESTION 46:
What is 2NF?

ANSWER:
A relation schema R is in 2NF if it is in 1NF and every non prime attribute A in R is fully functionally dependent on primary key.

www.allvupastpapers.blogspot.com

QUESTION 47:
What is 3NF?

ANSWER:
A relation schema R is in 3NF if it is in 2NF and for every FD X A either of the following is true

? X is a Super key of R.

? A is a prime attribute of R.

In other words, if every non prime attribute is non transitively dependent on primary key.

QUESTION 48:
What is BCNF (Boyce Codd Normal Form)?

ANSWER:
A relation schema R is in BCNF if it is in 3NF and satisfies an additional constraint that for every FD X A, X must be a candidate key.

QUESTION 49:
What is 4NF?

ANSWER:
A relation schema R is said to be in 4NF if for every Multivalued dependency X Y that holds over R, one of following is true

? X is subset or equal to (or) XY = R.

? X is a super key.

QUESTION 50:
What is 5NF?

ANSWER:
A Relation schema R is said to be 5NF if for every join dependency {R1, R2, ..., Rn} that holds R, one the following is true

? Ri = R for some i.

? The join dependency is implied by the set of FD, over R in which the left side is key of R.

www.allvupastpapers.blogspot.com

By : ADEEL ABBAS 

www.allvupastpapers.blogspot.com 

AdeelAbbasbk@gmail.com