1.3.2 Databases

Question 1

Entity

Answer 1

Item of interest about which information is stored

Question 2

Relational Database

Answer 2

Contains different tables for each entity

Question 3

What is a Flat File?

Answer 3

Database that consists of a single file

Question 4

What are flat files most likely based around?

Answer 4

A single entity and its attributes

Question 5

How are flat files written out? Describe each part.

Answer 5

__Entity1__(Attribute1, Attribute2, Attribute3…)

Question 6

What is a primary key?

Answer 6

A unique identifier for each record in a table.

Question 7

How do we show the primary key?

Answer 7

By underlining it

Question 8

Foreign Key

Answer 8

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 the other.

Question 9

How do we show the foreign key?

Answer 9

By using an asterisk

Question 10

Secondary key

Answer 10

Allows a database to be searched quickly

Question 11

ER Modelling

Answer 11

A visual way of describing data tables and the relationships between them.

They can be used to reduce redundancy and construct a relational database.

Question 12

What are the three kinds of relationships in ER modelling?

Answer 12

One-to-one (eg Husband —- Wife)
One-to-many (eg Mother —< Children)
Many-to-many (eg Students >–< Courses)

Question 13

Normalisation

Answer 13

The process of optimally designing data tables by reducing data redundancy and repetition by converting them into normal forms.

Question 14

Benefits of normalisation [4]

Answer 14

• No redundancy
• Consistent data throughout linked tables
• Records can be added and removed without issues
• Complex queries can be carried out

Question 15

What are the three types of normalisation?

Answer 15

First Normal Form
Second Normal Form
Third Normal Form

Question 16

First Normal Form

Answer 16

• Each field contains a single value
• Each field contains the same data type
• No duplicate records
• All field names are unique
• Table must have a primary key

Question 17

Second Normal Form

Answer 17

A table in 1NF that has no partial dependencies (data that repeats across multiple records removed and put into a new table with appropriate relationships)

Question 18

Third Normal Form

Answer 18

A table in 2NF where all attributes that are not the primary key are fully dependent on the primary key (no non-key dependencies).

Question 19

What is Indexing? What is it used to do?

Answer 19

• 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.

Question 20

Which key is automatically indexed?

Answer 20

Primary key

Question 21

Do we typically use the primary key for queries? Why or why not?

Answer 21

No. The primary key is normally not remembered. Secondary keys are used. They are indexed to make the table easier and faster to search through on those particular attributes.

Question 22

A bank needs to scan cheques. What data capturing method can they use?

Answer 22

Magnetic Ink Character Recognition. All the details on the cheque excluding the amount are printed in a special magnetic ink that can be recognised by the computer. The amount should be entered manually.

Question 23

A school wants to automatically mark multiple choice questions. What data capturing method can they use?

Answer 23

Optical Mark Recognition

Question 24

What are the four ways of capturing data to be entered into a database?

Answer 24

Manual.
Optical Mark Recognition
Optical Character Recognition
Magnetic Ink Character Recognition

Question 25

What is a common way of exchanging data?

Answer 25

Electronic Data Interchange

Question 26

What is good about Electronic Data Interchange?

Answer 26

It doesnt require human interaction, and enables data transfer from one computer to another.

Question 27

What is Structured Query Language used for?

Answer 27

Manipulating databases.

Question 28

What does the following SQL do?

SELECT MovieTitle, DatePublished
FROM Movie
WHERE DatePublished BETWEEN #01/01/2000# AND #31/12/2005#
ORDER BY DatePublished;

Answer 28

Two attributes are chosen: MovieTitle and DatePublished from the table known as Movie.

It will only select records where the value of DatePublished is between 01/01/2000 and 31/12/2005.

Lastly, it will order these records in ascending order. It won’t be descending order because ‘Desc’ is not present at the end of ORDER BY DatePublished.

Question 29

SQL Syntax: ORDER BY

Answer 29

Specifies whetehr you want it in ascending or descending order. By default, values are in ascending order. Adding Desc to the end causes them to be descending.

ORDER BY DatePublished Desc

Question 30

SQL Syntax: JOIN

Answer 30

It allows you to combine rows from multiple tables based on a common field between them.

Question 31

What does this SQL code do?

SELECT Movie.MovieTitle, Director.DirectorName, Movie.MovieCompany
FROM Movie
JOIN Director
ON Movie.DirectorName = Director.DirectorName

Answer 31

It selects the MovieTitle attribute belonging to the movie table, the director name attribute belonging to the director table, and the moviecompany attribute belonging to the movie table as well.

The two tables movie and director are joined.

The On keyword at the end describes the common field between the two tables.

Question 32

SQL Syntax: CREATE

Answer 32

Allows you to make new databases. The following details about each attribute must be specified: Data type, whether it must be filled in (not null), and if it is the primary key.

Question 33

What does this SQL code do?

CREATE TABLE TableName
(
Attribute1 INTEGER NOT NULL, PRIMARY KEY,
Attribute2 VARCHAR(20) NOT NULL,
)

Answer 33

Creates a new table called TableName, with two attributes (Attribute1, Attribute2).
Attribute1 is an integer. It is not null, meaning it must be filled in. It is a primary key.
Attribute 2 is a string of length 20. It is not null, meaning it must be filled in.