Thursday, September 22, 2016

Find all the minimal keys of a relation R

Find all the possible minimal keys of a relation / How to find keys of a relation in database management system / Keys or minimal key concepts in dbms / Role of keys or candidate keys in relational database design


Question:


2. For a relation R(A, B, C, D, E) with the set of functional dependencies F = {A → B, CD → E, B → D, E → A}, which of the following are the candidate keys (minimal keys) of R?

(a) AB, CD
(b) AC, BC, CD, CE
(c) AC, BC, AEC
(d) A, D, DE



Answer:

(b) AC, BC, CD, CE


To find a key or minimal key of a relation, we need to find the closure of certain attributes (most of the cases, the attribute(s) on the left hand side of a functional dependency). The closure that includes all the attributes of R in the result is one of the keys (minimal keys/candidate keys). Refer here for a detailed example on how to find the key of a relation. Let us apply the closure finding algorithm on all LHS attributes of the given set of functional dependencies. The result is as follows;
Closure of A, ie., (A)+ = ABD [from A → B and B → D]. ABD is not equal to ABCDE. Hence A is not a candidate key (minimal key).
(B)+ = BD [from B → D]. BD ≠ ABCDE, hence B is not a candidate key.
(E)+ = ABDE [from E → A, A → B, and B → D]. So  E is not a key.
(CD)+ = ABCDE [from CD → E, E → A, and A → B]. As the closure of CD is results in all attributes of R. Hence, CD is one of the candidate keys.

The other candidate keys can be found by combining two or more LHS attributes. In the line,
(AC)+ = ABCDE [from A → B, B → D, and CD → E]
(BC)+ = ABCDE [from B → D, CD → E, and E → A]
(CE)+ = ABCDE [from E → A, A → B, and B → D]


In the option (C), AEC cannot be a minimal key. Because the proper subset {(AE), (AC), (EC), (A), (E), (C)} contains the minimal key. But (AEC) is regarded as the super key as it is a super set of a minimal key.








         Previous Question                                                                                Next Question


Wednesday, September 21, 2016

CS6302 Database Management Systems November December 2015

Anna University Questions with Answers - CS6302 Database Management Systems November December 2015, Computer Science and Engineering, Information Technology Questions, Third semester and fifth semester, Regulation 2013

CS6302 Database Management Systems for B.E. Computer Science and Engineering

CS6302 Database Management Systems for B.Tech. Information Technology

CS6302 Database Management Systems for B.E. Mechanical and Automation Engineering


Exam
B.E/B.Tech. (Full Time) DEGREE END SEMESTER EXAMINATIONS
Academic Year
November December 2015
Subject Code
CS6302
Subject Name
Database Management Systems
Branch
Computer Science and Engineering
Semester
Third/Fifth Semester
Regulation
2013

B.E / B.Tech. DEGREE END SEMESTER EXAMINATIONS, NOVEMBER / DECEMBER 2015
Computer Science and Engineering
Third/Fifth Semester
CS6302 – DATABASE MANAGEMENT SYSTEMS
(Common to B.Tech. Information Technology B.E. Mechanical and Automation Engineering B.E. Computer and Communication Engineering)
(Regulations 2013)
Time : 3 Hours                      Answer A L L Questions                Max. Marks 100
PART-A (10 x 2 = 20 Marks)

1. State the anomalies of 1NF.
2. Is it possible for several attributes to have same domain? Illustrate your answer with suitable examples.
3. Differentiate Static and Dynamic SQL.
4. Why does SQL allow duplicate tuples in a table or in a query result?
5. What is meant by concurrency control?
6. Give an example of Two phase commit protocol.
7. Differentiate Static and Dynamic Hashing.
8. Give an example of a join that is not simple equi-join for which partitioned parallelism can be used.
9. List the types of privileges used in database access control.
10. Can we have more than one constructor in a class? If yes, explain the need for such situation.

Part-B (5* 16 = 80 Marks)

11. (a) (i) With help of a neat block diagram explain the basic architecture of a database management system. [8]
(ii) What are the advantages of having a centralized control of data? Illustrate your answer with suitable example. [8]
Or
(b) A car-rental company maintains a vehicle database for all vehicles in its current fleet. For all vehicles, it includes the vehicle identification number, license number, manufacturer, model, date of purchase, and color. Special data are included for certain types of vehicles:
Trucks: cargo capacity
Sports cars: horsepower, renter age requirement
Vans: number of passengers
Off-road vehicles: ground clearance, drivetrain (four- or two-wheel drive)
Construct an ER model for the car rental company database. [16]

