⏪Revision- Spreadsheets and Data Modelling

Learning Intentions:

You will need to download these 2 files and follow with class
instructions:

Worksheet TNBT Series

2 Comput... 1 USA Vot...

Save your work and upload it below:

• Understand what computer models are used for

Wednesday, April 22, 2026 12:19 P

• Understand that spreadsheets can be used to build financial models

Developing Databases Page 1

• Revise spreadsheet basics

Developing Databases Page 2

• Make use of relative and absolute cell referencing

• Be able to format cells and insert a graphic into a spreadsheet

Developing Databases Page 3

Developing Databases Page 4
Microsoft Access Tutorials

Microsoft Access is a Database Management System (DBMS) from Microsoft that combines the relational Microsoft Jet
Database Engine with a graphical user interface and software development tools. It is a part of the Microsoft Office suite of
applications, included in the professional and higher editions. In the tutorials included in this unit we will cover the bas ics of
MS Access.

Wednesday, November 26, 2025 10:21 A

Developing Databases Page 5

Developing Databases Page 6
 ❗Organisation of work

You all must complete this task:

3. Friday, May 1, 2026

Share a link to your folder here: 8:22 AM

MS-access-Aarush Shah

1. Open your OneDrive folder and Create a new Folder

Developing Databases Page 7

2. Name it : MSAccess_Tutorials_YourName

See my example:

Developing Databases Page 8

 Objects

Read and highlight key information you find below:

MS Access uses "objects" to help the user list and organise information, as well as prepare specially designed
reports. When you create a database, Access offers you Tables, Queries, Forms, Reports, Macros, and Modules.
Thursday, April 30, 2026 1:00 PM
Databases in Access are composed of many objects but the following are the major objects:

Form: Form is an object in a desktop database designed primarily for data input or display or for control of application
execution. You use forms to customise the presentation of data that your application extracts from queries or tables.

• Tables

• Queries

• Forms

• Reports

Together, these objects allow you to enter, store, analyse, and compile your data. Here is a summary of the major
objects in an Access database.

Developing Databases Page 9

 objects in an Access database.

• Forms are used for entering, modifying, and viewing records.

• The reason forms are used so often is that they are an easy way to guide people toward entering data correctly.
Table: Table is an object that is used to define and store data. When you create a new table, Access asks you to
define fields which is also known as column headings.

Developing Databases Page 10

• When you enter information into a form in Access, the data goes exactly where the database designer wants it to go in
one or more related tables.

• Each field must have a unique name, and data type.

• Tables contain fields or columns that store different kinds of data, such as a name or an address, andrecords or
rows that collect all the information about a particular instance of the subject, such as all the information about a
customer or employee etc.

Report: Report is an object in desktop databases designed for formatting, calculating, printing, and summarising
selected data.

Developing Databases Page 11

 • You can define a primary key, one or more fields that have a unique value for each record, and one or more indexes
on each table to help retrieve your data more quickly.

• You can view a report on your screen before you print it.

Query: An object that provides a custom view of data from one or more tables. Queries are away of searching
for and compiling data from one or more tables.

Developing Databases Page 12

 for and compiling data from one or more tables.

• If forms are for input purposes, then reports are for output.

• Anything you plan to print deserves a report, whether it is a list of names and addresses, a financial summary for a period,
or a set of mailing labels.

• Running a query is like asking a detailed question of your database.

Developing Databases Page 13

• Reports are useful because they allow you to present components of your database in an easy-to-read format.

• When you build a query in Access, you are defining specific search conditions to find exactly the data you want.

• You can even customise a report's appearance to make it visually appealing.

• In Access, you can use the graphical query by example facility or you can write Structured Query Language (SQL)
statements to create your queries.

Developing Databases Page 14

• Access offers you the ability to create a report from any table or query.

• You can define queries to Select, Update, Insert, or Delete data.

Developing Databases Page 15

• You can also define queries that create new tables from data in one or more existing tables.

Other MS Access objects are Macros and Modules

Using the information on this page, create a quiz using MS Forms that I can use to test the other class, Place the Form's
Collect responses link below:

Add your Form here:

MS Access quiz – Fill in form

From <https ://[Link]/Pages/[Link]?origin=NeoPortalPage&subpage=design&id=9c_3jmJYrU

-IhxgfldhYdICXz2ZGS-lAoPgLyq3omvlUNFZQS0ZINDBRM1pPQ00ySUowTlM3MzhWQS4u>

Developing Databases Page 16

Developing Databases Page 17
Creating a database *

Task Video Tutorial / steps Practice Reflection

Thursday, April 30, 2026 12:44 PM

To create a database from a template, we first need to open MS Access and you will see the following screen in which
Create a database from a template different Access database templates are displayed.

To view the all the possible databases, you can scroll down or you can also use the search box.

Developing Databases Page 18

 Let us enter project in the search box and press Enter. You will see the database templates
related to project management.

Select the first template. You will see more information related to this template

Developing Databases Page 19

 After selecting a template related to your requirements, enter a name in the File name field and you can also specify
another location for your file if you want.

Now, press the Create option. Access will download that database template and open a new blank database as shown in
the following screenshot.

Developing Databases Page 20

 Now, click the Navigation pane on the left side and you will see all the other objects that come with this database.

Click the Projects Navigation and select the Object Type in the menu.

Developing Databases Page 21

 You will now see all the objects types tables, queries, etc.

Open MS Access
Create a new blank database

Select Blank desktop database. Enter the name and click the Create button.

Developing Databases Page 22

 Access will create a new blank database and will open up the table which is also completely blank.

Developing Databases Page 23

 Create Tables

Data are stored in tables on databases and other database objects relies heavily on
these tables. Therefore any database design should start by creating all of its tables and
then creating any other object.

Thursday, April 30, 2026 12:48 PM

Task Video Tutorial Practice Reflection

1. Creat MSAcess_CreateTables1.mp4
ea
table
with
MS
Acce
ss

Table Design View

2. Impo
rtant
Task toVideo Tutorial Practice Reflection
2. Project data table
creat
ea
requi
Field Name reme Data Type
nt
table
prior
to
the
desig
n

Create a second table MSAcess_TableDesignView.mp4 What is the difference between the two ways to creating tables?
Before creating any tables, the requirements must be carefully considered to determine
using the table design
the
viewtables' needs.

Project ID AutoNumber

ProjectName Short Text

ManagingEditor Short Text

3. Follo
w
the
instr
uctio
ns in
the
Author video Short Text
to
creat
e
your
own
table

Task:
Scenario: School Equipment Loan Database

