1.3.2 Databases

Question 1

What is a database?

Answer 1

A structured store of data. Usually consists of tables, fields, and records.

Question 2

What is a flat-file database, and the issues they face?

Answer 2

A database where all data is stored in one table

Difficult to change the format of the data

Space is wasted through redundant data

Data is harder to update as it could be in multiple places

Data might become inconsistent for example the amount of an item in stock might be reduced in one record but not in other records.

Question 3

Define a relational database and the benefits of using them.

Answer 3

Multiple related tables

Simpler to change the format of data

Saves space by reducing data duplication

Maintains data consistency/integrity

Improved security as able to control access to data

Question 4

Define and give an example of a primary key.

Answer 4

A field that has a unique value to act as a unique identifier for every record in that table.

UserID in a table of users

Question 5

Define and give an example of a foreign key.

Answer 5

A field that links to a (primary) key in a second table.

Provides a link between tables

Question 6

Define and give an example of a secondary key.

Answer 6

Not unique but are likely to be the fields that users are likely to want to search by. Surname in a user table.

Indexed allowing for faster searching.

Question 7

Define indexing and its associated pros and cons.

Answer 7

The index gives the position of each record according to its primary key.

Advantage: Searches of indexed fields can be performed more quickly.

Disadvantage: The index has to be rebuilt on edit and takes up extra space in the database.

Question 8

What three types of database relationships exist, and which one is prohibited?

Answer 8

1:1 – One to One
1:M – One to Many
M:M – Many to many

M:M - These need to be resolved using a middle table with many coming out of each side.

Question 9

Give some examples of sensors and what they may be used for.

Answer 9

Altimeter: Measures height from sea level

Accelerometer: Measure forces – When it moves.

Gyroscope: Used to measure rotation.

Thermistor: To read the temperature

GPS: To determine geographical location

Magnetometer: To determine direction.

Heart rate sensor: Detects electrical activity in the heart

Question 10

Give some examples of how data can be captured.

Answer 10

Web Form

Optical Character Recognition (OCR)

Optical Mark Recognition (OMR)

Sensors

Question 11

Give some examples of the methods used to exchange data.

Answer 11

CSV: A format with values separated by commas

JSON: Uses human-readable text to transmit data objects consisting of attribute-value pairs

XML: A mark-up language that uses tags to denote data.

API: A prewritten set of subroutines that provide access to a company’s data. Used by programmers to transfer data between computer systems.

Question 12

What is referential integrity?

Answer 12

Ensures that changes are consistent across a database

Changes to data in one table must also happen to data in linked tables (cascaded).

A foreign key value must have a corresponding primary key value in another table.

Question 13

Why is transaction processing required?

Answer 13

Without careful transaction processing, one transaction could accidentally overwrite another or half complete leading to inaccurate data.

Question 14

What is record locking, why is it required and what issue can be faced?

Answer 14

Records should be locked when in use. If one transaction is amending a record, no other transaction should be able to modify it until the first transaction is complete.

The outcome of concurrent transactions is the same as if transactions were completed sequentially.

Can cause delays as users wait for access and could cause deadlock

Question 15

What are the four rules of transaction processing?

Answer 15

Atomicity

Consistency

Isolation

Durability

Question 16

Define atomicity from the ACID model for transaction processing

Answer 16

Transactions should be fully complete, or not at all

Question 17

Define consistency from the ACID model for transaction processing

Answer 17

The transaction should only change the database according to the rules of the database (validation).

Question 18

Define isolation from the ACID model for transaction processing

Answer 18

Each transaction shouldn’t affect/overwrite other transactions concurrently being processed.

Question 19

Define durability from the ACID model for transaction processing

Answer 19

Committed data should be saved in secondary storage, so it is not lost in the case of system failure.

Question 20

What is good and bad redundancy in a database?

Answer 20

Good redundancy – if part of a database is lost it should be recoverable from elsewhere.

Bad redundancy – Having duplicate data within tables and records. This is fixed by normalising the database tables.

Question 21

What are the rules for data to be in 1st Normal Form (1NF)

Answer 21

• No repeating fields
• The data is atomic (Cannot be broken down anymore)
• The data has a primary Key

Question 22

What are the rules for data to be in 2nd Normal Form (2NF)

Answer 22

It is in 1NF and every field is dependent on the primary key.

Question 23

What are the rules for data to be in 3rd Normal Form (3NF)

Answer 23

All attributes are dependent on the key, the whole key, and nothing but the key.

Question 24

Why is this table not in 3NF?

Answer 24

To be in 3NF all fields must only be dependent on the primary key. In this example the Destination Code and Destination Name have a connection and if one changes, the other must change as a result. This means they are dependent on both the primary key, and each other.

Question 25

What is the SQL to select all data from a table?

Answer 25

SELECT column_name(s)
FROM table_name

or

SELECT * FROM table_name

Question 26

What is the SQL to delete data from a table?

Answer 26

DELETE FROM table_name
WHERE some_column=some_value

Notice that we do not use the asterix as we dont get to choose which columns to delete

Question 27

What is the SQL to insert data into a table?

Answer 27

INSERT INTO table_name
VALUES (value1, value2…)

Question 28

What is the SQL to remove a table?

Answer 28

DROP TABLE table_name

Question 29

What is the SQL to remove a column?

Answer 29

ALTER TABLE table_name
DROP COLUMN column_name

Question 30

What is the SQL to join two tables together?

Answer 30

SELECT column_name(s)
FROM table_name1
JOIN table_name2ON table_name1.column_name = table_name2.column_name

Question 31

What is the SQL to select all fields from a table where the data in a column ends with ‘th’?

Answer 31

SELECT * FROM table_name WHERE column_name LIKE ‘%th’

Question 32

What is the SQL to update a value in a table?

Answer 32

UPDATE table_name
SET column1=value, column2=value
WHERE some_column=some_value