1.3.2 Databases pt2

Question 1

Normalisation

Answer 1

A process to come up with the best possible design for a database, organising tables so data is not duplicated in the same table or different tables.

Question 2

First Normal Form

Answer 2

1. All field names must be unique
2. Values in fields should be from the same domain (only contain one piece/type of information)
3. Values in fields should be atomic (only one piece of information in each field)
4. No two records can be identical
5. Each table needs a primary key

Question 3

Second Normal Form

Answer 3

1. It should already be in 1NF
2. Any partial dependencies have been removed (every field is dependent on the primary key)
3. Any many-to-many relationships should have been removed

Question 4

Removing partial dependencies

Answer 4

Creating a second table for fields which can be inferred from another which isn’t the primary key. Create a linking table with each table’s primary key

Question 5

Third Normal Form

Answer 5

1. The data is already in 2NF

2. Any transitive dependencies have been removed

Question 6

Removing transitive dependencies

Answer 6

Create extra tables where the value of a field is determined by another which isn’t the primary key.

Question 7

Normalisation advantages

Answer 7

Easier to maintain and manage
No unnecessary duplication of data
Data integrity is maintained
Faster searches and savings in storage (smaller table)

Question 8

EDI

Answer 8

Electronic Data Interchange - exchanges data between documents between computer systems, all documents must be in a standard format (can use EDI translation systems)

Question 9

Transaction

Answer 9

A single logical operation in a database, may consist of several operations, all of which must be processed.

Question 10

ACID

Answer 10

Atomicity, Consistency, Isolation, Durability - ensures the integrity of a database

Question 11

Atomicity

Answer 11

Requires that a transaction will be processed in its entirety or not at all

Question 12

Consistency

Answer 12

Ensures that no transaction can violate any of the defined validation rules

Question 13

Isolation

Answer 13

Ensures that each transaction will be isolated and dealt with in a way that does not affect others

Question 14

Durability

Answer 14

Once a transaction has been committed, it will remain so, even after a power cut. The data is held in a buffer

Question 15

Record locking

Answer 15

Prevents simultaneous access to objects in a database in order to prevent updates being lost or any inconsistencies.

Question 16

Deadlock

Answer 16

Where two users are trying to access the same record, serialism techniques are used to prevent it

Question 17

Timestamp Ordering

Answer 17

If the read timestamp of an object is different to when you opened it, another user must have accessed it so the transaction is cancelled

Question 18

Commitment Ordering

Answer 18

Orders transactions in terms of their dependencies on one another as well as the time of opening

Question 19

Redundancy

Answer 19

Stops systems going down with duplicate hardware in another location, now run on a backup

Question 20

Data integrity

Answer 20

Changes are consistent across a database so removing a record removed all references to that record

Question 21

Indexing

Answer 21

A method to store the position of each record ordered by an attribute. The private key is automatically indexed but rarely queried