DBMS question paper anna university NOV/DEC 2008(Regulation 2004)

B.E/B.Tech. DEGREE EXAMINATION,NOVEMBER/DECEMBER- 2008FIFTH SEMESTER(Regulation 2004)

Computer Science and Engineering

CS 1301-DATABASE MANAGEMENT SYSTEM

PART-A (10*2=20 MARKS)

1. what is Database Management Systems? Why do we need a DBMS?

2. What are three characteristics of a Relational database system?

3. State the difference between security and integrity.

4. What is decomposition and how does it address redundancy?

5. What is a heap file? How are pages organized in a heap file?

6. How does a B-tree differ from B+ trees? Why is B+ tree usually preferred as an access structure to a data file?

7. Give the meaning of the expression ACID transaction.

8. When are two schedules conflict equivalent?

9. Define the terms fragmentation and replication, in terms of where data is stored.

10. What are structured data types? What are collection types, in particular?

PART-B (5*16=80 MARKS)

11. (a) (i) Explain the component modules of a DBMS and their interactions with the architecture.(10)

(ii) Construct an ER diagram to a modal online book store.(6)

Or

(b) (i) Explain the basic relational algebra operations with the symbol used and example for each.(10)

(ii) Discuss about tuple relational calculus and domain relational calculus.(6)

12. (a) (i) Write short note on the following: (10 mark)

Data manipulation language(DML)

datadefinition language(DDL)

Transaction control statement(TCS)

Data control language (DCL)

Data administration statements (DAS).

(ii) Consider the database given by the following schemas: (6 mark)

( Cust _no, sales_ person _no, City)Sales_person(sales_person_no,Sales_person_name,common_prec,year_of_hire)Give the sql for the following:Display the list of all customers by cust_no with the city in which each is located.List the names of the sales persons who have accounts in Delhi.

Or

(b) (i) Consider the universal relation R( A,B,C,D,E,F,G,H,I,J) and the set of FD’s. G=({A,B}?{C}?{B,D}?{E,F} ,{A,D}?{G,H}, {A}?{I}’{H}?{J}) What is the key of R? Decompose R into 2NF, then 3NF relations. (10mark)

(ii) Discuss how schema refinement through dependency analysis and normalization can improve schemas obtained through ER design.(6 mark)

13. (a) (i) Describe the different types of file organization? Explain using a sketch of each of them with their advantages and disadvantages. (10marks)

(ii) Describe static hashing and dynamic hashing. (6 marks)

Or

(b) (i) Explain the index schemas used in DBMS. (10 marks)

(ii)How does a DBMS represent a relational query evaluation plan? (6 marks)

14. (a) (i) Explain Timestamp-based concurrency control protocol and the modifications implemented in it. (10 marks).

(ii)Describe shadow paging recovery technique (6 marks)

Or

(b)(i) Describe strict two-phase locking protocol. (10 marks)

(ii) Explain the log based recovery technique (6 marks)

15. (a)(i) Explain 2-phase commitment protocol and the behavior of this protocol during lost messages and site failures. (12 marks)

(ii) Describe X path and X query with an example.(4 marks)

Or

(b)(i) Explain Data mining and data warehousing. (12 marks)

(ii) Describe the anatomy of XML document.

DBMS QUESTION PAPER ANNA UNIVERSITY(MAY/JUNE 2007)

CS 1301- DATABASE MANAGEMENT SYSTEMSMAY/JUNE 2007

PART-A

1. List five reponsibilities of the DB manager.

2. Give the limitations of ER model? How do you overcome this?

3. Define query language.Give the calssification of query language.

4. Why it is necessary to decompose a relation?

5. Give any two advantages of spare index over dense dense index.

6. Name the different types of joins supported in SQL.

7. What are the types of transperencies that a distributed database must support? why?

8. What benefit is provided by strict-two phase locking? What are the disanvantages result?

9. Briefly write the overall process of dataware housing.

10. What is an active database?

PART - B

