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. 3 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.
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.