Introduction to Database Systems and the Relational Model
Data Organization Hierarchy
Data is organized into a specific structural hierarchy, moving from the smallest unit of digital information to complex software systems:
Bit (Binary Digit): The most fundamental unit of data, represented as either a or a .
Byte: A unit of storage typically used for a single character (such as a letter or number). It is usually composed of bits.
Field: A collection of characters that represents one single data item.
Record: A set of related fields grouped together.
Table: A collection of related records.
Database: A software system designed to store and manage a related set of tables.
Database Fundamentals
Definition: A database, often referred to as a Database Management System (DBMS), is software specifically designed to store and manage large volumes of data.
Primary Purpose: The core objective of a database is to maintain an organized track of data. This involves several critical functions:
Organization of information.
Storage of data.
Retrieval of stored information.
Searching through datasets.
Enforcement of security protocols.
Core Components: Databases store information regarding three primary elements:
Real-world entities.
The attributes of those entities.
The relationships existing between entities.
Entities, Attributes, and Relationships
Entities:
An entity is a real or abstract object in the real world.
Functionally, entities act like nouns (a person, place, or thing).
Examples include a person, an automobile, a book, or a student enrollment.
Attributes:
Attributes are the properties or characteristics of an entity.
A database focuses on attributes that are "of interest" to a specific application.
Examples for a person entity might include height, weight, name, address, salary, and age.
Relationships:
A relationship is an association or connection between two or more entities.
Examples include:
A professor "teaches" students.
A customer "places" orders.
One or more authors "writes" books.
Case Study: Henry’s Bookstores
Context: Henry owns several branches of a bookstore and requires a organized system for books, authors, and publishers.
System Entities:
Author
Branch
Book
Publisher
System Relationships:
A branch of the bookstore sells many books.
Books are written by authors.
Books are published by publishers.
Entity-Relationship Diagram (ERD): This is a visual tool used to represent entities, their attributes, and their relationships.
Branch Attributes: BranchName, Street, City, State, Zip.
Book Attributes: ISBN, Title.
Publisher Attributes: Pname, City, Country.
Author Attributes: FirstName, LastName.
Connections:
Branch (Sells) Book.
Author (Written by) Book.
Publisher (Published by) Book.
Comparison of Data Processing Approaches
Traditional Flat File Approach:
This method uses separate file systems for different programs.
Example: Three separate file systems for Personnel Programs, Payroll Programs, and Reading Circles Programs.
Each program has its own independent files (Personnel Files, Payroll Files, Reading Files).
Problem: Data Redundancy:
In flat file systems, a single person's name and address might be stored in different files.
This leads to wasted storage space.
It creates a high risk of inconsistent data, where information may be updated in one file but not others.
The DBMS Solution:
A centralized storage system where each data item is stored exactly once.
Relationships are accurately represented across the entire system.
Centralized data serves multiple programs (Personnel, Payroll, Reading Circles) simultaneously.
Advantages of the DBMS Approach
Increased Productivity: Users can derive more information and knowledge from the same amount of data quickly and easily.
Data Sharing: Data can be shared among different users within the same organization.
Professional Management: A Database Administrator (DBA) is responsible for making critical decisions regarding the database.
Minimized Redundancy: Ideally, only one copy of any given piece of information exists.
Better Data Integrity: Integrity refers to the correctness of the data. For example, the system can enforce a rule that a Social Security Number must contain exactly digits.
Enhanced Security: Information is restricted to authorized users. For example, sensitive payroll data might be viewable only by a specific subset of employees.
Disadvantages and Challenges of DBMS
Size and Resource Consumption:
DBMS software is a large program requiring significant storage space and main memory.
It may necessitate more advanced hardware to function efficiently.
Complexity:
The system is complex to set up and maintain.
DBAs and other database personnel require specialized training.
Economic Factors:
Acquisition costs are high for large-scale applications.
There are many recurring costs associated with maintenance and updates.
Risk Management:
Because the system is centralized, there is a higher impact if the system fails.
Recovery from a failure is more difficult compared to simpler systems due to the inherent complexity of the DBMS.
The Relational Model
History: The relational model was developed in by E. F. Codd.
Market Presence: It remains the most prevalent type of DBMS used today.
Major Systems:
Oracle
SQL Server
MySQL
DB2
Postgres
Personal Databases: Microsoft Access is categorized as a "personal database," designed for use by only one person at a time.