N5 Revision DBB

N5 Computing

Exam Revsion 2

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 Requirement

• Functional Requirement

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 Requirement

• Functional Requirement

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 Requirement

• Functional Requirement

Design

Two tools/tasks from the design stage are:

• Data Dictionary - to describe all the attributes to be created in each entity

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

Multiple Choice

Attribute types

There are five attribute types we need to know for data stored in a database field

Which of the following is not attribute type?

• Number

• Time

• Integer

• Boolean

• Date

Multiple Choice

Validation checks

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

Which validation check is this an example of?

Data must be a number between 1 and 6

• Presence check

• Length check

• Range check

• Restricted choice

Multiple Choice

Validation checks

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

Which validation check is this an example of?

A username must be 10 characters long

• Presence check

• Length check

• Range check

• Restricted choice

Multiple Choice

Validation checks

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

Which validation check is this an example of?

House must be one of G, L, O or X

• Presence check

• Length check

• Range check

• Restricted choice

Multiple Choice

Validation checks

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

Which validation check is this an example of?

You cannot leave the date of birth field blank

• 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

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

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 

Data Dictionaries

Draw

Identify the foreign key

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

E-R Diagrams

Multiple Choice

What is the relationship between the entities?

• One patient can go to one doctor

• One patient can go to many doctors

• One doctor has one patient

• One doctor has many patients

Multiple Choice

What is the primary to foreign key relationship?

• Department.DepartmentCode = Staff.DepartmentCode

• Doctor.StaffID = Patient.StaffID

• Doctor.StaffID = Patient.PatientID

• Doctor.DateJoined = Doctor.DateRegistered

Multiple Choice

What is the relationship between the entities?

• One department employs many staff

• One employee is employed by many departments

• One employee is employed by one department

• Many departments employ many staff

Multiple Choice

What is the primary to foreign key relationship?

• Department.DepartmentCode = Staff.DepartmentCode

• Department.Department = Staff.Staff

• Department.DepartmentCode = Staff.StaffIDNumber

• Department.DeptName = Staff.StaffName

Multiple Choice

What is the primary key in the Pupil table?

• pupilID

• schoolName

• surname

• schoolID

Multiple Choice

What is the primary key in the School table?

• schoolID

• schoolName

Multiple Choice

What is the foreign key and in which table does it appear?

• pupilID in Pupil table

• forename in Pupil table

• schoolID in Pupil table

• schoolID in School table

• schoolName in School table

Open Ended

What would be the result of this query?

SELECT forename FROM Pupil WHERE pupilID = 'S0004';

Open Ended

What would be the result of this query?

SELECT forename FROM Pupil WHERE surname = 'Smith';

Open Ended

What would be the result of this query?

SELECT forename FROM Pupil, School WHERE School.schoolID = Pupil.schoolID AND schoolName = 'Kirk Lane Junior School';

Open Ended

What would be the result of this query?

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

Open Ended

What would be the result of this query?

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

Open Ended

What would be the result of this query?

DELETE FROM School WHERE schoolID = 'P0011';

Open Ended

Referential Integrity

Describe the problem in the tables below.

Open Ended

Sorting

How has the Pupil table been sorted?