Database Management System

a) Discuss the advantages and disadvantages of DBMS.

b) Why is relation database approach better than carlier methods?

b) Explain Multi-valued dependencies with the help of examples.

Consider the following relational schema:

Emp (cmpno, ename, job, sal, comm., hiredate, deptno)

Dept (deptno, dname, location)

Give an expression in SQL for the following Queries:

a) Find the names of employees who work in deptno 10 and 20. 

Consider the following relational schema:

Emp (cmpno, ename, job, sal, comm., hiredate, deptno)

Dept (deptno, dname, location)

Give an expression in SQL for the following Queries:

d) Create the table Dept.

Explain the difference between physical and logical data independence.

List differences between file processing and a DBMS.

What is generalization and specialization? What is specialization and generalization hierarchies and lattices?

Explain aggregation with an example.

What is entity integrity, referential integrity and foreign keys? Explain. 

Consider the relational database.
Employee person names, street, city Works (person name, comp-name, salary)
Company (comp-name, city)
Manages (person name, manager name)
Write the queries in relational algebra and SOL
i) Find the names, street address, and cities of residence of all employees who work for First Bank Corp and earn more than Rs. 10K/month.
ii) Find the names of all managers whose employees work for First Bank Corp.

What do you mean by functional dependency?

State inference rules of functional dependency?

Consider the following relation:
STUDENT (Subject, Student, Faculty, Term, Grade)
Each student receives only one grade in a subject during a terminal examination. A student can take many subjects and each subject can have more than one faculty in a terminal. Define FD and MVD for this relation. Check the normal form for the relation. If necessary, further normalize.

Explain what is convert channel. Explain polyinstantiation.

Define authorization and security in the database.

Discuss about Statistical database.

Explain timestamp ordering protocol.

What is wound-wait and wait -die? Explain.

Discuss the relative advantages of centralized and distributed databases.

When it is useful to have replication or fragmentation of data?

Explain: fragmentation transparency, replication transparency, location transparency and naming transparency.

What is a data model? How schema is different from instances? Give examples.

Name different database users and explain the responsibilities of DBA.

What do you mean by mapping constraints? Distinguish between primary key and foreign key.

What do you mean by ISA relationship? Explain the concept of aggregation with a suitable example.

Consider the relational schema:
employee (employee no, name, office,age)
books(isbn, title, authors, publisher)
loan(employee no, isbn, date)
Find the names of employees who have borrowed a book published by McGraw-Hill, write this query in relational algebra

Consider the relational schema:
employee (employee no, name, office,age)
books(isbn, title, authors, publisher)
loan(employee no, isbn, date)
Find the names of employees who have borrowed all books published by McGraw Hil, write this query in relational algebra.

Consider the relational schema:
employee (employee no, name, office,age)
books(isbn, title, authors, publisher)
loan(employee no, isbn, date)
Find the names of employees who have borrowed more than six different published book by McGraw-Hil, write this query in relational algebra.

Consider the relational schema:
employee (employee no, name, office,age)
books(isbn, title, authors, publisher)
loan(employee no, isbn, date)
Find the names of employees who have borrowed more than six different book publishedby McGraw-Hill. write this query in relational algebra.

Consider the relational schema:
employee (employee no, name, office,age)
books(isbn, title, authors, publisher)
loan(employee no, isbn, date)
For each publisher , find the names of employees who have borrowed more than seven books of that publishers, write this query in relational algebra.

Why do we normalize a database? What are different normal forms?

What is DKNE? Explain multi-valued dependency.

What do you mean by referential integrity constraint? Explain.

What do you mean by data encryption? Why do we need statistical databases?

Define transaction. Discuss the problems associated with concurrent execution of multiple transactions.

Explain timestamp ordering protocol for concurrency control.

What are the fragments of relations in distributed systems?

Discuss the advantages of Distributed Database Management System.

What does defining, manipulating and sharing of a database mean?

Discuss the main characteristics of the database approach and how it differs from traditional file systems.

Describe the three - schema architecture. What is the difference between logical and physical data Independence? 

Differentiate the following:

I) Entity and Attribute 

Differentiate the following:

ii) Entity type and Entry set

Differentiate the following:

iii) Strong and weak Entity

