Database Fundamentals

Introduction to Database

Objectives

  • Differentiate data and information.
  • Differentiate file processing and database system.
  • Explain database structure and the three-level architecture.
  • Explain the evolution of database and the database system environment.

Differences between Data and Information

  • Data: Raw facts like telephone numbers, birth dates, names.
    • Data is the building block of information.
  • Information: Produced by processing data.
    • Information is used to reveal meaning.

Example of Data

  • A list of names and numbers (e.g., Baker, Kenneth D. 324917628) is considered data.
  • Data in context helps users understand the meaning.

Example of Information

  • Graphical displays turn data into useful information that managers can use for decision-making and interpretation.

Examples of Database Applications

  • Purchases from the supermarket.
  • Purchases using credit card.
  • Booking a holiday at the travel agents.
  • Using the local library.
  • Taking out insurance.
  • Using the Internet.
  • Studying at university.

Database Definition

  • A shared collection of logically related data (and a description of this data), designed to meet the information needs of an organization.
  • Logically related data comprises entities, attributes, and relationships of an organization's information.
  • Description of data is known as System catalog (metadata or data dictionary) which provides program–data independence.

Logically Related Data

  • Entity: A distinct object in the organization (e.g., a person, place, thing, concept, or event).
    • Example: Student, Course
  • Attribute: A property that describes some aspect of an entity.
    • Example: Name, Code
  • Relationship: An association between entities.
    • Example: Takes (a relationship between a student and a course).

Objectives (Repeated)

  • Differentiate data and information.
  • Differentiate file processing and database system.
  • Explain database structure and the three-level architecture.
  • Explain the evolution of database and the database system environment

File-based Systems

  • A collection of application programs that perform services for the end users (e.g., reports).
  • Each program defines and manages its own data.
  • Typically labelled and stored in cabinets but can also be implemented as an information system on top of the file system using languages like C, Java, or Cobol.

File-based Processing

  • Data redundancy is a characteristic of file-based processing.

Limitations of File-based Approach

  • Separation and isolation of data
    • Each program maintains its own set of data.
    • Users of one program may be unaware of potentially useful data held by other programs.
  • Duplication of data
    • Same data is held by different programs.
    • Wasted space and potentially different values and/or different formats for the same data.

Limitations of File-based Approach (Cont.)

  • Data dependence
    • File structure is defined in the program code.
  • Incompatible file formats
    • Programs are written in different languages and so cannot easily access each other's files.
  • Fixed Queries/Proliferation of application programs
    • Programs are written to satisfy particular functions. Any new requirement needs a new program.

Limitations of File-based Approach (Cont.)

  • Definition of data was embedded in application programs, rather than being stored separately and independently.

File System vs. Database: An Example

  • File System Example (Pascal-like syntax):

    TYPE customer = RECORD
    cid : CARDINAL;
    name : ARRAY [1..20] OF CHAR;
    city : ARRAY [1..20] OF CHAR;
    balance: REAL;
    rebate: REAL;
END;
    • Data definitions are repeated in each program.

  • Database Example (SQL):
    CREATE TABLE customers ( cid INTEGER CHECK (cid>0), name VARCHAR(20), city VARCHAR(20), balance FLOAT, rebate FLOAT );

    • Data definitions exist once in the central data dictionary of a Database and are the same for all applications.

Limitations of File-based Approach (Cont.)

  • No control over access and manipulation of data beyond that imposed by application programs.

File System vs. Database: Access Control

  • File System Example:
VAR o : order;
p : product;

fh1, fh2: file_handle;
fh1 = Open(order_file);

WHILE NOT EOF(fh1) DO
    o := getnext(fh1);
    IF o.month = 10 AND o.day >= 19 THEN
        fh2 = Open(product_file);
        WHILE NOT eof(fh2) DO
            p := getnext(fh2);
            IF p.pnr=o.pnr AND p.stock<100 THEN
                WriteCard(o.pnr, o.quantity);
            END;
        END;
        close(fh2):
    END;
END;
close(fh1);
  • Database Example:
    SELECT pnr, quantity FROM customers o, products p WHERE o.month = 10 AND o.day >= 18 AND o.pnr = p.pnr AND p.stock<100
  • Declarative queries are easy to read and maintain, usable from different applications and allow for efficient evaluation due to automatic optimization.

Objectives (Repeated)

  • Differentiate data and information.
  • Differentiate file processing and database system.
  • Explain database structure and the three-level architecture.
  • Explain the evolution of database and the database system environment

ANSI-SPARC Three-level Architecture

  • Defines three levels:
    • External level
    • Conceptual level
    • Internal level

ANSI-SPARC Three-level Architecture Details

  • External Level:
    • Users' view of the database.
    • Describes the part of the database that is relevant to a particular user.
  • Conceptual Level:
    • Community view of the database.
    • Describes what data is stored in the database and relationships among the data.
  • Internal Level:
    • Physical representation of the database on the computer.
    • Describes how the data is stored in the database.

Differences between Three Levels of ANSI-SPARC Architecture

  • Each level provides an abstract view of the database.
  • External: User-specific views.
  • Conceptual: Integrated view.
  • Internal: Storage details.

Objectives of Three-Level Architecture

  • All users should be able to access the same data.
  • A user's view is immune to changes made in other views (e.g., changes in application in User A won’t affect User B).
  • Users should not need to know physical database storage details.

Objectives of Three-Level Architecture (Cont.)

  • DBA should be able to change database storage structures without affecting the users' views.
  • Internal structure of database should be unaffected by changes to physical aspects of storage.
  • DBA should be able to change the conceptual structure of the database without affecting all users.

Major Objective: Data Independence

  • Data independence means that the upper levels are unaffected by changes to lower levels.
  • Types of data independence:
    • Logical Data Independence
    • Physical Data Independence

Data Independence and the ANSI-SPARC Three-level Architecture

  • Diagram illustrating the relationship between the three levels and data independence.

Data Independence

  • Logical Data Independence:
    • Refers to the immunity of external schemas to changes in the conceptual schema.
    • Conceptual schema changes (e.g., addition/removal of entities) should not require changes to external schema or rewrites of application programs.
  • Physical Data Independence:
    • Refers to immunity of conceptual schema to changes in the internal schema.
    • Internal schema changes (e.g., using different file organizations, storage structures/devices) should not require change to conceptual or external schemas.

Objectives (Repeated)

  • Differentiate data and information.
  • Differentiate file processing and database system.
  • Explain database structure and the three-level architecture.
  • Explain the evolution of database and the database system environment

Evolution of Database Systems

  • First-generation:
    • Hierarchical and Network models.
    • Disadvantages: complex programs, minimal data independence, no accepted theoretical foundation.
  • Second generation:
    • Relational model.
    • Problems: limited modeling capabilities.
  • Third generation:
    • Object Relational and Object-Oriented models.

The Database System Environment

  • Components that make up the database system environment.

The Database System Environment Details

  • Hardware:
    • Can range from a PC to a network of computers.
  • Software:
    • DBMS, operating system, network software (if necessary), and application programs.
  • Data:
    • Used by the organization and consists of operational data and meta-data.

The Database System Environment Details (Cont.)

  • Procedures:
    • Instructions and rules that should be applied to the design and use of the DBMS.
  • People (Roles in DBMS Environment):
    • Data Administrator (DA)
    • Database Administrator (DBA)
    • Database Designers (Logical and Physical)
    • Application Programmers
    • End Users (naive and sophisticated)

Database Management System (DBMS)

  • A software system that enables users to define, create, and maintain the database and which provides controlled access to this database.

DBMS Interaction

  • The DBMS manages the interaction between the end user and the database.

DBMS Facilities

  • Data definition language (DDL):
    • Permits specification of data types, structures, and any data constraints.
    • All specifications are stored in the database.
  • Data manipulation language (DML):
    • General enquiry facility (query language) of the data.

DBMS Facilities (Cont.)

  • Controlled access to database may include:
    • A security system – prevents unauthorized users accessing the database.
    • An integrity system – maintains the consistency of stored data.
    • A concurrency control system – allows shared access of the database.
    • A recovery control system – restores the database to a previous consistent state following a hardware or software failure.
    • A user-accessible catalog – contains descriptions of the data in the database.
    • A view mechanism – provides users with only the data they want or need to use.

Advantages of DBMS

  • Control of data redundancy
  • Data consistency
  • More information from the same amount of data.
  • Sharing of data
  • Improved data integrity
  • Improved security
  • Enforcement of standards
  • Economy of scale

Advantages of DBMS (Cont.)

  • Balanced conflicting requirements
  • Improved data accessibility and responsiveness
  • Increased productivity
  • Improved maintenance through data independence
  • Increased concurrency
  • Improved backup and recovery services

Disadvantages of DBMS

  • Complexity
  • Size
  • Cost of DBMS
  • Additional hardware costs
  • Cost of conversion
  • Performance
  • Higher impact of a failure