Relational Database Management System

Update Anomalies

Insertion Anomalies

Branch is related to roll number / student here. I cant add a branch until there are students linked to it.

Modification Anomalies

If Hod of a branch changes, then I have to manually go through every field where branch name is CS for example and change its HOD. REDUNDANCY

Deletion Anomalies

Root Cause is Redundancy

Dividing a table such that every table is connected via functional dependencies / keys … → NORMALISATION.

Functional Dependency

For all values of some column X, we have the same value in that particular instance in other column Y or

For all pairs of tuple/row T1[a] = T2[a], then we can say T1[b] = T2[b]

If a or b values are different, then FD cant fail

FD are transferrable. If A→ B and B→ C, then A→ ABC

Attributes Closure

set of attributes which can be functinally determined from F, denoted by F+

If A→ B, B→C and AB→ D

A+ (positive closure of A) : would be A, AB, ABC, ABCD

Armstrong Axioms

• Augmentation : if X→ Y, then XZ→ YZ
• Transitivity : if X→ Y and Y→Z, then X→Z
• Union, Decomposition, basic common sense ahh

Equivalence Of FD

Two FD sets F1 and F2 are equivalent if F1+ == F2+

Here if we have to find if FD F and FD G are equivalent …. Find closure of all the F terms, wrt G. So find closure of A+, AC+, E+ WITH RESPECT TO G and then check these closure with already given data of F. If everything is matching then we can say F is a subset of G.

Do the reverse also, Find closure of all G terms with respect to F now. If again all matching matching, then G is a subset of F also. HENCE EQUIVALENT

Minimal Cover

Eliminate redundancy from the FD set

How to do this ?

First break down the FD sets like this, unit by unit. Then Check one by one, if a particular FD is causing any change in the closure. First Case A→ B (in the photo)

A+ is AB, but if we ignore this very FD of A→B, then A+ is only A. This difference means that the FD is crucial and removing it will change the calculations. Do the same for everything else.

Keys

Super Keys

• Let X be the set of attributes in a relation R. If X+ (closure of X) determines all attributes of R, then X is said to be the super key of R.
• (Super Key)+ → all attributes of R.
• At least one super key with Not Null constraint.

How to Find Super Key when functional dependency is given ? Find nodes with no incoming edges.

Find number of Super keys ? Suppose (AB) is the key out of ABCDE, then ABC can also be a key, ABD will also be, find such combination _ _ _ for CDE, 2**3 i.e → 8.

Candidate Key

• Out of all super key combinations, the shortest / efficient one will be the candidate key. Or we can say, the super key whose subset is not a super key is the candidate key. MINIMAL SUPER KEY.
• At least one candidate key with Not Null Constraint.
• Attributes that are member of candidate key, are called PRIME ATTRIBUTES.

Primary Key

• One of the candidate keys is selected by database administrator as PRIMARY KEY.
• No technical difference, only 1 primary key per table is allowed.
• Candidate key not chosen as primary are Alternate Key.

Foreign Key

Foreign Key is a column or group of columns that refers the primary key of the table or other table to represent relationship. Foreign Key values should be a subset of that primary key in other table. I cant have Br_Code as 107 in the first table if there is no 107 in the second table.

Insertion in the second table is easy, I dont have to check anything. But insertion in the first table → I have to check if this value is already present in the second table or not and this is called → REFERENTIAL INTEGRITY CONSTRAINT.

DELETE CASCADE

One table, both have primary and foreign key referring to primary key. Now if i delete a row, isse primary key values ke subset mai ek value kam hogi, and ye value foreign key mai jaha jaha hai, wo bhi automatically delete hogi. Ab ye sab delete karte samay shayad primary key ki aur value delete hai, causing a chain reaction → DELETE CASCADING.