BTEC Level 3 Database Exam - Part A - Walk through - Part2

Summary

The video provides a detailed tutorial on setting up tables in Microsoft Access, focusing on using appropriate data types, creating primary and foreign keys, and establishing relationships between tables. It covers essential practices like using short text for phone numbers to preserve leading zeros, creating input masks for text fields, and applying validation rules to ensure accurate data entry. The tutorial also demonstrates how to enforce referential integrity and cascade updates/deletes to maintain consistency across related tables, concluding with a walkthrough of viewing relationships and linked data entries.

Takeaways

• 🔑 The task involves creating table structures and applying appropriate data types and validations in Microsoft Access.
• 🗃️ Primary keys will use auto numbers, and short text will be applied for fields like names and telephone numbers.
• ☎️ Telephone numbers are stored as short text to avoid issues with leading zeros being removed in number format.
• 📝 Foreign keys will be stored as numbers to align with the auto number primary keys in related tables.
• 📊 Users must apply data validation and input masks to ensure proper data entry and formatting (e.g., capitalizing first letters in names).
• 📐 It's essential to change field sizes and not rely on default settings, especially for fields like names and telephone numbers.
• 🔗 Relationships between tables are established by connecting foreign keys to primary keys, creating one-to-many relationships.
• 🛠️ Users should enforce referential integrity and enable cascading updates and deletes to maintain data consistency.
• 💡 Lookup tables help users avoid remembering ID numbers by providing drop-down lists for easy data entry.
• 🔍 Presence checks and validation rules (e.g., 'Is Not Null') are used to ensure required fields are filled in, improving data accuracy.

Q & A

What is the primary reason for using short text instead of a number data type for telephone numbers?

Short text is used for telephone numbers because if a number data type is chosen, the leading zero will be omitted when inputted into the database. To preserve the format, especially for phone numbers starting with zero, short text is more appropriate.

Why is it important to change the field size for short text fields such as names in the database?

It’s important to change the field size for short text fields to ensure that they are appropriately sized for the data that will be entered. The default size might be too large or too small for specific fields like names, and setting a proper size (e.g., 25 characters) prevents wasted space or truncation.

What is an input mask, and how is it used in this database setup?

An input mask is used to control how data is entered into a field. In this setup, it is used to ensure that the first letter of names (like forename and surname) is capitalized. The mask includes a greater-than symbol (>) for capitalization followed by a capital 'L' for the first letter and a series of question marks for the remaining characters.

Why are auto numbers used for primary keys in tables?

Auto numbers are used for primary keys because they automatically generate unique values for each record, ensuring that every entry in the table has a unique identifier without manual input. This helps maintain data integrity, especially in relational databases.

What role does the foreign key play in relational databases, as discussed in this script?

A foreign key in relational databases links two tables by referencing the primary key of another table. It ensures that relationships between records in different tables can be established, such as linking customer records to ticket sales.

What is the purpose of the 'presence check' mentioned in the script?

The presence check ensures that certain fields, like first name or surname, are not left empty when inputting data. It is implemented through a validation rule ('Is Not Null'), which forces users to enter a value for required fields.

What is the purpose of enforcing referential integrity in the relationships between tables?

Enforcing referential integrity ensures that relationships between tables remain consistent. For example, it prevents deleting a record in one table that is still being referenced by a foreign key in another table. It also allows cascading updates and deletions to maintain data consistency across related tables.

Why is a lookup table used for fields like customer type?

A lookup table is used to make it easier for users to select predefined values (e.g., customer types such as 'regular' or 'guest') from a dropdown list instead of manually entering data, reducing input errors and standardizing entries.

What is the purpose of creating meaningful names for database tables and fields?

Meaningful names help make the database structure more understandable and easier to work with, especially for others who may use or maintain the database. For example, naming a table 'tbl_Customer' instead of 'Table1' clearly indicates the table's content.

Why is it important to set validation rules and provide validation text in the database?

Validation rules ensure that the data entered into a field meets specific criteria (e.g., presence checks), while validation text provides users with feedback when their input doesn’t meet the required conditions. This helps prevent errors and improves data quality.

