Database Management Systems

Chapter wise Assignment K. Thammi Reddy

Associate Professor

Dept of CSE,IT & MCA

GITAM

1. Introduction to Database Systems

a.) Define DBMS and what are the advantages of DBMS?

b.) With neat sketch explain the levels of abstraction?

c.) With neat sketch explain the structure of DBMS?

d.) Explain the characteristics of people who deal with databases?

2. The Entity-Relationship model

a.) Explain the steps involved in the database design?

b.) Define the following terms: 1.) Entity 2.)Relationship 3.) KEY

4.)Descriptive attributes 5.) Primary key

c.) a university database contains information about teachers and courses. Teachers teach courses ,each of the following situations concerns the teachers relationship set. For each situation Draw an ER diagram that describes it.

1). Every teacher must teach some course.

2). Teacher can teach the same course in several semesters, and each offers must be recorded.

3). Every Professor must teach some course.

4). Explain about weak entries, class hierarch and aggregation.
3. The Relational model

a) Define relation scheme and a relation instance.

b) Write the syntax for the following commands:

1) Creating a table

2) Inserting values into it

3) Selecting values from a table

4) Deleting row from a table

c) Define a View?

d) Answer each of the following questions briefly:

The questions are based on the following relational scheme

Emp (eid: integer, enami: String, age: integer, salary:real

Works (eid: integer, did: integer, Pet-time: integer )

Dept ( did: integer, dnaname : string, budget : real, managerid: integer).

1). Write the SQL Statements required to create the above relations including appropriate versions of all primary key and foreign key constraint.

2). Write an SQL Statement to give every employee a 15% raise.

3). Write the conceptual Evolution strategy for select statement?

4. Relational Algebra and calculus.

a). Explain the following operators in relational Algebra.

1). Selection 2) Projection 3) Set operators 4) join

b). Explain about title Relational calculus.

c). Explain about Domain Relational calculus.

d). Consider the following schema

Suppliers ( Sid : integer, sham : string address: string )

Parts (pid : integer: pname: string, color: string)

Catalog ( Sid: integer, Pid : integer, cost: real)

Write the following queries in relational algebra, TRC, DRC

1). Find the names of suppliers who supply some red part.

2). Find the sids of supplers who supply some red or green part.

3). Find the sids of supplers who supply every part.

5). Scheme Refinement and Normal Forms.

a). What are the problems that will be caused by Redund any.

b). Discuss the problems related to de composition.

c). Define FD and Attribute closure.

d). Define different Normal forms with examples.

6). Transaction Management

a). Explain important properties of a transaction.

b). Explain different Anomalies Associated with Inter leaved Execution.

c). Discuss Lock based concurrency control algorithm.

7). Concurrency Control

a). Explain about view serializability

b). Explain about lock management

c). Explain about Dead locks

d). Explain about Concurrency control without locks

8). Crash Recovery

a). Explain about ARIES Algorithm.

b). Write briefly about Log?

c). Write about checkpoint?

d). Explain with Example the undo Algorithm?

e). Explain about media Recovery.