11 (a) (i) What are the types of knowledge discovered during data mining? Explain with suitable examples.

(ii) HIghlight the features of object oriented database.

OR

(b) (i) What is nested relationls? Give example.(ii) Explain the structure of XML with suitable example.

12 (a) (i) Compare file system with database system.

(ii) Explain the architecture of DBMS.

OR

(b) (i) What are the steps involved in designing a database application? Explain with an example(ii) List the possible types of relations that may exist between two entities. How would you realise that into tables for a binary relation?

13. (a) (i) What are the relational algebra operations supported in SQL? Write the SQL statement for each operation.

(ii) Justify the need of normalization with examples

OR

(b) (i) What is normalization? Explain 1NF,2NF,3NF and BCNF with suitable example.

(ii) What is FD? Explain the role of Fd in the process of normalization.

14.(a) (i) Explain the security features provided in commercial query languages.

(ii) What are the steps involved in query processing? How would you estimate the cost of the query?

OR

(b) (i) Explain the different properities of indexes in detail.

(ii) Explain various hashing techniques.

15.(a) (i) Explain the four important properities of transaction that a DBMS must ensure to maintain database .

(ii) What is RAID? List the different levels in RAID technology and explain its features.

OR

(b) (i) What is concurrenct control? How is it implemented in DBMS? Explain.

(ii) Explain various recovery techniques during ttansaction in detail.

SQL CREATE TABLE

CREATE TABLE
Tables are the basic structure where data is stored in the database. Given that in most cases, there is no way for the database vendor to know ahead of time what your data storage needs are, chances are that you will need to create tables in the database yourself. Many database tools allow you to create tables without writing SQL, but given that tables are the container of all the data, it is important to include the CREATE TABLE syntax in this tutorial.

Before we dive into the SQL syntax for CREATE TABLE, it is a good idea to understand what goes into a table. Tables are divided into rows and columns. Each row represents one piece of data, and each column can be thought of as representing a component of that piece of data. So, for example, if we have a table for recording customer information, then the columns may include information such as First Name, Last Name, Address, City, Country, Birth Date, and so on. As a result, when we specify a table, we include the column headers and the data types for that particular column.

So what are data types? Typically, data comes in a variety of forms. It could be an integer (such as 1), a real number (such as 0.55), a string (such as 'sql'), a date/time expression (such as '2000-JAN-25 03:22:22'), or even in binary format. When we specify a table, we need to specify the data type associated with each column (i.e., we will specify that 'First Name' is of type char(50) - meaning it is a string with 50 characters). One thing to note is that different relational databases allow for different data types, so it is wise to consult with a database-specific reference first.

The SQL syntax for CREATE TABLE is
CREATE TABLE "table_name"("column 1" "data_type_for_column_1","column 2" "data_type_for_column_2",... )
So, if we are to create the customer table specified as above, we would type in
CREATE TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date) ;

Sometimes, we want to provide a default value for each column. A default value is used when you do not specify a column's value when inserting data into the table. To specify a default value, add "Default [value]" after the data type declaration. In the above example, if we want to default column "Address" to "Unknown" and City to "Mumbai", we would type in
CREATE TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50) default 'Unknown',
City char(50) default 'Mumbai',
Country char(25),
Birth_Date date) ;
You can also limit the type of information a table / a column can hold. This is done through the CONSTRAINT keyword. The constraint concept is posted seperately in the blog

SQL PRIMARY KEY

PRIMARY KEY

A primary key is used to uniquely identify each row in a table. It can either be part of the actual record itself , or it can be an artificial field (one that has nothing to do with the actual record). A primary key can consist of one or more fields on a table. When multiple fields are used as a primary key, they are called a composite key.
Primary keys can be specified either when the table is created (using CREATE TABLE) or by changing the existing table structure (using ALTER TABLE).

The following is the syntax to implement a primary key constraint on a column while creating tables:

CREATE TABLE Customer (SID integer PRIMARY KEY, Last_Name varchar(30), First_Name varchar(30));

