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 11 or a 00.

    • Byte: A unit of storage typically used for a single character (such as a letter or number). It is usually composed of 88 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 33 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 99 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 19701970 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.