Lesson 2 · Year 8 Databases

Designing a Database

DataDrop's engineers need you to design the Artists table. Before you build it, you need to know which data type to use for each piece of information.

🎯

Learning Objectives

✓

Choose appropriate data types

Text, Number, Date/Time, Yes/No, Currency — and when to use each

✓

Explain the purpose of a primary key

Unique identifier — no two records can share the same value

✓

Add validation rules and validation text

Prevent bad data from being entered

✓

Create a table in Access Design View

Build the Artists table field by field

📊

Data Types — Quick Reference

Short Text

For words and sentences (names, genres, countries). Also use for phone numbers — you don't do maths on them!

Number

For values you'll calculate with — stream counts, years, listener numbers.

Date/Time

For dates — when an artist joined DataDrop, a song's release date.

Yes/No

For true/false, on/off — is the artist verified? Is the song explicit?

Currency

For money values — automatic £/$ formatting and 2 decimal places.

AutoNumber

Automatically generates a unique number. Perfect for primary key fields.

🧠

Data Type Challenge

For each piece of data below, click the correct data type. Think carefully!

1. An artist's name (e.g., "Ed Sheeran")

Number

Short Text

Yes/No

Date/Time

2. Whether an artist is verified on DataDrop

Short Text

Number

Yes/No

Currency

3. The date an artist joined DataDrop

Number

Date/Time

Short Text

AutoNumber

4. The number of monthly listeners (e.g., 85,000,000)

Number

Short Text

Currency

Yes/No

5. A unique ID for each artist (auto-generated)

Short Text

Number

AutoNumber

Yes/No

Step 2 of 5

Design the Artists Table

Before you build anything in Access, you plan it on paper. Complete the table design grid below — this is exactly what professional database developers do.

Design Brief: DataDrop needs to store information about each artist. They need to know the artist's name, country, genre, monthly listeners, whether they're verified, and the date they joined DataDrop. Each artist needs a unique ID.

📐

Artists Table — Design Grid

Complete every column. The first row (primary key) has been done for you.

Field Name	Data Type	Field Size / Format	Required?	Notes
ArtistID ⭐ PK	AutoNumber	Long Integer	Yes	Unique ID — primary key

🔑

Primary Key Questions

Which field is the primary key in your Artists table?

Why can't you use ArtistName as the primary key? Give two reasons.

Step 3 of 5

Build the Table in Access

Now transfer your design into Microsoft Access. Open the starter database and create the Artists table in Design View.

📁 File to open: DataDrop_L2_starter.accdb — this already has the Songs table. You are adding the Artists table.

🔨

Build Tasks

1

Open Design View

Go to Create → Table Design. This opens the Design View where you can define fields.

2

Add all 7 fields

Enter each field name and select its data type from your design grid. Use the Field Properties panel at the bottom to set field size.

3

Set the Primary Key

Click the ArtistID row, then click the Primary Key button in the toolbar (the key icon). A key symbol should appear on that row.

4

Save the table

Press Ctrl+S. When asked for a table name, type Artists (capital A, no spaces).

5

Enter 8 sample records

Switch to Datasheet View (Home → View → Datasheet View) and enter the 8 artist records your teacher has given you.

Step 4 of 5

Validation Rules

A database is only useful if the data in it is accurate. Validation rules stop bad data getting in before it causes problems.

🛡️

What is Validation?

A validation rule is a condition a value must meet before it can be saved. If it fails, Access shows a validation text message explaining the error.

Example: For the MonthlyListeners field you might set the rule >=0 with the message "Listeners cannot be negative." If someone types -5000, Access rejects it and shows the message.

⚙️

Add Validation to Your Artists Table

1

Validate MonthlyListeners

Go back to Design View. Click the MonthlyListeners field. In Field Properties → Validation Rule type: >=0
In Validation Text type: Monthly listeners cannot be negative.

2

Test the validation

Switch to Datasheet View. Try to enter -1000 as a MonthlyListeners value. Does the error message appear? Screenshot it.

3

Add a Lookup Wizard to Genre

In Design View, click the Genre field. Change its Data Type to Lookup Wizard. Choose "I will type in the values" and enter: Pop, Rock, Hip-Hop, Electronic, Indie, R&B, Folk, K-Pop, Synthpop (one per row).

4

Test the Lookup

Switch to Datasheet View. Click the Genre field on a record — does a dropdown appear? Try typing an invalid genre that's not in the list.

🚀 Extension

Relational Thinking

You now have two tables: Songs and Artists. Both have an "Artist" field (text). A proper relational database would link them using a foreign key — instead of storing the artist's name in Songs, you'd store their ArtistID. Why would this be better? Write your thoughts below.

Step 5 of 5

Exit Quiz

Check your understanding before the next lesson.

✅

Lesson 2 Check

1. A phone number like "07911 123456" — which data type should it be, and why NOT Number?

2. What is a primary key? Give the two rules a primary key must follow.

3. Write a validation rule for a field called "ReleaseYear" that must be between 1900 and 2030.

4. Why is it important to plan a table design on paper BEFORE building it in Access?

📤

Export Your Answers

Download all your answers from every section as a text file — then upload to Google Classroom.

🎵

Lesson Complete!

You've designed and built a real database table with validation rules. Next lesson: you'll start querying the DataDrop database to find specific data.

Go to Lesson 3 →