Friday, May 15, 2015

Date's Tweleve Rules for Distributed Database Systems - Database (DBMS) Independence

Date's Tweleve Rules for Distributed Database Systems - Database [DBMS] Independence


DBMS independence

The system must support any vendor's database product. The DBMS instances at different sites all support the same interface - they do not necessarily all have to be copies of the same DBMS software. The ideal distributed system should be heterogeneous and should provide DBMS independence.
For example, if Ingres and Oracle both supported the official SQL standard, the Ingres site and the Oracle site might be able to talk to each other in a distributed database system. Pictorial representation of the same is given below.


Fig 1 - Distributed database with database independence (source: Prof. Sin Min Lee ppt from CJ Date book)








Wednesday, May 6, 2015

Database Management Systems - Visvesvaraya Technological University December 2014 January 2015 Question

Database Management Systems - Visvesvaraya Technological University December 2014 January 2015 Questions / December 2014 January 2015 DBMS questions of Visvesvaraya Technological University / DBMS University questions with answers


USN                                                          10CS54
Fifth Semester B.E. Degree Examination, Dec. 2014/Jan. 2015

Database Management Systems

Time: 3 hrs.                                                     Max. Marks: 100
Note: Answer any FIVE full questions, selecting at least TWO questions from each part.
PART – A
1. a. Explain the typical component module of a DBMS, with a neat diagram. (10 marks)
b. Define the following with examples; (10 marks)
i) Value set ii) Complex attribute iii) Data model iv) Schema construct v) Metadata.

2. a. What are the structural constraints on a relation type? Explain with examples. (05 marks)
b. What is a weak entity type? Explain the role of partial key in design of weak entity type. (05 marks)
c. Draw an ER diagram for mail order database considering the following requirements. Here employee takes order for parts from customers. (10 marks)
i) The mail order company has employees each identified by a unique employee ID, first and last name, address, gender, zip code.
ii) Each customer of the company is identified by a unique customer ID, first and last name, address, location, and zip code.
iii) Each part sold by the company is identified by a unique part number, part name, price and quantity in stock.
iv) Each order placed by a customer taken by an employee and is given a unique order number. Each order contains specified quantities of one or more parts. Each order has a date of receipt as well as an expected ship date. The actual ship date is also recorded.
v) Each customer can place number of orders and each order placed by one customer only.
vi) Each employee can take any number of orders but each order belongs to only one employee.
vii) Each part placed by number of customers and each customer can place order for number of parts.
viii) Write assumptions made.

3. a. Discuss the entity integrity and referential integrity constraints. Why each is considered important? (05 marks)
b. Discuss the various types of JOIN operations. Why is theta join required? (05 marks)
c. Given the schema;
STUDENT (USN, NAME, BRANCH, PERCENTAGE)
FACULTY (FID, FNAME, DEPT, DESIGNATION, SALARY)
COURSE (CID, CNAME, FID)
ENROLL (CID, USN, GRADE)
Give the relational algebra expressions for the following;
i) Retrieve the name and percentage of all students for the course 10CS54.
ii) List the departments having an average salary of the faculties above Rs. 30,000.
iii) List the name of the course having students grade ‘A’ maximum. (10 marks)

4. a. Explain the different constraints that can be applied during table creation in SQL, with an example.  (08 marks)
b. Write the SQL queries for the following database schema;
Works (pname, cname, salary)
Lives (pname, street, city)
Located_in (cname, city)
Manager (pname, mgrname)
i) Find the names of all persons who live in the city ‘Bangalore’.
ii) Retrieve the names of all persons of ‘Infosys’ whose salary is between Rs. 50,000 and Rs. 90,000.
iii) Find the names of all persons who live and work in same city.
iv) List the names of the people who work for ‘Tech M’ along with the cities they live in.
v) Find the average salary of ‘Infosys’ persons. (12 Marks)

PART – B

5. a. Explain the syntax of SELECT statement in SQL. (04 marks)
b. How is view created and dropped? What problems are associated with updating views? (06 Marks)
c. Explain the following; i) Embedded SQL ii) Database stored procedure. (10 Marks)

