Relational Databases

Question 1

What is a database?

Answer 1

A collection of organised data that allows access, retrieval, and use of that data.

Question 2

What is a Relational database?

Answer 2

a collection of related/ linked tables

E.g. all of the student tables for NMHS and subject details tables and specific class details tables.

Question 3

How is data stored in a DBMS?

Answer 3

Data is grouped into tables (normalisation) and stored in records and fields.

Question 4

DBMS role

Answer 4

Process data to produce meaningful information for a user by applying, sorting, and/or grouping.

Store data in a way that reduces data redundancy.

Question 5

Normalisation principles

Answer 5

The need to minimise the duplication of data.

Question 6

Primary key

Answer 6

Consists of one or more fields that uniquely identifies each record in a table. The data in a primary key is unique to a specific record.

Non-key fields are dependent on the key, and not dependent on any other field.

Factless identifiers are ideal for use as a primary key because they do not change E.g. AutoNumber

Name Examples: Employee ID, Student ID

Question 7

Foreign key

Answer 7

Tables are linked by joining the primary key from the table on the one-side to the appropriate field, which is called the foreign key, on the many-side.

Question 8

Composite key

Answer 8

A composite key can be applied in the transaction table to prevent a double transaction.

When a primary key consists of more than one field.

Question 9

Field

Answer 9

a set of related characters

a single item type of information that appears in every record.

If you want to sort, search, calculate, or report based on an item of information, you should put that item in its own field.

E.g. on a student id card- ‘surname’ is a field

Question 10

Record

Answer 10

a collection of related fields

a meaningful and consistent way to combine information about something

E.g. everything on a student ID card makes up one record

Question 11

Tables

Answer 11

a collection of related records.

Lists of rows and columns

E.g. all students enrolled on the SC of NMHS

Question 12

What are the 3 links between table called?

Answer 12

• One-to-one
• One-to-many
• Many-to-many.

Question 13

One - to - many

Answer 13

a row in table A can have many matching rows in table B. But a row in table B can have only one matching row in table A.

Only occurs if one of the related columns is a primary key or has a unique constraint

e.g. Each ‘Publisher’ produces many titles. But each ‘Title’ comes from only one ‘Publisher’.

Question 14

Many - to - many

Answer 14

a row in table A can have many matching rows in table B, and vice versa.

E.g. a single ‘Order’ can include more than one ‘Product’, and a single ‘Product’ can appear on many ‘Orders’.

Question 15

Solving many to many relationships

Answer 15

The solving of a many-to-many relationship can be achieved by adding a transaction/junction table.

Question 16

One - to - one

Answer 16

a row in table A can have no more than one matching row in table B.

Only occurs if both of the related columns are primary keys or have unique constraints.

Question 17

How is data defined?

Answer 17

Data is defined according to a data dictionary that includes field names, types of data, sizes, key(s), required values, and the appropriate validation rules and text messages, default values, formats, and input masks.

These ensure integrity within a table.

Question 18

Master query

Answer 18

A master query uses fields from all tables and run the query.

Question 19

When should I use a database instead of a spreadsheet?

Answer 19

Databases are ideal when you have a large amount of related data to store and quickly need to be able to add to, update, query, and create reports about the data. This is fast and efficient and ease of backing up so data is not lost.

Use a spreadsheet instead of a database when you have less data to store but frequently need to perform calculations with the data, create charts, and review statistics.

Question 20

What is file maintenance?

Answer 20

Procedures that keep data current.

This includes:

• Adding records when new data is obtained
• Modifying records to correct inaccurate data or to update old data with new data
• Deleting records when they no longer are needed

Question 21

Validation rules

Answer 21

Process of comparing data with a set of rules or values to find out if the data is correct.

Set parameters (or boundaries) for the entry of data.

E.g. In a library system, return data must be greater than today’s date and less than a date in two weeks time.
If a date outside of the boundaries is entered, an error message will pop up for the user.

Question 22

What is a validity check and name different types?

Answer 22

Many programs perform a validity check that analyses data, either as you enter it or after you enter it, to help ensure that it is correct.

Types include:

• alphabetic check
• numeric check
• range check
• consistency check
• completeness check
• digit check

Question 23

Data integrity

Answer 23

Identifies the quality of the data, the more errors the data contains, the lower its integrity.

a change made in one table will automatically and accurately be seen/read by all other related tables.

Question 24

Sharing data

Answer 24

Information in one department can readily be shared with others.

Question 25

Data redundancy

Answer 25

This is when you store information more than once.

This is minimised with the use of relational DB.

Question 26

What are the qualities of valuable information?

Answer 26

Information should be:

• accurate: error free
• verifiable: can be proven to be correct or incorrect
• timely: has an age suited to its use
• organised: arranged to suit the needs and requirements of the decision makers
• accessible: available when the decision maker needs it
• useful: has meaning to the person who receives it
• cost effective: should give more value than it costs to produce

Question 27

What is data?

Answer 27

Data: is the raw material waiting to be processed.

Once processed, it becomes information.

E.g. text, numbers, images, audio, and video.

Question 28

What is information?

Answer 28

Processed and manipulated data according to instructions, that is organised, meaningful, and useful.

Question 29

How does a computer use data and information?

Answer 29

A computer processes data in a database into information.

Question 30

Default Values

Answer 30

E.g. The library system can also be set so that a default value of the date in 2 weeks time can be automatically set.

Question 31

Input Masks

Answer 31

To ensure a consistent format of data entry.

E.g. –/–/–