Databases

Learning Outcomes

Students should be able to:

Describe the purpose of a database
Create a table
Add fields with appropriate data types
Create a relationship between tables
Enter Records
Create forms
Create Queries (Simple and Complex)
Create Reports
Create a User Interface using Forms
Add validation
Explain the following terms: Database, Table, Field, Record, Primary Key, Foreign Key, Link Table, Relationship, Form, Query, Report, User Interface, Validation.

Database

A database is simply a method of storing data in an organised way so that we can access it again in the future when we need to. In the past databases were paper based, stored in large filing cabinets on printed pages. Nowadays most databases are digital, stored on computer system. Almost every app and online game you have used will make use of a database in some way. Even the contacts you store in your phone are held in a form of database.

In this task we will be creating a database for a video shop which will store information about the customers and movies they rent out.

Table

The image Above is an example of a table, and the one to the left shows all of the different data types available to us.

A database is made up of at least one table, this is the structure which is used to store the data. Most databases will have multiple tables linked together, this is a more efficient method of creating databases as it reduces the need for duplicated data saving storage space and makes the updating of records much easier. Think of a table like a big box which we are using to store all of the data about one object or thing in our database.

A table is made up of one or more fields, a field is simply a piece of data about an object. For example, we might create a table called students to hold data about the students in school.

What pieces of information might we need to store about each student?

Note ID is added automatically we simply need to rename it, this is the primary key for the table. It is used to uniquely identify each record. Two people might have the same name, their ID can be used to tell them apart.

These are the fields we need to create. When creating a field we need to choose an appropriate data type. The main ones we will be using are explained below.

Short Text (This is used to store text information such as name)

Number (This is used to store numeric data such as age)

Date (This is used to store dates such as date of birth)

Yes/No (This is used to store values which have only two possible values yes/no)

Look Up ( This is used to store values when there are a few options to choose from)

Below is an example of a customer table containing 7 records.

Activity One

Create two tables one for customers and one for movies use the field names below. You should add 10 records into each database. Think about all of the data you want to hold for each object and think about the most suitable data type. When you have created the table you can add 10 records for both customers and movies.

Customer Table Movie Table

Customer ID Movie ID

First Name Title

Surname Genre

Age Rating

House Number

Street

Town

Postcode

Over 18

Phone Number

Forms

A form is a method of entering data into a database, we create them to make the data entry process simpler for the user.

Activity Two

Create a form for both your movie and customer table and use it to enter another 5 records into each table.

Relationships

At the minute we have two separate tables we now need to link them together. To do this we need to create a link table. A link table will hold the primary key for both the tables it is linking together (as foreign keys) and any additional information. Our link table is going to be called rentals.

Activity Three

Create a rental table and use it to link all 3 tables together. Remember all tables must be closed before creating relationships and always select enforce referential integrity.

Rental Table

Rental ID

Customer ID

Movie ID

Order date

Return Date

Paid

Once the table has been created and linked we then need to add in 30 records to the rental table.

Query

A query is a way of "questioning" or searching a database. We can find all customers from Omagh, All customers over 18 years old or even everyone called Jim. We can also search for movies from certain genres or age ratings. These can be made to give the employees quick access to specific data.

A simple query is when we search on one field for example searching for all customers from Killyclogher. A complex query is when we search on more than one field for all customers from Omagh who are over 18.

When querying a text field we have to make sure we spell the query exactly as it is in the database and put it inside quotes.

"Omagh"

"Killyclogher"

We can use the greater than and less than sign when querying numbers.

>18 (Greater than 18)

<18 (Less than 18)

30 (30 only)

These can also be used on date fields.

When querying a YES/NO we don't need to use quote marks.

Yes

No

Activity Four

Create 3 queries on each table that will be useful for the manager of the video shop. Try to use a range of different querying techniques and data types.

Reports

A report takes the results of our query and presents them in a more user frendly way on the screen, or they can even be printed out. This allows the managers of a business to get quick access to key information as they need it.

Activity Five

Create a report for each of the queries you have made. Format the queries so they have a consistent style.

User Interface

A user interface allows the user to navigate and use a system much easier. To create a user interface in Access we will be using Forms and action buttons. An action button allows us to assign a task like printing a report or opening a form/report to a button. We can create a menu using action buttons.

Activity Six

Create a blank form and use the button tool to develop a menu linking to all of your forms and reports. You will also need to add a menu button on each form and report allowing the user to return back to the main menu. You can also add any other buttons to the forms you have created that you feel might be useful for the end user.

Success Criteria

Before you submit your work for assessment check you have completed each of the tasks below. You can also post your work on teams for others to view and give you feedback.

Create 3 tables (Customer, Movie and Rentals) (10 Marks)
Add fields with appropriate datatypes (10 Marks)
Add records to Customer, Rental and Movie Tables (10 Marks)
Create a relationship between the tables (10 Marks)
Create a form for each table (10 Marks)
Create 3 queries on each table (10 Marks)
Create reports (10 Marks)
Create a Main Menu (10 Marks)
Create an appropriate User Interface (10 Marks)
Use a consistent style across all forms and reports (10 Marks)