Differentiate the following:

iv) Recursive relationship and Identifying relationships

A database is being constructed to keep track of the teams and games of a sports league. A team has a number of players, not all of whom participate in each game. It is desired to keep track of the players participating in each game for each team, the positions they played in that game and the result of the game. Design an ER diagram for this application, stating any assumptions you make. Choose your favorite sport (e.g Cricket Base ball, Football). 

Explain the entity integrity and referential integrity constraints. Why each is considered important?

Briefly discuss the different types of update operations on a relation.

Consider the following relational schema.

  Emp (eid, ename, age, sal)

  Works_foreid, pid, #hrs)

  Proj (pid, pname)

Write the Queries in Relational algebra for the following:

i) Retrieve Employee Id and Name of the employees who work for all the projects. 

Consider the following relational schema.

  Emp (eid, ename, age, sal)

  Works_foreid, pid, #hrs)

  Proj (pid, pname)

Write the Queries in Relational algebra for the following:

ii). Retrieve Name and Age of employees whose salary > 10000.

Consider the following relational schema.

  Emp (eid, ename, age, sal)

  Works_foreid, pid, #hrs)

  Proj (pid, pname)

Write the Queries in Relational algebra for the following:

iii) For each employee, get the number of projects and number of hours worked on projects.

Consider the following relational schema.

  Emp (eid, ename, age, sal)

  Works_foreid, pid, #hrs)

  Proj (pid, pname)

Write the Queries in Relational algebra for the following:

iv) Retrieve Names of employees working on "CSE" project.

Give the complete syntax of SELECT statement in SQL and discuss all the clauses with examples. 

What are Aggregate functions in SQL? Explain with examples.

Consider the following Relational schema:

   Lives (Name, Street, City)

   Works (Name, Cname, Salary)

   Located (Cname, City) Manager (Name, MGR_Name)

Write the Queries in SQL for the following:

i) Find the people who earn more than every employee of "Canara Bank". 

Consider the following Relational schema:

   Lives (Name, Street, City)

   Works (Name, Cname, Salary)

   Located (Cname, City) Manager (Name, MGR_Name)

Write the Queries in SQL for the following:

ii) Find the Name and city of all the people who work for "SBI" and earn more than 35000 rupees. 

Consider the following Relational schema:

   Lives (Name, Street, City)

   Works (Name, Cname, Salary)

   Located (Cname, City) Manager (Name, MGR_Name)

Write the Queries in SQL for the following:

iii) Find the company employing the most people.

Consider the following Relational schema:

   Lives (Name, Street, City)

   Works (Name, Cname, Salary)

   Located (Cname, City) Manager (Name, MGR_Name)

Write the Queries in SQL for the following:

iv) Show 20% raise in salary of all managers.

Consider the following Relational schema:

   Lives (Name, Street, City)

   Works (Name, Cname, Salary)

   Located (Cname, City) Manager (Name, MGR_Name)

Write the Queries in SQL for the following:

v) Find the companies located in every city in which ICICI" is located.

Explain Insert, Delete and Update statements in SQL with examples.

What are views in SQL? Show how views are specified in SQL. List the advantages of views. 

Differentiate :

i) Trigger and Assertion

Differentiate :

ii) Embedded and Dynamic SQL

Differentiate :

iii) Outer join and Self Join

Differentiate :

iv) Super key and Candidate key

What are the informal guidelines for relation schemas? Explan.

Define INF, 2NF and 3NF with examples.

What is BCNF? How it is different from 3NF? Prove that a relation with two attributes is always in BCNF

What do you mean by MVD? When does it arise? Define 4NF.

Define Join Dependency and 5NF.

Let R = {SSN, Ename, Pnumber, Prame. Plocn, Hrs)

and D = {R₁, R₂. R₃, where

R₁ = Emp = {SSN, Ename)

R₂ = Proj= {Pnumber, Pnamie, Ploch)

R₃ = Work_ON = (SSN, Pnumber, Hrs)

The following functional dependencies hold on relation R.

F = (SSN  Ename;

              Pnumber    {Pname, Plocn);

         (SSN, Pnumber)  Hrs).

Prove that above decomposition of relation 'R' has the lossless join property. 

What are the problems with concurrency? Explain each with an example.

 With a neat state transition diagram, discuss the different states of a transaction. 

Wine and explain the Two-phase locking protocol for concurrency control. 

Define the following (Give example where necessary):-

(a) Multivalued dependency

Define the following (Give example where necessary):-

(b) Dependency preservation

Define the following (Give example where necessary):-

c) Functional dependency

Define the following (Give example where necessary):-

(d) Referential integrity

Define the following (Give example where necessary):-

(e) Internal schema

Define the following (Give example where necessary):-

(f) Candidate key

Define the following (Give example where necessary):-

(g) Triggers

Define the following (Give example where necessary):-

(h) DBA

Define the following (Give example where necessary):-

(i) Weak entity

Define the following (Give example where necessary):-

(j) Foreign key

(a) Differentiate between the term Generalization and Specialization with example. 

(b) Differentiate between Fragmentation. Replication and Transparency.

(a) Define closure of FD set. For the following relation

R (A, B, C, D)

(b) What is canonical cover? How it is computed?

(a) Explain the "ACID" properties in brief.

(b) What do you mean by serializability. Discuss the conflict serializability and view serializability with examples.

(a) Draw and explain a neat diagram of three level architecture of database system.

(b) Construct an ER diagram for 'Hospital management system. There would be a set of patients and set of doctors. For each patient, there would be a log of various tests and examinations conducted. Make assumptions if necessary and clearly state them. 

(a) How the B+ tree index files are maintained? Explain.

(b) Explain the method of "query processing" in brief.

(a) Discuss clearly inner join, outer join and theta join with example.

(b) For the relation R=(A, B, C) and S=(D, E, F) and relation (R) and s(s), give SQL statement for following expressions and explain.

 (i) 

(ii) 

(iii) 

(iv) 

Attempt the following

(a) How the deadlock is detected in transactions? Explain its recovery process also. 

Attempt the following

(b) Normalize the following relation to as much as possible citing the reasons and anamoloies. R (emp-no, name, street, city, company name, company-city, manager-name, age, salary, marital-status, spouse-name)

Attempt the following

(c) State the mulit-version time stamp based protocol. Suggests a scheme to avoid the phantom phenomenon.

a) Discuss the main characteristics of the database approach and how it litters from file systems.

b) Design an ER schema for keeping track of information about votes taken in the U.S. House of Representatives during the current two-year congressional session. The database seeds to keep track of each U.S. STATE's Name (e.B-, Texas, New York, California) and includes the Region of the state (whose domain is (Northeast, Midwest, Southeast, Southwest, West) Each CONGRESSPERSON in the House of Representatives is described by their Name, and includes the District represented, the StartDate when they were first elected, and the political Party they belong to (whose domain is (Republicar, Democrat, Independent, Other). The database keep track of each BILL (ie, proposed law), and includes the BillName, the DateOf Vote on the bill, whether the bill PassedOtFailed (whose domain is YES, NO}), and the Sponsor (the congressperson(s) who sponsored-i.e., proposed the bill). The database keeps track of how each congressperson voted on each bill (domain of vote attribute is (Yes. No, Abstain, Absent)). Draw an ER schema diagrams for the above application State clearly • any assumptions you make.

a) What are the responsibilities of the DBA and the database designers?

b) Consider the following set of requirements for a university database that is used to Keep track of students transcripts.

         • The university keep track of cach student's name, student number, social security number, current address and phone, permanent address and phone, birthdate, sex, class (freshuman, sophomore, ... graduate), major department, minor department (if any). and degree program (B.A., B.S.,..,Ph.D.). Some user applications need to refer to the city, state, and zip code of the student's permanent address and to the student's last name. Both social security number and student number have unique values for each student

         •  Each department is described by & name, department code, office number, office phone, and college. Both name and code have unique values for each department

         •Each course has & course name, description, course number, number of semester hours, level, and offering department. The value of course number is unique for each course

         •Each section has an instructor, semester, year, course, and section number. The section number distinguishes sections of the same course that are taught during the same semester/year, its values are 1, 2, 3,..up to the number of sections taught during each semester. 

         •A grade report has a student, section, letter grade, and numeric grade (0, 1, 2, 3, or 4). Design an ER scheme for this application, and draw an ER diagram for that schema Specify key attributes of each entity type and structural constraints on each relationship type. Note any unspecified requirements, and make appropriate assumptions to make the specification complete.