12. (a) Describe the six clauses in the syntax of an SQL query, and show what type of constructs can be specified in each of six clauses. Which of the six clauses are required and which are optional?
Or
(b) Assume the following table.
Degree (degcode, name, subject)
Candidate (seatno, degcode, name, semester, month, year, result)
Marks (seatno, degcode, semester, month, year, papcode, marks)
[degcode – degree code, name – name of the degree (Eg. MSc.), subject – subject of the course (Eg. Physis), papcode – paper code (Eg. A1)]
Solve the following queries using SQL;
Write a SELECT statement to display,
(i) all the degree codes which are there in the candidate table but not present in degree table in the order of degcode. [4]
(ii) the name of all the candidates who have got less than 40 marks in exactly 2 subjects. [4]
(iii) the name, subject and number of candidates for all degrees in which there are less than 5 candidates. [4]
(iv) the names of all the candidate who have got highest total marks in MSc. Maths. [4]

13. (a) (i) What is Concurrency control? How is implemented in DBMS? Illustrate with suitable example.  [8]
(ii) Discuss view serializability and conflict serializability. [8]
Or
(b) What is deadlock? How does it occur? How transactions can be written to
(i) Avoid deadlock.
(ii) Guarantee correct execution.
Illustrate with suitable example.

14. (a) (i) What is RAID? List the different levels in RAID technology and explain its features.
(ii) Illustrate indexing and hashing techniques with suitable examples.
Or
(b) Write short notes on
(i) Spatial and multimedia databases.
(ii) Mobile and web databases.

15. (a) (i) Describe the GRANT functions and explain how it relate to security. What types of privileges may be granted? How rights could be revoked?
(ii) Write short notes on Data Warehousing.
Or
(b) Suppose an object oriented database had an object A, which references object B, which in turn references C. Assume all objects are in disk initially. Suppose a program first dereferences A, then dereferences B by following the reference from A, and then finally dereferences C. Show the objects that are represented in memory after each dereference, along with their state.

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











Find the inference rule of the given functional dependency

Find the inference rule of the given functional dependency / Armstrong's axioms / Inference rules / Normalization process


Question:


1. For a relation R(A, B, C), if A → B and A → C holds, then A → BC also holds. Which of the following rule ensures this?

(a) Augmentation rule
(b) Union rule
(c) Decomposition rule
(d) None of the above



Answer:

(b) Union rule


Union rule says that a set of functional dependencies that have same left hand side attributes can be combined to form a single functional dependency. For example, the FDs AC → B, AC → DE and AC → F can be combined to AC → BDEF.


Decomposition rule is the opposite of union rule. Augmentation rule is about adding same set of attributes on both sides of a functional dependency.







         Previous Question                                                                                Next Question


Tuesday, September 20, 2016

Multiple Choices Questions in DBMS

Advanced concepts in DBMS

Multiple choices questions in DBMS / MCQ in DBMS / Solved multiple choice questions in DBMS / MCQ on all areas of Database Management Systems



TOPICS

  • Database Systems - Introduction
  • Structured Query Language (SQL)

Friday, September 16, 2016

Find the table that is union compatible with another table

Find the relation that is UNION compatible with another relation / Union compatibility in Relational Algebra / Set operations compatibility in Relational Algebra


Question:


10. Consider the following relation SAILOR with the given schema;
SAILOR(Sailor_ID:smallint, sailor_name:char(30), DOB:date, Age:int)
Which of the following relations is UNION compatible with SAILOR?

(a) SAILR(Sailor_ID:smallint, s_name:char(25), DOB:date, Age:int, Address:varchar(5))
(b) SAILOR2(S_ID:int, s_name:char(30), DOB:date)
(c) SAILOR3(SID:int, s_name:varchar(20), DOB:date, Age:smallint)
(d) All of the above



Answer:

(c) SAILOR3(SID:int, s_name:varchar(20), DOB:date, Age:smallint)

For set operations U, ∩, and - in relational algebra, the following are the properties must be held by both relations on either side of the operator;
1. The arity of the set operation should be same, ie., the number of attributes should be same in both relations.
2. The attributes should be compatible, ie., the first attribute of left side relation (LSR) and the first attribute of right side relation (RSR) should be compatible, the second attribute of LSR and the second attribute of RSR should be compatible, and so on.
These conditions are TRUE only for C while we perform SAILOR U SAILOR3.

The other relations SAILR and SAILOR2 given in options (a) and (b) respectively are not with correct number of attributes. Hence, there is no Union compatibility







         Previous Question                                                                                Next Question


Thursday, September 15, 2016

How many tuples would result in a natural join operation

How many tuples would result in a natural join operation / The number of records that a natural join produce in relational algebra


Question:


