CS403 – Database Management Systems Subjective type questions, Spring 2014

CS403 Currently Solved Final Term Papers

Differentiate simple and composite view?

Simple views are created from tables and are used for creating secure manipulation over the tables or structures of the database. Views make the manipulations easier to perform on the database

Complex Views Complex views are by definition views of type which may comprise of many of elements, such as tables, views sequences and other similar objects of the database. When talking about the views we can have views of one table, views of one table and one view, views of multiple tables views of multiple views and so on…

                                                                                                  
analyze the statement. Justify your answer

in RAID level 1 if either drive fails no data is lost.

RAID Level 1 provides redundancy by writing all data to two or more drives. The performance of a level 1 array tends to be faster on reads and slower on writes compared to a single drive, but if either drive fails, no data is lost. This is a good entry-level redundant system, since only two drives are required; however, since one drive is used to store a duplicate of the data, the cost per megabyte is high. This level is commonly referred to as mirroring.

How far should the recovery manager go backward in the log-file to trace the transaction affected from the crash that the recovery manager needs to redo or ignore.

The log file may contain so many entries and going too much back or starting right from the start of the log file will be inefficient. For this purpose another concept of ‘checkpoint’ is used in the recovery procedure.

 In case of crash, the RM monitors the log file up to the last checkpoint. The checkpoint guarantees that any prior commit has been reflected in the database. The RM has to decide which transactions to redo and which to ignore and RM decides it on the following bases:

·         Transactions ended before the checkpoint, are ignored altogether.

·         Transactions which have both begin and the commit entries, are Redone. It does not matter if they are committed again.

·         Transactions that have begun and an abort entry are ignored.

·         Transactions that have a begin and no end entry (commit or rollback) are ignored

Differentiate between procedural and non-prodedural DML.

There are two types of DML.

First is Procedural: in which the user specifies what data is needed and how to get it.

Second is Nonprocedural: in which the user only specifies what data is needed.

What are the criteria to judge the effectiveness of a cache memory?
The effectiveness of a cache is judged by its hit rate.

write rules you will follow while writing any SQL command ?

Following are the rules for writing the commands in SQL:-

• Reserved words are written in capital like SELECT or INSERT.

• User-defined identifiers are written in lowercase

• Identifiers should be valid, which means that they can start with @,_ alphabets ,or with numbers. The maximum length can be of 256. The reserved words should not be used as identifiers.

• Those things in the command which are optional are kept in [ ]

• Curly { } braces means required items

• | means choices

• [,…..n] means n items separated by comma

describe Dense key index

Dense Index:

·         An index record appears for every search key value in file.

·         This record contains search key value and a pointer to the actual record.

write two primary modes of locks ?

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

Q Analyze the statement is it correct or not:-

SQL is a structural language that is, it allow the user to concentrate on specifying what data is required rather than concentrating on the how to get?

The above statement is not correct. The correct statement is the following:

SQL is a non-procedural language that is, it allows the user to concentrate on specifying what data is required rather than concentrating on the how to get it.

discuss the purpose of FROM clause in SQL query?

The FROM clause specifies the tables accessed, the FROM clause always follows the SELECT clause. The SELECT clause specifies a list of columns to be retrieved from the tables in the FROM clause. In a SELECT statement, you specify data sources in the FROM clause. The FROM clause may also contain a JOIN operation.

Define the purpose of Deal Lock ?

A deadlock is a situation in which two computer programs sharing the same resource are effectively preventing each other from accessing the resource, resulting in both programs ceasing to function. OR

A deadlock is a situation in which two or more competing actions are each waiting for the other to finish, and thus neither ever does. 

What is unary and ternary Relationship?

Ternary Relationship:  A Ternary relationship is the one that involves three entities e.g.

STUDENT-CLASS-FACULTY.

Unary Relationship: An ENTITY TYPE linked with itself, also called recursive relationship. Example Roommate, where STUDENT is linked with STUDENT

SQL query on Student-Record table to find lowest semester marks

