Databases

Question 1

what is a database

Answer 1

An organised collection of data enabling efficient:
- Adding
-Modification
-Deleting
-Searching of data

Question 2

Primary key

Answer 2

Field that holds unique value for each record in a database.

Question 3

Foreign key

Answer 3

A field that links to tables together.

Question 4

Secondary key

Answer 4

A unique identifier which is indexed to allow fast searching

Question 5

Composite primary key

Answer 5

2 or more attributes uniquely identifying a record.

Question 6

Flat file database

Answer 6

Contains one table. Easy to set up and maintain, but can be inefficient and have repeated data

Question 7

Referential integrity

Answer 7

Ensures all foreign keys represent a valid and existing primary key in parent table

Question 8

SQL to extract field

Answer 8

SELECT …
FROM …
WHERE…
ORDER BY….
ASC/DESC

WILDCARD (*) = ALL FIELDS

Question 9

SQL to combine rows from different tables

Answer 9

JOIN table2
ON table1.attribute = table2.attribute

Question 10

SQL to create a new database

Answer 10

CREATE TABLE table1
(
Attribute 1 [Datatype,
Null/not null, Primary key]
…
)

Question 11

Data types for creating tables

Answer 11

CHAR(n)
VARCHAR(n)
BOOLEAN
INTEGER
FLOAT
DATE
TIME
CURRENCY

Question 12

Adding a column

Answer 12

ALTER TABLE table1
ADD attribute and data types

Question 13

Deleting a column

Answer 13

ALTER TABLE table1
DROP Attribute

Question 14

Modifying a column

Answer 14

ALTER TABLE table1
MODIFY COLUMN Attribute NEW DATATYPE

Question 15

Insert a new record

Answer 15

INSERT INTO COLUMN 1, COLUMN 2,…
VALUES(Value 1, Value2…)

Question 16

Updating a record

Answer 16

UPDATE table1
SET column1 = value1,….
WHERE columnX = value

Question 17

Deleting a record

Answer 17

DELETE FROM table1
WHERE columnX = value

Question 18

Normalisation

Answer 18

Coming up with the best possible layout for a relational database.

Question 19

Aims of Normalisation

Answer 19

No redundancy
Consistent data
Records added/removed without issue
Complex queries can be carried out

Question 20

1st Normal Form

Answer 20

No attributes that contain more than 1 value. Fixed by making more records but causes redundancy

Question 21

2nd Normal Form

Answer 21

Must be in 1st Normal Form
Must be no partial dependencies (no attributes must depend on part of a composite key.)
Fix partial dependency by creating another table with field which has the partial dependency and field it is dependent on.

Question 22

3rd Normal Form

Answer 22

Must be in 2nd Normal Form
Contains no non key dependencies (All attributes must depend on only the primary key)
Fixed by splitting table until each attribute in a table is only dependent on primary key

Question 23

Transaction

Answer 23

A single logical operation performed on data (Create, Read, Update, Delete)

Question 24

ACID

Answer 24

A set of rules that ensure database validation rules are met.
Atomicity, Durability, Isolation, Consistency

Question 25

Atomicity

Answer 25

A transaction must be completed entirely or not at all.

Question 26

Consistency

Answer 26

A transaction must maintain referential integrity rules

Question 27

Isolation

Answer 27

Simultaneous execution of transactions should produce same result as if done one after another

Question 28

Durability

Answer 28

Once a transaction has been executed it will remain so regardless of circumstances like power cuts.

Question 29

Record Locking and issue

Answer 29

Is a method to counter loss of updates due to simultaneous access to records.
A record is locked when a user is editing it until the changes are saved.

Issue: If two users need to update the same two records simultaneously, they will end up in a situation where they will wait for each other to stop updating a record, which means records dont become available and users wait endlessly. DEADLOCK.

Question 30

Serialisation

Answer 30

Ensures transactions do not overlap in time so they cannot interfere with each other. One transaction cannot start unless the previous is complete

Question 31

Timestamp ordering

Answer 31

Transactions are given timestamps whenever it starts. If multiple transactions affect the same object the transaction with earliest timestamp is applied first.
Each object has a read/write timestamp to ensure transactions are not lost.

Question 32

Commitment ordering

Answer 32

Transactions ordered in terms of their dependencies on each other as well as time of initiation.

Question 33

Types of data capture

Answer 33

Magnetic Ink Character Recognition [used in banks scanning cheques]
Optical Mark Recognition [ used in MCQ’s for a test]
Optical character Recognition [used in forms]