PStatus Short Text

Contracts Attachment

ProjectStart Date/Time

The data types were explained in the previous lesson refer to the table there to design

Developing Databases Page 24

Developing Databases Page 25
 ProjectStart Date/Time

The data types were explained in the previous lesson refer to the table there to design
the table requirements.

Tutorial Example: Employees database

ProjectEnd Date/Time

The school ICT department is having trouble keeping track of shared equipment such as laptops, cameras, chargers, and robotic s kits.

Budget Currency

ProjectNotes Long Text

1. Data types for tables needed:

Field Name Data Type

At the moment, items are borrowed informally and sometimes go missing, are returned late, or are damaged without a clear reco rd of who borrowed what and
EmployeelD AutoNumber
when.

FirstName Short Text

LastName Short Text

Address1 Short Text

Address2 Short Text

City Short Text

State Short Text

You have been asked to design a database using Microsoft Access to help the school store, organise, and manage information ab out equipment loans in a clear
and reliable way.

Developing Databases Page 26

Developing Databases Page 27
 Short Text

and reliable way.

Zip Short Text

Phone Short Text

Phone Type Short Text

Create Tables:

Before creating any tables in Access, you must first create a requirements table to plan the database structure properly.

1. Create a Requirements Table

Developing Databases Page 28

Developing Databases Page 29
 Create a requirements table below to plan one table for the database.

Your table must include:

Field Name

Data Type

Field Data
Nam Typ
e e

Loan Auto
ID Num
ber

Stud Shor
entI t
D Text

Stud Shor
ent t
Nam Text
e

Developing Databases Page 30

Developing Databases Page 31
 Text
e

Equi Shor
pme t
ntID Text

Equi Shor
pme t
ntNa Text
me

Cate Shor
gory t
Text

Date Date
Borr /Tim
owe e
d

Due Date
Date /Tim
e

Date Date
Retu /Tim
rned e

Con Shor
ditio t
nBef Text
ore

Con Shor
ditio t
nAft Text
er

Stat Shor
us t
Text

Not Long
es Text

2. Answer the following questions in full sentences:

Developing Databases Page 32

Developing Databases Page 33
 Why The field names were chosen because they clearly
did describe the information being stored in each field.
you For example, StudentID stores the student’s
cho identification number, EquipmentName stores the
ose name of the item being borrowed, and DueDate
thes stores when the equipment must be returned. Clear
e field names make the database easier to
field understand, organise, and use.
nam
es?

Why The data types were selected based on the kind of

did information each field stores. AutoNumber is used
you for LoanID because it automatically creates a unique
cho number for each record. Short Text is used for
ose names, IDs, and categories because these contain
thes letters and numbers. Date/Time is used for
e borrowed, due, and return dates so dates can be
data sorted and calculated. Long Text is used for Notes
type because extra comments may be longer.
s?

Developing Databases Page 34

Developing Databases Page 35
 Whi LoanID would be the best Primary Key because
ch every loan needs a unique identifier. Using
field AutoNumber ensures each record has a different
woul number, which prevents duplicate records and
d makes it easier to find, update, or delete specific
mak loans.
e
the
best
Prim
ary
Key
and
why
?

Wha If the wrong data type is chosen, the database may

t not work correctly. For example, if DueDate was set
prob as Short Text instead of Date/Time, dates could not
lems be sorted properly or used in calculations. If Notes
coul was Short Text instead of Long Text, there may not
d be enough space for detailed comments.
occu
r if
the
wro
ng
data
type
was
chos
en?

Developing Databases Page 36

Developing Databases Page 37
 How Planning with a requirements table helps organise
does the database before creating it in Access. It ensures
plan the correct fields, data types, and keys are chosen
ning early, reducing mistakes later. This makes the
with database more accurate, efficient, and easier to
a manage.
requ
irem
ents
tabl
e
impr
ove
data
base
desi
gn?

Developing Databases Page 38

Developing Databases Page 39
 Data Types

Read the information below and use your highlighter pen to highlight the key information you find:

Every field in a table has properties and these properties define the field's characteristics and behavior. The most important property
for a field is its data type. A field's data type determines what kind of data it can store. MS Access supports different types of data, each
with a specific purpose.
Friday, May 1, 2026 8:08 AM

• The data type determines the kind of the values that users can store in any given field.

Developing Databases Page 40

• Each field can store data consisting of only a single data type.

Here are some of the most common data types you will find used in a typical Microsoft Access database.

Developing Databases Page 41

 Type of Data Des Size
crip
tion

Short Text Tex Up

t or to
co 255
mbi cha
nati ract
ons ers.
of
text
and
nu
mb
ers,
incl
udi
ng
nu
mb
ers
tha
t do
not
req
uire
calc
ulat
ing
(e.g
.
pho
ne
nu
mb
ers)
.

Long Text Len Up

gth to
y 63,
text 999
or cha
co ract
mbi ers.
nati
ons
of
text
and
nu
mb
ers.

Developing Databases Page 42

 Number Nu 1,
me 2,
ric 4,
dat or 8
a byt
use es
d in (16
mat byt
he es
mat if
ical set
calc to
ulat Rep
ion lica
s. tion
ID).

Date/Time Dat 8
e byt
and es
tim
e
val
ues
for
the
yea
rs
100
thr
oug
h
999
9.

Developing Databases Page 43

 Currency Cur 8
ren byt
cy es
val
ues
and
nu
me
ric
dat
a
use
d in
mat
he
mat
ical
calc
ulat
ion
s
inv
olvi
ng
dat
a
wit
h
one
to
fou
r
dec
ima
l
pla
ces.

AutoNumber A 4
uni byt
que es
seq (16
uen byt
tial es
(inc if
re set
me to
nte Rep
d lica
by tion
1) ID).
nu
mb
er
or
ran
do
m
nu
mb
er
assi
gne
d
by
Mic
ros
oft
Acc
ess
wh
ene
ver
a
ne
w
rec
ord
is
add
ed
to a
tabl
e.

Developing Databases Page 44

 Yes/No Yes 1
and bit.
No
val
ues
and
fiel
ds
tha
t
con
tain
onl
y
one
of
two
val
ues
(Ye
s/N
o,
Tru
e/F
alse
, or
On/
Off)
.

Here are some of the other more specialized data types, you can choose from in Access.

Developing Databases Page 45

 Data Types Des Size
crip
tion

Attachment File Up
s, to
suc abo
h as ut 2
digi GB.
tal
pho
tos.
Mul
tipl
e
file
s
can
be
atta
che
d
per
rec
ord
.
This
dat
a
typ
e is
not
ava
ilab
le
in
earl
ier
ver
sio
ns
of
Acc
ess.

