Databases

Question 1

Database

Answer 1

An organised collection of related data

Question 2

Flat file database

Answer 2

A database where all the data is stored in a single table.
Can be sufficient if there is a small amount of data.
Useful when data is mostly static (doesn’t change frequently)

Question 3

Relational database

Answer 3

Where The data is held in more than one table and they are linked together using relationships.

Question 4

Field

Answer 4

Column
Provide category headings for each item

Question 5

Record

Answer 5

Row
A collection of data for a set of fields.

Question 6

Primary key

Answer 6

A field that uniquely identifies each record in the table.
A table can have only one primary key. Must be unique and can’t be null.

Question 7

Foreign key

Answer 7

A field in a table that appears as the primary key in another table. Used to join tables.

Question 8

Data consistency

Answer 8

When a database transaction can only change data in acceptable ways - must follow pre-defined rules and constraints.
If a record is updated by a user, any user accessing this record should also receive the update.
Ensures uniformity and accuracy

Question 9

Data redundancy

Answer 9

When there is duplication of data or unnecessary data is kept in the database.
Can happen when the same data is repeated in two or more fields, or the same field is repeated in two or more tables.
If a change is made in one place, not all the instances of the data will have the same value.

Question 10

Data independence

Answer 10

When the database is designed in a way that reflects the natural relationships between entities and is not structured in a way that suits a specific application.
When changes are made they will not affect the existing database applications.

Question 11

Data integrity

Answer 11

Maintaining and ensuring the reliability of the data in terms of its accuracy, completeness and consistency over its lifecycle.
Everybtable must have a primary key.
The primary key for each record must be unique and not null.

Question 12

Domain integrity

Answer 12

Every attribute in a relational database is associated with a domain - the set of allowed values the attribute can contain.

Question 13

Referential integrity

Answer 13

If a record is added to a table and a valuenis entered into a foreign key field, the value must exist in the primary key field of another table.
In some cases, the foreign key can be null if there is no relationship.

Question 14

First normal form

Answer 14

Each record has a primary key
Data is atomic
Records have no repeating groups of attributes

Question 15

Second normal form

Answer 15

No partial dependencies

Question 16

Third normal form

Answer 16

No non-key dependencies.
Data does not depend on fields that are not the primary key.

Question 17

Normalisation

Answer 17

Technique to help reduce dat duplication, resulting in an improvement in data integrity.

Question 18

SQL to delete a database

Answer 18

DROP DATABASE DatabaseName;

Question 19

SQL to delete a table

Answer 19

DROP TABLE tableName;

Question 20

SQL select statement

Answer 20

SELECT field1, field2 (or * for all)
FROM tableName;
WHERE criteria

Question 21

SQL insert code

Answer 21

INSERT INTO tableName (field1, field2, …)
VALUES (‘value1’, ‘value2’, …)

Question 22

SQL delete code

Answer 22

DELETE FROM tableName
WHERE criteria;

Question 23

Serialisation

Answer 23

When two or more transactions are executed concurrently, the effect should be the same as if they had been executed serially.
They cannot interfere with each other.

Question 24

Record locking

Answer 24

A method to allow concurrent transactions. The DBMS (Database Management System) will lock the affected record until the update is complete.
Might cause a slight delay to others.
Under some circumstances deadlock can occur.

Question 25

Timestamp ordering

Answer 25

Each record has two timestamps: last read and last updated

Question 26

ACID

Answer 26

Atomicity: the components of a transaction are indivisible - the whole transaction must succeed or fail.

Consistency: ensures that an illegal transaction is rejected so that the integrity of the transaction is upheld.

Isolation: ensures that each transaction will be isolated and dealt with in a way that does not affect others.

Durability: ensures that data is saved once a transaction is complete. Even if there is a hardware failure immediately after a transaction, the data will be safe.