4.10 Databases

Question 1

What is an entity?

Answer 1

An entity is the object / thing of interest about which data is to be recorded (a table in SQL).

Question 2

What is an attribute?

Answer 2

An attribute is property of an entity (a field in SQL).

Question 3

What is a relationship?

Answer 3

A relationship is the link or association between entities (primary keys ←→ foreign keys)

Question 4

What is a key (in a database)?

Answer 4

• A key is the name given to a special field in a database where the values in the field can be used to identify particular records, e.g. CustomerID.
• Sometimes a field that is already in the database can be used as the key field.

Question 5

What is a primary key?

Answer 5

A key which has a unique value for each record, and acts as a unique identifier for each record

Question 6

3 problems with a flat file database

Answer 6

1. One small change can involve tediously changing many records
2. Deleting the only instance of a record containing a particular category will also delete that category
3. You can’t add a new category until you create a record that contains that particular category

Question 7

How do you solve the problems with flat file databases?

Answer 7

• Relational databases that are made up of two or more linked tables.
• Linked tables can be used to find records which are linked together by using the value of the shared key column. They are a feature of relational databases.
• This shared key column acts as the primary key in the table in which its defined, and as a foreign key in the table it is used.

Question 8

What is a flat file database?

Answer 8

A simple store of information
- Columns are called fields
- Rows are called records
- A table is a group of records

Question 9

What is a composite key?

Answer 9

A key composed of two or more attributes that together uniquely identify a record.

Question 10

When is normalisation (database) achieved?

Answer 10

• When there is no redundant data and all related data is stored together
• (When each attribute depends on the key, the whole key and nothing but the key)

Question 11

What is the saying for database normalisation?

Answer 11

“Each attribute must depend on the key, the whole key, and nothing but the key”

Question 12

Requirements for 1st normal form

Answer 12

1. Every record has a primary key
2. No repeating groups → must include a copy of the primary key in the new table (creating a composite key)
3. The data in each field must be atomic (i.e. data cannot be sensibly subdivided e.g. name → fname & sname)

Question 13

What is a repeating group?

Answer 13

A set of attributes whose structure repeats between rows

Question 14

Requirements for 2nd normal form

Answer 14

No partial dependencies
this means…
- Do all nonkey attributes depend on all parts of the composite key?
- If not, take them out and create a new entity.
(So check all tables with composite keys)

Question 15

Requirements for 3rd normal form

Answer 15

No non-key dependencies
- All non-key attributes are checked to see if they are only dependent on the primary key.

Question 16

How do you identify the key in an entity relationship diagram?

Answer 16

Underlining

Question 17

SQL: Create a database called school

Answer 17

CREATE DATABASE school

Question 18

What does DDL stand for (in SQL)?

Answer 18

Data Definition Language

Question 19

SQL: Create a table called students
(StudentNumber as primary key, forename, surname, and date of birth)

Answer 19

CREATE TABLE students
(
StudentNumber INT(8) PRIMARY KEY,
forename VARCHAR(32),
surname VARCHAR(32),
DateOfBirth DATE
)

Question 20

SQL: What is the keyword for deleting a database/table?

Answer 20

DROP …

Question 21

SQL data types

Answer 21

CHAR(size) // → fixed length string
VARCHAR(size) // → string (with a maximum length)
ENUM // → string object that has to be chosen from a list of possible values
BOOL / BOOLEAN
INT(size) // → size is the number of digits
FLOAT(size,d) // → size is the number of digits , with d digits after the decimal point
DATE
TIME
DATETIME

Question 22

What does DML stand for (in SQL)?

Answer 22

Data manipulation language

Question 23

SQL: all strings beginning with ‘str’

Answer 23

LIKE ‘str%’

Question 24

SQL: Any strings at least five characters long

Answer 24

LIKE ‘_____%’

Question 25

SQL: between two numbers

Answer 25

... WHERE StudentNumber BETWEEN 10 AND 100

Question 26

SQL: Selecting from two tables

Answer 26

SELECT table1.attributeX , table2.attributeY FROM table1 , table2 WHERE table1.primarykey = table2.foreignkey AND ...

Question 27

SQL: ordering results

Answer 27

ORDER BY surname ORDER BY surname ASC ORDER BY surname DESC

Question 28

SQL: Inserting data

Answer 28

INSERT INTO students VALUES(__,__),(__,__),(__,__) INSERT INTO students (surname , forename) VALUES (__... //if you can't remember what order you're supposed to do it in)

Question 29

SQL: update a forename to 'Harry' where...:

Answer 29

UPDATE students SET forename = ‘Harry' WHERE...

Question 30

SQL: Delete record

Answer 30

DELETE FROM students WHERE ...

Question 31

What is the purpose of a client server database?

Answer 31

To provide simultaneous access to a database for multiple clients

Question 32

Why does concurrent access need to be managed in a client server database?

Answer 32

To preserve the integrity of the database

Question 33

State one problem that concurrent access could result in

Answer 33

Updates could be lost if two clients edit a record at the same time

Question 34

4 ways to manage concurrent access

Answer 34

1. Record locks 2. Serialisation 3. Timestamp ordering 4. Commitment ordering

Question 35

What does it mean for a schedule of transactions to be serialised?

Answer 35

If the schedule of transactions has the same effect as an **equivalent serial schedule**

Question 36

What is a transaction?

Answer 36

A set of operations that are grouped together as a single logical unit

Question 37

Why is serialisation useful?

Answer 37

It allows us to interleave and overlap operations within multiple transactions, reducing the time taken for transactions to be completed

Question 38

How does record locking work?

Answer 38

- When a transaction on a record starts, a lock is set on the record - Other transactions cannot access the record until the lock is released

Question 39

2 disadvantages of record locking

Answer 39

* Slows the system down slightly * Can lead to deadlock

Question 40

How does timestamp ordering work? (2•)

Answer 40

- Timestamps are generated **for each transaction**. These timestamps indicate the order transactions occurred in - The database server applies **rules** to determine if processing a transaction **will result in loss of data integrity**, and if so aborts the transaction

Question 41

2 situations in timestamp ordering when a transaction would be aborted

Answer 41

* If transactions is trying to **write** to a record: * then when the **read or write timestamp** on the record is greater that the time at which the transaction started * If transactions is trying to **read** a record: * then when the **write timestamp** on the record is greater that the time at which the transaction started

Question 42

When can a transaction be committed?

Answer 42

When all of its operations have been carried out

Question 43

What is commitment ordering?

Answer 43

Using an algorithm to determine an **optimum order** to commit half-complete transactions in order to reduce conflicts and deadlocks

Question 44

What is a foreign key?

Answer 44

The primary key field of one table that appears in another table to make a link between the tables