OLE objects OLE Up

obj to
ect abo
s ut 2
can GB.
stor
e
pict
ure
s,
aud
io,
vid

Developing Databases Page 46

 io,
vid
eo,
or
oth
er
BLO
Bs
(Bin
ary
Lar
ge
Obj
ect
s)

Hyperlink Tex Up
t or to
co 8,1
mbi 92
nati (ea
ons ch
of par
text t of
and a
nu Hyp
mb erli
ers nk
stor dat
ed a
as typ
text e
and can
use con
d as tain
a up
hyp to
erli 204
nk 8
add cha
ress ract
. ers)
.

Lookup Wizard The De

Developing Databases Page 47

 Lookup Wizard The De
Loo pen
kup den
Wiz t on
ard the
ent dat
ry a
in typ
the e of
Dat the
a loo
Typ kup
e fiel
col d.
um
n in
the
Des
ign
vie
w is
not
act
uall
ya
dat
a
typ
e.
Wh
en
you
cho
ose
this
ent
ry,
a
wiz
ard
star
ts
to
hel
p
you
defi
ne
eith
er a
sim
ple
or
co
mpl
ex
loo
kup
fiel
d.

A
sim
ple
loo
kup
fiel
d
use
s
the
con
ten
ts
of
ano
the
r
tabl
e or
a
val
ue
list
to
vali
dat
e
the
con
ten
ts
of a
sing
le
val
ue
per
row
.A
co
mpl
ex
loo
kup
fiel
d
allo
ws
you
to
stor

Developing Databases Page 48

 Calculated You You
can can
cre cre
ate ate
an an
exp exp
ress ress
ion ion
tha tha
t t
use use
s s
dat dat
a a
fro fro
m m
one one
or or
mo mo
re re
fiel fiel
ds. ds.
You You
can can
des des
ign ign
ate ate
diff diff
ere ere
nt nt
res res
ult ult
dat dat
a a
typ typ
es es
fro fro
m m
the the
exp exp
ress ress
ion. ion.

These are all the different data types that you can choose from when creating fields in a Microsoft Access table.

Developing Databases Page 49

 Take the short quiz and record your score:

Score:

Quiz on MS Access Data Types – Fill out form

Developing Databases Page 50

 Create Tables

Data are stored in tables on databases and other database objects relies heavily on
these tables. Therefore any database design should start by creating all of its tables and
then creating any other object.

Thursday, April 30, 2026 12:48 PM

Task Video Tutorial Practice Reflection

1. Creat MSAcess_CreateTables1.mp4
ea
table
with
MS
Acce
ss

Table Design View

2. Impo
rtant
Task toVideo Tutorial Practice Reflection
2. Project data table
creat
ea
requi
Field Name reme Data Type
nt
table
prior
to
the
desig
n

Create a second table MSAcess_TableDesignView.mp4 What is the difference between the two ways to creating tables?
Before creating any tables, the requirements must be carefully considered to determine
using the table design
the
viewtables' needs.

Project ID AutoNumber

ProjectName Short Text

ManagingEditor Short Text

3. Follo
w
the
instr
uctio
ns in
the
Author video Short Text
to
creat
e
your
own
table

Task:
Scenario: School Equipment Loan Database

PStatus Short Text

Contracts Attachment

ProjectStart Date/Time

The data types were explained in the previous lesson refer to the table there to design

Developing Databases Page 51

Developing Databases Page 52
 ProjectStart Date/Time

The data types were explained in the previous lesson refer to the table there to design
the table requirements.

Tutorial Example: Employees database

ProjectEnd Date/Time

The school ICT department is having trouble keeping track of shared equipment such as laptops, cameras, chargers, and robotic s kits.

Budget Currency

ProjectNotes Long Text

1. Data types for tables needed:

Field Name Data Type

At the moment, items are borrowed informally and sometimes go missing, are returned late, or are damaged without a clear reco rd of who borrowed what and
EmployeelD AutoNumber
when.

FirstName Short Text

LastName Short Text

Address1 Short Text

Address2 Short Text

City Short Text

State Short Text

You have been asked to design a database using Microsoft Access to help the school store, organise, and manage information ab out equipment loans in a clear
and reliable way.

Developing Databases Page 53

Developing Databases Page 54
 Short Text

and reliable way.

Zip Short Text

Phone Short Text

Phone Type Short Text

Create Tables:

Before creating any tables in Access, you must first create a requirements table to plan the database structure properly.

1. Create a Requirements Table

Developing Databases Page 55

Developing Databases Page 56
 Create a requirements table below to plan one table for the database.

Your table must include:

Field Name

Data Type

2. Answer the following questions in full sentences:

Developing Databases Page 57

Developing Databases Page 58
 Why
did
you
cho
ose
thes
e
field
nam
es?

Why
did
you
cho
ose
thes
e
data
type
s?

Whi
ch
field
woul
d
mak
e
the
best
Prim
ary
Key
and
why
?

Wha
t
prob
lems
coul
d
occu
r if
the
wro
ng
data
type

Developing Databases Page 59

Developing Databases Page 60
 data
type
was
chos
en?

How
does
plan
ning
with
a
requ
irem
ents
tabl
e
impr
ove
data
base
desi
gn?

Developing Databases Page 61

Developing Databases Page 62
Data Input*

An Access database is not a file in the same sense as a Microsoft Office Word document or a Microsoft Office PowerPoint
are. Instead, an Access database is a collection of objects like tables, forms, reports, queries etc. that must work together for a
database to function properly.

Use the following raw data to organise into your Database tables using the methods explained in the tutorials below.

Task Instructions Practice Reflection

Friday, May 1, 2026 7:49 AM

Input Data add some data into your tables by opening the Add the file after you added the data What went well? Was there any
Access database we have created. shared on this page challenging steps?

Adding Data
Raw infor...

Developing Databases Page 63

 Select the Views → Datasheet View option in the ribbon
and add some data as shown in the following screenshot.

Developing Databases Page 64

 Similarly, add some data in the second table as well
as shown in the following screenshot.

Developing Databases Page 65

 You can now see that inserting a new data and
updating the existing data is very simple in
Datasheet View as working in spreadsheet. But if
you want to delete any data you need to select the
entire row first as shown in the following
screenshot.

Developing Databases Page 66

 Now press the delete button. This will display the
confirmation message.

Developing Databases Page 67

 Click Yes and you will see that the selected record
is deleted now.

Developing Databases Page 68

Developing Databases Page 69
 Scenario

Task 1: Data Requirements Table

You work for a school sports club that needs a database to keep track of
members and the sports they play.

Tuesday, May 5, 2026 7:50 AM

Before creating a database, you must plan what data is needed.

Task 3: Data Input

Reflection

1. Switch each table to Datasheet View

How well did your data requirements table

prepare you for building the database?

The club wants to store member details and basic information about each sport
so they can organise events and teams more efficiently.

Developing Databases Page 70

 Be prepared to briefly share your work with the class.

2. Enter up to 5 rows of realistic data in each table

Complete the table below to identify:

• What information is required

3. Which design
Make choice had the biggest
sure:
impact on how effective your database is?

○ DuringData matches
the share, youthe field data types
will:

• Show one table from your database

• The data type that should be used in Microsoft Access

○ No fields are left incorrectly formatted

Developing Databases Page 71

• Explain one design decision you made (for example: a field name, data type, or primary key)

(For example: field names, data types, or primary keys.)

Save your database file and upload it when finished.

• For reference go to Data Types and Create Tables lessons to remember example of Data
Requirements table

If this database were used by a real sports

club, what improvement would you make
to your structure or data?

• Justify why this decision improves the accuracy, organisation, or usefulness of your database

Developing Databases Page 72

 Data Requirements Table (add any needed rows)

Explain why this change would make the database more reliable or useful.

Field Name Desc Data

ripti Typ
on e
(Wh (Tex
at is t,
this Nu
data mbe
?) r,
Date
,
etc.)

Member ID Uniq Auto

ue num
ID ber
Num
ber
for
each
club
me

FirstName Me Shor

Developing Databases Page 73

 FirstName Me Shor
mbe t
r's Text
first
nam

LastName Me Shor
mbe t
r's text
last
nam
e

YearLevel Scho Num

ol ber
Year
Leve
l of
Me
mbe
r

DateJoined Data Date

Me /Tim
mbe e
r
Join
ed
the
Club

CoachID To Num
fine ber
out
Coac
h

Email Me Shor
mbe t
r Text
Emai
l
Addr
ess

Developing Databases Page 74

 SportID Uniq Auto
ue Num
ID ber
num
ber
for
each
spor
t

SportName Nam Shor

e of t
the Text
spor
t

CoachName F+L Nam Shor

e of t
Spor Text
t
Coac
h

TrainingDay Day( Shor

s) t
train Text
ing
is
held

TeamCapacity Maxi Num

mu ber
m
num
ber
of
play
ers
allo
wed

Developing Databases Page 75

 Training Time Tim Date
e of /Tim
train e
ing

You will need another table for the Sports table

Task 2: Create the Database

Developing Databases Page 76

1. Open Microsoft Access

2. Create a new blank database

3. Create two tables, for example:

○ Members

○ Sports

4. Use your data requirements table to:

○ Name fields correctly

○ Choose the correct data types

Developing Databases Page 77

 Choose the correct data types

Developing Databases Page 78

 Multi- Queries and Relationships

Analysing data to make a decision

Scenario: School Equipment Loan Database

Success Criteria
Sunday, 17 May 2026 3:20 pm

Learning Intentions:

Task Instructions Practice

All of you will:

The school ICT department is having trouble keeping track of shared equipment such as laptops, cameras, chargers, and robotic s kits.

Import .csv files into Download these three .csv files and save them into the OneDrive folder that you created at the start of
• Use queries to filter and analyse data
this unit and named it " MSAccess_Tutorials_YourName"
an Access Database
file

• Import .csv files into an Access Database file

• Combine data from multiple tables

• Create one-one relationship

At the moment, items are borrowed informally and sometimes go missing, are returned late, or are damaged without a clear reco rd of who borrowed what and
Students
when.

Loans

• Create a calculated field

Equipment

Watch the video then apply how to import .csv files into a MS Access database file:

• Create a query using data from more than one table

Developing Databases Page 79

Developing Databases Page 80
 • Use data to answer real-world questions

Thinking Like a Data Analyst

What question does your query answer? Which borrowed equipment items have the highest total value and who borrowed them?

• Apply basic filtering criteria

You have been asked to design a database using Microsoft Access to help the school store, organise, and manage information ab out equipment loans in a clear
and reliable way.

Answer in full sentences:

[Link]
[Link]/:v:/g/personal/ghada_fahmi_hillsgrammar_nsw_edu_au/IQAcM3gwH75rQZnRqY_gwRnTAaJyWUqRRXWexbXKKwbkvUk?
nav=eyJyZWZlcnJhbEluZm8iOnsicmVmZXJyYWxBcHAiOiJTdHJlYW1XZWJBcHAiLCJyZWZlcnJhbFZpZXciOiJTaGFyZURpYWxvZy1MaW5rIiwicmVmZXJyYWxBcH
BQbGF0Zm9ybSI6IldlYiIsInJlZmVycmFsTW9kZSI6InZpZXcifX0%3D&e=l2zn8I

What does your results show? The results show the equipment name, borrower, quantity borrowed, due date, cost, and the
calculated total value of each loan.

Most of you will:

• Use AND / OR conditions

Create one-one Connection

relationship tables in
Relationshi
ps is very
The item with the highest total value is the one shown at the top after sorting the TotalValue useful in
Which item has the highest total value?
field in descending order. databases
• Create a calculated field as it helps
identifying
common
fields

• to work with
records from
more than
one table,
you often
must create
a query that
joins the
tables.

List down three questions the ICT team might need to find the answer to regarding the borrowed equipment that we take from th e Help Desk office frequently:

• Sort results

Some of you will:

Why is this information useful for the It helps the school track expensive equipment, reduce losses, monitor borrowing records, and
manage resources more effectively.
school?

• The query
works by
matching the
values in the
primary key
field of the
first table
with a
• Design efficient queries for complex questions foreign key
field in the
second
table.

Developing Databases Page 81

Developing Databases Page 82
 Design efficient queries for complex questions

table.

What action could be taken based on this The school could follow up overdue loans, increase security for expensive items, repair
damaged equipment, or purchase more frequently borrowed items.
data?

• When you
design a
database,
you divide
your
information
into tables,
each of
which has a
primary key
and then add
foreign keys
to related
• Explain how query results support decision-making tables that
reference
those
primary
keys.

• These
foreign key-
primary
key
pairings for
m the basis
for table
relationships
and multi-
table
queries.

Watch the
video to
learn how
to create a
one-one
Relationshi
p then
apply what
you have
learned
and show
it in the
practice
field:

Developing Databases Page 83

Developing Databases Page 84
 [Link]
mmarnsweduau
-
[Link].c
om/:v:/g/person
al/ghada_fahmi
_hillsgrammar_
nsw_edu_au/IQ
DjqihR3DPGQob
r4DbmHcstAbc0
z-gOOA-
X6kQdeqWamV
s?
nav=eyJyZWZlcn
JhbEluZm8iOnsi
cmVmZXJyYWxB
cHAiOiJTdHJlYW
1XZWJBcHAiLCJ
yZWZlcnJhbFZpZ
XciOiJTaGFyZUR
pYWxvZy1MaW
5rIiwicmVmZXJy
YWxBcHBQbGF0
Zm9ybSI6IldlYiIs
InJlZmVycmFsT
W9kZSI6InZpZXc
ifX0%
3D&e=S0KoZk

Create a query using • Open Create

→ Qu ry
data from more than Design
one table

1. Add:

• Equip
ment
table

• Loans
table

2. Close the
window

3. Add the
following
fields:

• From Equip
ment:

• ItemN
ame

• Cost

• From Loans:

Developing Databases Page 85

Developing Databases Page 86
 From Loans:

• Borro
wer

• Quanti
ty

• DueDa
te

• Click Run (!)

You should
now see:

• Data
from
both
tables
combi
ned

• Use Create Total Value

AND
/ OR
con
ditio
ns

Go to Design View and create a new field:

• Crea
te a
calc
ulat
ed
field

TotalValue: [Quantity] * [Cost]

Click Run (!)

A new column called TotalValue is visible

Developing Databases Page 87

Developing Databases Page 88
 Sort results Under TotalValue, choose:

Descending

This shows the highest value items first

Click Run (!)

Developing Databases Page 89

Developing Databases Page 90
 Case Study

1. This is because Zoe needs to figure out the patients for diagnosis and treatment

Monday, 25 May 2026 12:23 P

2. Reception Staff

3. So each patient is their own

4. Because they are all on MS access

5. The way database contains stuff about them, like allergies and procedures

Developing Databases Page 91

6. This data was collected through a database like MS Access,

Information in an emergencyDoctor Zoë Knights is a specialist in accident and emergency

medicine, and works in a variety of hospitals around Sydney. She needs to access and
communicate information about patients quickly and accurately (Figure 10.5). When a
patient first arrives at the emergency department, the triage nurse allocates a category
number to indicate which cases are most urgent. This helps Zoë to prioritise the [Link]
reception staff enter all relevant details into the hospital administration system database. If
the patient has not been admitted to the hospital before, they will be allocated a medical
record number. This identifies the patient throughout the time they are in the hospital, and
will be used again if they are admitted in the future. Alternatively, the patient’s record can be
found by searching for their name and date of [Link] STUDY Figure 10.5 Emergency
vehicles need access to critical data fast!After treatment, Zoë types her diagnosis into the
computer, as well as any instructions for further care. The patient may be sent home,
admitted to a medical ward or prepared for [Link] patients might need a computed
tomography (CT) scan, a blood test or other tests or treatments. These results can be
entered directly into the patient’s record by the radiologist or the pathology laboratory. It may
be critical to refer back to this information while the patient is undergoing further treatment.
This information might be needed for a consultation with a specialist in another hospital or
another town. CT images in the database can be viewed remotely by authorised personnel
and options for treatment [Link] addition to the important medical notes for each
individual patient, a database is built for the entire Australian hospital system. This allows
analysis of data over many years, such as the number of patient admissions and the types
of emergencies that were treated. This data will affect the funding of hospital systems and
planning for the future -

Developing Databases Page 92

 Part 2

Here are three highly reliable data sources for each major field. These are widely used by
researchers, governments, universities, and international organisations because they provide large-
scale, validated, and regularly updated datasets.

Developing Databases Page 93

 Education

1. UNESCO Institute for Statistics (UIS)

• Best global source for education statistics

Developing Databases Page 94

• Covers literacy, school enrolment, graduation rates, gender equity, teacher ratios, and SDG 4
indicators

• Data from 200+ countries

Developing Databases Page 95

• Frequently used in academic research and UN reports ( UNESCO UIS)

2. OECD Education at a Glance

Developing Databases Page 96

• Excellent for comparing developed countries

• Includes student performance, tertiary education, education spending, and workforce

outcomes

Developing Databases Page 97

• Trusted because OECD uses strict methodology standards ( OECD)

3. World Bank EdStats

Developing Databases Page 98

• Large international database linking education with economic development

• Useful for trend analysis and policy research

Developing Databases Page 99

• Integrates UNESCO and national statistical data ( [Link])

Health

1. World Health Organization (WHO) Data

Developing Databases Page 100

• Gold standard for international health statistics

• Includes disease prevalence, mortality, vaccination, healthcare access, and life expectancy

Developing Databases Page 101

• Widely cited in medical and public health research

Developing Databases Page 102

 2. Our World in Data – Health

• Excellent for visualisations and long-term trends

• Combines WHO, UN, and academic datasets

Developing Databases Page 103

• Very useful for understanding global health patterns

3. Centers for Disease Control and Prevention (CDC) Data &

Statistics

Developing Databases Page 104

• Highly trusted US-based health data source

Developing Databases Page 105

• Strong for epidemiology, public health, and disease tracking

• Frequently used in scientific studies

Economics

Developing Databases Page 106

 Economics

1. World Bank Open Data

• One of the most trusted economic databases globally

• Includes GDP, poverty, inflation, employment, trade, and development indicators

Developing Databases Page 107

• Used heavily in economics research and policy analysis ( Reddit)

Developing Databases Page 108

 2. International Monetary Fund (IMF) Data

• Excellent for macroeconomic analysis

Developing Databases Page 109

• Provides data on inflation, debt, exchange rates, and financial systems

• Frequently used by governments and economists

Developing Databases Page 110

 3. OECD Data

• Strong for high-quality comparative economic data

• Includes labour markets, productivity, inequality, taxation, and education -economy links

Developing Databases Page 111

• Known for rigorous statistical standards ( OECD)

Why These Sources Are Trusted

Developing Databases Page 112

 These organisations are considered reliable because they:

• Use transparent methodologies

Developing Databases Page 113

• Collect data from national statistical agencies

• Verify and standardise datasets

• Update data regularly

Developing Databases Page 114

 Update data regularly

• Are widely cited in academic journals and government reports

Developing Databases Page 115

 School Café DataBaseMangagmentSystem Part 1
Friday, 29 May 2026 8:58 AM

Scenario:

Understanding the data storage and organisation Strawberry Hill High School recently
established a student-run cafe. The cafe was the initiative of the school’s careers adviser
and some students on the Student Representative Council. They saw the opportunity to
provide students with hospitality experience, while also providing staff and senior
students with a service that could build positivity about their day at school. With a
minimal budget and limited technology expertise, the cafe was initially set up and run
with basic data logging in spreadsheet software for stock and the use of paper-based
data storage and organisation, including customer loyalty cards and student rosters. The
cafe only sells drinks but does have plans for selling baked goods in the future. During
the first year, the cafe has become increasingly successful, resulting in greater data
processing demands and issues with errors due to its paper-based components. The
student managers have identified the need for a complete digital system. With the use of
a digital system they also see the potential for additional processes that could be used to
improve efficiency, customer experience and the further success of the cafe.

Identify the Entities

- Highlight the important stuff

Developing Databases Page 116

○ Student Run Café

○ Minimal Budget

○ Limited Technology Expertise

○ Basic Data logging in Spreadhseet software

Developing Databases Page 117

○ Paper based cards

○ Student Rosters

○ Only sells drinks

○ Plans for baked goods in the future

Developing Databases Page 118

○ Increasingly Successful

○ Greater data processing demands

○ Issues with errors due to its paper based componenets

○ Identified the need for a complete digital system

Developing Databases Page 119

 ▪ Improve Efficiency

▪ Customer Expierence

▪ Further Success

- Define the tables

○ Payments

○ Student helpers

Developing Databases Page 120

 ○ Studetns who payed

○ Rosters======

- Who are the users

○ Students

○ Teachers

Determine The Attributes

- Table Details

Developing Databases Page 121

 Table Details

○ Things sold is the amount of things sold

○ Sales amount is the amount of things sold

- Data Types

Payments

Thing Type Explana

tion

Sales Currenc The

y amount
of

Type Short The

sold Text types of
thing
sold at
the
Cantee
n

Developing Databases Page 122

 Amoun Numbe The
t of r amount
Sales of sales
that
happen

Numbe Autonu The

r of mber Numbe
Type r of the
sales -
makes
it easier
to
identify

Students

Developing Databases Page 123

 Students

Thing Type Explana

tion

Who Short
bought Text
it

Age Numbe
r

Diatary Short
Requir Text
ments

How Currenc
much y
paid

What Short
they Text
bought

Student Autonu
Numbe mber
r

Developing Databases Page 124

 Students Volunteers

Thing Type

Who Short
sold it text

Year Numbe
group r

How Numbe
much r
they
sold

Price of Currenc
what y
they
sold

Time Date/Ti
me

Developing Databases Page 125

 Time
me

Student Autonu
ID mber

Rosters

Thing Type

Student Shortte
xt

Student Autonu
ID mber

Time Date/Ti
me

Year Numbe
Level r

Item

Thing Type

ItemID Auto
Numbe
r

Developing Databases Page 126

 Item Short
Text

User questions

1. How much sales did the canteen make today?

2. How much stock do we have left?

3. How much drinks did we sell

Developing Databases Page 127

 How much drinks did we sell

4. How much people worked today

5. How much profit did we make

6. Is anythinh outdated

7. Who bought the things

Developing Databases Page 128

8. What did they buy

9. What diatary requirments did they have.

Developing Databases Page 129

 AI Data Generation**

Task 1:

Work with AI to create a .csv data file based on the fields and data types you designed last lesson.

Monday, June 1, 2026 8:20 AM

Prompt improvement ladder

Weak prompt

“Generate a CSV file.”

Developing Databases Page 130

 Better prompt

“Generate a CSV file with 100 rows using these fields: CustomerID, FirstName, LastName, Order, Date.”

Strong prompt

“Generate a downloadable CSV file with 100 rows for a school café database. Use these fields exactly: CustomerID,
FirstName, LastName, Order, Date. Make the orders realistic for a school café, use Australian-style dates, and ensure
the data is clean enough to import into Microsoft Access.”

Developing Databases Page 131

 Even stronger prompt

“Generate a CSV file with 100 rows for a school café database. Use these exact headings: CustomerID, FirstName,
LastName, Order, Date. CustomerID must be unique. Names should be realistic. Orders should suit a school café
menu. Dates should be formatted DD/MM/YYYY. Return the output as plain CSV only.”

. Enhance your prompts until you get a populated .csv file that can be used in your school cafe example

Developing Databases Page 132

 **Delete my attempts and fill with your own, and don’t use my examples above!

Attempt Task AI Prompt Used What AI Produced Problems I Noticed How I Improved My Final Evidenc
Prompt Decisio e of My
n Own
Thinkin
g

1 Genera “Generate a CSV file with 100 Sales,Typesold,AmountofSales,TypeNumber,Date It is not in a file Ask to put it in my file Change
te CSV rows for a school café the
data database. Use these exact prompt
headings: Sales, Typesold,
AmountofSales, TypeNumber,
Date. Date must be unique.
Names should be realistic.
Orders should suit a school
café menu. Dates should be
formatted DD/MM/YYYY.

Developing Databases Page 133

 data prompt

formatted DD/MM/YYYY.
Return the output as plain CSV
only.”

Ham Sandwich,Sandwich,24,1001,01/01/2025

Chicken Wrap,Wrap,18,1002,02/01/2025

Cheese Toastie,Hot Food,15,1003,03/01/2025

Veggie Roll,Snack,12,1004,04/01/2025

Developing Databases Page 134

 Veggie Roll,Snack,12,1004,04/01/2025

Apple Juice,Drink,20,1005,05/01/2025

Orange Juice,Drink,17,1006,06/01/2025

Chocolate Muffin,Bakery,14,1007,07/01/2025

Blueberry Muffin,Bakery,11,1008,08/01/2025

Developing Databases Page 135

 Banana Bread,Bakery,16,1009,09/01/2025

Chicken Burger,Hot Food,19,1010,10/01/2025

Beef Pie,Hot Food,13,1011,11/01/2025

Sausage Roll,Hot Food,21,1012,12/01/2025

Developing Databases Page 136

 Fruit Salad,Healthy,10,1013,13/01/2025

Greek Yogurt,Healthy,9,1014,14/01/2025

Caesar Wrap,Wrap,18,1015,15/01/2025

Turkey Sandwich,Sandwich,22,1016,16/01/2025

Developing Databases Page 137

 Egg Sandwich,Sandwich,12,1017,17/01/2025

