Section 11 - Databases and Normalisation Flashcards
What is an entity?
A category of object, person, event or thing of interest to a organisation about which data is to be recorded
What does every entity in a database have?
Attributes
What are attributes?
Details of an entity
How are attributes listed?
Inside the brackets
What is a primary key?
A unique identifier for each record in a table
How is a primary key shown?
It is underlined
What are the 3 types of entity/table relationships?
One-to-one
One-to-many
Many-to-many
What is a one-to-one relationship?
Where each entity can only be linked to one other entity
What is a one-to-many relationship?
One table can be associated with many other tables
What is a many-to-many relationship?
One entity can be associated with many other entities, and vice versa
How are one-to-one relationships displayed?
A single line connecting the 2 entities
How are one-to many relationships displayed?
The line will have a branch on one side (the many side)
How are many-to-many relationships displayed?
There will be branches on both sides of the line
What is a relational database?
A collection of tables in which relationships are modelled by shared attributes
What is a foreign key?
A unique identifier that links 2 tables together. In one table the key will exist as the primary key, whilst in another table it will exist as a foreign key
What is a composite primary key?
A primary key that consists of more than one attribute
What is a secondary key?
A unique identifier that allows a database to be searched quickly.
How can a record with a particular primary key be found quickly?
The database will automatically maintain an index of primary keys, giving the position of their respective records
What is normalisation?
The process of designing the best possible layout for a relational database
What does normalisation attempt to accomplish?
- No redundancy
- Consistent data throughout the linked tables
- Records can be added and removed without issues
- Complex queries can be carried out
What is first normal form?
A table is in 1NF if each attribute is atomic with no repeating attribute
What is the definition of atomic?
Containing a single value
What is a relation?
A table in a relational database
When is a link table needed?
Whenever 2 entities have a many-to-many relationship
What is second normal form?
A table is in 2NF if it is in 1NF and it contains no partial dependencies
What is a partial dependency?
When one or more attributes depend on only part of the primary key
What does partial dependency occur?
When the primary key is a composite key
What is third normal form?
A table is in 3NF if it is in 2NF and all attributes are dependant on the key, the whole key, and nothing but the key
What are the advantages of normalisation?
- Maintaining and modifying the database
- Faster sorting and searching
- Deleting records
How is a normalised database easier to maintain and modify?
- Data integrity is maintained because there is no unnecessary duplication of data. This means that when an attribute needs updating, it only needs to be changed in a single position
How is a normalised database faster to sort and search?
A normalised database produces smaller tables with fewer fields, meaning there is less data and operations are quicker
How is a normalised database advantageous for deleting records?
If set up correctly, a normalised database will not allow records in a table on the ‘one’ side of a one-to-many relationship to be deleted accidently
