1.3.2 Databases

Question 1

Whats a database?

Answer 1

An organised collection of related data

Question 2

What’s a flat file database?

Answer 2

A database in 0NF, where all data is stored in a single table, about a single entity. It’s useful for static (non-changing) data

Question 3

What’s an entity

Answer 3

A category about which data needs to be recorded e.g. a person, or thing. (A table in the conceptional phase)

Question 4

What’s a table

Answer 4

A Two-dimensional representation of data that is stored in rows and columns

Question 5

What’s a field

Answer 5

A single piece of data about an entity

Question 6

What’s a record?

Answer 6

A group of fields about a specific entity (AKA a row)

Question 7

What’s a relational database?

Answer 7

Where data is held in multiple tables that are linked via relationships.

Question 8

What’s a primary key?

Answer 8

A field used to uniquely identify each record in the table

Question 9

What’s a feature of a primary key field?

Answer 9

It’s automatically indexed

Question 10

What’s a composite primary key

Answer 10

When multiple fields are used to uniquely identify each record in the table

Question 11

What are the three types of relationships between entities?

Answer 11

One-to-one,
One-to-many,
Many-to-many

Question 12

What’s a foreign key?

Answer 12

A field that creates a relationship between two tables, appearing as the primary key in another table.

Question 13

What are the benefits of foreign keys?

Answer 13

They maintain the relationship between tables

Question 14

Why do we use junction tables?

Answer 14

To eliminate many-to-many relationships

Question 15

What are junction tables?

Answer 15

A junction table contains the primary key columns of the two or more tables you want to relate.

Question 16

What is data integrity

Answer 16

Maintaining and ensuring the reliability of data

Question 17

What needs to be considered when reinforcing data integrity in a database

Answer 17

Accuracy, completeness, consistency over lifecycle

Question 18

What is Entity integrity

Answer 18

Stipulating that every table must have a primary key

Question 19

What is domain integrity

Answer 19

Ensuring every attribute in a relational database is associated with a domain

Question 20

What’s a domain

Answer 20

The set of allowed values that an attribute can contain, e.g. numerical

Question 21

What is referential integrity

Answer 21

It’s a data quality concept that prevents data redundancy and inaccuracies

It a rule that foreign keys in one table can only refer to primary keys in
another table.

Question 22

What’s data validation?

Answer 22

Ensuring that data entered into a program is as accurate as possible

Question 23

What’s data verification?

Answer 23

A method of recognizing and preventing human errors, by ensuring info is entered correctly

Question 24

What are the two types of data verification?

Answer 24

Visual check and Double entry

Question 25

What’s Visual Check data verification?

Answer 25

When a person checks that the data being entered from one document to another is the same

Question 26

What’s Double Entry data verification?

Answer 26

Requires same info to be input twice into 2 separate fields

The two values are then compared to check they’re the same

Question 27

What’s data accuracy?

Answer 27

A term used to describe the validity of the process used to ensure the data stored is trustworthy and the validity of the data itself.

Question 28

What’s normalization?

Answer 28

The concept of splitting tables in a database and arranging the data to move it from 1NF - 3NF

Question 29

At what point is a database considered normalized

Answer 29

When it meets third normal form

Question 30

What’s 0NF

Answer 30

A flat file prior to normalization

Question 31

Define 1NF

Answer 31

When…

Each record has a primary key

Data is of a single value type (atomic)

There are no repeating groups of attributes

Values in fields are from the same domain

Question 32

Define 2NF

Answer 32

No partial dependencies- each field depends on primary key

Data in 1NF already

Question 33

Define 3NF

Answer 33

Data already in 2NF

No non-key (transitive) dependencies

All fields depend on the key, the whole key and nothing but the key

Question 34

What are the steps in eliminating many-to-many relationships?

Answer 34

Create a junction table

Assign primary keys to initial tables

Create a composite key for junction table (and provide foreign keys)

Initial tables will both have one to many relationships with junction table

Question 35

What does SQL stand for?

Answer 35

Structured Query Language

Question 36

What are the different SQL commands and their uses?

Answer 36

SELECT- List of fields to be displayed

FROM- List the table(s) data will come from

WHERE- List of the search criteria (operators used here)

ORDER BY- List the fields that data is to be sorted in (e.g. price ASC)

• / (Wild Card) = ‘All’

LIKE- Used to search for a pattern

BETWEEN- Used to search in an inclusive range

IN- Used to search in a specific column/ field

Question 37

Why is a join clause used in SQL?

Answer 37

To combine data from two or more tables into one data set, by identifying the primary-to-foreign key relationship.