Database management systems - basics and ER modelling quiz

Database management system quiz - DBMS quiz with answers - DBMS multiple choice questions - DBMS basics quiz - DBMS interview questions - ER modelling quiz in DBMS - ER modelling in Database interview questions

QUIZ

1. _________ attribute can uniquely identify a record (row/tuple) in a table (relation).
(a) Partial key	(b) Candidate key
(c) Foreign key	(d) All of the above
2. Which of the following is used to logically connect a table with the other?
(a) Super key	(b) Candidate key
(c) Unique key	(d) Primary key
3. One driver drives one or more cars, and one car is driven by one or more drivers. What type of relationship is this?
(a) One-to-one	(b) One-to-many
(c) Many-to-one	(d) Many-to-many
4. A customer can have one or more vehicles and a vehicle is owned by exactly one customer. What type of relationship exists between customer and vehicle?
(a) One-to-one from customer to vehicle	(b) One-to-many from customer to vehicle
(c) Many-to-one from customer to vehicle	(d) Many-to-many from customer to vehicle
5. A cricket player can play for exactly one team, and a team can consist of several players. What type of relationship exists between team and player from team to player?
(a) One-to-one	(b) One-to-many
(c) Many-to-one	(d) Many-to-many
6. __________ schema defines the view(s) of a relation to the end users.
(a) Conceptual schema	(b) External schema
(c) Physical schema	(d) Logical schema
7. What type of relationship would exist between Tourist entity set and Passport entity set?
(a) One-to-one	(b) One-to-many
(c) Many-to-one	(d) Many-to-many
8. If a strong entity set A of an ER diagram contains a multi-valued attribute, then which one of the following is true?
(a) Reducing A into relation must not contain that multi-valued attribute	(b) A separate relation has to be created for multi-valued attribute
(c) A cannot be in 1NF, if that multi-valued attribute is included with A.	(d) All of them are TRUE
9. An author can write many books, in turn, a book can be written by at most one author. What type of relationship would exist between Author and Book entity sets?
(a) One-to-one from Book to Author	(b) One-to-many from Author to Book
(c) Many-to-one from Book to Author	(d) Both (b) and (c)
10. A candidate key is a key _____________ .
(a) that uniquely identify any rows in a table.	(b) that is redundant
(c) that cannot uniquely identify any rows in a table.	(d) None of the above

Go to ER modeling quiz page

Go to QUIZZ in Database / DBMS / Advanced DBMS page

Dependency preserving decomposition - solved exercises 2

Dependency preserving decomposition - Dependency preserving decomposition solved exercises - How to verify that a decomposition is dependency preserving? - Steps to find dependency preserving decomposition - Dependency preserving decomposition examples

Dependency preserving decomposition

Consider a relation R (A, B, C, D) with the following set of functional dependencies;

F = {A → B, B → C, and C → D}.

Is the decomposition of R (A, B, C, D) into R1 (A, C, D) and R2 (B, C) a dependency preserving decomposition?

Solution:

The above said decomposition of R into R1 and R2 is a dependency preserving decomposition if (F₁ U F₂)⁺ = F⁺, where F₁ is set of FDs hold by R1, F₂ is set of FDs hold by R2, and F is the set of FDs hold by R. (F₁ U F₂)⁺ is the closure of (F₁ U F₂), and F⁺ is the closure of F.

Step 1: for R1, the derivable non-trivial functional dependency is, C → D. Hence, F₁ = {C → D}

Step 2: for R2, the derivable non-trivial functional dependency is, B → C. Hence, F₂ = {B → C}

(F₁ U F₂) = ({C → D} U {B → C}) = {C → D, B → C} ≠ F.

(F₁ U F₂)⁺ = {C → D, B → C, B → D}

F⁺ = {A → B, B → C, C → D, A → C, A → D, B → D}

Hence, (F₁ U F₂)⁺ ≠ F⁺

The FD A → B is not supported by (F₁ U F₂)⁺. Hence, the decomposition of R (A, B, C, D) into R1 (A, C, D) and R2 (B, C) is not a dependency preserving decomposition.

Go back to Dependency preserving decomposition - solved exercises/examples page

 

Dependency preserving decomposition - solved exercises 1

Dependency preserving decomposition - Dependency preserving decomposition solved exercises - How to verify that a decomposition is dependency preserving? - Steps to find dependency preserving decomposition - Dependency preserving decomposition examples

Dependency preserving decomposition

Consider a relation R (A, B, C, D) with the following set of functional dependencies;

F = {A → B, B → C, and C → D}.

Is the decomposition of R (A, B, C, D) into R1 (A, B, C) and R2 (C, D) a dependency preserving decomposition?

Solution:

The above said decomposition of R into R1 and R2 is a dependency preserving decomposition if (F₁ U F₂)⁺ = F⁺, where F₁ is set of FDs hold by R1, F₂ is set of FDs hold by R2, and F is the set of FDs hold by R.

Step 1: for R1, the derivable non-trivial functional dependencies are, A → B, and B → C. Hence, F₁ = {A → B, B → C}

Step 2: for R2, the derivable non-trivial functional dependency is, C → D. Hence, F₂ = {C → D}

(F₁ U F₂) = ({A → B, B → C} U {C → D}) = {A → B, B → C, C → D} = F.

F₁ U F₂ and F both have same set of functional dependencies. Hence, the decomposition of R (A, B, C, D) into R1 (A, B, C) and R2 (C, D) a dependency preserving decomposition.

Go back to Dependency preserving decomposition - solved exercises/examples page. 

Dependency preserving decomposition - solved exercises

Dependency preserving decomposition - Dependency preserving decomposition solved exercises - How to verify that a decomposition is dependency preserving? - Steps to find dependency preserving decomposition - Dependency preserving decomposition examples

List of solved exercises/examples for Dependency preserving decomposition:

• Dependency preserving decomposition –Solved Exercise/Example 1

• Dependency preserving decomposition –Solved Exercise/Example 2

• Dependency preserving decomposition –Solved Exercise/Example 3

• Dependency preserving decomposition – Solved Exercise/Example 4

• Dependency preserving decomposition – Solved Exercise/Example 5

• Dependency preserving decomposition – Solved Exercise/Example 6

• Dependency preserving decomposition – Solved Exercise/Example 7

• Dependency preserving decomposition – Solved Exercise/Example 8

• Dependency preserving decomposition – Solved Exercise/Example 9

• Dependency preserving decomposition – Solved Exercise/Example 10