1.3.2 Databases

Question 1

Define Entity

Answer 1

An entity is an item of interest about which information is stored.
e.g. doctor/patient

Question 2

Define 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

Define primary key

Answer 3

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

Question 4

Define foreign key

Answer 4

• A foreign key is the 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 5

Define secondary key

Answer 5

• A key other than primary key that used to identify a record.
• Might be same for some records
• Allows a database to be searched quickly

Question 6

Define normalisation

Answer 6

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

Question 7

Why the fields ‘‘Artist” “Title” “Length” is not suitable to be a primary key (2)

Answer 7

• A primary key must have a unique value for every record
• The value for all these fields coule repeated

Question 8

Define indexing

Answer 8

• A method used to store the position of each record ordered by a certain attribute
• The primary key is automatically index
• Secondary keys are index to used for searching

If secondary key not index, the searching progress will be :
Row 1: Check if AuthorID = 102 (No, move to the next row)

Row 2: Check if AuthorID = 102 (Yes, include in the result)

Row 3: Check if AuthorID = 102 (No, move to the next row)

If it is index :
Row 1: Not checked individually, as the index directly located the relevant row.

Row 2: Retrieved based on the index entry.

Question 9

Give advantage and disadvantage of indexing the field Artist

Answer 9

• Advantage: Searches of Artist can be performed more quickly
• Disadvantage: The index takes upextra space in the database.

Question 10

5 rules for first normal form

Answer 10

1. All field names must be unique
2. Values in fields should be from the same domain - only integers can be stored in ‘age’
3. Values in fields should be atomic - single value in each grid
4. No two records can be identical
5. Each table needs a primary key

Question 11

2 rules to get second normal form

Answer 11

1. The data is already in 1NF
2. Any partial dependencies have been removed

Question 12

2 rules to get third normal form

Answer 12

1. The data is already in 2NF
2. Any transitive dependencies have been removed

Otherwise it is

No any field depends on any field other than primary key

Otherwise it is susceptible to becoming inconsistent

Question 13

How does a file written out

Answer 13

Entity (primaryKey, Attribute2, Attribute3)
primaryKey should be underlined

e.g. Car(CarID, Age. Price)

Question 14

Define capturing data

Answer 14

Input data into database

Question 15

Methods of capturing data

Answer 15

1. Optical Mark Recognition - used for multiple choice on a test
2. Optical Character Recognition - extract the text in a picture by interpreting the shape of letters - car number plate recognition
3. Parper-based forms - manual method
4. Magnetic stripes - bank card
5. Barcodes
6. Sensors

Question 16

What is SQL

Answer 16

• Structured Query Language
• Declarative language used to manipulate database

Question 17

What is the format for SQL selection

Answer 17

SELECT field1,field2
FROM entity
WHERE field3 = ‘any value valid’

Question 18

ordered by SQL

Answer 18

ORDERED BY field (ascending)
ORDERED BY field Desc (descnding)