Microsoft Access Database Worksheet -
Step-by-Step Practical Questions
Scenario: School Library Management System
You are tasked with creating a database for Greenwood High School's library to manage
books, students, and borrowing records.
PART A: Database Creation and Table Design
Question 1: Creating a New Database
Step-by-step instructions to follow:
1. Open Microsoft Access
2. Create a new blank database called "LibrarySystem"
3. Save it in your Documents folder
Your Task:
What file extension does Access use for databases? ________________
Where is your database file saved? Write the full file path: ________________
Question 2: Creating the BOOKS Table
Follow these steps:
1. Create a new table in Design View
2. Add the following fields with specified data types:
Field Name Data Type Field Size Description
BookID AutoNumber Primary Key
Title Text/Short Book title
Text
Author Text/Short Author name
Text
Genre Text/Short Book category
Text
ISBN Text/Short ISBN number
1|Page
Text
YearPublished Number Publication year
NumberOfCopies Number Total copies available
Publisher Text/Short Publisher name
Text
Your Tasks: a) Which field should be set as the Primary Key and why?
Answer: ________________________________
b) Why is BookID set as AutoNumber data type?
Answer: ________________________________
c) Set BookID as Primary Key and save the table as "Books"
Question 3: Creating the STUDENTS Table
Create a second table with these specifications:
Field Name Data Type Field Size Additional Properties
StudentID Text/Short Text Primary Key
FirstName Text/Short Text
LastName Text/Short Text
Class Text/Short Text
DateOfBirth Date/Time
Email Text/Short Text
PhoneNumber Text/Short Text
Your Tasks:
a) Create this table in Design View
b) Set appropriate field properties (Required, Format, etc.)
c) Save the table as "Students"
Question: Why might you choose Text data type for StudentID instead of AutoNumber?
Answer: ________________________________
Question 4: Creating the BORROWING Table
Create a third table to track book borrowing:
Field Name Data Type Field Size Description
BorrowingID AutoNumber Primary Key
StudentID Text/Short Text Foreign Key
BookID Number Foreign Key
DateBorrowe Date/Time Borrow date
d
2|Page
DateDue Date/Time Due date
DateReturned Date/Time Return date (can be empty)
Fine Currency Late return fine
Your Tasks:
a) Create this table following the specifications
b) Save as "Borrowing"
Questions:
1. Which fields in this table are Foreign Keys?
Answer: ________________________________
2. Why might Date Returned be left empty for some records?
Answer: ________________________________
PART B: Data Entry and Validation
Question 5: Adding Sample Data
Enter the following data into your BOOKS table:
NumberOfCopie
Title Author Genre ISBN YearPublished Publisher
s
To Kill a Harper 978- Harper
Fiction 1960 3
Mockingbird Lee 0061120084 Perennial
George 978- Signet
1984 Fiction 1949 2
Orwell 0451524935 Classics
The Great F. Scott 978-
Fiction 1925 4 Scribner
Gatsby Fitzgerald 0743273565
Pride and Jane 978- Penguin
Romance 1813 2
Prejudice Austen 0141439518 Classics
The Catcher J.D. 978- Little
Fiction 1951 3
in the Rye Salinger 0316769488 Brown
Your Task: Enter this data into the Books table using Datasheet View.
Question: What happens to the BookID field as you enter each record?
Answer: ________________________________
3|Page
Question 6: Student Data Entry
Add these students to the STUDENTS table:
StudentI FirstNam LastNam DateOfBirt PhoneNumbe
Class Email
D e e h r
GH00123
John Smith 10A 15/03/2008 [Link]@[Link] 555-0101
4
GH00123 [Link]@[Link]
Emma Johnson 10B 22/07/2008 555-0102
5 m
GH00123 [Link]@school.c
Michael Brown 9A 10/11/2009 555-0103
6 om
GH00123
Sarah Davis 10A 05/01/2008 [Link]@[Link] 555-0104
7
GH00123 [Link]@[Link]
James Wilson 9B 18/09/2009 555-0105
8 m
PART C: Relationships and Referential Integrity
Question 8: Creating Table Relationships
Follow these steps to create relationships:
1. Go to Database Tools → Relationships
2. Add all three tables to the Relationships window
3. Create relationships:
o [Link] → [Link] (One-to-Many)
o [Link] → [Link] (One-to-Many)
Your Tasks:
a) Create these relationships
b) Enforce Referential Integrity for both relationships
c) Enable Cascade Update Related Fields
Questions:
1. What does "One-to-Many" relationship mean?
Answer: ________________________________
2. Why shouldn't you enable "Cascade Delete Related Records" for the Books-
Borrowing relationship?
PART D: Queries
4|Page
Question 10: Simple Select Query
Create a query to show all Fiction books:
1. Create a new query in Design View
2. Add the Books table
3. Include fields: Title, Author, Genre, NumberOfCopies
4. Add criteria: Genre = "Fiction"
5. Save as "Fiction Books Query"
Your Task: Run the query and write down how many fiction books are displayed:
Answer: ________________________________
Question 11: Parameter Query
Create a query that asks for a specific author:
1. Create a new query using the Books table
2. Include fields: Title, Author, YearPublished, Publisher
3. In the Author criteria, enter: [Enter Author Name:]
4. Save as "Books by Author Query"
Question: When you run this query, what happens?
Answer: ________________________________
Question 13: Multi-table Query
Create a query showing student borrowing details:
1. Use all three tables (Students, Books, Borrowing)
2. Include: FirstName, LastName, Title, DateBorrowed, DateDue
3. Sort by LastName then FirstName
4. Save as "Student Borrowing Details"
PART E: Forms
Question 14: Simple Form Creation
Create a form for entering new students:
1. Use the Form Wizard
2. Select the Students table
3. Include all fields
4. Choose Columnar layout
5. Save as "Student Entry Form"
Your Tasks:
5|Page
a) Create the form following the steps
b) Use the form to add a new student with your own details
Microsoft Access Worksheet
Complete Step-by-Step Questions with Answers
SCENARIO: Online Bookstore Database
You are creating a database for "BookHub Online" to manage their books, customers, and
orders.
ANSWER KEY
Answer Question 1a: What file extension does Access use for database files?
ANSWER: .accdb (Access Database)
Answer Question 1b: Why is it important to save the database in a specific location?
ANSWER: To ensure easy access, organization, and to prevent loss of work. It also helps
with backup procedures and file management.
Question 2: Create BOOKS Table
6|Page
Answer Question 2a: Why is BookID set as AutoNumber? ANSWER: AutoNumber
automatically generates unique sequential numbers, ensuring each book has a unique
identifier without manual input and prevents duplicate primary keys.
Answer Question 2b: What is the purpose of the validation rule for Genre? ANSWER: To
ensure data consistency by only allowing specific, predefined values to be entered, preventing
spelling errors and maintaining data integrity.
Answer Question 2c: Write the complete validation rule for the Genre field. ANSWER:
"Fiction" Or "Non-Fiction" Or "Science" Or "History" Or "Biography"
Question 3: Create CUSTOMERS Table
Answer Question 3a: Why might CustomerID be Text instead of AutoNumber?
ANSWER: Text allows for meaningful customer codes (like CUS001, CUS002) that can be
used for customer service, marketing, and easier identification compared to random numbers.
Answer Question 3b: List three fields that should be set as Required and explain why.
ANSWER:
1. FirstName - Essential for customer identification and communication
2. LastName - Required for complete customer identification
3. Email - Needed for order confirmations and customer contact
Question 4: Create ORDERS Table
Answer Question 4a: Which fields are Foreign Keys and what do they reference?
ANSWER:
CustomerID references [Link]
BookID references [Link]
Answer Question 4b: Why set Quantity default to 1?
ANSWER: Most orders are for single books, so setting default to 1 speeds up data entry and
reduces errors.
Question 5: Enter Sample Data
Answer Question 5a: What happens if you try to enter "Drama" in the Genre field?
7|Page
ANSWER: Access displays the validation error message "Please enter a valid genre" and
prevents the record from being saved until a valid genre is entered.
Answer Question 5b: What happens to the BookID field as you enter each record?
ANSWER: Access automatically assigns sequential numbers (1, 2, 3, 4, 5) to each new
record without manual input.
Question 6: Create Table Relationships
Answer Question 6a: What does "One-to-Many" relationship mean?
ANSWER: One customer can have many orders, and one book can appear in many orders,
but each order belongs to only one customer and references only one book.
Answer Question 6b: Why enable "Cascade Update Related Fields"?
ANSWER: If a primary key value changes, all related foreign key values automatically
update, maintaining data consistency across tables.
Answer Question 6c: Why NOT enable "Cascade Delete Related Records"?
ANSWER: Deleting a customer or book shouldn't automatically delete order history, as this
information may be needed for business records, accounting, or legal purposes.
Question 7: Simple Select Query
Answer Question 7a: Write the SQL for this query.
Answer Question 7b: How many Fiction books should appear in results?
ANSWER: 2 books (The Great Gatsby and 1984)
Question 8: Parameter Query
Answer Question 8a: What appears when you run this query?
ANSWER: A dialog box asking "Enter Author Name:" where you can type the author's
name.
Answer Question 8b: How many books appear for "George Orwell"?
ANSWER: 1 book (1984)
8|Page
Question 9: Calculated Field Query
Answer Question 9a: Write the calculated field expression.
ANSWER: StockValue: [Price]*[Stock]
Answer Question 9b: Which book has the highest stock value?
ANSWER: 1984 (£10.99 × 30 = £329.70)
Answer Question 9c: What is the total stock value of all books combined?
ANSWER:
The Great Gatsby: £12.99 × 25 = £324.75
A Brief History of Time: £15.50 × 15 = £232.50
1984: £10.99 × 30 = £329.70
The Art of War: £8.99 × 20 = £179.80
Steve Jobs: £18.99 × 12 = £227.88 Total: £1,294.63
Question 10: Multi-table Query
Answer Question 10a: Write the table relationships needed for this query.
ANSWER:
CUSTOMERS joined to ORDERS on CustomerID
BOOKS joined to ORDERS on BookID
Answer Question 10b: How many orders has customer John Smith placed?
ANSWER: 2 orders (The Great Gatsby and A Brief History of Time)
9|Page
