Relational databases
Databases consist of many tables that contain data that is related in some way
- called relational databases
- managed by the DBMS – database management systems
Keys
Primary key
A field in a table that uniquely identifies each record in the table
- no data values in the primary key field are repeated
- shown by underlined word
Super keys
A combination of fields in the tables which uniquely identify each row
- any number of fields that will uniquely identify a record
Candidate keys
Minimal super keys
Primary keys
A minimal candidate key that uniquely identifies a record
composite/compound/concatenated keys
A combination of two or more fields to form a primary key
Normalisation
Database design
- the process of representing a problem in the real world as a set of tables
Normalisation avoids
- repeating groups
- data redundancy
- anomalies (update, insertion, deletion)
- complex queries
Repeating groups and data redundancy
Repeating groups
- a field hat can have multiple values
- when repeating groups are removed, data redundancy is produced
- in databases : all fields should have a single values / not many
Summary
A repeating group ccurs when a field contains more than one value
Repeating groups produce invalid tables since it infringes on the primary key
Inserting multiple values in a cell is not possible
Expanding the primary key produces data redundancy
Anomalies
Update anomaly
Occur when the same data needs to be updated in more than one place
- human error could lead to inconsistencies and if the data in the database is
inconsistent then it is of little value
Deletion anomaly
Occur when a deletion causes unnecessary loss of data
Insertion anomaly
Databases consist of many tables that contain data that is related in some way
- called relational databases
- managed by the DBMS – database management systems
Keys
Primary key
A field in a table that uniquely identifies each record in the table
- no data values in the primary key field are repeated
- shown by underlined word
Super keys
A combination of fields in the tables which uniquely identify each row
- any number of fields that will uniquely identify a record
Candidate keys
Minimal super keys
Primary keys
A minimal candidate key that uniquely identifies a record
composite/compound/concatenated keys
A combination of two or more fields to form a primary key
Normalisation
Database design
- the process of representing a problem in the real world as a set of tables
Normalisation avoids
- repeating groups
- data redundancy
- anomalies (update, insertion, deletion)
- complex queries
Repeating groups and data redundancy
Repeating groups
- a field hat can have multiple values
- when repeating groups are removed, data redundancy is produced
- in databases : all fields should have a single values / not many
Summary
A repeating group ccurs when a field contains more than one value
Repeating groups produce invalid tables since it infringes on the primary key
Inserting multiple values in a cell is not possible
Expanding the primary key produces data redundancy
Anomalies
Update anomaly
Occur when the same data needs to be updated in more than one place
- human error could lead to inconsistencies and if the data in the database is
inconsistent then it is of little value
Deletion anomaly
Occur when a deletion causes unnecessary loss of data
Insertion anomaly
