Normalisation

Data redundancy = same data being stored more than once or at different places

Will cause issues when inserting, updating and deleting data from database
Normalisation reduces data redundancy, ensuring that each piece of information is stored only once
E.g.
Causes data anomalies when NOT ALL of the required changes in the redundant data are made successfully
Insertion anomalies: a new student cannot be inserted unless a class has been created. Otherwise a dummy class will be used and that will lead to data inconsistencies
Update anomalies: if school changes class name, all students from original class need to be updated. If any records missed, will lead to data inconsistencies
Deletion anomalies: if all records in student table are deleted, info on the class will be lost (e.g. if record of Angel Ng is deleted, class S6E will be deleted too [since she is the only one in S6E], which is not desirable)
Normalisation prevents update anomalies, ensuring that updates are consistent

Functional dependency (FD)

To ensure tables are normalized ⇒ avoid data redundancy / table with anomalies
FD ensure that all attributes in a table belong to that table ⇒ eliminates redundancies and anomalies
In databases, when a column B is dependent on another column A, means value of B can be derived from column A
FD in a database enforces a set of constraints between attributes
Occurs when 1 attribute (X) in a relation uniquely determines another attribute (Y)
X→Y; i.e. Y is functionally dependent on X (usually primary key)
For any relation R, attribute Y is functionally dependent on attribute X, if for every valid instance of X, that value of X uniquely determines the value of Y
E.g. StudentID → Name; StudentID → NRIC¹; StudentID → Gender
BUT reverse statement Name → StudentID is not true: there might be students with the same name

Transitive dependency

X → Z is a transitive dependency if the following dependencies hold true:
X determines Y (X → Y)
Y does not determine X (Y does not → X)
Y determines Z (Y → Z)
⇒ Z is transitively dependent on X
In a database, transitive dependence means that a column’s value relies upon another column through a second intermediate column
I.e. A functionary dependency is transitive if it is indirectly formed by 2 functional dependencies
Can only occur in a relation of 3 or more attributes
E.g. A, B, C are columns of a table
Transitive dependency exists when there are functional dependencies such that A → B, B → C and A is the primary key
Dependency A → C is a transitive dependency because A determines value of C via B

Normalisation

= process of organising tables in database to reduce data redundancy and prevent inconsistent data
Goal: provide mechanisms for transforming schemas in order to remove redundancy
Typically used in conjunction with the entity relationship modeling
Used for new database structure design: after ERD has been constructed, normalisation analyses relationships among the attributes within each entity and determines if the structure can be improved through normalization
Used for existing data structures: to analyse relationships among attributes in the data structure to improve existing database structure and create an appropriate database design
Defines 6 normal forms (NF)
Each NF involves a set of dependency properties that a schema must satisfy
Higher NFs have less redundancy, fewer update problems

First normal form (1NF)

States that the domain of an attribute must include only atomic (simple, indivisible) values and that value of any attribute in a tuple/record must be a single value from the domain of that attribute ⇒ columns must be atomic
I.e. no multi-valued columns, e.g. columns that would hold a collection such as an array or another table ⇒ info in each column should not be able to be broken down further
Disallows relations with relations or relations/tables ²as attribute values within tuples (not atomic?)
The only attribute values permitted by 1NF are single atomic (indivisible) values
E.g. NOT 1NF since “Subjects” column contains multiple values, which are <— non-1NF
Student_Class_Subject(SubjectID, StudentName, ClassID, ClassName, Subjects)
To convert to 1NF: delete non-atomic items or move to another table
E.g. of 1NF:
Everything is atomic
StudentID is a FK that is a PK in Student table ⇒ dotted underline
SubjectID is a FK that is a PK in Subject table ⇒ dotted underline
Student-Subject is a good composite key (i.e. this table’s PK) as it can uniquely identify each row ⇒ solid underline
NOT 2NF: CohortAverage is partial dependent (depend on part of PK,not full PK)
Mark is dependent on the PK Student-Subject
CohortAverage is dependent on SubjectID, which is NOT primary key

Second normal form (2NF)

Relation must first be in 1NF
All the non-key attributes are fully dependent (functional dependencies) on the relation’s entire primary key (PK)
If relation has a composite PK, then each non-key attribute must be fully dependent on the entire composite PK and not on a subset of the PK
Does not allow partial functional dependencies
E.g. of 2NF (but NOT 3NF)
Since table is 1NF and has partial dependency and transitive dependency
StudentID is PK
Name is dependent on StudentID
ClassID is dependent on StudentID
Class is dependent on ClassID (which is dependent on StudentID)
CivicsTutor is dependent on ClassID (which is dependent on StudentID)
To make it 3NF, move Class and CivicsTutor to a new table where ClassID is PK, and keep ClassID here as foreign key

Third normal form (3NF)

Relation must first be in 2NF
Remove all transitive dependencies, i.e. table will contain only columns that are non-transitively dependent on the primary key
A non-key attribute may not be functionally dependent on another non-key attribute
E.g. NOT 3NF
expiry date is more dependent on passport number than on student ID
passport no. dependent on studentID
⇒ expiry date is transitively dependent on student ID via passport no. (?)³
⇒ 2NF not 3NF
To convert to 3NF:

Student ID	StudentName	PassportNo		PassportNo	ExpiryDate
…	…	…		…	…

Comments from the Word document

Comment by ANDREA TAN KAI XUAN HCI: Reverse true so does this go both ways ↩
Comment by ANDREA TAN KAI XUAN HCI: ? ↩
Comment by ANDREA TAN KAI XUAN HCI: check ↩