Welcome to the World of Databases!
Hello there! Today, we are diving into one of the most important chapters in your AS Level IT journey: Database and File Concepts. If you have ever used Netflix to find a movie, or if your school keeps track of your grades on a computer, you have interacted with a database!
In these notes, we will break down how databases are built, how they stay organized, and how they help businesses make big decisions. Don't worry if it seems like a lot to take in at first—we will take it step-by-step!
10.1 Creating a Database: The Building Blocks
Before we can store data, we need to decide what *kind* of data it is. Think of this like choosing the right container for your food—you wouldn't put soup in a cardboard box!
Assigning Data Types
Every "field" (a single category of information, like "First Name") needs a Data Type. Here are the common ones you need to know:
• Text: For words and sentences.
• Alphanumeric: A mix of letters and numbers (like a license plate).
• Numeric: Numbers only. This can be Integer (whole numbers) or Decimal (numbers with points).
• Date and Time: For specific calendar days or clock times.
• Boolean: For things that only have two options, like Yes/No or True/False.
The "Keys" to the Database
To keep data organized, we use special fields called Keys. These are like ID badges for your data.
1. Primary Key: This is a unique field that identifies every single record. No two people can have the same Primary Key (like your National ID or Student Number).
2. Foreign Key: This is a Primary Key from one table that appears in another table to link them together.
3. Compound Key: Sometimes, one field isn't unique enough, so we combine two fields to make a unique ID.
4. Composite Key: Similar to a compound key, this is a primary key that consists of more than one attribute to uniquely identify an entity occurrence.
Flat Files vs. Relational Databases
A Flat File is like a single, giant spreadsheet. It’s simple, but it can lead to lots of mistakes and repeated data.
A Relational Database is a collection of several tables linked together.
Why use Relational? It reduces data redundancy (storing the same thing twice) and makes it much easier to update information in just one place.
Relationships: How Tables Talk
Tables connect to each other in three main ways:
• One-to-One (1:1): One person has one passport. One passport belongs to one person.
• One-to-Many (1:M): One mother can have many children, but each child has only one biological mother. (This is the most common type!)
• Many-to-Many (M:M): Many students can take many subjects, and each subject has many students.
Referential Integrity
This sounds fancy, but it just means "keeping the links healthy." Referential Integrity ensures that you cannot have a Foreign Key pointing to a Primary Key that doesn't exist. It’s like making sure you don't have a "Child" record in a database if the "Parent" record has been deleted!
Validation and Verification
We need to make sure the data going in is correct.
• Validation: The computer checks if the data is *sensible* (e.g., a "Range Check" makes sure your age isn't 500).
• Verification: Humans check if the data is *accurate* (e.g., "Double Data Entry" where you type your password twice).
Key Takeaway: Relational databases use keys and relationships to keep data organized, unique, and connected without wasting space.
10.2 Normalisation: Cleaning Up the Mess
Normalisation is the process of organizing a database to reduce data repetition and improve data integrity. It’s like tidying up a messy room by putting everything into the right drawers.
The Three Stages of Normalisation
1. First Normal Form (1NF): No "repeating groups" of data. Every cell should have only one piece of information, and every record must have a Primary Key.
2. Second Normal Form (2NF): It must already be in 1NF. All non-key fields must depend on the *entire* Primary Key. If a field only depends on *part* of a compound key, it has to move to a new table!
3. Third Normal Form (3NF): It must already be in 2NF. There should be no "transitive dependencies." This is a big term that just means: a non-key field should not depend on another non-key field. Everything should point directly to the Primary Key.
Key Takeaway: Normalisation makes the database smaller, faster, and much less likely to have errors when you update it.
10.3 The Data Dictionary
A Data Dictionary is basically a "manual" or "recipe book" for your database. It doesn't contain the actual names or addresses, but it contains the Metadata (data about data).
What’s inside a Data Dictionary?
• Field names
• Data types (Text, Numeric, etc.)
• Field sizes (how many characters are allowed)
• Validation rules (e.g., must be > 0)
• Relationships between tables
10.4 File and Data Management
How does the computer actually find the data on the hard drive? There are two main ways to access files:
Methods of Access
1. Indexed Sequential Access: The computer keeps an "index" (like the index at the back of a textbook). It looks at the index to find the general area, then searches in order (sequentially) to find the specific record.
2. Direct File Access: The computer uses a mathematical formula (hashing) to go *directly* to the exact spot where the data is stored. This is much faster for things like ATM withdrawals or online bookings.
Database Management Systems (DBMS)
A DBMS is the software that lets you create and manage the database (like Microsoft Access or MySQL). There are different types:
• Relational: Uses tables (the most common).
• Hierarchical: Looks like a family tree.
• Network: Like a web where everything can connect to everything.
• Object-Oriented: Stores data as "objects" rather than just text/numbers.
Management Information Systems (MIS)
An MIS takes the raw data from a database and turns it into useful reports for managers.
Example: A supermarket database has a list of every apple sold. The MIS summarizes this into a report saying "Apple sales are down 20% this month," so the manager knows to lower the price or run an ad.
Key Takeaway: While a database stores the data, a DBMS manages it, and an MIS uses it to help humans make smart business decisions.
Final Encouragement
You’ve made it through the core concepts of Databases! Don't worry if 2NF or 3NF feels a bit confusing right now—practice drawing out the tables, and it will start to click. Databases are all about logic and organization. You've got this!