CHAPTER 18: DATABASES

CONTENT

• 18.1 Create a database structure.

• 18.2 Manipulate data
• 18.3 Present data
 18.1 CREATE A DATABASE STRUCTURE.

• 18.1.1 Create a database structure

• 18.1.2 Flat-file Database Example
• 18.1.3 Relational databases
• 18.1.4 Advantages and disadvantages of using flat-file and relational databases
• 18.1.5 Data Types and sub-types
• 18.1.6 Create a flat-file database from an existing file
• 18.1.7 set appropriate data types
• 18.1.8 Enter data using a table
 18.1 CREATE A DATABASE STRUCTURE.

• 18.1.9 Add field to existing table

• 18.1.10 Add a field to the existing table
• 18.1.11 Create a data entry form
• 18.1.12 Edit a data entry form
• 18.1.13 Add a new field to an existing form
• 18.1.14 Edit a data entry form
• 18.1.15 What does a well-designed form look like?
 18.1.1 CREATE A DATABASE STRUCTURE

• What is a Database?
• Organised collection of data
• Software stores & retrieves data in a structured way
• Data includes:
• Fields
• Records
• Files
 FIELD, RECORD, AND FILE

• Field: Single data item (e.g. Forename, e.g. Name, DOB)

• Has a name & data type (text, number, date, etc.)
• Record: collection of fields (e.g. one person)
• A row – group of related fields
• File: collection of records, may contain tables
• Organised collection of records
• Often seen as a table in a database
FIELD, RECORD, AND FILE
 TYPES OF DATABASES

• Flat-file database:
• Data stored in one table (rows & columns)
• All records are stored together
• Relational database:
• Data stored in multiple linked tables
• Avoids repeating data
• Tables linked by relationships
 18.1.2 FLAT-FILE DATABASE
EXAMPLE

• One table contains all teacher & student data

• Repetition of teacher info (name, subject, room)
• Difficult to update data – must change in all rows
• Uses more storage space
• Example: Teacher info repeated for every student taught
FLAT-FILE DATABASE EXAMPLE
 RELATIONAL DATABASE EXAMPLE

• Data split into 2 tables: Teachers & Students

• Linked using Teacher_ID
• Each teacher appears once
• Each student is linked to a teacher
• Creates a one-to-many relationship
RELATIONAL DATABASE EXAMPLE
RELATIONAL DATABASE EXAMPLE
 18.1.3 RELATIONAL DATABASES

Keys in Relational Databases

• Primary Key:
• Uniquely identifies each record in a table
• Example: Teacher_ID or Student_ID
• Foreign Key:
• Field in one table that links to the primary key in another
• Creates relationships between tables
 ONE-TO-MANY RELATIONSHIP

• One teacher → teaches many students

• Common type of relationship in schools, companies, etc.
• Makes searching and linking records easier
 18.1.4 ADVANTAGES OF
RELATIONAL DATABASES

• No repeated data – reduces storage use

• Easier to update – change data in one place
• More efficient for large datasets
• Better for producing reports
• Clear structure – better data integrity
 18.1.4 DISADVANTAGES OF FLAT-
FILE DATABASES

• Data redundancy – repeated info

• Wasted storage space

• Harder to update – multiple changes needed

• Risk of inconsistent data

• Less suitable for complex data relationships

 SPEED & PERFORMANCE

• Relational DBs not always faster

• Depends on:
• Use of indexed fields
• Size of data
• Database structure
• Flat-file may be faster for small/simple datasets
 DATABASE MANAGEMENT
SOFTWARE

• Microsoft Access (part of MS Office)

• Flat-file when using 1 table

• Relational when using multiple linked tables

• Other tools: MySQL, SQL Server, Oracle, etc.

• Supports adding, editing, deleting records

 18.1.5 DATA TYPES AND SUB-TYPES

• Data type defines how data is stored and used

• Set a data type for each field in a database

• Helps prevent data entry errors

• Limits what kind of data can be stored

 ALPHANUMERIC (TEXT) DATA TYPE

• Stores letters, numbers (not used for calculations)

• In Access: called Text

• Use for names, telephone numbers, registration numbers, etc.

• Can be:
• Short Text (up to 255 characters)
• Long Text (for longer entries)
 NUMERIC DATA TYPE

• Stores numbers used for calculations

• In Access: called Number

• Do NOT use for phone numbers or IDs

 NUMERIC SUB-TYPES

• Integer: whole numbers only

• Use Long Integer for 3+ digits

• Decimal: supports decimal places

• Can limit the number of decimals

• Currency: formatted numbers for prices

• Includes currency symbols in display