a) How can the key and foreign key constraints be enforced by the DBMS is the enforcement technique you suggest difficult to implement? Can the constraint checks be executed efficiently when updates are applied to the database? 

b) What is a view in SQL and how is a defed? Discuss the problems that may arise when one attemps to update a view. How a view typically implemented?

c) How does SOL allow implementation of the entity integrity and referential in egrley constraints? What about general integrity constraints?

a) What is join dependencies? Explain with the help of example.

b) What are the types of anomalies? How these anomalies can remove explain with the help of example.

b) Explain B-index files with the help of examples

a) Since every conflict-serializable schedule is view serializable, why do we emphasize conflict serializability rather than view serializability?

b)  Show that the two-phase looking protocol ensures conflict serializability, and that  transactions can be serialized according to their lock points

a) List the physical storage media available on the computers you use routinely. Give the speed with which data can be accessed on each medium

Consider the following two transactions:

Add lock and unlock Instructions to transactions The and 132. so that they observe the two-phase locking protocol. Can the execution of these transactions result in a deadlock?

a) What is a view? How it is related to data independence?

b) What is a Relation Schema and a Relation?

c) What is Functional Dependency?

d) Define Join and enlist its types

c) What is database Trigger?

f) Compare Non-clustered and clustered index

g) Define correlated subqueries".

h) What is Fully Functional dependency?

i) What do you mean by Correlated subquery?

j) What is cursors give different types of cursors?

Consider the following six relations for an order pressing database application in a company

CUSTOMER(Cust, Crame, City)

ORDER(Ordert, Odate, Cust, Ord Amt)

ORDER_ITEM(Ordery, Item, Quy)

ITEM(Item# Unit_price)

SHIPMENT(Ordert, Warehouse, Ship_date)

WAREHOUSE(Warehouse, City)

Here, Ord. Amt refers to total dollar amount of an order; Odate is the date the order was placed Ship date is the date an order is shipped from the warehouse. As me that an order can be shipped from several warehouses. Specify the foreign keys for the above schema, stating any Assumptions you make. Then specify the following queries in relational algebra # List the Order and Ship date for all orders shipped from Warehouse number 'W2"

a) What is cursors give different types of cursors?

Consider the following six relations for an order pressing database application in a company

CUSTOMER(Cust, Crame, City)

ORDER(Ordert, Odate, Cust, Ord Amt)

ORDER_ITEM(Ordery, Item, Quy)

ITEM(Item# Unit_price)

SHIPMENT(Ordert, Warehouse, Ship_date)

WAREHOUSE(Warehouse, City)

Here, Ord. Amt refers to total dollar amount of an order; Odate is the date the order was placed Ship date is the date an order is shipped from the warehouse. As me that an order can be shipped from several warehouses. Specify the foreign keys for the above schema, stating any Assumptions you make. Then specify the following queries in relational algebra # List the Order and Ship date for all orders shipped from Warehouse number 'W2"

b) List the Warehouse information from which the Customer named 'Jose Lopez' was supplied his orders. Produce a listing Order Warehouse.

Consider the following six relations for an order pressing database application in a company

CUSTOMER(Cust, Crame, City)

ORDER(Ordert, Odate, Cust, Ord Amt)

ORDER_ITEM(Ordery, Item, Quy)

ITEM(Item# Unit_price)

SHIPMENT(Ordert, Warehouse, Ship_date)

WAREHOUSE(Warehouse, City)

Here, Ord. Amt refers to total dollar amount of an order; Odate is the date the order was placed Ship date is the date an order is shipped from the warehouse. As me that an order can be shipped from several warehouses. Specify the foreign keys for the above schema, stating any Assumptions you make. Then specify the following queries in relational algebra # List the Order and Ship date for all orders shipped from Warehouse number 'W2"

c) Produce a listing: CUSTNAME, #OFORDERS, AVG ORDER AMT, where the middlecolumn is the total number of orders by the customer and the last column is the average order amount for that customer.