Iced Coffee,Drink,15,1018,18/01/2025

Hot Chocolate,Drink,14,1019,19/01/2025

Chocolate Chip
Cookie,Bakery,20,1020,20/01/2025

Developing Databases Page 138

 Anzac Biscuit,Bakery,11,1021,21/01/2025

Mini Pizza,Hot Food,17,1022,22/01/2025

Chicken Nuggets,Hot Food,23,1023,23/01/2025

Garden Salad,Healthy,8,1024,24/01/2025

Developing Databases Page 139

 Tuna Sandwich,Sandwich,13,1025,25/01/2025

Strawberry Milk,Drink,16,1026,26/01/2025

Chocolate Milk,Drink,18,1027,27/01/2025

Vanilla Slice,Bakery,9,1028,28/01/2025

Developing Databases Page 140

 Spinach Roll,Snack,12,1029,29/01/2025

Ham and Cheese Toastie,Hot

Food,20,1030,30/01/2025

Chicken Sandwich,Sandwich,21,1031,31/01/2025

Veggie Wrap,Wrap,14,1032,01/02/2025

Developing Databases Page 141

 Apple Muffin,Bakery,10,1033,02/02/2025

Orange Poppy Seed

Muffin,Bakery,8,1034,03/02/2025

Beef Burger,Hot Food,16,1035,04/02/2025

Chicken Pie,Hot Food,11,1036,05/02/2025

Developing Databases Page 142

 Fruit Cup,Healthy,9,1037,06/02/2025

Berry Smoothie,Drink,15,1038,07/02/2025

Mango Smoothie,Drink,13,1039,08/02/2025

Oat Cookie,Bakery,12,1040,09/02/2025

Developing Databases Page 143

 Cheese Sandwich,Sandwich,17,1041,10/02/2025

BLT Sandwich,Sandwich,19,1042,11/02/2025

Chicken Caesar
Salad,Healthy,10,1043,12/02/2025

Water Bottle,Drink,25,1044,13/02/2025

Developing Databases Page 144

 Lemonade,Drink,14,1045,14/02/2025

Brownie,Bakery,18,1046,15/02/2025

Mac and Cheese Cup,Hot

Food,12,1047,16/02/2025

Veggie Burger,Hot Food,11,1048,17/02/2025

Developing Databases Page 145

 Ham Wrap,Wrap,15,1049,18/02/2025

Chicken Pasta,Hot Food,13,1050,19/02/2025

Cheese Pizza Slice,Hot Food,20,1051,20/02/2025

Pepperoni Pizza Slice,Hot

Food,18,1052,21/02/2025

Developing Databases Page 146

 Fruit Smoothie,Drink,16,1053,22/02/2025

Yogurt Parfait,Healthy,9,1054,23/02/2025

Banana Muffin,Bakery,10,1055,24/02/2025

Ham Burger,Hot Food,14,1056,25/02/2025

Developing Databases Page 147

 Chicken Roll,Snack,17,1057,26/02/2025

Veggie Sandwich,Sandwich,12,1058,27/02/2025

Milkshake,Drink,13,1059,28/02/2025

Chicken Toastie,Hot Food,15,1060,01/03/2025

Developing Databases Page 148

 Cheese Roll,Snack,11,1061,02/03/2025

Apple Slice,Bakery,9,1062,03/03/2025

Beef Sausage Roll,Hot Food,18,1063,04/03/2025

Chicken Salad,Healthy,10,1064,05/03/2025

Developing Databases Page 149

 Orange Smoothie,Drink,12,1065,06/03/2025

Ham Pizza Slice,Hot Food,16,1066,07/03/2025

Egg Wrap,Wrap,14,1067,08/03/2025

Banana Smoothie,Drink,13,1068,09/03/2025

Developing Databases Page 150

 Chocolate Brownie,Bakery,17,1069,10/03/2025

Turkey Wrap,Wrap,12,1070,11/03/2025

Chicken Burrito,Hot Food,15,1071,12/03/2025

Fruit Muffin,Bakery,8,1072,13/03/2025

Ham Roll,Snack,11,1073,14/03/2025

Developing Databases Page 151

 Ham Roll,Snack,11,1073,14/03/2025

Veggie Pizza Slice,Hot Food,14,1074,15/03/2025

Iced Tea,Drink,16,1075,16/03/2025

Chicken Club
Sandwich,Sandwich,18,1076,17/03/2025

Cheese and Crackers,Snack,10,1077,18/03/2025

Developing Databases Page 152

 Cheese and Crackers,Snack,10,1077,18/03/2025

Berry Yogurt,Healthy,9,1078,19/03/2025

Apple Juice Box,Drink,20,1079,20/03/2025

Chicken Panini,Hot Food,15,1080,21/03/2025

Developing Databases Page 153

 Ham Panini,Hot Food,13,1081,22/03/2025

Blueberry Smoothie,Drink,12,1082,23/03/2025

Chocolate Cupcake,Bakery,11,1083,24/03/2025

Vanilla Cupcake,Bakery,10,1084,25/03/2025

Developing Databases Page 154

 Chicken Quesadilla,Hot Food,17,1085,26/03/2025

Veggie Quesadilla,Hot Food,12,1086,27/03/2025

Fruit Skewers,Healthy,8,1087,28/03/2025

Orange Cake,Bakery,9,1088,29/03/2025

Developing Databases Page 155

 Ham and Salad
Sandwich,Sandwich,14,1089,30/03/2025

Chicken and Salad

Wrap,Wrap,16,1090,31/03/2025

Apple Danish,Bakery,10,1091,01/04/2025

Cheese Croissant,Bakery,11,1092,02/04/2025

Developing Databases Page 156

 Chicken Croissant,Sandwich,13,1093,03/04/2025

Fruit Yogurt Cup,Healthy,9,1094,04/04/2025

Mango Juice,Drink,15,1095,05/04/2025

Ham and Cheese Roll,Snack,12,1096,06/04/2025

Developing Databases Page 157

 Chicken Snack Box,Hot Food,14,1097,07/04/2025

Veggie Snack Box,Healthy,10,1098,08/04/2025

Chocolate Milkshake,Drink,16,1099,09/04/2025

Developing Databases Page 158

 Strawberry Smoothie,Drink,18,1100,10/04/2025

From <https ://[Link]/>

2 Make Can you turn it into a CSV file Your CSV file is ready: It worked It worked It
the file please worked

Download the CSV file

From <https ://[Link]/c/6a1cbdb2-d8a0-83ec-bed4-

da 8d6efc1d8b>

3 Make
data
realistic
for
school
café

