DBMS Comprehensive Notes

Key Terms and Definitions

  • Data: a collection of unprocessed facts, figures, and symbols.
  • Database: a collection of data organized in terms of fields, records, and files. A database is a structured collection of data that can be stored, managed, and retrieved efficiently.
  • DBMS (Database Management System): a database software that allows users to create a computerized database; add, change, and delete data; sort and retrieve data; and create forms and reports from the data in the database.
  • Data vs. Information (implied): data becomes information when organized and processed to be meaningful for specific users.

Three-Level Database Architecture (ANSI-SPARC) – Purpose and Levels

  • Purpose of three-level architecture: to provide a framework that separates how data is viewed by users from how it is organized and stored physically, enabling data independence and better data management.
  • Levels in brief:
    • External Level (View Level): describes how individual users view the data. Multiple external views can exist for different user needs. 33 Levels total: External, Conceptual, Internal.
    • Conceptual Level (Logical Level): defines the logical structure of all data in the database and the relationships among those data. It hides the details of the physical storage.
    • Internal Level (Physical Level): describes how and where the data are stored physically, including storage space allocation, files, and access methods.
  • Contents of levels (summary):
    • External: user views and data representations from user perspective.
    • Conceptual: logical structure of entire database, independent of how data is stored.
    • Internal: physical storage details and access paths.

Logical and Physical Data Independence

  • Logical Data Independence: protection from changes in the logical structure of data (e.g., adding/removing entities or attributes) without rewriting application programs.
  • Physical Data Independence: protection from changes in the physical structure of data (e.g., file organization, storage devices) without changing the conceptual level information.
  • Importance: allows DBA to modify storage or conceptual structures without impacting applications or user views, facilitating evolution and maintenance of the database system.

Data Models and Data Model Components

  • Data model: a collection of concepts for describing data, relationships among data, and constraints on the data.
  • Three components of a data model:
    • Structural part: rules for constructing the database (schema).
    • Manipulative part: operations allowed on data (e.g., retrieve, delete).
    • Integrity constraints: ensure data accuracy and consistency.
  • Purpose: provides a formal framework to define and manipulate data.

Data Definition Language (DDL) and Data Manipulation Language (DML)

  • DDL: commands used to create and destroy databases and database objects.
  • DML: commands to insert, retrieve, and modify data contained within the database.
  • Data language is sometimes called a data sublanguage because it is embedded into high-level programming languages (e.g., C++, Java).

Procedural vs Non-Procedural DML

  • Procedural DML: tells the system what data is needed and how to retrieve it (explicitly specifies operations); used to manipulate/process sets of records.
  • Non-procedural DML: states what data is needed without detailing how to retrieve it (abstracts the retrieval process); examples include SQL and Query-By-Example.

Entities, Attributes, Records, and Data Hierarchy

  • Entity/File: a person, place, thing, event, or condition about which data are collected.
  • Attribute/Field: a category of data describing an entity; each attribute is a fact about the entity.
  • Record: a grouping of attributes that describes an entity’s specific occurrence.
  • Data hierarchy (summary):
    • Field (column): single piece of data.
    • Record (row): complete set of fields for one entity instance.
    • File: collection of records.
    • Database: collection of files.

Example of a Database Table (Illustrative)

  • Access example (illustrative table structure):
    • Fields: CustID, Company, Last Name, Business Phone, Address, State/Province, ZIP/Postal Code, Country/Region.
    • Data types in typical tables: Number, Text, Memo, etc.
    • Field properties (example): Field Size, Format, Validation Rules, Default Values, Required/Indexed settings.

Internal Schema vs Conceptual Schema

  • Internal Schema: describes how data is physically stored to optimize performance (e.g., storage space allocation, file locations, access methods).
  • Conceptual Schema: describes all conceptually relevant, general, time-invariant structural aspects of the database (the logical view of the data for the universe of discourse).
  • External/View Schema: describes parts of the database in a form convenient to a particular user group's view; describes data storage and relationships from user perspectives.

