N5 Database Design & Development Revision

National 5 Computing Science

Database Design & Development Revision

Analysis

At the analysis stage of developing a database, you must identify end-user and functional requirements

End-user requirements describe tasks that a user of the database would want to carry out

Functional requirements describe what must be created or coded in the database

Multiple Choice

Is the following is an end-user requirement or a functional requirement?
The database must be able to produce a list of all S4 pupils in
alphabetical order of surname

• end-user

• functional

Multiple Choice

Is the following is an end-user requirement or a functional requirement?
Different tables are required to store details of all subjects, sets, classes and pupils

• end-user

• functional

Multiple Choice

Is the following is an end-user requirement or a functional requirement?
When a new pupil joins the school, all of their details and subjects must
be added to the timetable

• end-user

• functional

Design

Two tools/tasks from the design stage are:

Data Dictionary - to describe all the attributes to be created in each entity. It is a plan for the database.

Entity-Relationship Diagram - to show the connection and
relationship between two entities joined with a primary and foreign key.

Multiple Choice

Which of the following is not attribute type?

• Time

• Date

• Integer

• Text

Validation checks

There are 4 types of validation check, used to make sure data entered in a database field is sensible and allowable

Presence check
Length check
Range check
Restricted choice

Database Entities/Tables

Data in a database file is organised into entities/tables to store related information

An entity/table is made up of multiple attributes/fields to store a single item of information

You can add records to the table storing information about one person or thing

Each table must have a primary key, a unique identifier for each record in a table

A database may have more than one table and the tables can be linked together using a foreign key

Data Dictionaries

You identify the attributes to be stored for each entity in a data dictionary

Indicate primary or foreign keys using the notation PK or FK
Always indicate Yes or No in the required column
Show the criteria of the validation rules

E-R Diagrams

Show entities as a rectangle and attributes as ovals, underline PK, star (*) foreign key,
show one to many relationship, add a word to describe the relationship

Hotspot

Identify the foreign key

Hotspot

Identify the primary keys

Dropdown

This database has a ​

Select answer

relationship between Doctor and Patient

Dropdown

This database has a ​

Select answer

relationship between the Department and Staff tables.

Labelling

What is the primary key in the Pupil table?

Drag labels to their correct position on the image

schoolID

forename

surname

pupilID

Relational databases

What is the primary key in the School table?

Labelling

What is the primary key in the School table?

Drag labels to their correct position on the image

schoolName

schoolID

Multiple Choice

Which is the foreign key?

• Pupil.pupilID

• Pupil.schoolID

• School.schoolID

Multiple Choice

What is the relationship between Pupil and School?

• 1:1

• 1:M

• M:M

• M:1

Fill in the Blanks

Fill in the Blanks

Fill in the Blanks

Multiple Choice

What would be the effect of this query?

INSERT INTO Pupil (pupilID, forename, surname, schoolID)
VALUES ('S0006', 'Emma', 'Jones', 'P0018');

• Insert Record

• Amend Record

• Delete Record

Multiple Choice

What would be the effect of this query?

UPDATE Pupil SET schoolID = 'P0018'
WHERE schoolID = 'P0011';

• No records changed

• 1 record changed

• 4 records changed

• All records changed

Multiple Choice

What would be the effect of this query?

DELETE FROM School
WHERE schoolID = 'P0011';

• 1 record deleted

• 4 records deleted

• all records deleted

• no records deleted

Fill in the Blanks

Multiple Choice

How has this table been sorted?

• schoolID Descending, surname Ascending

• pupillid Descending, surname Ascending

• surname Ascending, forename Ascending