INFS2001 LEC2

INFS2001/INFS2002: Database Design and Development Lecture Notes

Tutorial Guidelines

  • Completion Requirement:

    • Students should complete tutorial questions prior to attending the tutorial/practical session.

    • Practical exercises are to be completed during the designated tutorial/practical session.

    • If a student cannot finish all practical exercises in the class, they must ensure those exercises are completed before attending the next session.

Today’s Topics

  • Introduction to Databases (continued)

  • Components of Database Management System (DBMS) Environment

  • ANSI/SPARC 3-Level Architecture

  • Schemas

  • Data Independence

  • Functions of DBMS

Introduction to Databases - Continued

Program-Data Independence

  • Definition: A database is not solely owned by one organizational department; it is typically shared across multiple departments.

  • Components:

    • A database contains both actual data and metadata (data about data).

    • The metadata is stored in the system catalog or data dictionary.

  • Self-Describing Nature: This capability of databases provides what is termed as program-data independence.

Database Management System (DBMS)

Bridging Users and Databases

  • Structure:

    • End Users: Individuals who interact with the database.

    • Application Requests: Queries or commands sent from users.

    • Database Structure: The organization of the data and relationships in the database.

    • Metadata: Supplemental information that clarifies the data.

  • Diagram Elements: Customers, invoices, product requests, and management systems serve as typical interfaces between users and databases.

Types of Databases

  • Single-user Database: Supports only one user at a time.

  • Multi-user Database: Capable of supporting multiple simultaneous users.

  • Centralized Database: All data is stored at a single physical location.

  • Distributed Database: Data is spread across multiple locations.

Components of DBMS Environment

Key Components

  1. Hardware:

    • Ranges from PCs to networks of computers (single-user, multi-user, centralized, or distributed).

  2. Software:

    • Includes DBMS, operating systems, network software, and application programs.

  3. Data:

    • This refers to the actual data used within an organization along with its description, referred to as the schema (structure of the database).

Additional Components

  1. Procedures:

    • Guidelines for designing and using the database.

    • Examples include instructions on how to log on or start/stop the database and handle failures.

  2. People:

    • Comprises end users (both untrained and sophisticated), data administrators, database administrators, and other automated machine interfaces (e.g., automated stock monitoring).

Specific Components of a Database

  • Metadata Repository: Storage for all metadata like field names, formats, and data integrity rules.

  • Data Repository: Actual storage for data entered into the database.

  • Database Interface: The connection point between the database and the outside world (e.g., graphical user interfaces or Open Database Connectivity - ODBC).

  • CASE Tools: (optional) Tools to automatically generate database designs.

Advantages of Integrated DBMS

  • Data Sharing: Facilitates easier sharing of data.

  • Data Redundancy Control: Better management of redundancy.

  • Consistency and Standards: Improves data consistency and standards through established rules.

  • Security and Integrity: Enhances data security and integrity with defined constraints.

  • Efficiency: Balances conflicting data requirements and speeds application development.

  • Accessibility: Users gain better access without needing IT intervention.

  • Cost Efficiency: Achieves economies of scale; productivity increases; maintenance costs decrease due to program-data independence.

Disadvantages of DBMS

  • High Costs: Relevant to the database management system and hardware (requires more memory and processing power).

  • Conversion Challenges: Higher costs when initially implementing a database system.

  • Centralization Vulnerability: Increased risk due to centralized storage of data.

  • Complexity and Size: Databases can become complex and large, affecting manageability.

People’s Roles in Database Environment

  • Data Administrators (DA): Responsible for managing data and ensuring integrity.

  • Database Administrators (DBA): Oversee database systems and architectures.

  • Database Designers: Involved in logical and physical design of databases.

  • Application Programmers: Write programs interacting with the database.

  • End Users: Can range from naive (basic users) to sophisticated (advanced users).

Database Views

  • Purpose: Allow users to obtain personalized views of the data.

  • Stored Procedures: Used to generate user-specific views.

  • Security: Users can view data without permissions to alter the database.

  • Example: In a university, payroll staff can see employee hours but cannot access creditor information.

  • Concept: A view acts as a subset of the database.

