3.2 Databases Flashcards
What are 2 types of databases?
flat file database
relational database
What is a flat file database?
A single file that is typically based around a single entity and its attributes.
What is a relational database?
A database where a different file/table is created for each entity. The tables are linked together via attributes.
Why use a relational database instead of a flat file?
If there is a 1 to many relationship between 2 entities, multiple records will be needed for the same entity to be linked to multiple other entities. This results in redundant data/wasted space as the records will contain repeated information.
What is an entity?
An item of interest about which information is stored.
What are attributes?
Characteristics of the entity about which data is collected.
What are the 3 keys?
primary key
secondary key
foreign key
What is a primary key?
The field/attribute that acts as a unique identifier for each record in the table.
What is a field and what is a record?
A field is a database column and a record is a database row. i.e. personID
What is a foreign key?
The attribute that links 2 database tables together. This acts as the primary key of one table and the foreign key of another.
What is a secondary key?
An attribute that is used for indexing in order to search for records quickly or put records in order i.e. name
What are 3 types of table entity relationships?
one-to-one
one-to-many
many-to-many
What is the one-to-one relationship?
Each entity can only be associated with one other entity i.e. husband and wife
What is the one-to-many relationship?
One entity can be associated with many other entities i.e. mother and children
What is the many-to-many relationship?
One entity can be associated with many other entities and the same the other way around i.e. students and courses
How are these shown in diagrams?
direct line attached to a table is one
three lines attached to the table is many
What is normalisation?
The process of creating the best possible layout for a relational database.
What are the aims of normalisation?
- remove unnecessary duplicates (minimise data redundancy)
- consistent data throughout linked tables (maximise data consistency)
- records can be added/removed without causing issues
What are 3 types of normalisation?
1st 2nd and 3rd normal form
What are the requirements for 1st normal form?
data is atomic
no repeating fields
contains a primary key
What are the requirements for 2nd normal form?
1nf
every field is dependent on the primary key
What are the requirements for 3rd normal form?
2nf
all attributes depend on the primary key, the whole primary key and nothing but the primary key
i.e. no transitive relationships
(non key fields can’t depend on other non key fields)
What is indexing?
Storing the position of each record in the order of a specific attribute. This is automatically the primary key but typically a secondary key will be indexed to make data easier and quicker to search for.
What does SQL stand for?
structured query language
What is referential integrity and why is it important?
Making sure every foreign key links to an existing valid primary key in another table. This ensures data consistency throughout a relational database.
How can referential integrity be broken?
If the primary key in a table is updated or deleted but the foreign key referencing it hasn’t been changed.
What is a transaction?
A single operation executed on data.
What is ACID and what does it stand for?
The rules for transaction processing in databases.
atomicity
consistency
isolation
durability
What is atomicity?
A transaction must be processed in its entirety or not at all.
What is consistency?
A transaction must maintain the referential integrity rules between linked tables.
What is isolation and how is it acheived?
Simultaneous execution of transactions should lead to the same result as if they were processed at separate times. This can be achieved through record locking.
What is durability and how is it achieved.
Once a transaction has been executed, it will remain so no matter what happens e.g. power cut. This is achieved by storing all completed transactions in secondary storage as it’s non-volatile.
What is record locking?
Preventing simultaneous access to records in a database to prevent inconsistencies or loss of updates.
What is a problem that can be caused by record locking?
deadlock
user 1 accesses record 1
user 2 accesses record 2
user 1 tries to access record 2 at the same time as user 2 tries to access record 1
both are waiting for the other record to be free resulting in no progress
What is data redundancy?
When the same piece of data exists multiple times in different locations.