Outlines

00:00

🧑‍💻 Understanding Table Structures and Validations

This section introduces the task of setting up table structures and validations in a database. It discusses selecting the right data types for various elements in the table, such as auto numbers for primary keys, short text for names, and telephone numbers. The importance of matching foreign keys with primary key data types for relationship consistency is also emphasized. The process of entering the data types and validation rules in Microsoft Access is outlined, including creating and naming tables, and ensuring relationships work properly.

05:01

🔠 Naming Conventions and Input Masks for Customer

This section explains the importance of using consistent naming conventions and input masks for fields such as telephone numbers and zip codes. It highlights best practices for ensuring data integrity and user-friendly database management.

Keywords

💡Table Structure

A table structure refers to the organization of data within a database table, including the fields, data types, and relationships between tables. In the script, it is emphasized that designing the correct table structure, including using appropriate data types, is crucial to ensuring accurate data storage and relationships between tables.

💡Data Types

Data types define the kind of data that can be stored in a field of a database. Examples include 'short text' for names and 'auto number' for primary keys. In the video, data types are carefully selected for each field, such as using 'short text' for phone numbers instead of 'number' to prevent the loss of leading zeros.

💡Primary Key

A primary key is a unique identifier for each record in a table. It is used to ensure that each entry is unique and can be referenced elsewhere. In the video, the primary key is often set as an 'auto number' to automatically generate a unique identifier, such as 'customer_id' or 'ticket_type_id'.

💡Foreign Key

A foreign key is a field in a table that links to the primary key of another table, establishing a relationship between the two tables. The script discusses the importance of using foreign keys, such as 'customer_type_id', to link tables and create relationships between customer data and ticket sales.

💡Input Mask

An input mask is a rule applied to a field to control how data is entered and displayed. For example, a mask can enforce the first letter of names to be capitalized. In the video, an input mask is applied to fields like 'full name' and 'surname' to ensure consistency in data entry.

💡Validation Rule

A validation rule ensures that data entered into a field meets certain criteria. In the script, validation rules like 'Is Not Null' are used to ensure that critical fields such as 'first name' and 'surname' are not left empty. This helps maintain the integrity of the data.

💡Design View

Design View is a mode in database applications like Microsoft Access where users can define table structure, including field names, data types, and validation rules. The video demonstrates switching to Design View to create and customize tables, such as 'tbl_customer' and 'tbl_ticket_type'.

💡Auto Number

Auto number is a data type used for fields where a unique number is automatically generated for each new record. In the script, the 'auto number' data type is used for primary keys like 'customer_id' to ensure each customer has a unique identifier automatically assigned.

💡Lookup Wizard

The Lookup Wizard is a feature in Microsoft Access that allows users to create a field that looks up values from another table or a list of options. In the video, the Lookup Wizard is used to create dropdowns for 'customer type' with options like 'Guest', 'New', or 'Regular', making data entry more user-friendly.

💡Referential Integrity

Referential integrity ensures that relationships between tables remain consistent, preventing orphaned records or mismatched data. In the video, referential integrity is enforced when linking tables, ensuring that updates or deletions in one table automatically cascade to related tables.

Highlights

Introduction to task 2, focusing on table structures and validation.

Emphasis on using the correct data types for table elements, including primary and foreign keys.

Primary keys should use auto numbers, while short text is used for fields like names and telephone numbers.

Explanation of why short text is used for telephone numbers instead of numbers, to preserve leading zeros.

The importance of matching data types between foreign keys and primary keys to maintain relationships.

Step-by-step guide on creating a blank Access database and setting up tables with appropriate names and data types.

Using 'tbl_' as a naming convention for tables to ensure clarity and organization.

Creating an input mask for name fields to automatically capitalize the first letter.

Detailed process of adding a telephone number field with a validation rule to ensure it accepts only digits.

Explanation of creating a customer type foreign key in the customer table for future relationships.

Importance of reviewing and checking data types, field sizes, and input masks for accuracy before proceeding.

Step-by-step instructions for setting up a new table for ticket types, including cost fields with currency format.

Introduction of 'presence check' validation to ensure fields are not left blank.

Utilizing table lookups to simplify referencing values for foreign keys, such as customer types.

Establishing relationships between tables by linking primary keys and foreign keys, enforcing referential integrity.

Transcripts

in task 2 we're going to be looking at

the table structures and validations

going to be required to be

input into our tables themselves

so as you can see we have our four

tables within these tables we're going

to need to make sure that we're using

the right data types for each of the

elements inside

these data types will range from primary

keys which will be using auto numbers

they'll be also using short text for

things like names

and surnames we're also going to be

using things like short text

for our telephone numbers as a telephone

number starts with a zero

and if we use a number in this instance

the zero will be emitted

when we put the data set in when we deal

with our foreign keys

we need to make sure that these foreign

keys are going to be using a number

as when we use auto number and move into

our relationships

the numbers generated will need to match

the data types

in each of the tables so we place our

foreign keys as numbers

so now let's jump into the access

database program

and start to apply our data types

structures

and validation to our tables so that

when we create relationships they work

properly

once we gain access to access database

we're going to need to choose

to create a blank database we do that by

simply selecting the blank database

option

access is very good because this forces

us to create an

appropriate name to save our database

with

straight from the outset so make it that

it's meaningful

and in this instance we're going to use

part a

and click on the create or press enter

what happens is you'll be displayed with

this data area

that we want to change by changing the

view

and we're going to change it to design

view and again this forces us to create

and save our tables as we go along we

need to make sure that we're using an

appropriate naming convention

and in this instance we're going to use

tbl to represent table

then underscore and customer

click ok and this puts us into the

design view stage

here you can see that we all have a

primary key

and that it's automatically set as an

auto number

but this primary key doesn't really have

much of a meaning so we're going to put

some information in front of it

and we're going to put customer and use

it underscore rather than a space

notice the little key on the side shows

that it's primary key

and on the description element it's

always good practice to make sure that

we put something in here to make sure

that

if anybody else was to pick up the

database they know what this is for

so as you can see this is a unique

identifier

and that is with the information that

we're going to be putting inside of our

optional area for the description

you can also put in here some

information about the

type of data that you're going to be

putting in here or the data type

so as you can see we put that that's

going to be auto number

and if we do anything like an input mask

or a

piece of data validation we can also put

that information in there

so that anybody looking at the table

from a snapshot point of view

they'll be able to see what we're going

to do the next one we're going to put in

here is going to be the full name

and we're going to choose a data type

that's appropriate for name

and this is going to be our short text

option

as you can see there are a variety of

choices we can make here

but short text is the most appropriate

down at the bottom we have our

properties

and in the properties for this field

name we can choose to change the field

size

do not leave this as the default as this

is very important that we need to change

these to 25

or anything most appropriate for the

field information that's going to be put

in there

so we need to move on to the next part

and

we're going to make making an input mask

for this

instance of our field name and

we're going to be putting some

information in to make sure that any

entry

will capitalize the first letter so what

we do is do a greater than symbol

a capital l to represent a capital

letter

then a less than symbol and then a

series of question marks

once we've done that what we're going to

do is we're going to copy that and we're

going to put it in the description

just so that it's clear to anybody as we

said before

that there is an input mask attached to

this

entry or field name so

because we have 25 characters in our

field size

it'll allow us to make sure that the

first one of any entry will be

capital letter now we're going to move

on to

the next entry within our field name

we're going to use surname

we're going to also choose this to be

short text and we're going to

do the same again as we did with our

full name and make sure that there's an

input mask in there to

capitalize the first letter for us

and again good practice just put some

information inside of the description

so it's clear to users what's going on

and as i didn't put it in the aboriginal

four name one i'm going to put in there

the

input mask as well and that's going to

be greater than l

capital l less than and then a series of

question marks after

and just for good practice i will update

that one now so there's got four name

and it's got the description to the

input master that's there as well

then as you can see on the left hand