SQL Example of a View

  • Base Table: SQL statement to create a base table.

  CREATE TABLE Staff (
      id INT,
      name VARCHAR(40),
      addr VARCHAR(80),
      email VARCHAR(80)
  );

  • View Creation: SQL for a view to display specific fields.

  CREATE VIEW StaffPub AS
  SELECT id, name, email FROM Staff;

  • Note: SQL details will be covered in later sessions.

Benefits of Views

  • Reduced Complexity: Simplifies database interaction for users.

  • Security: Enhances security by restricting access to sensitive data.

  • Customization: Allows custom appearances tailored for different user needs.

  • Consistency: Provides a stable view of the database structure, regardless of underlying changes.

Historical Development of Databases

  • Scope: This optional topic can be explored through self-directed study, with brief discussions during future tutorial/practical sessions. Refer to the textbook for details (1.5).

Database Environment Overview

ANSI/SPARC, 3-Level Architecture

  • Definition: ANSI - American National Standards Institute; SPARC - Standards Planning and Requirements Committee.

  • Purpose: Defines the conceptual architecture for understanding DBMS functionality, emphasizing implementation-independent layers.

Three-Level Architecture Characteristics

  1. External Level: User perception of data.

  2. Internal Level: How the operating system and DBMS manage data.

  3. Conceptual Level: Provides mapping and aids separation between external and internal levels.

    • Access Control: Users should not access physical storage directly.

    • Changes can be made by DBA without impacting user views.

Objectives of the Three-Level Architecture

  • All users access the same data; views are independent of changes from other views.

  • Users do not need to know the physical storage details.

  • DBAs can modify database storage or conceptual schemas without altering user views.

Database Schemas

Definition and Types

  • Schema: Used to define user views within the database.

    • External Schema: Defines user-specific external views.

    • Conceptual Schema: Outlines the entire database structure and constraints.

    • Internal Schema: Deals with data storage structures and access paths.

  • The comprehensive description of a database is known as the Database Schema, comprising the three types.

Examples of Schema Use

  • Internal Schema: Describes storage structures including fields, methods of representation, and indexes.

  • Conceptual Schema: Enumerates all entities, attributes, relationships, and integrity constraints.

  • External Schema: Tailors descriptions specific to various user views.

Data Independence

Types

  1. Logical Data Independence: Immunity of external schemas from changes in the conceptual schema.

    • Changes like adding/removing records should not affect external views or require rewriting application programs.

    • Users interested in changes can see the updates without affecting other users.

  2. Physical Data Independence: Immunity of the conceptual schema from changes in the internal schema.

    • Adjustments to internal structures (e.g., different file organizations) should not necessitate changes to external schemas.

    • Changes in data structures should maintain the integrity of the logical schema.

Example Illustrations

  • Books Database Configuration:

    • Components include Authors, AuthorISBN, Titles.

    • The design would be considered at the conceptual level based on the ANSI-SPARC standard.

  • PHP Example for External Level Design:

  $dbc= mysqli_connect('localhost', 'zhuhan', 'pass', 'Books')
  OR die(mysqli_connect_error());
  $query="SELECT ISBN, Title FROM Titles";
  if (!( result=mysqliquery(result = mysqli_query(dbc, $query) )) {
      echo "could not execute $query";
      die (mysqli_error());
  }
  mysqli_close( $dbc );
  echo "<pre>\n";
  while ( row=mysqlifetchrow(row = mysqli_fetch_row(result) ) {
      foreach ( $row as $value ) echo "$value", "\t";
      echo "\n";
  }

Functions of a DBMS

  • Data Operations: Storing, retrieval, and updates.

  • Catalog Accessibility: Users should have access to the catalog that describes data items.

  • Transaction Support: Guarantees that all operations in a transaction either succeed together or fail without affecting the database.

  • Concurrency Control: Ensures correct updates when multiple users are working concurrently.

  • Recovery Services: Mechanisms to recover the database in case of corruption.

  • Authorization Services: Protects database access to ensure only authorized users can retrieve or modify data.

  • Integrity Services: Guarantees that data manipulations adhere to established rules.

  • Utility Services: Provides additional features such as data loading from flat files, monitoring usage, and garbage collection.