Normalization solved exercise 4

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 / Normalization to BCNF.

Question:

Consider a relation R(A, B, C) with FD's AB → C, AC → B, BC → A.

Determine all the keys of relation R. Is the relation R in BCNF?

Solution:

From AB → C, we obtain that AB is one of the keys.

From AC → B, we obtain that AC is one of the keys.

From BC → A, we obtain that BC is one of the keys.

Every single FD given above, includes all the attributes of R. Hence, all the left hand side attributes form the key. And the keys are AB, AC, and BC.

Is R in BCNF?

Requirements: R should be in 2NF, 3NF, and every determinant must be a candidate key.

From the set of functional dependencies given, we observe the following;

• No partial key dependencies. So, R is in 2NF.
• No transitive dependencies. So, R is in 3NF.
• Every determinant (AB, BC, AC) is a candidate key. Hence, R is in BCNF as well.

Go back to Normalization - solved exercises page.

Normalization solved exercise 3

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 a relation R(A, B, C, D, E) with FD's AB → C, CD → E, C → A, C → D, D → B.

The possible candidate keys for R are AB, AD, C

(a) List all the functional dependencies that violate 3NF. If any, then decompose R accordingly.

(b) List all the FD's that violate BCNF. If any, then decompose R accordingly.

Solution (a):

To be in 3NF, the following points should not be violated by any of the FDs;

(i) R should be in 2NF;

According to the properties that are to be held for 2NF (No partial functional dependencies), all the non-prime attributes (E is the only non-prime attribute in our case) depend on prime attributes (A, B, C, and D in our case) or keys as a whole. No partial dependencies found. Hence, we would say that R is in 2NF.

(ii) Transitive functional dependencies of non-prime attributes on candidate keys are prohibited;

In the list of functional dependencies, we found no non-key dependencies. That is, no non-prime attribute is functionally dependent on another non-prime attribute.

From points (i) and (ii), it is clear that there are no FDs that violate 3NF. Hence, we would say that R is in 3NF.

Solution (b):

R can be in BCNF if and only if R is in 3NF and every determinant is a candidate key. 

From the solution (a), you would understand that R is in 3NF. But every determinant is not a candidate key. In the given set of FDs, D → B is the only functional dependency that violates this rule. That is, D is not a candidate key on its own. Hence, the relation R is not in BCNF.

To convert R into a BCNF relation, we need to decompose R using the FD D → B. Then we shall get the following relations R1 and R2;

R1 (D, B) and R2 (A, C, D, E)

Go back to Normalization - Solved Exercises page

Normalization Solved Exercise Set 3

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 a relation R(A, B, C, D, E) with FD's AB → C, CD → E, C → A, C → D, D → B.

Determine all the keys of relation R. Do not list superkeys that are not a candidate key.

Solution:

Key 1:

From AB → C and C → D, we obtained AB → D. (as per Transitivity rule)

From AB → C and AB → D, we obtained AB → CD. (as per Union rule)

From AB → CD and CD → E, we obtained AB → E. (as per Transitivity rule)

From above points, we would know that the attributes AB together can identify all the other attributes of R uniquely. Hence, AB is one of the keys.

Key 2:

From C → A and C → B, we obtained C → AB. (as per Union rule)

From AB, we can obtain the rest of the attributes. (See the discussion in Key 1 above). Hence, C is one of the keys.

Key 3:

From D → B, we can get AD → AB. (as per Augmentation rule)

From AB, we can obtain the rest of the attributes. (See the discussion in Key 1 above). Hence AD is one of the keys.

The keys are AB, AD, and C.

Go back to Normalization - Solved Exercise page.

SQL Exercise 6 - Solved

SQL Exercises for Beginners / Simple SQL Exercises with Answers / SQL Exercises for simple Table creation and SELECT queries / Solved SQL exercises / SQL solved exercises with subqueries

Consider the following relations; primary key attributes are underlined.

Product(productCode, productName, productCategory, productDescription)

Manufacturer(manuCode, manufacturerName, city, Phone)

Supply(manuCode, productCode, storeID, wholesaleUnitPrice, quantity)

Store(storeID, storeName, phoneNumber , city)

Write the SQL queries to answer the following questions. You can assume appropriate data types for the columns. Note: do not use the aggregation functions in SQL for these questions.

a) Find names of all stores that are in the city of “Chandigargh”.

SELECT storeName

FROM Store

WHERE city = “Chandigargh”;

b) Find the name of the manufacturer that supplies the largest quantity of any product.

SELECT manufacturerName

FROM supply

WHERE qty >= ALL (SELECT quantity FROM supply);

c) Find the names and the cities of all manufactures that supply any product of more than 100 units whose wholesale unit price is greater than 50.

SELECT m.manufacturerName, m.city

FROM Manufacturer m, Supply s

WHERE m.manuCode = s.manuCode

AND quantity > 100

AND wholesaleUnitPrice > 50;

d) Find the names of store-manufacturer pairs where the store and the manufacturer in each pair is located in the same city and there is a supply record of the manufacturer whose total cost (i.e. unit price multiplied by quantity) is greater than 10,000.

SELECT s.storeName, m.manufacturerName

FROM Store s, Manufacturer m, Supply sp

WHERE m.manuCode = sp.manuCode

AND s.storeID = sp.storeID

AND m.city = s.city

AND sp.wholesaleUnitPrice*sp.quantity > 10000;

e) Find the store name, city, and product name of all the products whose wholesale unit price is less than 100 and the city is not ‘Chennai’.

SELECT s.storeName, s.city, p.productName

FROM Store s, Supply sp, Product p

WHERE s.storeID = sp.storeID

AND p.productCode = sp.productCode

AND wholesaleUnitPrice < 100

AND city NOT LIKE ‘Chennai’;

Go back to SQL Exercise home page.

 

DBMS General - Quiz

Relational Database Design Quiz

1. In the relational modes, what do we mean by the term “Cardinality”?

Number of tuples
Number of attributes
Number of tables
Number of constraints

2. In relational algebra, Cartesian product is a ___________ operator?

Unary operator
Binary operator
Ternary operator
None of the above

3. Which of the following is an example of procedural language?

Relational calculus
Relational algebra
SQL
All of the above

4. ODBC stands for _________ .

Object Database Connectivity
Open Database Connectivity
Oracle Database Connectivity
Orient Database Connectivity

5. A database schema can be defined using which of the following languages?

DML
DCL
DDL
TCL

6. ________ is used as the defacto standard language to interact with the database.

SQL
DDL
NoSQL
None of the above

7. A Cartesian product of two relations is the same as their union. TRUE/FALSE.

TRUE
FALSE

8. Data independence means

Data is defined separately and not included in programs
Programs are not dependent on the physical attributes of data
Programs are not dependent on the logical attributes of data
Both B and C

9. Which of the following is used to represent the relationships between two relations (tables)?

Primary key
Secondary key
Foreign key
Surrogate key

10. The file organization that provides very fast access to any arbitrary record of a file is ___ .

Hashed file
Unordered file
Ordered file
Clustered file

Score =

Correct answers: