1.3.2 - Databases

Question 1

What is a Database?

Answer 1

A structured and persistent store of data for ease of processing; allowing for data to be:
- Retrieved quickly
- Updated easily
- Filtered for different views

Question 2

What are Records

Answer 2

• Records are made up of fields
• Example: Person could be represented as record with fields as name, age and address

Question 3

Describe a Flat-File Database

Answer 3

• A simple data structure table that is easy to maintain as only a limited amount of data is stored.
• They are of limited use because they may have redundant data which can waste space and leave inconsistent data.
• No specialist knowledge is needed to operate.
• They are harder to update and the data format is difficult to change.

Question 4

Describe a Relational database

Answer 4

• Based on tables whose records are linked by certain fields (relation)
• Each table has data on one entity
• Each table has a relationship to other tables using primary and foreign keys

Question 5

Benefits of a Relational Database over a Flat File Database

Answer 5

• Relational data allows for less redundancy of data (less repeated data)
• Relational databases improve the consistency of data
• Relational databases allow for complex queries and/or searches to be performed

Question 6

Redundancy

Answer 6

• Redundant data is data that is repeated in a database
• A single table file is inefficient as it is full of redundant data

Question 7

Primary Key

Answer 7

• A field that is a unique identifier for every record in that table
• Example - ID numbers

Question 8

What is Concatenated Primary Key?

Answer 8

When more than one field is added together to form a unique primary key for a table.

Question 9

Foreign Key

Answer 9

• Primary key in one table used as an attribute (foreign key) in another
• Provides a link between tables
• Represents many-to-one relationship

Question 10

Secondary Key

Answer 10

An attribute that is indexed and allows a group of records to be searched for quickly; usually more memorable than PK

Question 11

Different types of Entity Relationship Modelling (ERM)

Answer 11

• One to one
• One to many
• Many to Many

Question 12

One to one relationship

Answer 12

• When one entity is linked to another entity
• Makes no sense to put in separate tables

Question 13

One to many relationship

Answer 13

• When one entity is linked to several entities
• Used in most well designed RDBs

Question 14

Many to many relationship

Answer 14

• When several entities are linked to several other entities
• Problematic; will lead to data redundancy

Question 15

Database management

Answer 15

Handled by the database management system (DBMS) such as:
- MySQL
- Oracle
- Bigtable

Question 16

Methods of Capturing Data

Answer 16

• Paper-Based Forms - Manual data input involving a human reading and typing the information into a system
• Optical Mark/Character Recognition (OCR/OMR) - Automatically reads text/marks by interpreting the shape of the letters; better for printed text and used for Road Cameras and Multiple Choice Tests
• Chip and Pin/QR Codes

Question 17

Methods of Exchanging Data

Answer 17

• XML and JSON - Human-readable, open formats for structuring data
• CSV (Comma Separated Value file) - Stores each record on a separate line and each field is separated by a comma

Question 18

What is Normalisation?

Answer 18

The process of arranging data in tables, and setting their relationships to move them through normal forms.

Question 19

What is Indexing?

Answer 19

The process of creating a database index, which is a data structure that improves the speed of data retrieval operations on a dataset table at the cost of additional writes and storage space to maintain the index data structure.

Question 20

What is Normal Forms?

Answer 20

A way of structuring the data in a relational database according to formal rules, in order to avoid problems of efficiency and security in accessing and maintaining the data.

Question 21

What is 0NF?

Answer 21

A table with no normalisation. All data and all fields in one table.

Question 22

What is 1NF?

Answer 22

• No Repeating fields; all fields must be unique
• Data must be atomic
• Database has a primary Key

Question 23

What is 2NF?

Answer 23

Data must be in 1NF and any partial dependencies must be removed

Question 24

What is 3NF?

Answer 24

Data must be in 2NF and any transitive dependencies must be removed

Question 25

SQL

Answer 25

Structured Query Language: The language and syntax used to write and run database queries.

Question 26

What is Referential Integrity?

Answer 26

Refers to the accuracy of and consistency of data across a database
Conditions for Referential Integrity:
- If a record is removed, all references to it are removed
- A foreign key value must have a corresponding Primary key value in another table.

Question 27

What is Transaction Processing?

Answer 27

Information processing that is divided into individual, indivisible operations, called transactions. Each transaction must succeed or fail as a complete unit, and it can never be only partially complete.

Question 28

What is ACID, and what do the terms stand for?

Answer 28

ACID: Atomicity, Consistency, Isolation, Durability.

A set of properties that guarantee that database transactions are processed reliably. In the context of databases, a single logical operation on the data is called a transaction.

Question 29

Atomicity

Answer 29

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

Question 30

Consistency

Answer 30

Any changes in the database must retain the overall state of the database

Question 31

Isolation

Answer 31

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

Question 32

Durability

Answer 32

Once a change has been made to a database, committed data/transactions must not be lost in case of power / system failure

Question 33

What is Record Locking?

Answer 33

Allows one user to access record level data at any one time so data that is being used elsewhere cannot be modified