ANSI-SPARC Architecture Details

  • Views Level (External): describes user-specific data views and what data is accessible.
  • Conceptual Level (Logical): defines the logical structure of the entire database; independent from physical storage.
  • Internal Level (Physical): defines the physical storage structures and access paths.
  • Objectives of the 3-Level Architecture:
    • All users should be able to access the same data; a user’s view should be immune to changes in other views.
    • Users should not need to know physical storage details.
    • DBA should be able to change database storage structures without affecting users’ views.
    • Internal structure should be unaffected by changes to physical storage.
    • DBA should be able to change the conceptual structure without affecting all users.

Examples of DBMS Components and Schemas

  • External/ View Schema describes parts of the database for a user group’s view.
  • Conceptual Schema describes all conceptually relevant aspects of the database.
  • Internal Schema describes physical storage details and access methods.

Functions of a DBMS

  • Data Storage, Retrieval, and Update: store, retrieve, and update data.
  • User Accessible Catalog: a catalog describing attributes and schemas accessible to users (e.g., conceptual level).
  • Transactions Support: ensure updates are properly linked among entities; support ACID properties in transactions.
  • Concurrency Control Service: ensure correct database updates when multiple users update concurrently.
  • Recovery Service: recover database after damage or failures; rollback to a consistent state if transactions fail.
  • Authorization Service: ensure only authorized users can access the database (read/write access).
  • Data Communication Support: integrate with data communication software; share databases locally or over networks.
  • Integrity Services: enforce data integrity rules (e.g., entity integrity, referential integrity).
  • Utility Services: tools to help database administrators improve productivity and efficiency.

Roles and Responsibilities of DBMS Users (IT Professionals)

  • System Analysts:
    • Communicate with prospective database users and developers to understand information and processing needs.
    • Develop specifications for user groups and document them; integrate information and processing needs.
  • Database Designers:
    • Choose appropriate structures to represent information from analysts.
    • Normalize data to guarantee integrity and consistency; document design.
  • Application Developers:
    • Implement the database design and application programs.
    • Test and debug database implementations; document implementations.
  • Database Administrators (DBAs):
    • Manage database structure; participate in requirements analysis and design.
    • Develop procedures for data integrity and quality; facilitate changes to the database structure.
    • Seek community-wide solutions; assess impact on all users; provide configuration control; maintain documentation.
  • End Users (Non-IT Professionals):
    • Parametric end users: query/update using canned transactions for standard queries.
    • Casual end users: occasional access, may need different information each time; may use sophisticated query tools.
    • Sophisticated end users: complex requirements; deeply familiar with DBMS capabilities (e.g., IT Manager).

DBMS Examples and Applications

  • Consumer / Flat-file alternatives:
    • Flat Files, Microsoft Excel (limits: e.g., 65,536 rows in older Excel versions), Microsoft Access, FileMaker Pro, MySQL (Open Source), PostgreSQL (Open Source).
  • Enterprise RDBMS examples:
    • Oracle, IBM/DB2, MS SQL Server, Sybase, Informix, Lotus Notes, MySQL (Open Source), PostgreSQL (Open Source).
  • Examples of DBMS applications in real life:
    • Purchases from the supermarket, Online purchases with credit card, Booking holidays via travel agents, Using a local library, Insurance policy management, University student registrations.

Quick Reference: Notable Points to Remember

  • DBMS stores and manages data; separates user views from storage details via the 3 levels.
  • DDL creates/destroys database structures; DML manipulates data within those structures.
  • Procedural vs Non-Procedural DML define how much the user specifies about the retrieval process.
  • Data independence (logical vs physical) enables flexible evolution of schemas and storage without breaking applications.
  • The ANSI-SPARC architecture emphasizes consistency, scalability, and user-centric data access through distinct levels and schemas.