1.3.2 - Databases

Question 1

What is an entity?

Answer 1

An entity is an item of interest about which information is stored

Question 2

What is a relational database?

Answer 2

A relational database is a database which recognises the differences between entities by creating different tables for each entity.

Question 3

What is a flat file?

Answer 3

A flat file is a database that consists of a single file. The flat file will most likely be based around a single entity and its attributes.

Question 4

What is a primary key?

Answer 4

A primary key is a unique identifier for each record in the table.

Question 5

What is a foreign key?

Answer 5

A foreign key is an attribute which links two tables together. The foreign key will exist in
one table as the primary key and act as the foreign key in another.

Question 6

What is a secondary key?

Answer 6

A secondary key allows a database to be searched quickly. It can be allocated to a value which is likely to be specific to a user, e.g surname.

Question 7

What is one to one entity relationship modelling?

Answer 7

Each entity can only be linked to one other entity, such as the relationship between a husband and wife.

Question 8

What is one to many entity relationship modelling?

Answer 8

One table can be associated with many other tables, such as a mother having multiple children

Question 9

What is many to many entity relationship modelling?

Answer 9

One entity can be associated with many other entities and the same applies the other way round. An example is students and courses.

Question 10

What is normalisation?

Answer 10

Normalisation is the process of coming up with the best possible layout for a relational database

Question 11

What does normalisation try to accomplish?

Answer 11

No redundancy (unnecessary duplicates).
Consistent data throughout linked tables.
Records can be added and removed without issues.
Complex queries can be carried out.

Question 12

What is the first normal form?

Answer 12

There must be no attribute that contains more than a single value.

Question 13

What is the second normal form?

Answer 13

A database which doesn’t have any partial dependencies and is in first normal form can be
said to be in second normal form.
This means that no attributes can depend on part of a composite key.

Question 14

What is the third normal form?

Answer 14

If the database is in second normal form and contains no non-key dependencies, it is in third normal form.
A non-key dependency means the attribute only depends on the value of the primary key and nothing else.

Question 15

What is indexing?

Answer 15

Indexing is a method used to store the position of each record ordered by a certain attribute. This is used to look up and access data quickly. Secondary keys are normally indexed, because the primary key is not normally remembered.

Question 16

How can data be captured for databases?

Answer 16

Manually
Magnetic Ink Character Recognition (cheques)
Optical Mark Recognition
Optical Character Recognition

Question 17

How can data be transferred?

Answer 17

Using Electronic Data Interchange, which doesn’t require human interaction and enables data transfer between computers.

Question 18

What is SQL?

Answer 18

SQL is Structured Query Language, a declarative language used to manipulate databases.

Question 19

Basic SQL commands

Answer 19

SELECT
FROM
WHERE

Question 20

Ordering in SQL

Answer 20

SELECT
FROM
ORDER BY value Asc/Desc

Question 21

Joining in SQL

Answer 21

This provides a method of combining rows from multiple tables based on a common field.
SELECT
FROM
JOIN
ON value = value

Question 22

Creating in SQL

Answer 22

CREATE TABLE TableName
(
Attribute1 description, ,
)
The details should be specified:
whether it is the primary key.
Its data type.
Whether it must be filled in (not null)

Question 23

Data types in SQL

Answer 23

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

Question 24

Altering in SQL

Answer 24

Adding a column:
ALTER TABLE TableName
ADD AttributeX and their dataTypes

Deleting a column:
ALTER TABLE TableName
DROP COLUMN AttributeX

Modifying the data type of a column:
ALTER TABLE TableName
MODIFY COLUMN AttributeX NewDataType

Question 25

Inserting in SQL

Answer 25

INSERT INTO (column1, column2, …)
VALUES (value1, value2, …)

Question 26

Updating in SQL

Answer 26

UPDATE TableName
SET column1 = value1, column2 = value2 …
Where columnX = value

Question 27

Deleting records in SQL

Answer 27

DELETE FROM TableName
WHERE columnX = value

Question 28

What is referential integrity?

Answer 28

This is the process of ensuring consistency. It ensures info is not removed if it is required elsewhere in a linked database.

Question 29

What is a transaction?

Answer 29

A single operation executed on data

Question 30

What is atomicity?

Answer 30

A transaction must be processed in its entirety or not at all

Question 31

What is consistency?

Answer 31

A transaction must maintain the referential integrity rules between linked tables

Question 32

What is isolation?

Answer 32

Simultaneous executions of transactions should lead to the same result as if they were executed one after the other

Question 33

What is durability?

Answer 33

Once a transaction has been executed it will remain so regardless of the circumstances surrounding it, such as in the event of a power cut.

Question 34

What is record locking?

Answer 34

The process of preventing simultaneous access to records in a database is called record
locking and it is used in order to prevent inconsistencies or a loss of updates.
While one person is editing a record, this ‘locks’ the record so prevents others from accessing the
same record.

Question 35

What is the danger with record locking?

Answer 35

It can lead to a deadlock, where people are waiting to access a locked record indefinitely.

Question 36

What is redundancy?

Answer 36

Redundancy is the process of having one
or more copies of the data in physically different locations.
This means that if there is any damage to one copy the others will remain unaffected and can be recovered.