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:

Normalization - solved exercise

Normalization process - solved exercise / How to find candidate key? / How to normalize to BCNF? / Normalize to 2NF, 3NF, BCNF / Normalization Examples in DBMS / Normalization in Database

The following relation schema can be used to register information on the repayments on micro loans.

Repayment (borrower_id, name, address, loanamount, requestdate, repayment_date, repayment_amount)

A borrower is identified with an unique borrower_id, and has only one address. Borrowers can have multiple simultaneous loans, but they always have different request dates. The borrower can make multiple repayments on the same day, but not more than one repayment per loan per day.

a) State a key (candidate key) for Repayment.

b) Make the normalization to BCNF. Show the steps.

Answer a):

From the given information, we can derive the following set of functional dependencies;

Borrower_id → name [given: every borrower is identified with an unique id]

Borrower_id → address [given: each borrower has only one address]

Borrower_id, Requestdate → loanamount [given: more than one loan cannot be requested by a single borrower] 

Borrower_id, requestdate, repayment_date → repayment_amount [given: a borrower can make multiple repayments on a single day, but not on a single loan]

From the above set of FDs, it is evident that we can uniquely identify all the other attributes of Repayment table, if we know the values of (borrower_id, requestdate, repayment_date). That is,

Borrower_id, requestdate, repayment_date → name, address, loanamount, repayment_amount.

Hence, attributes (Borrower_id, requestdate, repayment_date) together forms a candidate key.

Answer b):

Is the given relation Repayment is in 1NF? 

Yes. It has a key. Hence, we can make unique identification of records.

Is the given relation is in 2NF? 

No. We have the following partial key dependencies. 

1. We can easily derive name and address of every borrower if we know the borrower_id from the FDs Borrower_d → name, and Borrower_id → address.

2. We can derive the loanamount if we know borrower_id, and requestdate from the FD Borrower_id, Requestdate → loanamount.

Hence, the relation Repayment is not in 2NF. To convert it into a 2NF relation, we can decompose Repayment into the following relations;

Borrower (Borrower_id, Name, Address)

Borrower_loan (Borrower_id, Requestdate, Loanamount)

Repayment (Borrower_id, Requestdate, Repayment_date, Repayment_amount)

From the derived FDs, we know that all these tables are in 2NF.

Are these tables in 3NF? 

Yes. There are no transitive dependencies present in the above tables’ set of functional dependencies. Hence, we would say that all these tables are in 3NF.

Are these tables in BCNF? 

Yes. There are no more than one candidate keys present in the above set of tables. Hence the following decomposed tables are in Boyce-Codd Normal Form.

Borrower (Borrower_id, Name, Address)

Borrower_loan (Borrower_id, Requestdate, Loanamount)

Repayment (Borrower_id, Requestdate, Repayment_date, Repayment_amount)

Go back to Normalization Exercises page.

Step by step procedure to normalize a table to BCNF