Notes on Database Concepts
7.1 Introduction
In this section, the significance of data management is discussed, particularly in the context of a school’s student attendance system. The traditional method of manual record-keeping presents several problems:
- Labor-Intensive: Recording attendance for 50 students daily is cumbersome, especially when errors in student details occur during manual entry.
- Data Loss Risks: There is a high risk of losing physical records, such as attendance registers.
- Inconsistency: Incorrect entries, such as misspelled names or skipped records, lead to inaccuracies.
- Difficult Data Retrieval: Finding or changing information in a mass of paper records is impractical.
To address these issues, the adoption of computerized systems for managing student information is proposed. Digital records enable easy data entry, retrieval, modifications, and deletions, improving accuracy and efficiency.
7.2 File System
7.2.1 Definition of a File
A file acts as a container for storing diverse data types on a computer, such as text documents, images, and program codes.
7.2.2 Limitations of File System
Despite its straightforward storage solution, file systems face several limitations:
- Difficulty in Access: Application programs are needed to retrieve data, which may not always predict all access scenarios.
- Data Redundancy: Storing the same data in multiple files leads to unnecessary duplication and possible inconsistencies.
- Data Inconsistency: Changes made in one file (e.g., new spellings of a name) may not reflect in others, resulting in discrepancies.
- Data Isolation: Files are maintained independently without a relational structure, complicating data linkage.
- Data Dependence: Changes in data structure may necessitate updates in application programs, leading to maintenance burdens.
- Controlled Data Sharing: Implementing user-specific access controls is challenging in a file system.
7.3 Database Management System (DBMS)
A DBMS is a software that facilitates the creation, management, and organization of databases, designed to alleviate the limitations of file systems by ensuring logical data relationships.
Types of DBMS
Various DBMS applications, such as MySQL and Oracle, serve different needs.
Key Features of DBMS
- Data Abstraction: Hides storage details from users, providing an abstract view of data.
- Centralized Storage: Maintains data at a central location, allowing multiple users to access it simultaneously.
Key Terminology:
- Database Schema: Outline of the database design, identifying tables, their fields, and relationships.
- Data Constraints: Restrictions that define permissible data types within each table column.
- Meta-data: Catalog containing the database schema and constraints information.
- Database Instance: Snapshot of the database at a certain time, detailing all data entries.
Data Operations in DBMS
Common operations include insertion, deletion, and updates of records to facilitate dynamic data management through a structured query language (SQL).
7.4 Relational Data Model
The chapter introduces the relational data model where data is structured in tables (relations). Each table has unique attributes and records, allowing for clear organization and retrieval of data.
Key Characteristics of Relations
- Attributes: Column names that represent characteristics of data stored (e.g., GUID, GName).
- Tuples: Each row in a table represents a unique entry.
- Domain: Possible values for an attribute; data types define these domains.
- Degree: Number of attributes in a relation.
- Cardinality: Number of tuples in a relation.
7.5 Keys in a Relational Database
Keys are vital for ensuring each tuple is unique and maintain relationships between tables. Classification of keys:
- Candidate Key: Attributes that can uniquely identify tuples in a relation.
- Primary Key: A chosen attribute used to uniquely identify records.
- Composite Primary Key: A combination of attributes used when a single attribute cannot uniquely identify tuples.
- Foreign Key: An attribute that creates relationships by referencing the primary key of another relation, enabling connections between related data in different tables.
Summary Points
- File System Limitations: Manual record-keeping has inherent flaws, such as data inconsistency and redundancy.
- Importance of DBMS: A robust DBMS overcomes the challenges of file systems by managing data relationships efficiently.
- Database Schema: The formal structure of the database, delineating the organization of data and relationships between elements.
- Data Constraints: Rules governing the permissible values for database entries, ensuring data integrity.
- Relational Model: This model organizes data into related tables, with rows as records and columns as attributes.
- Types of Keys: Primary keys uniquely identify records, while foreign keys link related data across tables.
- Data Operations: The ability to insert, update, and manipulate data within the database is facilitated via structured queries.
- Metadata: The data about the database schema, used for various definitions and constraints.
- Attributes and Tuples: Each column represents data characteristics, while each row denotes a unique entity within a relation.
- SQL: A standard programming language for managing and querying relational databases effectively.