SELECT MIN(column_name) FROM table_name;

SELECT MIN(semester_marks) FROM Student-Record;

Tell the abbreviation of ACID? 

Atomicity, Consistency, Isolation, and Durability 

Read the statement if incorrect rewrite in correct form.

“Hashing provides slow, time consuming data retrieve from sequential files”. Marks 2

Answer: Correct statement is

Hashing provides rapid, non-sequential, direct access to records.

What is the purpose of windows controls? Write two examples. Marks 2

Windows Controls: There are number of controls which are used to take input and display output. 

Examples

1.      buttons,

2.      checkboxes etc

Use the Alter command in the data structure?

The purpose of ALTER statement is to make changes in the definition of a table already created through Create statement. It can add, and drop the attributes or constraints, activate or deactivate constraints. It modifies the design of an existing table. The format of this command is as under:

Syntax

ALTER TABLE table { ADD [COLUMN] column type [(size)] [DEFAULT default]

| ALTER [COLUMN] column type [(size)] [DEFAULT default]

| ALTER [COLUMN] column SET DEFAULT default

| DROP [COLUMN] column | RENAME [COLUMN] column TO columnNew }

Using the ALTER TABLE statement, we can alter an existing table in several ways. We can:

·         Use ADD COLUMN to add a new column to the table. Specify the name, data type, an optional size, and an optional default value of the column.

·         Use ALTER COLUMN to alter type, size or default value of an existing column.

·         Use DROP COLUMN to delete a column. Specify only the name of the column.

·         Use RENAME COLUMN to rename an existing column. We cannot add, delete or modify more than one column at a time.

Describe To_Date() function

Term: TO_DATE

Definition:

The Oracle TO_DATE function will convert either a character string or an expression into a date value.

Write query to Delete a row from table  ........3 marks.

SQL DELETE Syntax

DELETE FROM table_name
WHERE some_column=some_value;

Write query to Delete a row from table  ........3 marks.

SQL DELETE Syntax

DELETE FROM table_name
WHERE some_column=some_value;

How view can be used for security purpose?
This increases the security of the data because users can see only the data that is defined in the view and not the data in the underlying table.

Analyze the statement is it correct or not:-

SQL is a structural language that is ,it allow the user to concentrate on specifying what data is required  rather than concentrating on the how to get ?

 SQL is a non-procedural language that is, it allows the user to concentrate on specifying what data is required rather than concentrating on the how to get it.

discuss the purpose of FROM clause in SQL query?

From is used to get data from specify table like select  fname from employee

Write query to Delete a row from table  ........3 marks

 Delete from table name where column name=’value’

what are the primary modes of locks? Differentiat between shared and update lock.
An update lock ensures that another transaction can take only a shared lock on the same data. Update locks are held by transactions that intend to change data (not just read it).

A shared lock signifies that another transaction can take an update or another shared lock on the same piece of data. Shared locks are used when data is read (usually in pessimistic locking mode).

What is the difference Between Voltile and non volatile Memory?

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

Computer storage that is not lost when the power is turned off is called as non – volatile storage,

Wht is physical Memory device write three characteristics?

Classification of Physical Storage MediaStorage media are classified according to following characteristics: Speed of access Cost per unit of data Reliability We can also differentiate storage as either Volatile storage Non-volatile storage

Write a query for creating the view

CREATE VIEW st_view1 AS (select stName, stFname, prName FROM student WHERE prName = 'MCS')

Analyze the below query and findout why it will not work. correct the error

SELECT*FORM customer WHERE Year>'2004'

year column is invalid because its reserved word

indexes can only be define when table have atleast 100 rows

Indexes can be defined even when there is no data in the table

Differentiate between procedural and non-prodedural DM

There are two types of DML.First is procedural in which: the user specifies what data is needed and how to get it. Second is nonprocedural in which the user only specifies what data is needed.

Dif b/w RAID & tape

RAID disk drives are used frequently on servers but aren't generally necessary for personal computers.