• Date/Time: stores date and time values as numbers

 BOOLEAN (LOGICAL) DATA TYPE

• Stores True/False, Yes/No, or 0/1

• In Access: called Yes/No

• Used for fields like:

• "Does the car need cleaning?"
• "Is the item in stock?"
 CHOOSING THE RIGHT DATA TYPE

• Carefully select data types to prevent data loss

• Example:
• Integer field won't store decimal values (e.g., 3.8 → 3)

• Wrong data type = lost or corrupted data

 Field Name Best Practices

•Use short, meaningful names

•Avoid spaces in field names (e.g., P_Price instead of Purchase Price)
•Consistency improves readability & reduces errors
•Use prefixes if helpful (e.g., PPrice, SPrice)
 TASK 18A

Field Description Data Type Notes

Who manufactured the car? Text Use field name: Make

Model Text Field name: Model

Colour Text Field name: Colour

Purchase price Currency 2 decimal places – PPrice

Selling price Currency 2 decimal places – SPrice

Year Integer Field name: Year

Extras Text Field name: Extras

Does the car need cleaning? Boolean (Yes/No) Field name: Clean
 Final Setup Instructions

•Edit field names directly in Excel before importing .csv

•Save updated file as [Link]
•Add Primary Key field if required (e.g., CarID)
•Save and import data into Microsoft Access
 18.1.6 CREATE A FLAT-FILE DATABASE
FROM AN EXISTING FILE

• Create a Blank Database

1. Open Microsoft Access.
2. Click on Blank Desktop Database.
3. Use the Browse button to choose the folder where you want to save
your database.
4. Type in the filename task18a.
5. Click Create.
• This opens a new, empty database.
 Import the CSV File

[Link] to the External Data tab.

[Link] the Import & Link section, click on New Data Source.
[Link] the drop-down menu select From File → Text File (because .csv is a text file).
[Link] Get External Data – Text File window will open.
[Link] the Browse button to find and select [Link].
[Link] the top radio button:
•Import the source data into a new table in the current database.
•(Do not select the linking option by mistake).
[Link] OK.
 Use the Import Wizard

1. The Import Text Wizard will open.

[Link] Delimited (since a .csv file uses delimiters).
[Link] Next.
[Link] sure Comma is selected as the delimiter.
[Link] whether the first row of your file contains field names.
•If it does, tick First Row Contains Field Names.
•You will see the first row change to proper field headers.
[Link] the Advanced button if you need to open the Import
Specification window (to adjust data types, field lengths, etc.).
[Link] the wizard by assigning a name to the new table (or accept
the default).
 18.1.7 SET APPROPRIATE DATA TYPES
(TASK 18A)

[Link] & Set Correct Data Types

•PPrice and SPrice: Change data type to Currency
•Valet: Change data type to Yes/No (Boolean)
[Link] Decimal Places
•In Design View, set Decimal Places to 2 for both PPrice and SPrice
[Link] Boolean Field
•In Valet field → Under General tab, set Format to Yes/No
 18.1.7 SET APPROPRIATE DATA TYPES
(TASK 18A)

[Link] & Examine Data File

•Open .txt or .rtf in Notepad
•Identify the separator (comma, tab, semicolon) and use it
during import
[Link] Data into Access
•During import, select: Let Access add primary key
•Access adds a new ID field as the Primary Key
[Link] the Table: tblCars
[Link] the Database as: task18a
 18.1.8 ENTER DATA USING A TABLE
(TASK 18B)

1. Open Database
•Open the database saved as [Link]
2. Open Table
•Double-click on tblCars to open in Datasheet View
3. Adjust Column Widths
•Click the grey box left of ID to select all rows
•Move cursor between column headers → Double-click to auto-resize
 18.1.8 ENTER DATA USING A TABLE
(TASK 18B)

4. Add New Car Record

• Scroll to the row with the asterisk (*) to add new data
• Enter the following values:
• Make: Ford
• Model: Focus
• Colour: Silver
• Year: 2018
• PPrice: 13228
• SPrice: 14228
• Valet:Yes
• Extras: Alarm, Central Locking, Alloy Wheels
 18.1.8 ENTER DATA USING A TABLE
(TASK 18B)
5. Data Entry Tips

•Enter only numbers in PPrice/SPrice (no symbols)

•Press Enter to save each field automatically
•Valet defaults to No – change to Yes
6. Verify Entry
•Use visual verification: compare input with task brief
7. Save Database
•Save as: [Link]
 18.1.9 ADD FIELD TO EXISTING TABLE
(TASK 18C)

1. Open database and go to tblCars

