ExploreDatabase – Your one-stop study guide for interview and semester exam preparations with solved questions, tutorials, GATE MCQs, online quizzes and notes on DBMS, Data Structures, Operating Systems, AI, Machine Learning and Natural Language Processing.
Wednesday, February 24, 2016
Monday, February 22, 2016
Database Normalization Solved Exercise 7
Set of solved exercises in Normalization / Normalization Solved Examples / How to find candidate keys, and primary keys in database? / Sets of examples to find the keys of a tables / Process of Key finding in a database – Examples
Question:
Consider the relation scheme R
= (E, F, G, H, I, J, K, L, M, N) and the set of functional dependencies {{E, F}
→ {G}, {F}
→ {I, J}, {E,
H} → {K, L} → {M}, {K}
→ {M}, {L} → {N}} on
R. What is the key for R?
Solution:
To find the key of a relation, we need
to find the closure of attributes. If any attribute’s or set of attributes’
closure gives all the attributes of the relation, then we would say that
attribute/set of attributes as the key for that relation.
To simplify this task or to avoid
finding closure of all attributes, let us do find the closure for left hand
side (LHS) attributes of the functional dependencies.
For the given question,
attributes F, (E, F), (E, H), K, and L are the LHS attributes.
- The closure of F, i.e., (F)+ = FIJ ≠ EFGHIJKLMN. Hence F is not a candidate key. [Refer, How to find closure]
- (EF)+ = EFGIJ ≠ EFGHIJKLMN. Hence EF is not a candidate key.
- (EH)+ = EHKLMN ≠ EFGHIJKLMN. Hence EH is not a candidate key.
- (K)+ = KM ≠ EFGHIJKLMN. Hence, K is not a candidate key.
We observed that the left side
attributes of any FDs alone cannot form a key. Let us try with the combination
of two FDs LHS.
- (EFH)+ = EFGHIJKLMN = R.
Hence, EFH is the key for this
relation.
[
Derivation of EFH+:
Step 1: result = EFH
Step 2: result = EFGH from {E, F}
→ {G}
Step 3: result = EFGHIJ from {F} → {I, J}
Step 4: result = EFGHIJKLM from {E,
H} → {K, L} → {M}
Step 5: result = EFGHIJKLMN from
{L} → {N}
We have reached that the result =
R. hence, EFH is the key.
]
Go back to Normalization – solved exercises page.
Related Posts:
How to find candidate keys using closure finding algorithms
Sunday, February 21, 2016
CS 8303 – DATABASE MANAGEMENT SYSTEMS April May 2014
Anna University CS 8303 – DATABASE
MANAGEMENT SYSTEMS April May 2014, Computer Science and Engineering, Third Semester, Regulation 2012. Database Management System Question Paper April May 2014
Exam
|
B.E/B.Tech. (Full
Time) DEGREE END SEMESTER EXAMINATIONS
|
Academic
Year
|
April May 2014
|
Subject
Code
|
CS 8303
|
Subject
Name
|
Database Management Systems
|
Branch
|
Computer Science and Engineering
|
Semester
|
Third Semester
|
Regulation
|
2012
|
B.E
/ B.Tech. (Full Time) DEGREE END SEMESTER EXAMINATIONS, APRIL / MAY 2014
Computer Science
and Engineering
Third Semester
CS
8303 – DATABASE MANAGEMENT SYSTEMS
(Regulations 2012)
Time : 3 Hours Answer A L L Questions Max. Marks 100
PART-A
(10 x 2 = 20 Marks)
1. List the responsibilities of a
database administrator.
2. List four advantages of database
systems over file systems.
3. List the properties to be satisfied
by a decomposition of relation
4. Define PJNF. Give an example.
5. What is dynamic SQL?
6. Can views be modified? Justify.
7. What is savepoint?
8. What is the advantage of indent
locking?
9. What are the two types of spatial
queries? Give examples.
10. What are the issues in mobile
databases?
Part-B
(5* 16 = 80 Marks)
11. (i) Explain the architecture of
database systems with a neat diagram. (10)
(ii) Write short notes on data models.
(6)
12. a) (i) A university registrar's
office maintains data about the following entities: (a) courses, including
number, title, credits, syllabus, and prerequisites; (b) course offerings,
including course number, year, semester, section number, instructor(s), timings,
and classroom; (c) students, including student-id, name, and program; and (d)
instructors, including identification number, name, department, and title.
Further, the enrollment of students in
courses and grades awarded to students in each course they are enrolled for
must be appropriately modeled.
Construct an E-R diagram for the
registrar's office. Document all assumptions that you make about the mapping
constraints. (10)
(ii) Convert the ER schema shown in
figure 12 (a) (ii) into relational schema. Document all assumptions that you
make about the mapping constraints. (6)
(OR)
(i) Consider a database the following
relational schema: (10)
Customer (Cust#,
Cname, City)
Order(Order#, Odate,
Cust#, Ord_Amt)
Order_ltem(Order#,
ltem#, Qty)
ltem(ltem#,
Unit_Price)
Shipment(Order#,
Warehouse#, Ship_date)
Warehouse(Warehouse#,
City)
Ofd_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. An order can be shipped from any
warehouse.
Express the following queries in RA:
A)
List the Order# and Ship_date for all orders shipped from Warehouse# "W2".
B)
Delete all orders for customer named "Jose"
C) List
all customer names whose orders were shipped from a warehouse in the same city
as they live in.
D)
List
the Order# for orders that were shipped from all warehouses in New York.
E) Produce
a listing: Cname, #ofOrders, Avg_Order_Amt, where the middle column is the
total number of orders by the customer and the last column is-the average order
amount for that-customer.
(ii) Consider the relation for
published-books: (6)
BOOK (Title,
Author, Type, Price, Affil, Publisher)
Affil refers to the affiliation of the
author.
Suppose the following dependencies
exist:
Title ->
Publisher, Type
Type -»Price
Author -> Affil
What normal form is the relation in?
Explain your answer. Apply normalization until you cannot decompose the
relations further. State the reasons behind each decomposition.
13. a) ( i ) Explain how database
security is achieved using SQL. (10)
(ii) What are triggers? Discuss the
use of triggers with an example. (6)
(OR)
(i) Consider a database the following
relational schema: (10)
CUSTOMER (Cust Id, Cust_Name,
Annual_Revenue, Cust_Type)
SHIPMENT (Shipments#, Cust Id,
Weight, Truck#, Destination, Ship_Date)
Note: Cust_id references CUSTOMER
Truck# references TRUCK
Destination references CITY
Assume each shipment contains one
package.
TRUCK (Truck#, Driver_Name)
CITY (City Name, Population)
Express the following queries in SQL:
A) List
the name and id of customers for whom the driver, Kennedy has delivered
shipments.
B) List the cities to which the
driver, Kennedy has delivered shipments.
C) List the average weight of the
shipments received by each customer.
D) For each city which has received
atleast 10 packages, what is the average weight of a package sent to that city?
E) List the drivers who have delivered
shipments to every city.
(ii) Explain referential integrity
constraint, and the ways in which updates of such attributes are handled. (6)
14. a) (i) Explain 2PL and its
variants with the advantages and disadvantages. (10)
(ii) Explain the properties of a
transaction with suitable examples. (6)
(OR)
b) (i) What are deadlocks? How are
they handled? (10)
(ii) Explain 2PC protocol. (6)
15. a) (i) Explain the different
algorithms used for selection operation and their associated cost. (10)
(ii) Discuss the steps involved in
query processing with a neat diagram. (6)
(OR)
b) (i) What are temporal databases?
Explain insertion and deletion in temporal databases. (10)
(ii) Write notes on data mining. (6)
************************
Go back to Database Management Systems Question Papers page
Go
back to Anna University B.E
Computer Science and Engineering Questions April May 2014 Regulation 2012 page
Subscribe to:
Comments (Atom)