✅1.3.2 Databases

Question 1

State 2 benefits of using a relational database instead of a flat file database?

Answer 1

1. Relational databases allows for less redundancy of data/less repeated data
2. Relational databases improve the consistency of data

Question 2

State the difference between a primary key and a foreign key

Answer 2

A primary key will only appear once in a table/is a unique identifier. Whereas a foreign key may appear multiple times a table/may not be unique

Question 3

What does ACID stand for?

Answer 3

1. Atomicity
2. Consistency
3. Isolation
4. Durability

Question 4

What is a database?

Answer 4

An organised collection of data.

Question 5

What is a flat file database?

Answer 5

A database that stores all data in a single table.

Question 6

What is a primary key?

Answer 6

A unique identifier for each record in a table.

Question 7

What is a secondary key?

Answer 7

A field or fields that are indexed for faster searching.

Question 8

What is a foreign key?

Answer 8

A field in a table that refers to the primary key in another table used to link tables and create relationships.

Question 9

What is a composite primary key?

Answer 9

A combination of 2 or more fields that are unique for all records.

Question 10

What is a relational database?

Answer 10

A database that organises data into multiple tables, using keys to connect related data

Question 11

What is a database management system?

Answer 11

Software used to store, retrieve, manipulate, and manage data in databases

Question 12

What is normalisation?

Answer 12

The process of organising a database to reduce data duplication and improve data accuracy and consistency

Question 13

What are the types of relationships?

Answer 13

One-to-one
One-to-many
Many-to-many

Question 14

What is SQL?

Answer 14

A programming language used to interact with a database management system
(DBMS)

Question 15

Describe what is meant by transactions being durable

Answer 15

(Committed) transactions are not lost in case of system failure.

Question 16

Identify two methods of exchanging data with other computer systems

Answer 16

1. CSV
2. JSON
3. XML
4. SQL

Question 17

State three requirements for a database to be in First Normal Form (1NF).

Answer 17

1. Each record has a primary key
2. Data is atomic
3. No repeating groups of attributes

Question 18

State two requirements for a database to be in Second Normal Form (2NF).

Answer 18

1. In 1NF
2. Have no partial dependencies (non-primary key attributes must not depend on only part of the primary key (in the case of a composite primary key))

Question 19

State three requirements for a database to be in Third Normal Form (3NF).

Answer 19

1. In 2NF
2. Contains no non-key dependencies (all attributes are dependant on the key, the whole key and nothing but the key)
3. Have no transitive dependencies (non-primary key attributes must not depend on other non-primary key attributes)

Question 20

What is a field?

Answer 20

A single piece of data in a record.

Question 21

What is a record?

Answer 21

A group of related fields, representing one data entry.

Question 22

What is a table?

Answer 22

A collection of records with a similar structure.

Question 23

What are 3 pros of electronic databases?

Answer 23

1. Easier to add, delete, modify and update data
2. Data can be backed up and copied easier
3. Multiple users, from multiple locations, can access the same database at the same time

Question 24

What is an entity?

Answer 24

Something worthy of capturing and storing data about e.g. students, orders.

Question 25

What does referential integrity do?

Answer 25

It ensures consistency between related tables in a relational database. And maintains valid relationships between primary and foreign keys

Question 26

Name 2 pros of referential integrity

Answer 26

1. Ensures data consistency and accuracy 2. Prevents orphaned records.

Question 27

Name 2 cons of referential integrity?

Answer 27

1. Can impact performance due to additional checks 2. May require additional planning and design

Question 28

What are 3 ways to maintain referential integrity?

Answer 28

1. Use a DBMS with built in support 2. Implement triggers to enforce custom referential integrity rules 3. Regularly validate and clean up data to ensure consistency

Question 29

What is an entity relationship diagram?

Answer 29

A diagram that represents the entities (tables) that will be in a database and the relationships between these entities.

Question 30

How can you resolve implementing a many to many entity relationship model?

Answer 30

Create a new table known as a 'link' table that goes between the entities. []—<[]>—[]

Question 31

What is data integrity?

Answer 31

Data integrity refers to maintaining and ensuring the reliability of data in terms of its accuracy, completeness, and consistency over its lifecycle.

Question 32

What is indexing?

Answer 32

A technique used to speed up data retrieval in a database.

Question 33

What are 2 pros and 3 cons of a flat file database?

Answer 33

Pros: 1. Simple 2. Easy to understand Cons: 1. Causes data redundancy 2. Inefficient storage 3. Harder to maintain

Question 34

Describe what is meant by transactions being atomic

Answer 34

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

Question 35

Describe what is meant by transactions having consistency

Answer 35

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

Question 36

Describe what is meant by transactions having isolation

Answer 36

Simultaneous executions of transactions should lead to the same result as if they were executed one after the other.

Question 37

What is Record Locking?

Answer 37

The process of preventing simultaneous access to records in a database, ensuring data consistency and integrity.

Question 38

Give one way that durability can be achieved for a completed transaction.

Answer 38

Completed transactions stored in secondary storage / data not stored long-term in RAM/cache

Question 39

Give two disadvantages of using record locking

Answer 39

1. Can cause delays (as users wait for access) 2. Can cause deadlock

Question 40

Explain how record locking can be used to ensure that the ACID principle of isolation is achieved when carrying out multiple transactions. [3]

Answer 40

1. Transactions having isolation means simultaneous executions of transactions should lead to the same result as if they were executed one after the other. 2. Record locking allows one user to access record level data at any one time 3. So data that is being used elsewhere cannot be used

Question 41

What is redundancy?

Answer 41

The process of having one or more copies of the data in physically different locations. This means that if there is any damage to one copy the others will remain unaffected and can be recovered.

Question 42

Name one disadvantage of indexing

Answer 42

The index takes up extra space in the database.

Question 43

Users can build up playlists of their songs. Two tables are created called Playlist and Songs. Explain why a third table called PlayListEntry is needed. [3]

Answer 43

1. Songs and Playlist would have a many to many relationship 2. This is not allowed 3. Adding a table between them resolves this

Question 44

Explain what is meant by verification using double entry? [2]

Answer 44

1. Data input twice 2. The two inputs are compared by computer to ensure they are identical

Question 45

Describe two validation checks that can be carried out on the price of a product [4]

Answer 45

1. Range check. Parameters imposed beyond which the data cannot go e.g. Tin of beans must be between 0.10 and 1.50 2. Length check. The maximum number of characters is specified. Price of a tin of beans must be three characters

Question 46

An airport wishes to allow airlines to be able to access the data it has on flights via the internet. Describe one format or method the airport could use to provide the data to the airlines so they can use it in their own applications. [2]

Answer 46

CSV/Comma Separated Value file can be used which is a text file with values separated by comma.

Question 47

What are 2 differences between a flat file database and a relational database?

Answer 47

1. Flat file database stores all data in a single table, whereas a relational database organises data into multiple tables 2. Flat file database causes redundancy whereas relational database uses keys to connect related data which reduces data redundancy