Welcome to Database Development!
Have you ever wondered how Spotify remembers your favourite playlists, how your school keeps track of attendance, or how Amazon manages millions of products without losing orders? Behind all of these systems is a database.
In this chapter for Unit 2: Digital Authoring Concepts, you will learn how databases are designed, built, and tested. Don't worry if databases seem a bit intimidating or technical at first! We will break every concept down into small, bite-sized steps using clear real-world examples.
1. Flat-File vs. Relational Databases
Before jumping into building databases, we need to understand the two main types of database structures.
What is a Flat-File Database?
A flat-file database stores all of its data in a single table or file (just like a simple spreadsheet). While it is easy to set up for a small contact list, it causes huge problems for larger systems.
Problems with Flat-File Databases:
• Data Redundancy: Information is unnecessarily repeated over and over (e.g., typing a customer's full address every time they buy a single item).
• Data Inconsistency: If an address changes, you have to update it in dozens of places. If you miss one, the data becomes conflicting and unreliable.
• Wasted Storage Space: Repeating the same text hundreds of times uses up unnecessary memory.
• Security Risks: Everyone who accesses the file sees all the data; you cannot easily restrict access to specific parts.
What is a Relational Database?
A relational database solves these problems by splitting data into multiple separate tables that are linked (related) together using common fields.
Analogy: Imagine your phone's contact book. Instead of writing your friend's full name, address, and school on every text message they send you, your phone stores their details once in Contacts and simply links their phone number to the messages.
Key Benefits of Relational Databases:
• Reduced Data Redundancy: Data is entered once and stored in one place.
• Improved Data Integrity: Updating a record in one table automatically updates the connection everywhere.
• Better Security: Permissions can be set so staff only see the tables they need for their job.
• Easier Searching: Complex queries can extract information across multiple tables seamlessly.
Key Takeaway: Flat-file databases store everything in one table and cause errors and duplicate data. Relational databases organize data across linked tables to keep data accurate, secure, and efficient.
2. Database Terminology & Structure
To master databases, you need to speak the language of database developers. Here are the core building blocks:
• Entity: A real-world person, place, object, or event about which data is collected (e.g., STUDENT, DOCTOR, PRODUCT). In a database, an entity becomes a Table.
• Attribute / Field: A specific category of data stored about an entity (e.g., FirstName, DateOfBirth, Price). In a table, fields are the columns.
• Record / Tuple: A complete set of data relating to a single item or person (e.g., all the details belonging to one specific student). In a table, records are the rows.
Memory Aid:
Think of a table as a grid:
• Fields go From top to bottom (Columns).
• Records go from left to Right (Rows).
Key Takeaway: Entities become Tables, attributes become Fields (columns), and individual entries are Records (rows).
3. Data Types
Every field in a database must be assigned a specific data type. Choosing the correct data type ensures that the database allocates the right amount of storage and validates data entry.
• Text / Alphanumeric: Stores letters, numbers, symbols, and spaces (e.g., Names, Postcodes, Phone Numbers).
• Number / Integer / Decimal: Stores numerical values that will be used in mathematical calculations (e.g., Age, Quantity in Stock, Exam Marks).
• Date / Time: Stores dates and times in standard formats (e.g., \(14/05/2008\) or \(09:30\text{ AM}\)).
• Currency: Stores monetary values and formats them automatically with currency symbols and two decimal places (e.g., \(\$19.99\) or \(\pounds 14.50\)).
• Boolean / Yes/No: Stores only one of two possible values (e.g., True/False, Yes/No, Pass/Fail).
• Autonumber: Automatically generates a unique sequential number for every new record (e.g., \(1, 2, 3, \dots\)). Often used for IDs.
Common Student Mistake to Avoid:
Why is a Telephone Number stored as Text, not Number?
Always store telephone numbers as Text because:
1. They often start with a leading zero (e.g., \(028\dots\)). If stored as a Number, the database will delete the leading zero!
2. Telephone numbers are never used in mathematical calculations (you never add two phone numbers together).
Key Takeaway: Match the data type to how the information is used. Use Text for non-calculated codes and numbers with leading zeros.
4. Keys and Relationships
Relationships are what make a relational database work. To connect tables, we use special fields called Keys.
Types of Keys
• Primary Key: A unique field that identifies one, and only one, record in a table. No two records can share the same primary key value (e.g., StudentID, NationalInsuranceNumber).
• Foreign Key: A primary key from one table that appears in another table to form a link/relationship between them.
• Composite Key: A primary key made by combining two or more fields together when no single field is unique on its own.
Types of Entity Relationships
• One-to-One (\(1:1\)): Each record in Table A relates to only one record in Table B.
Example: A Country has one National Flag, and that Flag belongs to one Country.
• One-to-Many (\(1:\text{M}\)): Each record in Table A can relate to multiple records in Table B, but each record in Table B relates to only one in Table A. This is the most common relationship.
Example: One Customer can place many Orders, but each Order belongs to only one Customer.
• Many-to-Many (\(\text{M}:\text{N}\)): Multiple records in Table A relate to multiple records in Table B.
Example: A Student studies many Subjects, and a Subject contains many Students.
The Junction / Linking Table
Relational database management systems (like Microsoft Access) cannot link two tables directly in a Many-to-Many relationship. To fix this, we split the \(\text{M}:\text{N}\) relationship into two One-to-Many (\(1:\text{M}\)) relationships using a Junction Table (also known as a linking table).
How it works:
The junction table contains the primary keys from both tables as foreign keys, often combining them into a composite primary key.
Referential Integrity
Referential Integrity is a database rule that prevents "orphan" records. It ensures that a foreign key value cannot be entered in a child table unless that value already exists as a primary key in the parent table.
Example: You cannot create an order for Customer ID \(999\) if Customer ID \(999\) does not exist in the Customers table.
Key Takeaway: Primary keys uniquely identify rows; foreign keys link tables. Many-to-many relationships must be resolved using a junction table.
5. Data Validation and Verification
A famous computer science rule is GIGO (Garbage In, Garbage Out). If bad data is typed into a database, the search results and reports will be wrong. We protect data using Validation and Verification.
Data Validation (Computer Checks)
Validation is an automatic check carried out by the computer to ensure that data entered is sensible, reasonable, and follows specific rules. It does not guarantee the data is 100% correct!
• Presence Check: Ensures a field is not left blank (e.g., Surname must be filled in).
• Range Check: Checks that a number or date falls between an upper and lower limit (e.g., Exam score must be between \(0\) and \(100\), or \(0 \le \text{Score} \le 100\)).
• Length Check: Checks that text contains an exact number of characters or falls within a set character limit (e.g., A UK postcode must not exceed \(8\) characters).
• Format / Picture Check: Ensures data matches a predefined pattern (e.g., Postcode pattern: \(LLNN\ NL\text{L}\) like \(BT48\ 6A\text{T}\)).
• Type Check: Checks that data entered is of the correct data type (e.g., Entering letters into an Age field triggers an error).
• Lookup Check: Restricts input to a predefined list of choices (e.g., Selecting Title from a drop-down list: Mr, Mrs, Miss, Dr).
Data Verification (Human/Process Checks)
Verification is checking that data entered into the computer matches the original source document exactly.
• Double Data Entry: Two people (or the same person twice) enter the exact same data. The computer compares both entries. If they differ, an error alert appears (e.g., Typing a new password twice when creating an account).
• Proofreading / Visual Check: The user carefully reads the screen against the original paper document to spot any typing mistakes before saving.
Key Takeaway: Validation checks if data is sensible and follows rules. Verification checks if data matches the original source.
6. Searching and Querying Data
A Query is a search tool used to extract specific records from a database that meet given search conditions (criteria).
Comparison Operators:
• \(=\) (Equal to)
• \(<\) (Less than)
• \(>\) (Greater than)
• \(\le\) (Less than or equal to)
• \(\ge\) (Greater than or equal to)
• \(\ne\) or \(<>\) (Not equal to)
Boolean / Logical Operators:
• AND: Returns records only when all conditions are true.
Example: \(\text{Town} = \text{"Belfast"}\ \mathbf{AND}\ \text{Age} \ge 18\) (Must live in Belfast AND be at least 18).
• OR: Returns records when at least one condition is true.
Example: \(\text{Town} = \text{"Belfast"}\ \mathbf{OR}\ \text{Town} = \text{"Derry"}\)
• NOT: Excludes specific values.
Example: \(\mathbf{NOT}\ \text{Town} = \text{"Belfast"}\)
Wildcards and Sorting:
• Wildcard (\(*\)): Used to search for incomplete patterns. For example, searching for "S*" finds Smith, Stewart, and Simpson.
• Sorting: Results can be ordered in Ascending (\(A \to Z\), \(0 \to 9\)) or Descending (\(Z \to A\), \(9 \to 0\)) order.
Key Takeaway: Queries interrogate the database. Use AND to narrow down results and OR to widen your search.
7. User Interfaces: Forms and Reports
Databases are designed for everyday end-users who may not understand database technicalities. We create Forms and Reports to make interactions simple and professional.
Forms (Input)
A Form provides an attractive, user-friendly interface designed for entering, editing, and viewing individual records.
Features of a Good Form:
• Clear, uncluttered layout with logical tab order.
• Drop-down boxes (combo boxes) and radio buttons to speed up data entry and minimize errors.
• Action buttons (e.g., Save, Delete, Next Record, Close).
• Meaningful field labels and helpful validation error messages.
Reports (Output)
A Report is a formatted, professional presentation of database information designed to be printed or viewed on screen.
Features of a Good Report:
• Headers and Footers: Page numbers, report titles, and the current date/time.
• Grouping: Data organized into categories (e.g., grouping sales by department).
• Calculations / Summary Functions: Calculating totals, averages, or counts (e.g., \(\text{Sum(Price)}\) or \(\text{Count(StudentID)}\)).
• Clean branding, consistent fonts, and appropriate column widths so text is not cut off.
Key Takeaway: Forms are used to put data INTO the system easily; Reports are used to present and summarize data to take OUT of the system.
Quick Revision Checklist
Before your exam, make sure you can:
• State the difference between flat-file and relational databases.
• Define table, record, field, and primary key.
• Explain why a foreign key is needed to link tables.
• Resolve a many-to-many relationship using a junction table.
• Choose suitable data types and validation checks for given scenarios.
• Explain the difference between validation and verification.
• Construct simple query criteria using operators like \(>, \le\), AND, OR, and NOT.
• Describe the purpose and main features of forms and reports.