10: Fundamentals of Databases

Question 1

Relational Database

Answer 1

A database where data is held in multiple tables, which are linked together by relationships

Question 2

Primary Key

Answer 2

An attribute in a database table, that acts as a unique identifier for each record

Question 3

Composite Primary Key

Answer 3

A primary key that consists of multiple attributes

Question 4

Attribute

Answer 4

A category heading for each item of data in a column of a table

Question 5

Foreign Key

Answer 5

A primary key of one table, that appears in another table to create a relationship between them

Question 6

First Normal Form (3)

Answer 6

• Each record has a primary key
• Data is atomic
• No repeating group of attributes

Question 7

Second Normal Form

Answer 7

No partial dependencies (where a non-key attribute varies with only part of a composite primary key, a new table must be created)

Question 8

Third Normal Form

Answer 8

No non-key dependencies (where one non-key attribute varies with another, a new table must be created)

Question 9

Benefits of Normalised Databases (3)

Answer 9

• Reduction in the amount of duplicated data
• Improved data and referential integrity
• Elimination of insertion, update and deletion anomalies

Question 10

SQL Retrieve Command

Answer 10

SELECT column_1, column_2, …
FROM table
WHERE condition[s]
ORDER BY column [DESC]
LIMIT num_limit
OFFSET num_offset

Question 11

SQL Update Command

Answer 11

UPDATE table
SET column_1 = new_value_1, column_2, = new_value_2, …
WHERE condition[s]

Question 12

SQL Insert Command

Answer 12

INSERT INTO table [(column_1, column_2, …)]
VALUES (value_1, value_2, …)

Question 13

SQL Delete Command

Answer 13

DELETE FROM table
WHERE condition[s]

Question 14

SQL Create Table Command

Answer 14

CREATE TABLE table (
column_1 datatype_1 [PRIMARY KEY],
column_2 datatype_2,
…
[FOREIGN KEY (foreign_key_1) REFERENCES other_table (primary_key),
…]
)

Question 15

A Client Server Database System Provides ____

Answer 15

Simultaneous access to the database for multiple clients

Question 16

Concurrent Access Can Result In ____ If Two Clients ____

Answer 16

The problem of updates being lost, edit a record at the same time

Question 17

Record Locks (4)

Answer 17

• A record is locked whenever a user retrieves it for updating
• Another user trying to retrieve the same record is denied access until the transaction is complete
• If two users are trying to update two records, deadlock can occur
• Deadlock is where user 1 locks record 1 and tries to update record 2 in the same transaction, but user 2 has locked record 2 and is trying to update record 1 so neither user can proceed

Question 18

Serialisation (3)

Answer 18

• Serialisation ensures transactions do not overlap in time so cannot interfere
• A transaction cannot start until the previous one has finished
• Serialisation can be implemented using timestamp or commitment ordering

Question 19

Timestamp Ordering (5)

Answer 19

• Whenever a transaction starts, it is given a timestamp
• If two transactions affect the same object, the transaction with the earlier timestamp is applied first
• To ensure transactions are not lost, every object has read and write timestamps, which are updated whenever it is read or written to
• When a transaction starts, it reads data, setting a read timestamp
• Before it writes back, if the read timestamp is different, it knows another transaction is taking place, avoiding problems

Question 20

Commitment Ordering (2)

Answer 20

• Transactions are ordered in terms of their dependencies on each other
• Where one request would cause a deadlock, it is blocked until another is completed