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