Chapter 10 - Database Management Systems

Notes for Year 10 Computing Technology

10.1 Introduction to Databases

What is a Database?
• An organised collection of data
• Used in almost every industry and aspect of daily life
• Can be simple (paper-based) or very complex (computerised)
• Examples: banking, health, schools, sport, social media

What is a DBMS?
• Database Management System - software that lets users define, record, edit,
analyse and display data
• Sits between the user and the stored data, handling all interactions
• Microsoft Access - desktop, beginner-friendly, good for learning
• MySQL - server-based, open-source, used by Facebook, Netflix, Amazon, Airbnb

Advantages of a Computerised Database

• Data is organised and accessed more easily
• Storage space is greatly reduced
• Human errors in collecting and accessing data are minimised
• Data can be sorted, filtered, queried and reported automatically
• Multiple users can access data at once (server-based systems)

Common Uses of Databases

• Banking/e-commerce - managing products, users and purchases
• Health - storing patient records, test results, diagnoses
• Hospitality - managing rooms, guests and bookings
• Education - storing student, teacher and class data
• Sport - tracking players, teams, games and results
• Employment - recording employee details, hours and pay
• Social media - storing data on people, posts, events and groups

DBMS vs Spreadsheet Software

• Spreadsheet = good for flat-file databases (one table, one topic)
• DBMS = needed when there are multiple related tables
• Spreadsheets cannot efficiently handle relationships between tables
• Once data involves multiple related things, a DBMS is required
10.2 Building a Database
Key Concepts
• Entity - a real-world object or concept we store data about (e.g. Book, Student,
Customer)
• Event - an occurrence involving entities (e.g. a Loan connecting a Student to a Book)
• Attribute - one piece of data about an entity (e.g. Title, Author, Price)

Database Hierarchy
• Database - contains all tables
• Table (File) - stores all records about one type of object
• Record (Row) - all information about one specific instance (one book, one student)
• Field (Column) - one single piece of data per record (e.g. the title of one book)

Primary Key
• A field that uniquely identifies every record in a table
• No two records can share the same primary key value
• AutoNumber is commonly used - automatically generates a unique number
• Examples: BookID, StudentID, CustomerID

Foreign Key
• A field in one table that references the primary key of another table
• This is how tables are linked/related to each other
• Example: CustomerID in the Orders table links to CustomerID (PK) in the Customers
table

Types of Relationships
• One-to-one - one record in Table A links to exactly one record in Table B (rare)
• One-to-many - one record in Table A links to many records in Table B (most
common)
• Many-to-many - needs a junction/linking table in between to resolve

Planning Tools
• Schema diagram - visual map showing tables, fields, keys and how they connect
• Data dictionary - table listing each field's name, data type, size and an example
• Both should be created before building the database in the DBMS
• Planning prevents mistakes that are hard to fix once the database is built

Data Dictionary Example

Field Name Data Type Field Size Example
 StudentID AutoNumber - 2001335
FamilyName Short Text 30 Smith
FirstName Short Text 20 Mary
DateOfBirth Date/Time 8 25/04/87
YearGroup Short Text 2 10

Common Data Types

Data Type Used For
Short Text Names, IDs, phone numbers (up to 255 characters)
Long Text Lengthy descriptions and notes
Number Mathematical calculations
Date/Time Dates and times
Currency Money values
AutoNumber Unique auto-generated ID for each record
Yes/No True or false values
Attachment Files and images

10.3 Collecting and Checking Data

Data Sources
Primary Sources (collected directly)
• ATM and EFTPOS terminals
• Application forms
• Surveys and questionnaires
• Bar code scanners
• RFID chips (travel cards, tolls, public transport)
• Biometric devices (fingerprints, facial recognition)

Secondary Sources (existing documents)

• Newspapers, research papers, websites
• Quicker but potentially less reliable
• Sources must always be acknowledged and documented

Data Validation
• Checks that data makes sense when it is entered
• The DBMS automatically rejects values that break the rules
 • Reduces errors but does not eliminate them

Examples of Validation Rules

◦ Date of birth must give a realistic age
◦ Year group can only be one of six specific values (use a drop-down list)
◦ Number of sugars must be between 0 and 4
◦ Email must follow the format x@x.x

Data Verification
• Checks that data is actually true and correct (not just sensible)
• Requires human checking against the original source document

Examples of Verification
◦ A clerk checks an entered date against a birth certificate
◦ Logging in - your password is verified against the stored version
◦ Banks send SMS codes to confirm transactions
◦ Bar codes include a check digit - a formula verifies the scan is correct

Validation vs Verification - Key Difference

• Validation = is the data REASONABLE? (automated by the DBMS)
• Verification = is the data CORRECT? (checked against the source)

Forms
• A well-designed form minimises data entry errors
• Should include drop-down lists, radio buttons and validation messages
• Labels should be clear and the layout logical
• Used for both input and displaying query results