4 Check
formatt
ing for
import
into
Access

Developing Databases Page 159

 Task 2 - similar to lesson "Multi- Queries and Relationship" on OneNote

1. Create a new MS Acces DM file

2. Import the file into MS Access

3. Set up the Primary Keys and data types

Developing Databases Page 160

4. Set up a one-one relationship

5. Using the questions you came up with last lesson, design yoru queries

Developing Databases Page 161

6. Present your File to class:

Add file link here with access:

Developing Databases Page 162

 Practice Scenario
Wednesday, June 10, 2026 11:49 AM

Australian Public Transport Usage Analysis

A transport planning team in New South Wales is analysing how people use public
transport, including trains, buses, and light rail services across different regions. The
team has access to publicly available datasets that include information such as
passenger numbers, routes, stations, and service usage over time. However, the data is
currently stored in raw files and is difficult to interpret. They need a database system
that can organise the data so they can identify trends such as the busiest routes, peak
travel times, and differences between locations. This will help improve transport
planning and decision-making across the state.

1. Identify Key Information from the Scenario

List the most important pieces of information from the scenario.

Data Sources
[Link] [[Link]]

Developing Databases Page 163

Developing Databases Page 164
• What is the main purpose of the system?

Search tips:

• Search “transport” or “Opal” or “passenger”

○ Organise the data

▪ Trends such as the busiest routes

▪ Peak Travel Times

• Filter by CSV format

▪ Differences between locations

• Choose a dataset (e.g. usage, routes, stops)

• Who will use the system?

○ Transport planning team in NSW

Developing Databases Page 165

Developing Databases Page 166
 ○ Transport planning team in NSW
• What data is being managed
Australian open government datasets (CSV filter) [NSW BioNet...d Heritage]

○ Busiest Routes

○ Peak Trave Times

○ Differences between locations

Search tips:

• Search for keywords like “transport”, “traffic”, “public transport”

2. Key Questions the Database Must Answer

Developing Databases Page 167

Developing Databases Page 168
 Write at least 3 meaningful questions that your database should be able to answer.

• Select a simple CSV dataset

Your questions should:

• Be relevant to the scenario

• Require data to answer

Developing Databases Page 169

Developing Databases Page 170
• Help solve the real-world problem

1. Which routes carry the highest number of passangers , and how does ridership vary by transport mode (train,
bus, light rail)

2. What are the peak travel times across different NSW regions, and how do morning and afternoon peaks
compare

Developing Databases Page 171

Developing Databases Page 172
3. Which stations or stops experience the greatest passanger volumes, and are there regional differences in usage
platform

Developing Databases Page 173

Developing Databases Page 174
3. What Data Will Be Needed?

Think about the information required to answer your questions.

Consider:

• What details need to be stored?

Developing Databases Page 175

Developing Databases Page 176
• What categories of information exist?

1. First question

a. Route & Transport

i. Route ID,

ii. Route name

iii. Transport mode

iv. Origin spot

v. Destination Spot

Developing Databases Page 177

Developing Databases Page 178
 b. Number 2

i. Direction

1) Inbound

2) Outbound

ii. Service Frequency

c. Number 3

i. Operator

ii. Agency responsible for each route

2. Second Question

a. First

i. Stop/Station ID

Developing Databases Page 179

Developing Databases Page 180
 ii. Name

iii. Address

b. Number 2

i. Scheduled departure

ii. Arrival Times

c. Number 3

i. NSW region

ii. Local Government Area

d. Number 4

i. Accessible facilities

3. Third Question

a. 1

i. Trip ID

b. 2

i. Scheduled Departure

ii. Arrival Times

Developing Databases Page 181

Developing Databases Page 182
 ii.

Arrival Times

c.

3
i.

Day Typoe

1)

Weekday

2)

Weekend

3)

Public Holiday

d.

4
i.

Route

ii.

Vehicle Information

4. How Is the Data Connected?

Explain how different pieces of data might relate to each other.

Developing Databases Page 183

Developing Databases Page 184
 For example:

Are there different groups of data (tables)?

Do some types of data link together?

Developing Databases Page 185

Developing Databases Page 186
 There are different groups of data

It will be organised in X tables.

1.

Route

a.

RouteID

b.

Route Name

2.

Modes

a.

ModeID

b.

ModeName

3.

Regions

a.

RegionID

b.

RegionName

4.

Stops

a.

StopID

b.

StopName

Developing Databases Page 187

Developing Databases Page 188
 StopName

c.

RegionID

5.

RouteStops

a.

RouteStopID

b.

RouteID

c.

StopID

d.

StopSequence

6.

DailyRidership

a.

Ridership ID

b.

RouteID

c.

ServiceData

d.

TotalPassangers

Developing Databases Page 189

Developing Databases Page 190
7.

StopUsage

a.

UsageID

b.

StopID

c.

ServiceData

d.

TimeInterval

e.

Boardings

f.

Alightings

8.

Trips

a.

TripID

b.

RouteID

c.

ServiceData

d.

DepatureTime

e.

ArrivalTime

Developing Databases Page 191

Developing Databases Page 192
 f.

VehicleId

9.

TripStopTimes

a.

TripStopTime

b.

TripID

c.

StopID

d.

ArrivalTime

e.

DepatureTime

10.

PeakPeriods

a.

PeakID

b.

PeakName

c.

StartTime

d.

EndTime

11.

RoutePeakUsage

Developing Databases Page 193

Developing Databases Page 194
11.

RoutePeakUsage

a.

RoutePeakUsageID

b.

RouteID

c.

PeakID

d.

ServiceData

e.

Passengers

5. Summary (Short Response 3–4 sentences)

Summarise your understanding of the scenario:

Developing Databases Page 195

Developing Databases Page 196
 Explain:

What the database needs to do

Why it is needed

The scenario asks us to construct a few tables on a Data Base Management System based on the transportation
data. This helps them understand route times and passangers.

Developing Databases Page 197

Developing Databases Page 198
6.

Create the requirement tables

Ensure that you have the field name they way you will add it to the DB file

Add the data type you will use

Developing Databases Page 199

Developing Databases Page 200
 Add the data type you will use

Add an explanation of what you need this field and explain briefly the data type choice

7.

Download the .csv files

Organise the data if needed to match the requirement tables

Developing Databases Page 201

Developing Databases Page 202
8.

Import to MS Access DB

9.

Create relationships

10.

Design and run queries aligned with the questions you came up with.

Developing Databases Page 203

Developing Databases Page 204
Developing Databases Page 205
Developing Databases Page 206