Databases

Question 1

Entity Relationship Database:

Answer 1

Allows to visual how data will be organised

Question 2

One to one relationship:

Answer 2

A record in one table relates to only one record in another.

Question 3

One to many relationship:

Answer 3

A record in one table relates to multiple records in another table

Question 4

Many to many relationship:

Answer 4

Not desirable, means a table isn’t fully normalised, not to 3NF

Question 5

Primary Key:

Answer 5

Entity identifiers which are used to uniquely identify each entity in a table. (represented with a line)

Question 6

Composite Primary Keys:

Answer 6

Multiple fields combined to form a composite primary key

Question 7

Foreign Keys:

Answer 7

When an attribute appears in multiple tables and it is the primary key in one table, it is a foreign key in the other.

Question 8

Normalisation:

Answer 8

To reduce redundancies (duplicated data), ensure data is stored logically, reduce the amount of storage space used.

Question 9

First Normal Form:

Answer 9

Data must be atomic (a cell only has one value), each record is unique

Question 10

Second Normal Form:

Answer 10

Must already be in 1NF, must only be a single column primary key (no composite primary key)

Question 11

Third Normal Form:

Answer 11

Must already be in 2NF, no transitive functional dependencies

Question 12

Atomic Data:

Answer 12

Each cell only holds one value.

Question 13

Single Column Primary Key:

Answer 13

No composite primary key.

Question 14

Functional Dependencies:

Answer 14

An attribute uniquely determines another attribute.

Question 15

Transitive Functional Dependencies:

Answer 15

Functional dependencies which don’t rely on the primary key.

Question 16

What does SQL stand for:

Answer 16

Structured Query Language

Question 17

What does DDL stand for:

Answer 17

Data Definition Language

Question 18

DDL uses:

Answer 18

Create and delete tables and databases

Question 19

SQL uses:

Answer 19

Select, insert, update, and delete

Question 20

Example of SELECT:

Answer 20

SELECT *
FROM table
WHERE Attribute = ‘example’
ORDER BY Attribute 2 Asc;

Question 21

Example of INSERT:

Answer 21

INSERT INTO Table (Attribute1, Attribute2)

VALUES (‘Example’, 123);

Question 22

Example of UPDATE:

Answer 22

UPDATE table
SET Attribute = ‘example’
WHERE Attribute2 = ‘example2’;

Question 23

Example of DELETE:1

Answer 23

DELETE FROM table

WHERE Attribute = ‘example’;

Question 24

Example of DDL create database:

Answer 24

CREATE TABLE TableName(
Column1 datatype
Column2 datatype
Column3 datatype
);

Question 25

SQL datatypes:

Answer 25

TEXT, INTEGER, NUMERIC, REAL, BLOB

Question 26

Example of DDL drop:

Answer 26

DROP TABLE TableName;

Question 27

Constraints:

Answer 27

Rules for data e.g. unique, or not null.

Question 28

Client Server Database:

Answer 28

Allows for simultaneous access to a database

Question 29

Client Server Database Advantages:

Answer 29

Improved data sharing
Minimised data inconsistency
Improved data security

Question 30

Client Server Database Disadvantages:

Answer 30

Concurrent Access

Question 31

Concurrent Access:

Answer 31

Two people accessing/editing the same data at the same time

Question 32

Solving concurrent access:

Answer 32

Record Locks
Timestamp/ordering
Commitment
Serialisation = ordering or commitment

Question 33

Record Locks:

Answer 33

Locks a record when someone is using it

Question 34

Serialisation:

Answer 34

Each query is scheduled to happen one after the previous query.

Question 35

Timestamp ordering:

Answer 35

Each query is assigned a timestamp, so if two querys are on the same record, the earliest happens first.

Question 36

Commitment Ordering:

Answer 36

If a record is dependant on another, then this query will happen first.