Normalisation Flashcards
Attributes of a table can be Atomic or Composite.
What do these terms mean?
Composite attributes can be broken down into smaller, more accurate attributes. Example: Address.
Atomic attributes can not be broken down any further. Example: Street name, House number, Postcode, etc.
What is the difference between a single valued attribute and a multi valued?
Single valued- the attribute can only hold one value (student only can have ONE Student ID).
Multi valued- the attribute can hold multiple values (a student may have multiple phone numbers saved).
What is a basic attribute?
What is a derived attribute?
Basic- the data held is manually entered.
Derived- the data held is acquired, for example a sum of other numbers.
Describe these anomaly types.
- Insertion
- Deletion
- Modification
Insertion- inserting a new person working on the same project as someone else for example. But yet both have different end project end dates? Someone has INSERTED the wrong end date for one of them.
Deletion- Ross quits the company but yet he is the only one working on project X. Suddenly with his deletion, project X isn’t mentioned at all in the database now.
Modification- Updating data, similar to insertion, despite working on the same project, the updating of Lino and james’ records say contradicting end dates. One of the updates for these records is incorrect!
Describe each stage of normalisation.
Aka, 1st Normal Form, 2nd Normal Form, 3rd Normal Form.
1st Normal Form- All data must be Atomic, (no multi valued fields of composite fields). No repeating groups of attributes. Must be a primary key.
2nd Normal Form- Be in 1st NF. All non key columns are dependent on the tables primary key. (Eliminate partial key dependencies).
3rd Normal Form- Be in 2nd NF. Remove all transitive dependencies. A transitive dependency is when one non key field relies on/gets data from another non key field. Remove these to reach 3rd Normal Form.