The syntax to add a primary key by a altering table is given below

ALTER TABLE Customer ADD PRIMARY KEY (SID);

Note: Before using the ALTER TABLE command to add a primary key, you'll need to make sure that the field is defined as 'NOT NULL' -- in other words, NULL cannot be an accepted value for that field.

SQL CONSTRAINTS(NOT NULL,CHECK,UNIQUE)

CONSTRAINTS
When you bind a business rule to a column in the table then those rules are called the Constraints. Constraints are defined while creating the table. Say for example, you cannot have an employee who does not have a name, then employee name column in employee table should be a NOT NULL column. The NOT NULL is a constraint.
The constraint types and short descriptions are given below:
Constraint Types And Description
NOT NULL:-
you must provide the value in that column. you cannot leave that column blank
PRIMARY KEY:-
No duplicate values allowed, for example Empno in Employee table should be unique
CHECK:-
checks the value and controls the inserting and updating values.
DEFAULT:-
Assigns a default value if no value is given.
-------------------------------------------------------------------------------------------------
NOT NULL:-
If we have a business rule saying that all customers should have a name, we cannot have any customer with out a name. So to implement that business rule we can create customer table and specify customer name column as NOT NULL (constraint)
Example
CREATE TABLE EMPLOYEE (EMPNO NUMBER(4) PRIMARY KEY, ENAME VARCHAR(4) NOT NULL);
-----------------------------------------------------------------------------------------------
CHECK
Check constraint is used where we define a condition on a column. Check constraint consists of the keyword
col_name datatype CHECK (col_name in(value1, value2))
Example
If you have a business rule saying that all employees in the organization should get atleast $500 then we can use CHECK constraint while creating table.
CREATE TABLE EMPLOYEE ( EMPNO NUMBER(4) PRIMARY KEY, ENAME VARCHAR(4) NOT NULL, SALARY NUMBER(7,2) CHECK (SALARY > 500) );
-----------------------------------------------------------------------------------------------------------------
DEFAULT
While inserting a row into a table without giving values for every column, SQL must insert a default value to fill in the excluded columns, or the command will be rejected. The most common default value is NULL. This can be used with columns not defined with a NOT NULL.
Default value assigned to a column while creating the table using
CREATE TABLE operation.
Example
CREATE TABLE ITEM (ITEM_ID NUMBER(4) PRIMARY KEY, ITEM_NAME VARCHAR(15),ITEM_DESC VARCHAR(100), QOH NUMBER(4) DEFAULT 100);
Assigning a default value 0 for numeric columns makes the computation.
-------------------------------------------------------------------------------------------------
UNIQUE:-
The values entered into a column are unique ie no duplicate values exists.This constraint ensures business that there is no duplicates allowed.

SQL WHERE

Next, we might want to conditionally select the data from a table. For example, we may want to only retrieve stores with sales above $1,000. To do this, we use the WHERE keyword. The syntax is as follows:
SELECT "column_name"FROM "table_name"WHERE "condition"
For example, to select all stores with sales above $1,000 in Table Store_Information,
Table Store_Information:
-----------------------------------------------------------
store_name Sales Date
------------------------------------------------------------
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999
------------------------------------------------------------
we key in,
SELECT store_nameFROM Store_InformationWHERE Sales > 1000;
Result:
store_name
Los Angeles

SQL DISTINCT

The SELECT keyword allows us to grab all information from a column (or columns) on a table. This, of course, necessarily mean that there will be redundancies. What if we only want to select each DISTINCT element? This is easy to accomplish in SQL. All we need to do is to add DISTINCT after SELECT. The syntax is as follows:
SELECT DISTINCT "column_name"FROM "table_name"
For example, to select all distinct stores in Table Store_Information,
Table Store_Information
----------------------------------------------------------------
store_name Sales Date
----------------------------------------------------------------
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999
-----------------------------------------------------------------
we key in,
SELECT DISTINCT store_name FROM Store_Information;
Result:
store_name(column name)
Los Angeles
San Diego
Boston