Normalization - solved exercises set 1

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

1. Consider the following relation schema R and set of Functional Dependencies F:

R(A,B,C,D,E),   F = {AC → E, C → D, D → A}

One of the FDs contains an extraneous attribute that can be removed without changing the dependencies implied by the above set. Explain which one. 

Answer: Since the functional dependencies C → D and D → A imply C → A (transitive dependency), the A in AC → E is extraneous. C alone can determine the other attributes.

2. For the following relation schema R and set of Functional Dependencies F:

R(A,B,C,D,E),  F = {AC → E, B → D, E → A}

List all candidate keys.

Answer: From the given set F of functional dependencies, it is very evident that B and C must be in the candidate key as they are not present in the Right Hand Side (RHS) of the given set of FDs. Hence, at first we can check for BC as the candidate key as follows;

If you know B, then you know B and D through FD B → D. Along with this, if you know C, then you know BCD. That is, BC → BCD. B and C together cannot determine A and E, so BC cannot be a candidate key. 

Then we can try with the attributes that are present in the LHS like B and C. First let us take A. Then we have,

ABC → ABCDE. So, ABC is a candidate key.

Now we shall try with the other LHS attribute E. Then we have,

BCE → ABCDE. So, BCE is another candidate key.

Checking BCA and BCE, we see that both of them are candidate keys.

3. The relation schema given in question number 2 above is not in BCNF due to the reason that it has two candidate keys. List one functional dependency that violates the rules for BCNF.

Answer: E → A violates the rules. If we don’t have a functional dependency like this, we have only one candidate key, i.e, ABC.

Normalization - solved exercises set 2

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

1. Suppose that you are given a relation R = (A,B,C,D,E) with the following functional dependencies: {CE → D,D → B,C → A}.

Find all candidate keys.

Answer: From the given set of functional dependencies, we can observe that only the attributes C and E are present only on LHS. Hence, we can try with C and E attributes to find candidate keys.

C alone cannot determine all the other attributes.

Hence, C → C

E alone cannot determine all the other attributes.

Hence, E → E

C and E together can form a candidate key.

CE → ABCDE. Hence, CE is the only candidate key for the given relation R.

2. Suppose that you are given a relation R = (A,B,C,D,E) with the following functional dependencies:

{BC → ADE, D → B}.

Find all candidate keys.

Answer: Let us start with LHS of given functional dependencies;

• From D → B, D cannot uniquely determine the values of all the other attributes. Hence, D alone cannot be a candidate key.

• From BC → ADE, it is very clear that if you know values of B and C, you can determine the values of attributes A, D, and E. Hence, BC → ABCDE is holding. So, BC is one candidate key.

• From D → B, if you know D then you know B. If you know C also, then you can determine all the other attributes. Hence, CD is another candidate key.

3. You are given the following set of functional dependencies for a relation R(A,B,C,D,E,F),

F = {AB → C, DC → AE, E → F}.

What are the keys of this relation?

Answer: From the given set of FDs F, it is very evident that we cannot have any one attribute as the key for R. Hence, we need to check with the different combination of attributes.

• Let us try this example with the algorithm that is used for finding Attribute Closure. Click in the above link to visit the page.

• Let us start with AB. Assume that the result is AB.

Result = AB;

From AB → C, (if you know A and B, then you would know C) Result = ABC.

We cannot move further. That is, AB can determine only A, B, and C. Hence, AB cannot be a key.

• Let us try with the other combination ABD.

Result = ABD;

From AB → C, Result = ABCD

From DC → AE, Result = ABCDE

From E → F, Result = ABCDEF.

At last, Result includes all the attributes of the relation R. Hence, ABD is one of the keys for the relation R.

• Let us try with the other combination BCD.

Result = BCD;

From DC → AE, Result = ABCDE

From E → F, Result = ABCDEF.

At last, Result includes all the attributes of the relation R. Hence, BCD is also one of the keys for the relation R.

Transaction Control Language (TCL)

What is TCL? / List of operations that can be performed by Transaction Control Language / TCL examples / Different commands of TCL / What is Transaction Control Language? / Define TCL

Transaction Control Language (TCL)

The Transaction Control Language (TCL) is yet another component of SQL which is used to control/manage transactions in a database. The data stored in a database can be manipulated differently as and when required. The manipulation could be smaller or larger, i.e, may involve one or more SQL statements. Hence, we need a mechanism to differentiate one manipulation from other. A transaction is a unit which is used to mention the boundary of every manipulation.

A transaction is logical unit of work that comprises one or more SQL statements, usually a group of Data Manipulation Language (DML) statements.

The following list shows the major operations that are performed as part of every transaction;

• Insertion of a record in a table/multiple tables

• Modification of the values in a table

• Deletion of records in a table/multiple tables

The each of the following examples is considered as individual transactions;

• When you deposit some money in your account, your deposit will modify the data in your record. This is treated as single transaction.

• If you would like to withdraw some money from your account from the bank, your withdrawal performs one modification in your record. In some cases, the operation that you performed might by logged in another table for maintaining a history of transaction. This involves an insertion. Here, both modification and insertion put together considered as single transaction.   

• If you would like to transfer some money from your account to your friend’s then your transfer involves modification in your record and your friend’s record. Here, modification of values in both the records treated as single transaction.

Hence, transactions are units or sequences of work performed in a logical order.

The TCL commands include the following;

• Commit – to permanently save the changes that are executed as part of a transaction in a database.

The syntax for using commit;

COMMIT;

• Rollback – to undo the changes that are made on a database through a transaction.

The syntax for using rollback;

ROLLBACK;

• Savepoint - A SAVEPOINT is a point in a transaction that you can use to roll the transaction back to a certain point without rolling back the entire transaction.

The syntax for SAVEPOINT is;

SAVEPOINT Savepoint_Name;

If you have declared a savepoint as part of your transaction, then you can use rollback command to rollback the transaction upto that point. For example,

ROLLBACK TO svpoint1;

This command will rollback the transaction by undoing changes upto the savepoint svpoint.

Data Control Language (DCL)

What is DCL? / List of operations that can be performed by Data Control Language / DCL examples / Different DCL commands

Data Control Language (DCL)

Data Control Language is used for controlling access privileges that are assigned for database users. It is one of the components of SQL like the DDL and DML. It does the following;

Granting privileges – through DCL we can permit individual users with individual permissions to access database and data differently. For example, we can restrict a user only to insert data into a table and to view data of the table. Some users may be permitted to create tables, some may be allowed to delete tables and so on.

This can be done with the GRANT command. For example, the following statement will allow a user to only insert the data into a table.

• GRANT INSERT ON Teacher TO Kumar;

Here, Teacher is the table name and ‘Kumar’ is the name of the user. This statement permits the user Kumar to insert data into the table Teacher.

Other examples:

• GRANT INSERT, SELECT ON Teacher TO Kumar;

• GRANT INSERT ON Teacher TO Kumar, User2;

Revoking privileges – we can revoke the privileges given to an user using revoking privileges statement.

For example, the following query will revoke the permission given to the user kumar in the previous statement.

• REVOKE INSERT ON Teacher FROM Kumar;

Other examples:

• REVOKE INSERT ON Teacher FROM Kumar, User2;