Consider the following six relations for an order pressing database application in a company

CUSTOMER(Cust, Crame, City)

ORDER(Ordert, Odate, Cust, Ord Amt)

ORDER_ITEM(Ordery, Item, Quy)

ITEM(Item# Unit_price)

SHIPMENT(Ordert, Warehouse, Ship_date)

WAREHOUSE(Warehouse, City)

Here, Ord. Amt refers to total dollar amount of an order; Odate is the date the order was placed Ship date is the date an order is shipped from the warehouse. As me that an order can be shipped from several warehouses. Specify the foreign keys for the above schema, stating any Assumptions you make. Then specify the following queries in relational algebra # List the Order and Ship date for all orders shipped from Warehouse number 'W2"

c) List the orders that were not shipped within 30 days of ordering.

Consider the following six relations for an order pressing database application in a company

CUSTOMER(Cust, Crame, City)

ORDER(Ordert, Odate, Cust, Ord Amt)

ORDER_ITEM(Ordery, Item, Quy)

ITEM(Item# Unit_price)

SHIPMENT(Ordert, Warehouse, Ship_date)

WAREHOUSE(Warehouse, City)

Here, Ord. Amt refers to total dollar amount of an order; Odate is the date the order was placed Ship date is the date an order is shipped from the warehouse. As me that an order can be shipped from several warehouses. Specify the foreign keys for the above schema, stating any Assumptions you make. Then specify the following queries in relational algebra # List the Order and Ship date for all orders shipped from Warehouse number 'W2"

c) List the Order# for orders that were shipped from all warehouses that the company has in New York.

a) Suppose you are given a relation (table) R=(A,B,C,D,E) with the functional dependencies: 

1. Find all candidata keys

a) Suppose you are given a relation (table) R=(A,B,C,D,E) with the functional dependencies: 

2.Identify the highest normal form that satisfies 

a) Suppose you are given a relation (table) R=(A,B,C,D,E) with the functional dependencies: 

3 If is not in Boyce Codd Normal Form (CNF), then decompose the relation into additional relations until all relations are in BCNF. 

a) Explain the CODD's rules of RDBMS.

b) Discuss the timestamp ordering techniques for concurrency control.

a) Discuss the different types of transaction failures that may occur in database environment,

b) What is checkpoint? Explain the different recovery techniques when database crashes.

Answer the following:

Draw and explain the architecture of DBMS. Compare it with file system.

Answer the following:

b) Explain the role of E-R model in database design.

Answer the following:

c) How can the two tables be joined using left outer and right outer joins?

Answer the following:

d) What problems are encountered if data is not stored in normalized table?

Answer the following:

e) List the problems associated with two phase locking protocol.

Consider the following relational schema:

Emp (cmpno, ename, job, sal, comm., hiredate, deptno)

Dept (deptno, dname, location)

Give an expression in SQL for the following Queries:

b) Increase the salary by Rs 1500 for the employees who are "CLERK.

Consider the following relational schema:

Emp (cmpno, ename, job, sal, comm., hiredate, deptno)

Dept (deptno, dname, location)

Give an expression in SQL for the following Queries:

c) Display the details of employees who work in same deptno as of the employee 'SMITH'. 

Consider the following relational schema:

Emp (cmpno, ename, job, sal, comm., hiredate, deptno)

Dept (deptno, dname, location)

Give an expression in SQL for the following Queries:

e) Display total salary of employees of each deptno and display those deptno

whose total salary is more than Rs. 30000. 

Differentiate between:

a) Primary key and Foreign Key

Differentiate between:

b) View and Indexes

Differentiate between:

c) Serializable and non serializable transactions

Differentiate between:

d) 2NF and 3CNF

Differentiate between:

e) Data and Metadata

a) Construct an E-R diagram for a hospital management system with a set of doctors and a set of patients. With each patient, a series of various tests and examinations are conducted. On the basis of preliminary report patients are admitted to a particular specialty ward.

a) Explain the differences among extenal, internal and conceptual schemas. 

 Related with database, explain the term:

i) Data integrity

 Related with database, explain the term:

ii) Concurrency

 Related with database, explain the term:

iii) Data independence

 Related with database, explain the term:

iv) Referential integrity