3 – Creating Databases

• Understand how to set up a database by using fields, data types and validation rules.
• Know how to import data into a database.
• Be able to create a database from scratch by following a list of instructions.

• Database
• Table

• Field
• Validation

• Record

Can you fill in the blanks on the comic book story and describe databases to Batman? Download the starter worksheet below to find out.

Step 1

Open Microsoft Access by searching for “Access” in the search bar at the bottom of your screen.

Step 2

Once Access is open you should click once on “Blank database”.

Step 3

The following box will appear, change the default name to “Film Characters.accdb”, then click Create.

Step 4 – Tables

Tables are where records are stored and it is essential that they are set up correctly. Your table will open up straight away and look like this:

1. Save your table by clicking on the File button in the top left corner and then “Save”. Change the name of the table from “Table1” to “Characters” and click OK.
2. You need to click on the button below to take you to the design view so you can enter your fields and data types. In design view you can enter the fields, data types and other information needed to store all of your records:

3. Enter your table’s fields as shown below, and change the data types to the most appropriate.

Step 5 – Adding a drop down list

Now you are going to add a drop down list, as this allows information to be added easier and quicker.

1. On the “Origin of Powers” field, on the data types field, click on “Lookup Wizard”.

2. Click on the “I will type in the values that I want” and then “Next>”.

3. Start to type in all the possible options you want, one on each line (Alien, Chemicals, Inheritance, Magic, Mutant Powers, Radiation, Technology, Training) and then click “Finish”.

4. If you click on the view button and have a look on the field, it should show a drop down box so you can choose an option.

Adding validation rules and text means only certain information can be added. On the following fields add the following validation rules and then add your own validation text for each one (use the Operators section below for help): –

• Number of Powers – Only allow a number between 1 and 10.
• Role – Only allow Super Hero, Super Villain, Team Member or Henchman.
• Cost to Insure – Only allow a number less than or equal to 1000.

Mathematical Operators

Operator Symbol	Descripton
>	More Than or After
<	Less Than or Before
>=	More Than or Equal To
<=	Less Than or Equal To

Logical Operators

Operator Symbol	Description
Between And	Between 1980 And 2000
Or	January Or April
<>	Used to exclude data – <>Male

Database – collection of data or information that has been stored in an organised way.

Field – one type or one piece of information.

Record – all the data about a person or an object.

Table – collection of the database’s fields and records.

Validation – checking the validity of data entered by the user.

In this lesson, you…

• Understood how to set up a database by using fields, data types and validation rules.
• Know how to import data into a database.
• Be able to create a database from scratch by following a list of instructions.

In the next lesson, you will…

• Understand what a database form is.
• Know how to add, amend and delete database records using a form.
• Be able to create and re-design a database form.

• Upload your completed Starter – Fill in the Blanks worksheet to the Silver badge task on Bourne to Learn.

• Screenshot the design view of your database and then upload to the Gold badge task on Bourne to Learn.

• Screenshot your filled database in the data view and then upload to the Platinum badge task on Bourne to Learn.