Social, Ethical and Legal Considerations

Social
◦ Privacy - people have the right to keep personal information private
◦ Bias - biased collection methods can discriminate against vulnerable groups
◦ Transparency - organisations must be open about how data is collected and used

Ethical
◦ Consent - individuals must agree before their data is collected
◦ Purpose limitation - only necessary data should be collected, only for stated
purposes

Legal
◦ Must comply with the Australian Privacy Law
◦ Must protect data from unauthorised access using cybersecurity measures
◦ Must not retain personal data longer than necessary
 ◦ Must have policies for the secure disposal of data

10.4 Analysing Data

Sorting
• Records displayed in a different order - does NOT change the stored data
• Ascending = A to Z or 0 to 9
• Descending = Z to A or 9 to 0
• Secondary sort = sorting by a second field within the first (e.g. by Category, then by
Name)

Querying
• The process of extracting specific data for a particular purpose
• Results can be displayed in a table, form or report
• Two main methods: QBE and SQL

QBE - Query by Example

• Used in Microsoft Access
• Select tables, choose fields to display, enter criteria in the query grid
• No coding required - uses a graphical interface
• Results shown in a datasheet view

SQL - Structured Query Language

• Code-based querying used in all relational DBMS
• AND = combines two conditions that must both be true
• OR = returns records matching either condition
• Queries across multiple tables use JOINs to match primary and foreign keys

Basic SQL Structure

SELECT <fields to display>
FROM <tables>
WHERE <criteria>
ORDER BY <field> ASC/DESC

Calculated Fields
• Created inside a query using a formula
• Not stored in the table - calculated at run time
• Example: TotalValue: [Quantity] * [Cost]
• Can be formatted as currency using the Property Sheet
Common Mathematical Functions
• SUM - adds all values together
• COUNT - counts the number of records
• AVERAGE - finds the mean value
• MIN / MAX - finds the smallest or largest value
• Age can be calculated using DateDiff from date of birth

Macros
• A series of tasks grouped together to run with one click
• Attached to a button on a form
• Saves time when tasks need to be constantly repeated
• Example: clicking 'New Order' could clear the form and prepare it for a new record

10.5 Presenting Information

Forms (Output)
• Used to display query results in a user-friendly layout
• Structure: Header (title/logo) + Detail (fields/data) + Footer (buttons)
• Can be used for both input and output

Reports
• A formatted, output-only document generated from a query
• Used for printing or display - cannot be used for data input
• Structure: Header (name/date/logo) + Data rows + Footer (page numbers/totals)
• Summary reports - condense data for management use
• Example: a list of all overdue library books with student names and year groups

Visualisation
• Quantitative data is best shown as charts
• Pie chart - shows proportions of a whole
• Bar/column chart - compares values or categories
• Microsoft Access can generate charts embedded in reports or forms
• Charts make patterns and trends immediately visible

10.6 Integration
Importing Data
• Existing data from spreadsheets, text files or other databases can be imported
• Most common format: tab-delimited text - fields separated by a tab, records by a
new line
• Allows data to move between spreadsheets and databases
 • Avoids re-entering all data manually

Exporting Data
• Data exported to Excel, PDF, text files or other applications for further analysis
• Some databases allow direct linking to spreadsheets so data updates automatically

Mail Merge
• Database fields are merged into a word-processed letter template
• Each letter is automatically personalised with customer name, address, details etc.
• Useful for bulk marketing, communications and mailing labels

Web-Based Databases
• Most large databases exist online, stored on servers managed by cloud services
• MySQL is the most common server-based DBMS
• Web pages use PHP code embedded in HTML to connect to and query a MySQL
database

Advantages
◦ Accessible by many users simultaneously from different locations
◦ Can be integrated with websites and mobile apps

Security Measures Required

◦ Passwords and firewalls to prevent unauthorised access
◦ Encryption makes sensitive data unreadable without a special key

Key Vocabulary - Quick Reference

Term Definition
Database An organised collection of data
DBMS Software to define, record, edit, analyse and display data
Entity A real-world object or concept stored in a database
Attribute A piece of data (field) about an entity
Table / File Stores all records about one type of object
Record / Row All information about one specific instance
Field / Column One piece of data per record
Primary Key Unique identifier for every record in a table
Foreign Key Links one table to another table's primary key
Schema Diagram showing tables, fields, keys and relationships
Data Dictionary Describes each field's name, data type and size
Flat-file Database All data in one table - no relationships
Relational Database Multiple linked tables with relationships
Data Validation Automatic check that data is sensible/reasonable
Data Verification Manual check that data is true and correct
QBE Query by Example - graphical querying in Access
SQL Structured Query Language - code-based querying
Report Formatted output-only document from a query
Mail Merge Personalising documents using database fields
Delimited Text Text file with fields separated by tabs or commas