tapes are used only for backup and archiving because they are sequential-access devices

Differentiate simple and composite view?

Simple Views:

As defined earlier simple views are created from tables and are used for creating secure manipulation over the tables or structures of the database. Views make the manipulations easier to perform on the database.

Complex Views

 Complex views are by definition views of type which may comprise of many of elements, such as tables, views sequences and other similar objects of the database. When talking about the views we can have views of one table, views of one table and one view, views of multiple tables views of multiple views and so on…

Difference between horizontal and vertical partitioning? Write in tabular form
Horizontal Partitioning:

Table is split on the basis of rows, which means a larger table is split into smaller tables. Now the advantage of this is that time in accessing the records of a larger table is much more than a smaller table. It also helps in the maintenance of tables, security, authorization and backup.

What is view? Write general syntax of view? Write types of view?

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.

• Materialized View • Simple Views • Complex View • Dynamic Views.

CREATE VIEW CLASSLOC2 AS SELECT COURSE#, ROOM FROM CLASSLOC

Write two index classifications?

Primary Indexes:

Secondary Indexes:

Creating Index

This sql command is incorrect. Correct the command

 CREATE FURNITURE TABLE CREATE VUSTUDENT TABLE

Write a query for creating the view.

CREATE VIEW st_view1 AS (select stName, stFname, prName FROM student WHERE prName = 'MCS')

What is the Difference between DDL  AND DML?
Data Definition Language:
 It deals with the structure of database. The DDL (Data Definition Language) allows specification of not only a set of relations,
Following are the three different commands of DDL:-

Create

ALTER

DROP

TRUNCATE

Data Manipulation Language:

The non-procedural nature of SQL is one of the principle characteristics of all 4GLs - Fourth Generation Languages - and contrasts with 3GLs (eg, C, Pascal, Modula-2, COBOL, etc) in which the user has to give particular attention to how data is to be accessed in terms of storage method, primary/secondary indices,

The DML component of SQL comprises of following basic statements:

Insert To add new rows to tables.

Select To retrieve rows from tables Update To modify the rows of tables

Wht is physical Memory device write three characteristics?
Classification of Physical Storage MediaStorage media are classified according to following characteristics: Speed of access Cost per unit of data Reliability We can also differentiate storage as either Volatile storage Non-volatile storage

Creat a table.. us k attributes or data type b mention karnay thay.

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

What is the difference Between Voltile and non volatile Memory?

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

Computer storage that is not lost when the power is turned off is called as non – volatile storage,

Write query to Delete a row from table  ........3 marks

The DELETE statement is used to delete rows in a table.

SQL DELETE Syntax

DELETE FROM table_name
WHERE some_column=some_value;

What are the primary modes of locks? Differentiate between shared and update lock
An update lock ensures that another transaction can take only a shared lock on the same data. Update locks are held by transactions that intend to change data (not just read it).

A shared lock signifies that another transaction can take an update or another shared lock on the same piece of data. Shared locks are used when data is read (usually in pessimistic locking mode).

Analyze the below query and findout why it will not work. correct the error

SELECT*FORM customer WHERE Year>'2004'

year column is invalid because its reserved word

Discuss the purpose of FROM clause in SQL query?

From is used to get data from specify table like select  fname from employee

Analyze the statement is it correct or not:-

SQL is a structural language that is ,it allow the user to concentrate on specifying what data is required  rather than concentrating on the how to get ?

SQL is a non-procedural language that is, it allows the user to concentrate on specifying what data is required rather than concentrating on the how to get it.

indexes can only be define when table have atleast 100 rows

Indexes can be defined even when there is no data in the table

Differentiate between procedural and non-prodedural DM

There are two types of DML.First is procedural in which: the user specifies what data is needed and how to get it. Second is nonprocedural in which the user only specifies what data is needed.

Dif b/w RAID & tape

RAID disk drives are used frequently on servers but aren't generally necessary for personal computers.

tapes are used only for backup and archiving because they are sequential-access devices