Relational Databases

Do Now! Go ro BBC Bitesize. Read all the information about Databases and then do the quiz

BBC Bitesize

https://www.bbc.co.uk/bitesize/guides/zvnny4j/revision/1

Learning Objective : Create a relational database with at least 3 tables

Use the database to enter records

Choose appropriate field name

Use the Lookup wizard to link data from each table

Use data validation rules so that the user enters sensible data

Key Words : Table, records, fields

Data Types, Text, Number, Alphnumeric, Boolean and Date

How to create a simple relational database in MS Access

Open a new MS Access Database and call it Library

You need to create 3 tables

  1. Customer
  2. Lend
  3. Book

Table 1 : Customer

Go to Design View and add 3 fields

  • CustomerID
  • FirstName
  • Secondname

Make sure CustomerID is the primary key and has the Data Type, Autonumber

Go to the Datasheet View and enter a record.

Table 2 : Book

To create a new table in the same database, go to Create > Table

Call the new table, Book

Go to Design View and add 2 fields

  • BookID
  • Title

Make sure BookID is the primary key and has the Data Type, Autonumber

Go to the Datasheet view and enter a record

Table 3 : Lend

Call the new table Lend

Go to Design View and add 3 records

  • LendID
  • CustomerID
  • BookID

Make sure BookID is the primary key and has the Data Type, Autonumber

Making relationships using the LookUp Wizard

Go to the Data Type for Customer ID, and choose LookUp Wizard (later, do the same for BookID)

Choose the first option

Choose which table you want to relate it to

Then follow as shown below

Repeat the process for BookID

Let’s check your database

Go the Datasheet view and enter a record.

Go to Table and Relationships

You should see relationships that look like this

Extension tasks

  1. Add more relevant fields to the records in your database
  2. Create more tables (e,g, gender) and find a way to relate it to your existing database
  3. Create a new database for a DVD shop, e.g CEX

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: