Database Design & Development Revision

Database Design & Development

National 5 Computing Science

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?​

​

When a new pupil joins the school, all their details and subjects must be added to the timetable

• 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

Design

Two tools/tasks from the design stage are:

Data Dictionary - a plan 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

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

Open Ended

What was the 5th missing data type?

Validation Checks

There are 4 types of validation used in databases

• Presence Check

• Restricted Choice

• Range Check

• Length Check

Multiple Choice

Which validation check is this an example of?​

​

Data must be a number between 1 and 6

• Presence

  Check

• Restricted Choice

• Range Check

• Length Check

Multiple Choice

Which validation check is this an example of?​

​

A username must be 10 characters

• Presence Check

• Restricted Choice

• Range Check

• Length Check

Multiple Choice

Which validation check is this an example of?​

​

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

• Presence Check

• Restricted Choice

• Range Check

• Length Check

Multiple Choice

Which validation check is this an example of?​

​

You cannot leave the date of birth field blank

• Presence Check

• Restrcited Choice

• Range Chek

• Length Check

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

Primary Keys

A primary key is a field used to uniquely identify every record in the database. Sometime primary keys are obvious, for example a car license plate could be used to uniquely identify cars. Often, primary keys are less obvious and it is necessary to create a unique value – often an ID number.

A primary key is a guaranteed way of uniquely identifying each record. In the school example, the primary key used to identify each member of staff in the teacher table is Staff ID.

Foreign Keys

A foreign key is a primary key from one table that appears in another table to link the two together. The Department ID is the primary key in the Department table that appears as a foreign key in the Staff table to link the two.

Key Relationships

The link between the primary and foreign key provides a relationship between the tables.

These can be

• 1:1

• 1:M

• M:M

Draw

Identify the Foreign Key

Multiple Choice

What is the relationship between the entities?​

• 1:M

• Employs

• Departments employ Staff

• DepartmentCode:StaffIDNumber

Multiple Choice

What is the primary to foreign key relationship?​

• 1:M

• Employs

• Department.DepartmentCode : Staff.StaffIDNumber

• Department.DepartmentCode : Staff.DepartmentCode

Open Ended

What is the relationship between the entities?​

Open Ended

What is the primary to foreign key relationship?​

Multiple Choice

What are the Primary Keys?

• Pupil.PupilID

  School.SchoolID

• Pupil.SchoolID

  School.SchoolID

Multiple Choice

What is the Foreign Key?

• Pupil.SchoolID

• School.SchoolID

• Pupil.PupilID

Multiple Choice

What is the relationship between Pupil and School?

• 1:M

• M:1

• 1:1

• M:M

SQL Queries

Queries are a method used for requesting information from a database which matches set criteria. Queries are designed using a programming language called SQL (Structured Query Language).

There are 4 types of queries

• SELECT - search for

• INSERT - add a record in

• DELETE - remove a record

• AMEND - update and existing record

Multiple Choice

What would be the result of this query?

SELECT forename

FROM Pupil

WHERE pupillid = 'S0004';

• Jane

• Atkins

• St Kristen's

• P0011

Multiple Choice

What would be the result of this query?

SELECT forename FROM Pupil, School

WHERE School.schoolID = Pupil.schoolID

AND schoolName = 'Kirk Lane Junior School';

• Julie Stevens

• S0002

• Julia

• Kirk Lane Junior School

Multiple Choice

What would be the effect of this query?

UPDATE Pupil

SET schoolID = 'P0018'

WHERE schoolID = 'P0011';

• All P0011 become P0018

• All P0018 become P0011

Multiple Choice

What would be the effect of this query?

DELETE FROM School

WHERE schoolID = 'P0011';

• St Kristen's P0011 is removed from the Pupil table

• St Kristen's P0011 is removed from the School table

Multiple Choice

What would be the effect of this query?

INSERT INTO Pupil (pupilID, forename, surname, schoolID)

VALUES ('S0006', 'Emma', 'Jones', 'P0018');

• Remove Emma Jones from the database

• Add Emma Jones to the database

• Find Emma Jones in the database

Referential Integrity

In Relational Database Management Systems (RDBMS) referential integrity can be enforced by working with primary and foreign keys. Each foreign key must have a matching primary key so that reference from one table to another must always be valid.

Then, with referential integrity in place, the database contents will be joined together to be jointly updated when necessary.

Open Ended

What is the referential Integrity issue in this database?

Sorting Databases

Sorting a database is placing the data within it into a specific order

• ASC - A to Z or numerically smallest to largest

• DESC - Z to A or or numerically largest to smallest

Multiple Choice

How has the database been sorted?

• Pupil table ASC on schoolID

• Pupil table ASC order on surname

• School table ASC on schoolID

• School table ASC on schoolName