Introduction to Databases: Purpose and Structure

Definition: A structured collection of related data stored in a logical way.

The "Why": Transition from paper-based systems to digital efficiency. Databases allow for faster retrieval, better organization, and reduced redundancy compared to manual systems.

Real-world Examples

• School registers: Tracking student attendance, grades, and personal information
• Amazon inventory: Managing millions of products, orders, and customer data
• Hospital patient records: Storing medical histories, treatment plans, and appointment schedules

Physical (Manual) Databases

Traditional systems like filing cabinets, card indexes, and paper records.

• Limitations: Require physical space, slow retrieval, prone to damage, difficult to duplicate
• Security: Physical locks required, vulnerable to fire/water damage
• Sharing: Only one person can access at a time

Conceptual (Computerized) Databases

Electronic storage systems using database software.

• Benefits: Fast retrieval, minimal physical space, easy duplication and backup
• Security: Password protection, encryption, access controls
• Sharing: Multiple users can access simultaneously
• Reduced redundancy: Data stored once, linked where needed

Definition: Software used to create, store, and manage the database (e.g., Microsoft Access, MySQL, Oracle).

Functions of a DBMS

• Data Definition: Creating tables, fields, and data structures
• Data Manipulation: Adding, deleting, updating, and retrieving records
• Data Security: Controlling access through user permissions
• Data Integrity: Ensuring accuracy and consistency through validation rules
• Backup and Recovery: Protecting data from loss or corruption

Databases organize information in a structured hierarchy from smallest to largest:

Data Hierarchy Levels

• Characters: The building blocks (letters, numbers, symbols)
• Field: A single piece of data about one entity (e.g., FirstName, StudentID)
• Record: A collection of related fields about one entity (e.g., one student's complete profile)
• Table: A collection of related records (e.g., all students in a school)
• Database: The entire system containing all related tables

Every field in a database must have a specified data type that determines what kind of data it can store.

Common Data Types

• Text/Alphanumeric: Names, addresses, phone numbers (can include letters, numbers, and symbols)
• Numeric:
  • Integer: Whole numbers (e.g., age, quantity)
  • Decimal/Real: Numbers with fractions (e.g., price, weight)
• Date/Time: Dates, times, or both (e.g., date of birth, appointment time)
• Boolean/Logical: Yes/No or True/False values (e.g., FeesPaid, IsActive)
• Memo/Long Text: Large amounts of text (e.g., descriptions, comments)

Primary Key

A unique identifier for every record in a table. No two records can have the same primary key value.

• Examples: StudentID, ProductCode, InvoiceNumber
• Characteristics: Unique, never changes, never null (empty)

Foreign Key

A field in one table that links to a Primary Key in another table to create a relationship between tables.

• Purpose: Connect related data across multiple tables without duplicating information
• Benefit: Reduces data redundancy and maintains referential integrity

Data Integrity

Ensuring data is accurate, consistent, and reliable throughout its lifecycle.

Validation Checks

• Range Check: Verifies data falls within acceptable limits (e.g., Age must be 11–19)
• Type Check: Ensures data matches the expected type (e.g., Letters only in a Name field)
• Presence Check: Confirms required fields are not left blank (e.g., Email cannot be empty)
• Format Check: Validates data follows a specific pattern (e.g., Phone number: ###-###-####)
• Consistency Check: Ensures related data makes sense (e.g., End date cannot be before start date)

Summary Flashcards

Review key terms and definitions for your exam preparation.

Multiple Choice Quiz

Test your knowledge with 10 questions modeled after Paper 1 CSEC IT style.

Short Answer Practice