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)
*****************
|
No comments:
Post a Comment