side the last one that we need to put in

is a telephone number

now this is very much a tripping block

for most students when it comes to

doing the exam because they

instinctively want to choose number

because it is a phone number but

unfortunately access doesn't deal with

numbers in the same way that we'd expect

it to

because it'll emit the zero at the

beginning so what we need to do is

choose a short text option

and what we can also do is we can define

the field length to the standard length

of the phone number

and this is around 11 characters

without any area code or

country codes added

so what we're going to do is just again

put some information in the description

just to make sure that it's clear that

this is going to be short text because

the 0 would be removed

and again this will allow us when it

comes to our exams

to show that we understand why we're

doing something and the reasons behind

what we've done

but what we can also do is if we go down

to the properties

we're going to change the field size as

mentioned to the standard size of a

phone number which is approximately

or is 11 characters or digits long

and we can also make sure that we've got

some form of input mask

attached to that as well the input mask

can allow only numbers to be entered in

to our short text field so in order for

us to do that

we can do the following

so we're going to add a validation rule

to this field name and we're going to go

into the validation rule section

in order for so we're going to add a

validation rule to this

exclamation mark zero

hyphen nine close the square bracket

and then star again

once we set everything how we want it to

be in the table it's good practice just

to go back over the field names and

check

that all our data types are correct the

field sizes are also correct

and any input masks are also correctly

input

this is an opportunity for us to make

sure that there's going to be no issues

coming into the relationships later on

or even if when we come to put the data

into the database

and finally we're going to add the

customer underscore type

underscore id as a foreign key into our

customer table so that we can see what

customer types

are inside of our customers

lastly once we've done all that we need

to click on save

and close

next we're going to create another table

and this time we click on the design

view

we're going to make sure that this table

is called

tbl underscore

ticket underscore type

again as we did with the customer table

we're going to jump into our data view

and we're going to change the id to be

something meaningful so we're going to

put in ticket

underscore type

underscore id we're going to leave the

data type to be an auto number

and we're going to put some information

inside the description area just to make

sure that it's clear that we know what's

happening

and we're going to put as primary key

next we need to look at our ticket

underscore type

and this is going to be a short text

next we move on to the ticket

underscore cost

this time around we need to change the

data type

to be currency

and if we move down to the bottom part

of the properties you'll see that the

format is that this is in

currency here we can choose to change it

if we was using european information we

could change the format to be

in a euro but obviously we're dealing

with

uk so we're going to check leave it as

pounds

and we're going to go back up to our

description section and just add

a little bit more information in

so as you can see here i've added some

information relating to the ticket types

we've got our friday saturday

and our camping

now we're going to move on to the next

table which is tbl

underscore ticket

sales

this table is going to be dealing with a

primary key again

but this time around we're going to put

ticket

underscore number

and instead of the data type being an

auto number we're going to change this

we're going to change this to number

because auto number starts from 0 or

from 1

the next number will be 2 3. as you can

see in our example

inside of our exam the number starts at

1000 and then goes 1001

2 3 and so on

so what we're going to do is we're going

to leave it as a primary key but we're

going to define the number ourselves

so we're going to allow the user to

input the ticket number

then we're going to place in our first

of three

foreign keys the next foreign key we're

going to place in

is customer underscore id this will

allow us to relate

the ticket number to an appropriate

customer

and then finally we're going to select

ticket

underscore type underscore id

again choosing the id

to be a number and this will allow us to

work out what type of

ticket that the person has bought

whether it's a camping

or a friday or saturday ticket

now that we have all of our tables we

want to now add something that's known

as a presence check

a presence check will make sure that

there is something entered inside of our

fields

and to do that we can go inside of the

validation rule option

go to the very end of the row and you'll

find there

is a button with three dots on it if you

click on that that will open up the

expression builder

inside the expression builder we can

start to formulate and build our

rules so that we can make sure that

something is true

so what we can do is we can set a

validation rule of is

not null this will make sure that

this value cannot be omitted or missed

and that a value has to be entered

additional to a validation rule it's

always good practice to add some

validation text

this text will allow the user to see

