Database Systems - Chapter 2 Data Models

• Database systems refer to the database environment
• Chapter 2 focuses on database system concepts and architecture

Data Models

• Data models are a set of concepts used to describe the structure of a database, used to achieve data abstraction
• Data models include basic operations to specify retrievals and updates on the database
• Operations include basic model operations such as insert, delete, update and user-defined operations
• Database designers define rules and operations to specify how data is stored, accessed, and manipulated by database objects

Categories of Data Models

• Data models are classified based on the concepts used to describe database structure
• There are 3 types of Data Models: high-level, low-level, and implementation
• High-level or conceptual data models provide concepts close to how users perceive data
• Uses concepts such as entity (real-world object), attributes (describe an entity), and relationships (associations among entities)
• Referred to entity-based or object-based data models
• Low-level or physical data models describe how data is stored on computer storage
• Represents information like the "access path," which makes database record searches efficient
• Implementation (representational) data models represent data using record structure, thus record-based models
• Provide concepts between high and low levels, used by commercial DBMS implementations like relational, network, and hierarchical data models

Terminologies

• A database schema is the description of a database
• Acts as a blueprint, outlining tables, fields, relationships, and rules governing the data
• A schema diagram is an illustrative display of a database schema
• Schema construct represents each object within the schema
• Database state is the actual data stored in a database at a particular moment
• Also called the database instance or snapshot
• An empty database state contains no data, and the database schema is only specified to the DBMS
• The initial database state refers to when a database is loaded with its initial data
• A valid state satisfies the structure and constraints

Three-Schema Architecture

• The goal of the three-schema architecture is to separate user applications from the physical database
• Three-schema architecture defines DBMS schemas at three levels
• Internal, conceptual, and external

Internal Level

• The internal schema describes the physical storage structures and access paths of the database
• Typically uses a physical data model

Conceptual Level

• The conceptual schema layer describes the structure and constraints for the entire database for a community of users
• Uses a conceptual or implementation data model

External Level

• External schemas describe various user views
• Usually uses the same data model as the conceptual schema
• Each external schema describes the part of the database a user is interested in and hides the rest

Schemas Layers

• Mappings among schema levels are needed to transform requests and data
• Programs refer to an external schema and are mapped by the DBMS to the internal schema for execution
• Data extracted from the internal DBMS level is reformatted to match the user's external view, such as reformatting SQL query results for web display

Data Independence

• Data independence means that upper levels are unaffected by changes to lower levels
• There are two types of data independence: logical and physical
• Logical data independence is the ability to change the conceptual schema without changing external schemas and associated application programs
• This refers to the mapping between external and conceptual schemas
• Physical data independence is the ability to change the internal schema without changing the conceptual schema
• This refers to the mapping between the conceptual and internal schemas
• When a schema at a lower level is changed, only the mappings between this and higher-level schemas need to be changed in a DBMS that fully supports data independence
• The higher-level schemas themselves remain unchanged, and application programs do not need to be changed since they refer to the external schemas

DBMS Languages

• Data Definition Language (DDL) is used by the DBA and database designers to specify a database's conceptual schema
• In many DBMSs, DDL is also used to define internal and external schemas
• Some DBMSs, separate storage definition language (SDL) and view definition language (VDL) are used to define internal and external schemas
• Data Manipulation Language (DML) is used to specify database retrievals and updates
• DML commands (data sublanguage) can be embedded in general-purpose programming languages (host languages) like COBOL, C, C++, or Java
• A library of functions can also access the DBMS from a programming language
• Alternatively, stand-alone DML commands can be applied directly, referred to as a query language

Types of DML

• There are 2 types of DML: high level or low level
• High-level or non-procedural language allows users to state what data is needed rather than how to retrieve it, such as SQL
• Also referred to as declarative languages
• Also called "set-at-a-time" DMLs because it can retrieve many records using a single DML statement
• Low-level or procedural language allows users to tell the system exactly how to manipulate data
• Embedded in a general-purpose programming language
• Also called "record-at-a-time"

Database System Utilities and Tools

• Most DBMSs have database utilities that help the DBA manage the database system
• They perform functions such as loading (loading existing data files into a database, includes data conversion tools)
• Backup creates a copy for restoration in case of failure
• Performance monitoring monitors database usage and provides statistics

Classification of DBMSs

• There are several criteria used to classify a DBMS
• Classification includes model and number of users
• Based on the data model:
• Relational data model: represents the database as a collection of tables
• Object-oriented data model: defines the database in terms of objects, properties, and relationships
• Hierarchical data model: represents data as a tree structure where each child has only one parent
• Network data model: represents objects and their relationships in the form of a graph where a child can be related to more than one parent
• Based on the number of users
• Single-user system: supports only one user at a time
• Multiuser system: supports multiple users at the same time
• This is the most common of DBMS