3.2 Databases

Question 1

What are 2 types of databases?

Answer 1

flat file database
relational database

Question 2

What is a flat file database?

Answer 2

A single file that is typically based around a single entity and its attributes.

Question 3

What is a relational database?

Answer 3

A database where a different file/table is created for each entity. The tables are linked together via attributes.

Question 4

Why use a relational database instead of a flat file?

Answer 4

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.

Question 5

What is an entity?

Answer 5

An item of interest about which information is stored.

Question 6

What are attributes?

Answer 6

Characteristics of the entity about which data is collected.

Question 7

What are the 3 keys?

Answer 7

primary key
secondary key
foreign key

Question 8

What is a primary key?

Answer 8

The field/attribute that acts as a unique identifier for each record in the table.

Question 9

What is a field and what is a record?

Answer 9

A field is a database column and a record is a database row. i.e. personID

Question 10

What is a foreign key?

Answer 10

The attribute that links 2 database tables together. This acts as the primary key of one table and the foreign key of another.

Question 11

What is a secondary key?

Answer 11

An attribute that is used for indexing in order to search for records quickly or put records in order i.e. name

Question 12

What are 3 types of table entity relationships?

Answer 12

one-to-one
one-to-many
many-to-many

Question 13

What is the one-to-one relationship?

Answer 13

Each entity can only be associated with one other entity i.e. husband and wife

Question 14

What is the one-to-many relationship?

Answer 14

One entity can be associated with many other entities i.e. mother and children

Question 15

What is the many-to-many relationship?

Answer 15

One entity can be associated with many other entities and the same the other way around i.e. students and courses

Question 16

How are these shown in diagrams?

Answer 16

direct line attached to a table is one

three lines attached to the table is many

Question 17

What is normalisation?

Answer 17

The process of creating the best possible layout for a relational database.

Question 18

What are the aims of normalisation?

Answer 18

• remove unnecessary duplicates (minimise data redundancy)
• consistent data throughout linked tables (maximise data consistency)
• records can be added/removed without causing issues

Question 19

What are 3 types of normalisation?

Answer 19

1st 2nd and 3rd normal form

Question 20

What are the requirements for 1st normal form?

Answer 20

data is atomic
no repeating fields
contains a primary key

Question 21

What are the requirements for 2nd normal form?

Answer 21

1nf
every field is dependent on the primary key

Question 22

What are the requirements for 3rd normal form?

Answer 22

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)

Question 23

What is indexing?

Answer 23

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.

Question 24

What does SQL stand for?

Answer 24

structured query language

Question 25

What is referential integrity and why is it important?

Answer 25

Making sure every foreign key links to an existing valid primary key in another table. This ensures data consistency throughout a relational database.

Question 26

How can referential integrity be broken?

Answer 26

If the primary key in a table is updated or deleted but the foreign key referencing it hasn’t been changed.

Question 27

What is a transaction?

Answer 27

A single operation executed on data.

Question 28

What is ACID and what does it stand for?

Answer 28

The rules for transaction processing in databases.

atomicity
consistency
isolation
durability

Question 29

What is atomicity?

Answer 29

A transaction must be processed in its entirety or not at all.

Question 30

What is consistency?

Answer 30

A transaction must maintain the referential integrity rules between linked tables.

Question 31

What is isolation and how is it acheived?

Answer 31

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.

Question 32

What is durability and how is it achieved.

Answer 32

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.

Question 33

What is record locking?

Answer 33

Preventing simultaneous access to records in a database to prevent inconsistencies or loss of updates.

Question 34

What is a problem that can be caused by record locking?

Answer 34

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

Question 35

What is data redundancy?

Answer 35

When the same piece of data exists multiple times in different locations.