2. Open in Design View
3. Add new field below Valet:
• Field Name: PDate
• Data Type: Date/Time
4. Set Format to Long Date
 18.1.9 ADD FIELD TO EXISTING TABLE
(TASK 18C)

5. Save database as task18c

6. Switch to Datasheet View
7. Find PDate field for Silver Ford Focus
8. Use Calendar icon to select correct date
 18.1.10 ADD A FIELD TO THE EXISTING
TABLE (TASK 18D)

1. Open Database Tools tab

2. Click Relationships icon
3. In Add Tables pane, hold Shift and select both tables
4. Click Add, then close the Add Tables pane
5. Resize tables for full field visibility
6. Drag SCode from tblTeachers to Maths in tblStudents
 18.1.10 ADD A FIELD TO THE EXISTING
TABLE (TASK 18D)

7. Edit Relationships window appears

8. Ensure correct one-to-many relationship
9. If incorrect:
• Click Cancel
• Set Primary Key in tblTeachers and Foreign Key in tblStudents
• Redo relationship
10. Take a screenshot using Alt + Prt Scr for evidence
11. Click Create to finish
 18.1.11 CREATE A DATA ENTRY FORM
(TASK 18E)

1. Open your database from Task 18d.

2. Go to the Create tab → Click Form Wizard.
3. Select tblStudents → Click >> to add all fields.
4. Choose Columnar layout → Click Next.
5. Name the form frmStudents → Click Finish.
6. Use the navigation bar at the bottom to move between records.
7. Repeat steps 2–6 for tblTeachers, name it frmTeachers.
8. Edit form labels if needed to make them easy to understand.
9. Save the database as task18e.
BOTTOM OF FORM PAGE
 18.1.12 EDIT A DATA ENTRY FORM
(TASK 18F)

1. Open the database Task 18e.

2. Double-click frmStudents under Forms.
3. Go to Home > View > Design View.
4. Hide the Navigation Pane (click Shutter Bar).
5. Open Property Sheet (Design tab > Tools).
6. Click the Form Header label (outline turns orange).
 18.1.12 EDIT A DATA ENTRY FORM
(TASK 18F)

7. Edit the heading text, resize if needed.

8. (Optional) Change font, color, alignment in Property Sheet.
9. Add a new Label (Design > Controls) with instructions.
10. In Detail section, edit all label texts to be clearer.
11. Resize and move labels if needed.
12. Switch to Form View to check.
13. Save as Task18f.
 18.1.13 ADD A NEW FIELD TO AN
EXISTING FORM (TASK 18G)

1. Open the database (e.g., Task18f).

2. Restore Navigation Pane (click the Shutter Bar Open/Close Button).

3. Open tblStudents in Design View.
4. Under Science, add:
•Field Name: Year_Group
•Data Type: Change to Number
•Description: (Type as given in task)
 18.1.14 EDIT A DATA ENTRY FORM
(TASK 18G)

5. Add another field:

•Field Name: Tutor
•Data Type: Short Text
•Description: (As given)
•Set Field Size to 3 (in General tab)
6. Save and close the table.
 18.1.14 EDIT A DATA ENTRY FORM

Update frmStudents Form:

[Link] frmStudents in Design View.
[Link] Form Footer down to create space.
[Link] Year_Group:
•Use Option Group from Design > Controls.
•Add Option Buttons for values: 7, 8, 9, 10, 11.
[Link] the Tutor field as a text box if not auto-added.
[Link] and preview in Form View
 18.1.15 WHAT DOES A WELL-DESIGNED
FORM LOOK LIKE? (TASK 18H)

1. Open Database
• Open the file saved as Task 18g.
2. Open Form in Design View
• Open frmStudents → Right-click → Design View.
3. Delete Existing Tutor Field
• Remove both the label and text box for the Tutor field.
4. Insert a List Box
• Go to Design tab > Controls > List Box.
• Drag it onto the form → List Box Wizard opens.
 18.1.15 WHAT DOES A WELL-DESIGNED
FORM LOOK LIKE? (TASK 18H)

5. Configure List Box Wizard

• Select: "I want the list box to get values from another table or query" → Next
• Choose Table: tblTeachers → Next
• Double-click SCode to move it to Selected Fields → Next
• Sort by SCode (Ascending) → Next
• Adjust column width → Next
• Store value in: Tutor → Next
• Name the label: Tutor → Finish
 18.1.15 WHAT DOES A WELL-DESIGNED
FORM LOOK LIKE? (TASK 18H)

6. Adjust Design
•Resize & move the list box as needed.
•Set label Text Align to Right in Property Sheet.
•Keep fields grouped with clear spacing for a clean
layout.
7. Save Your Work
•Save the form and database as Task18h
THANK YOU