9. Assume a relation R(A, B, C) with n tuples(records) and a relation S(C, D) with m tuples. Let us suppose that the Natural Join (⋈) between R and S (ie., R ⋈ S) produces t tuples as the result. Which of the following (n, m, t) number of tuples is TRUE for this natural join?

(a) (6, 12, 720)
(b) (1, 1, 3)
(c) (10, 5, 2)
(d) (10, 5, 100)



Answer:

(c) (10, 5, 2)

Natural Join (⋈) between R and S can result in maximum of nXm number of records if n and m are the number of tuples in R and S respectively. This is the maximum limit. It can never exceed this. Natural join matches the value of common attributes of both tuples. If there is a match, then result will include that tuple. If no match that tuple will be discarded. Hence, a natural join may results in minimum zero and maximum nXm records. The result (10, 5, 2) may be true if we have 10 records in R and 5 records in S and only two tuples matches the join condition.

For (6, 12, 720), 720 > 6 * 12. Hence, it is impossible. For (1, 1, 3), 3 > 1 * 1. Hence, it is impossible. For (10, 5, 100), 100 > 10 * 5. Hence, it is impossible.







         Previous Question                                                                                Next Question


Wednesday, September 14, 2016

Which of the relational algebra operations are eliminating duplicates

Which of the relational algebra operations are eliminating duplicates?


Question:


8. Which of the following Relational Algebra operations is/are eliminating duplicates?

(a) SELECTION (σ)
(b) PROJECTION (∏)
(c) UNION (U)
(d) All of the above



Answer:

Both (b) and (c)

In relational algebra, a relation (table) is considered as a set. According to the set theory, a set cannot have duplicates. That is, "A set is a collection of well defined and distinct objects". PROJECTION is about picking up some attributes and they may show duplicates. Also, UNION operation simply joins two relations into one if both relations have same number of compatible attributes. If a tuple in one relation is same as the one in the other, then duplicates are possible. Hence, we need to eliminate duplicates to comply with set theory concepts. So, PROJECTION and UNION eliminates duplicates

SELECTION operation filters tuples (records) as per the given condition. A relation as a set cannot have two tuples similar. Hence, selection need not remove duplicates as there are no duplicate records.







         Previous Question                                                                                Next Question


Monday, September 12, 2016

Which is the equivalent SQL query for the given relational algebra expression

Which is the equivalent SQL query for the given relational algebra expression?


Question:


7. Consider relations(tables) R(A, B, C) and S(B, D). Which of the given SQL queries are equivalent to the Relational Algebra expression "ΠA, R.B, C, S.B, D(R X S)"?

(a) SELECT * FROM R, S;
(b) SELECT * FROM R, S WHERE R.B = S.B;
(c) SELECT A, R.B, C, S.B, D FROM R, S;
(d) SELECT A, R.B, C, D FROM R, S WHERE R.B = S.B;



Answer:

Both (a) and (C)

SELECT with * or SELECT with all attributes are equal to the Projection operation in relational algebra. Hence, for the given relational algebra projection on R X S, the equivalent SQL queries are both (a) and (c)

The queries in options (b) and (d) are operations involving a join condition. These two queries are equivalent to a SELECTION operation in relational algebra with a JOIN condition or PROJECTION operation with a JOIN condition.
The query "SELECT * FROM R, S WHERE R.B = S.B;" is equivalent to "σR.B = S.B(R X S)".
The query "SELECT A, R.B, C, D FROM R, S WHERE R.B = S.B;" is equivalent to "σR.B = S.B(R X S)".







         Previous Question                                                                                Next Question


Thursday, September 8, 2016

Result of set difference operator in relational algebra

Result of a set difference operator in relational algebra


Question:


6. Which of the following is equivalent to the expression R - (R - S)?

(a) R
(b) S
(c) R - S
(d) R ∩ S



Answer:

(d) R ∩ S

R - (R - S) is equivalent to R ∩ S . This could be understood from the following example;



Let us take the following tables (relations) R and S with 3 and 2 tuples respectively.
R

B
D
Tuple 1
b1
d1
Tuple 2
b2
d2
Tuple 3
b3
d3

S

B
D
Tuple 1
b1
d4
Tuple 2
b3
d3

The following table shows R-S. The result contains all the records that are unique for R and not for S. In other words, the common tuple in R and S is removed and the tuples that are only part of R is retained.

R – S

B
D
Tuple 1
b1
d1
Tuple 2
b2
d2

The following table shows R-(R-S). The result contains records that are unique for R and not for (R-S).

R – (R – S)

B
D
Tuple 1
b3
d3

From the result, it is very clear that the tuple (b3, d3) is the common tuple that presents in both R and S. Hence, R-(R-S) is equivalent to R ∩ S.









         Previous Question                                                                                Next Question


Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents