Database Concepts
1. Introduction
Data is crucial for various applications, and its management is essential for efficiency. The chapter highlights the transition from manual record-keeping to using computerized systems for data management.
Example: School Attendance
In a schooling context, managing student attendance manually can become tedious and error-prone as data volume increases. For instance, a class with 50 students has 1300 records to maintain monthly, leading to potential inconsistencies if handled through manual systems.
2. File System
A file system organizes data into files but has serious limitations:
- Difficulty in Access: Retrieving data requires specific application programs, making access cumbersome.
- Data Redundancy: Redundant data occurs when the same information is stored in multiple files, consuming space and causing inconsistencies.
- Data Inconsistency: Inconsistencies arise when the same data is updated in one file but not in another.
- Data Isolation: Lack of relationships between files makes data difficult to integrate.
- Data Dependence: Changes in data structure require updates in all application programs accessing it.
- Controlled Data Sharing: Security and access restrictions are difficult to enforce in file systems.
Example Files: STUDENT and ATTENDANCE
Files to capture student data can be structured into STUDET and ATTENDANCE files, facilitating data entry and retrieval, but the limitations discussed above still apply.
3. Database Management System (DBMS)
A DBMS facilitates the organization of related data into databases. Key features include:
- Data Abstraction: Users interact with data at a higher level without seeing complex underlying processes.
- Efficiency: Data can be efficiently manipulated, updated, and retrieved.
- Centralized Data: Multiple users can access data at the same time without losing consistency.
DBMS Examples
Common DBMS software includes MySQL, Oracle, PostgreSQL, and MS Access.
4. Transition from File System to DBMS
In a structured database, files are interlinked to eliminate redundancy.
- Single Repository: Centralized data reduces redundancy and inconsistencies.
- Relation schemas allow for efficient retrieval and management of related data.
Key Components of a Database
- Database Schema: The structure of the database defining tables, columns, and data types.
- Constraints: Rules applied to data for accuracy, such as restricting a phone number to 10 digits.
- Meta-Data: Data about the data, stored within the system catalog.
5. Relational Data Model
The relational data model organizes data into tables or relations where rows represent records and columns represent attributes. Key terms include:
a. Attributes
Characteristics stored in the relations; managed as columns.
b. Tuples
Each row in a table, representing a specific record.
c. Domain
The permissible values that can be assigned to attributes.
d. Degree & Cardinality
- Degree: The number of attributes in a table.
- Cardinality: The number of tuples in a table.
Key Properties
- Unique names for attributes.
- Distinct tuples within a relation.
- Domain consistency and atomicity in attributes.
6. Keys in a Relational Database
Keys ensure tuples' uniqueness in relations and establish relationships between them:
- Candidate Key: Attributes that could uniquely identify tuples.
- Primary Key: A chosen candidate key for unique identification.
- Composite Key: When multiple attributes are needed as a key for uniqueness.
- Foreign Key: An attribute referring to the primary key of another table, establishing relationships.
Summary of DBMS Concepts
- File Systems: Containers for storing data but suffer from redundancy and inconsistency.
- DBMS: Software for handling databases, improving data management through structured organization.
- Relational Model: Organizes data into tables, each defined by attributes, tuples, and relationships.
- Keys: Necessary for maintaining uniqueness within relations and establishing relationships.