6. a. What is functional dependency and who specifies the functional dependency that holds among the attributes of a relation schema? (05 Marks)
b. Consider R = {A, B, C, D, E, F}. FDs are {A BC, C E, CD EF}. Show that AD F. (05 Marks)
c. Find the key and normalize. (10 marks)
Book_title | Auth_name | Book_type | List_price | Affiliation | Publication.
FDs are {Book_title Book_type, Publication,
                   Auth_name Affiliation,
                   Book_type List_price}

7. a. Which normal form is based on the concept of multi-valued functional dependency? Explain the same with example. (10 Marks)
b. Given a relation R with 4 attributes R = (A, B, C, D) and following FDs. Identify the candidate key for R and highest normal form.
i) C D, C A, B C                  ii) B C, D A. (10 Marks)

8. Write short notes on the following;
a. Two phase locking protocol.
b. Transaction support in SQL.
c. Time stamp ordering algorithms.
d. ACID properties.                          (20 marks)

*****************





Tuesday, May 5, 2015

Database Management Systems - Visvesvaraya Technological University June July 2014 Question

Database Management Systems - Visvesvaraya Technological University June July 2013 Questions / June July 2013 DBMS questions of Visvesvaraya Technological University / DBMS University questions with answers



USN                                                      10CS54
Fifth Semester B.E. Degree Examination, June/July 2014

Database Management Systems

Time: 3 hrs.                                                     Max. Marks: 100
Note: Answer any FIVE full questions, selecting at least TWO questions from each part.
PART – A
1. a. What is database? Explain the implicit properties of database. (08 marks)
b. With neat diagram, explain “three schema architecture”. (08 marks)
c. Define the following terms: (04 marks)
i) Data model       ii) Schema   iii) Metadata                  iv) Snapshot

2. a. Explain with sketch the different phases of database design. (10 marks)
b. Draw an ER diagram of hospital management system. Assume your own entities (minimum 4), attributes and relations. (10 Marks)

3. a. What is constraint? Give the detailed explanation of key constraints.         (10 marks)
b. Consider the following schema and write the relational algebra expressions for the queries given below;
Suppliers (sid : integer, sname : string, address : string)
Parts (pid : integer, pname : string, color : string)
Catalog (sid : integer, pid : integer, cost : real)
i) Find the names of suppliers who supply some red parts.
ii) Find the sids of suppliers who supply some red parts or at 221 packer street.
iii) Find the sids of suppliers who supply some red part and some green part. (10 marks)

4. a. Consider the data given in question 3(b) and write the following queries in SQL:
i) Find the sids of suppliers who supply some red and some green parts.
ii) Find the pairs of sids such that the supplier with first sid charges more for some part than the supplier with the second sid.
iii) Find the pids of parts supplied by at least two different suppliers.      (10 marks)
b. Write a note on NULL and three valued logic. (10 marks)

PART – B

5. a. Explain insert, delete, and update statements in SQL, with example. (09 marks)
b. How is view created and dropped? What problems are associated with updating of views? (11 marks)

6. a. State the informal guidelines for relational schema design. Illustrate how violation fo these guidelines may be harmful. (12 marks)
b. What is normalization? Explain third normal form with example. (08 marks)

7. a. Define multi-valued dependency. Explain 4NF with example. (10 marks)
b. Let R = {ssn, ename, pnumber, pname, plocation, hours} and
D = {R1, R2, R3} where
R1 = EMP = {ssn, ename}
R2 = PROJ = {pnumber, pname, plocation}
R3 = WORKS_ON = {ssn, pnumber, hours}
The following functional dependencies hold on relation R.
F = {ssn ename; pnumber {pname, plocation}; {ssn, pnumber} hours}. Prove that the above decomposition of relation R has the lossless join property. (10 marks)

8. Write a short note on:
a. Two phase locking protocol.
b. Transaction support in SQL.
c. Write ahead log protocol.
d. Time stamp ordering algorithm.                       (20 marks)

*****************