Unit 18: Lesson 3 - Web Tutor

Learning Outcomes

Be able to design, create and populate a relational database

Assessments

November 18th Assessment 1: Database Design and Implementation

Week 3 Activities - Creating Access Tables

Week	Lesson Activity	Learning Assessment	Skill Developed
1.1	10 min A reminder of the benefits of a relational database using Basic Spreadsheet of Student Enrolments 16 min Video: Explain the principles of a Access Database , four short Microsoft Videos 20 min Demonstrate how to create an Access Database, and import Mia's Pizza Sales into new Access tables Video (5min) Importing Spreadsheet data into an Access Table 20 min Demonstrate how to create Make Table or Append Table queries to create the People and Address Tables from the Customers Table 20 min Demonstrate how to create the relationships between People and Addresses 20 min Demonstrate how to add Addresses to People, and Staff to People. 20 min Demonstrate how to add Orders and then create the relationships between Orders and Sandwhiches 20 min Demonstrate how to add a Customer Person to the Order (1:M) and relate Staff to Shops (M:1)	Questions and Answers Access Tables	Questions and Answers Table Relationships

Week

Lesson Activity

Learning Assessment

Skill Developed

perform queries using multiple tables and multiple criteria

evaluate a database against the specified user need

1.1

10 min A reminder of the benefits of a relational database using Basic Spreadsheet of Student Enrolments
16 min Video: Explain the principles of a Access Database , four short Microsoft Videos
20 min Demonstrate how to create an Access Database, and import Mia's Pizza Sales into new Access tables
Video (5min) Importing Spreadsheet data into an Access Table
20 min Demonstrate how to create Make Table or Append Table queries to create the People and Address Tables from the Customers Table
20 min Demonstrate how to create the relationships between People and Addresses
20 min Demonstrate how to add Addresses to People, and Staff to People.
20 min Demonstrate how to add Orders and then create the relationships between Orders and Sandwhiches
20 min Demonstrate how to add a Customer Person to the Order (1:M) and relate Staff to Shops (M:1)

Questions and Answers
Access Tables

Questions and Answers
Table Relationships

Useful Resources

Video (5min) Importing Spreadsheet data into an Access Table
Video Explain the principles of an Access Database , a 4 minute videos by Microsoft. Essential viewing as it covers all the basics of database design. The videos do not stream that well, but be patient they are worth it.
Video Access Database Objects , a 4 minute video by Microsoft.
Video Build Tables and set Data Types , a 4 minute video by Microsoft.
Video Build Tables with the Table Designer , a 4 minute video by Microsoft.
Video Add Calculated Fields to a Table , a 4 minute video by Microsoft.
Video Setting up Table Relationships , a 5 minute video by Microsoft.
Video Creating relationships using the lookup wizard , a 3 minute video by Microsoft.
Video Creating relationships using the Relationship Pane , a 3 minute video by Microsoft. This is an alternative to the lookup wizard, but the wizard is usually a better way.
Video Creating Many to Many relationships , a 4 minute video by Microsoft. It uses the Relationships pane, but I would use the lookup wizard for Product to Order-Details so that when you look in the Order Details table you see the Product name, and not an unrecognisable number.
Video Creating One to One relationships , a 3 minute video by Microsoft.
Video Copying data from Excel , a 3 minute video by Microsoft. I think that importing Excel data is a better an more controllable option.
Video Getting Started with Queries , a 6 minute video by Microsoft. Does not demonstrate Make Table or Append Table queries.
Tutorial Using Tables in Access 2016 A microsoft tutorial that contains more detail than you need. You need all the topics upto External Data. The first section should help with P1, P2
Tutorial How to Add or Change a Table Primary Key. a Microsoft Tutorial

Extended National Diploma in IT

Unit 18: Database Design

Learning Outcomes

Assessments

Week 3 Activities - Creating Access Tables

Useful Resources

Week Index