4.10.3 Fundamentals of Databases (Normalisation)

The process which helps to organise and structure a database in a way which improves efficiency and integrity

• Reduces data redundancy • Reduces Data inconsistency • One change cascades across related record making modification simpler • Eliminates insertion/update/delete anomalies

Where data cannot be added to a database because it depends on the presence of unrelated data (e.g. In a “Student-Course” table, you cannot add a new course until at least one student enrols in it, because the student ID (primary key) cannot be null.)

Where deleting a record unintentionally removes unrelated, valuable data due to poor normalization and data redundancy. (e.g. If a database lists students and their enrolled courses in one table, deleting the only student in a specific course unintentionally deletes the information about the course itself.)

data inconsistencies in a database occurring when redundant data is updated in some, but not all, records.

• If the data is mad too atomic, fields may become unmeaningful • A more complex database is made meaning that querying data is also more complex and potentially slower

A value that varies in line with another value

• Non-Atomic data • Repeating groups of data • A PK for each record

• No repeating geoups of attributes (2 of the same attribute) • Data is atomic • Each record must have a primary key

• Separate repeating groups of data into a seperate table. • Separate non-atomic values into separate attributes • Declare a primary key

Where the value of a data field relies entirely on part of the primary key where the primary key is a composite key

• Must already be in 1st Normal Form • The must be no Partial Key dependencies

• First ask for each non-key attribute “if the primary key changes does the attribute value change • Make n new tables where for each new table the primary key is the part of the primary key that one of the field relied on.

Where an attribute is determined by another attribute in a table that is not the primary key

• Must already be in second normal form • Must have no non-key dependencies

Ask yourself: “Is it ever possible for two records to have the same value in this field?”.

• Make n new tables, and for each one you should contain the non key dependency attributes

When each column has only one piece of data in it

• Redundant data may waste storage space • Data inconsistency where two copies of the same data store different values • No cascade updates meaning each copy of the same data needs to be separately updated if change • It may be difficult to select/edit data if it is not atomic or if there are repeating groups