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 what issues do 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 which increases the size of the database
• 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.
• Data access speed is slow

Question 3

What is a relational database, and what are 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.

Example: 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

Example: CustomerID in an orders table to link to the users table

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.
• Indexed allowing for faster searching.

Example: Surname or date of birth in a user table.

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 must 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

What would a resolved M:M relationship diagram look like?

Answer 9

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

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 sensors and what they may be used for.

Answer 11

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 12

Give some examples of the methods used to exchange data

Answer 12

• 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 13

What is referential integrity?

Answer 13

• 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 14

Why is transaction processing required?

Answer 14

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

Question 15

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

Answer 15

• 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 16

What are the four rules of transaction processing?

Answer 16

Atomicity

Consistency

Isolation

Durability

Question 17

Define atomicity from the ACID model for transaction processing

Answer 17

Transactions should be fully complete, or not at all

Question 18

Define consistency from the ACID model for transaction processing

Answer 18

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

Question 19

Define isolation from the ACID model for transaction processing

Answer 19

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

Question 20

Define durability from the ACID model for transaction processing

Answer 20

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

Question 21

What are some examples of good and bad redundancy in a database system? How can bad redundancy be prevented?

Answer 21

Duplication of data

• Good redundancy – if part of a database is lost it should be recoverable from elsewhere (back-ups).
• Bad redundancy – Having duplicate data within tables and records. This is fixed by normalising the database tables.

Question 22

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

Answer 22

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

Question 23

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

Answer 23

It is in 1NF and every field is dependent on the primary key (All non-key fields depend on key field).

Question 24

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

Answer 24

All non-key fields don’t depend on another non-key field

Question 25

Why is this table not in **3NF**?

Answer 25

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 26

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

Answer 26

SELECT column_name(s) FROM table_name or SELECT * FROM table_name

Question 27

What is the format of a **nested SQL statement**?

Answer 27

SELECT column_name(s) FROM table_name WHERE some_column = (SELECT column_name FROM table_name WHERE some_column)

Question 28

What is the SQL to **delete** data from a table?

Answer 28

DELETE FROM table_name WHERE some_column=some_value ## Footnote *Notice that we do not use the asterisk as we dont' get to choose which columns to delete*

Question 29

What is the SQL to **insert** data into a table?

Answer 29

INSERT INTO table_name VALUES (value1, value2...)

Question 30

What is the SQL to **remove** a **table**?

Answer 30

DROP TABLE table_name

Question 31

What is the SQL to **remove** a **column**?

Answer 31

ALTER TABLE table_name DROP COLUMN column_name

Question 32

What is the SQL to **join** two tables together?

Answer 32

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

Question 33

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

Answer 33

SELECT * FROM table_name WHERE column_name LIKE ‘%th’ ## Footnote Like and % always go hand in hand

Question 34

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

Answer 34

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

Question 35

Data Capture: What is **OMR** and **OCR**?

Answer 35

**Optical Mark Recognition (OMR)** - Reads marks on pre-formatted forms to enable inputs to be quickly scanned. Also reduces human error. **Optical Character Recognition (OCR)**- Allows a computer to identify characters and convert them into their binary equivalents. Example use: Automatic number plate recognition (ANPR).