that there is a

required element inside of the actual

field and the message is asking them to

please enter

a customer first name

as we did with the full name we're going

to follow through and use this on the

surname

so we're gonna open our expression

builder and type in

is not null and then click ok

and as before we're going to make sure

that we've got some validation text that

asks the person to please enter in

a surname

at this stage we're going to be looking

at how we can make it easy for our users

to use the system

as you can see we're looking at the

customer type id here

and we don't want to remember an id

number to

reference to a value so what we can do

is we can use what's known as a

table lookup in order to do that select

the lookup option

and then inside here we can choose an

option to list

box choose our row source to be a table

or query

we're going to select the table that we

want and then we're going to bound

the column to which the value is going

to be coming from so that's going to be

the first column

inside of the customer type table

and we're going to show two columns in

the column count

so as you can see from the example here

we're just entering in some ticket

number details

and we're assigning that ticket number

to a customer

customer number one and then you'll see

now that we've added

the lookup table that we can actually

choose from an option

of friday saturday and camping

we're now going to create another table

this time it's going to be our

customer type table so go ahead and

create a new table

and then when you go inside we're going

to name this one tbl

underscore customer underscore type

in here we're going to give our first

field the

primary key and we're going to make this

the customer underscore

type underscore id i'm going to leave

this as an auto number

and we're going to put some information

inside of the description just to remind

ourselves that this is actually

the primary key

then we're going to move on and make

something that's very new to us i'm

going to use what's known as a value

lookup

so in order for us to create a value

lookup we're going to create a new field

and we're going to call it customer type

and this time inside of our data or the

data type area

we're going to choose from the very

bottom of the options that we have

available

the lookup wizard

in here we want to type in the values

that i want

this will open up a dialog box which

will allow us to enter in the

information we want

and how relevant it will be

so in here we're going to give the

option of guest of organizer

we're going to make a value for new and

we're going to put the final one which

is regular

we can add more columns if we want to

but this would be sufficient for us

right now

what should happen once we've selected

that

and we're going to add some information

into the description area

this is going to be just information

that is drop down box

of the options available

i'm going to enter the table and we're

going to look and we're going to apply

our

customer id types so as you can see from

the drop down box we have regular

we're going to do one for new and we're

going to do one for the guest organizer

we're going to save that

relationships within relational

databases

are key to their success relationships

work by having a foreign key and a

primary key

link two three or even more tables

together

so that data can be referenced between

them

in our example of this exam paper we're

going to be pulling in

all of the four tables that we have

choose the relationships option inside

of access

and then using the dialog box open all

of

the tables so that they display as so

if you have them open you're going to

need to close them so that we can then

see

in our relationships area all of the

tables

here you'll notice that you can see the

primary keys inside of each of the table

in order for relationships to be

established between the tables

simply drag the primary key into the

location where the foreign key is

located

you'll notice that the relationship type

is a one to many

we need to enforce the referential

integrity and ensure the cascade update

and cascade delete options are selected

i'm going to move over to ticket type

and i'm going to drag the ticket type

into

the ticket sales id make sure there's a

one-to-many relationship there

and finally we're going to move the

customer id out of the customer table

and place this inside the ticket cells

we're going to enforce the referential

integrity cascade the update

and cascade delete and click create

now we should have our relationships

between our tables established

and hopefully that will mean that the

data will cascade between the two or

three or four tables

so that we know that we've got the

correct information present

so what i'm going to do now is just open

up the tables just to see if the

relationships are established between

them

and i can notice as soon as i've opened

some of the tables that there are little

plus signs next to

the primary key if we click on these

they'll expand open and they'll see the

links between

the data so we can see that mildred

mitchell our customer id number one has

bought ticket number

1000 and 1001 and we can see that

eric ferguson has bought ticket number

1004

1005 but this works for all of the

tables so we can see

how many people or what tickets were

linked to

new customers or regular customers or

organizing customers

or guest organizers and we can see what

tickets are

for what days as well so you can see

that there's an allocation there for

camping tickets saturday tickets and

friday tickets