PRINCIPLES OF DATABSE MANAGEMENT
INHOUD
1. Fundamental Concepts of Database Management.......................................................................................2
2. Architecture and classification of DBMS.......................................................................................................4
Architecture of a DBMS................................................................................................................................4
Connection & security manager...................................................................................................................5
DDL Compiler................................................................................................................................................5
Query Processor...........................................................................................................................................5
Storage manager..........................................................................................................................................6
DBMS Utilities...............................................................................................................................................6
DBMS Interfaces...........................................................................................................................................6
Categorization of DBMS................................................................................................................................6
3. Conceptual Data Modeling using the (E)ER Model and UML Class Diagram.................................................8
Phases of Database Design...........................................................................................................................8
The ER model................................................................................................................................................8
The enhanced ER model (EER)......................................................................................................................9
The UML Class Diagram..............................................................................................................................10
4. Organizational Aspects of Data Management............................................................................................11
Data Management......................................................................................................................................11
5. Relational Databases (chapter 6 HB)...........................................................................................................13
The Relational Model..................................................................................................................................13
Normalization.............................................................................................................................................14
Mapping a Conceptual ER Model to a Relational Model............................................................................15
Mapping a Conceptual EER Model to a Relational Model...........................................................................16
6 & 7. SQL (chapter 7).....................................................................................................................................17
Relational Database Management Systems and SQL..................................................................................17
SQL Data Definition Language.....................................................................................................................17
SQL Data Manipulation Language...............................................................................................................18
SQL Views...................................................................................................................................................19
SQL Indexes................................................................................................................................................19
SQL Privileges..............................................................................................................................................20
SQL for Metadata Management.................................................................................................................20
8. Data Warehousing and Business Intelligence (chapter 17).........................................................................20
Decision-Making levels...............................................................................................................................20
Data Warehouse Definition........................................................................................................................20
Data Warehouse Schemas..........................................................................................................................20
The Extraction, Transformation and Loading (ETL) Process........................................................................22
1
, Data Marts..................................................................................................................................................22
Virtual Data Warehouses and Virtual Data Marts.......................................................................................22
Operational Data Store (ODS).....................................................................................................................23
Data Warehouses vs. Data Lakes................................................................................................................23
Business Intelligence...................................................................................................................................24
10. Basics of Transaction Management (chapter 14)......................................................................................25
Transactions, Recovery, and Concurrency Control.....................................................................................25
Transactions and Transaction Management...............................................................................................26
Recovery.....................................................................................................................................................27
Concurrency Control...................................................................................................................................28
ACID Properties of Transactions.................................................................................................................32
11. Accessing Databases and Database APIs (chapter 15)..............................................................................32
Database System Architectures..................................................................................................................32
Classification of Database APIs...................................................................................................................33
Universal Database APIs.............................................................................................................................33
1. Fundamental Concepts of Database Management
File Approach: every application has its own data file
- Duplicate data
- Danger of inconsistent data (updated in only one file)
- Structural change data file change in all applications that use it
- Hard to manage concurrency control (multiple users)
- Hard to integrate applications (cross-company)
Database Approach: applications directly interface with DBMS (superior)
2
, - Raw data
- Metadata: data definitions are stored in catalogue of the DBMS
metadata no longer included in applications, managed by DBMS
Database model: description of database data (…); specified during database design
and not changed frequently.
Conceptual data model
- Data items with their characteristics and relationships
- EER or object oriented
- Conceptual (user-friendly, implementation independent)
Logical data model
- Translation/mapping of conceptual data model towards specific implementation
environment
- Hierarchical, relational, object-oriented, XML, etc.
Internal data model (which data stored where, what format, etc. highly DBMS
specific)
External data (subsets of the data items in the logical model (=views) tailored
towards need of specific application or groups of users) ↓
Control data access and enforce security
Database state: current state of instances
Catalog:
- data definitions, metadata
- definitions of the views, logical and internal data models and synchronization
between these three data models
Database users:
- Information architect (conceptual data model)
- Database designer (logical and internal)
- Database administrator (DBA, implementation and monitoring)
3
, - Application developer (applications; Java/Python)
- Business user
Languages :
- Data Definition Language (DDL) for definitions stored in catalog (to express
external, logical & internal models)
- Data Manipulation Language (DML) to retrieve, insert, delete and modify data
- Structured Query Language (SQL) offers both DDL and DML statements for
relational DS
+ Data Independence, specifying integrity rules
- Changes in data definitions no impact on applications
- Physical: no changes in application/views/logical data model when data storage
specification are changed in internal data model
- Logical: software applications minimally affected by changes in
conceptual/logical data model
Structured Data: described to a formal logical data model (number, name, address,
etc); integrity rules (syntactical: string, integer, etc. or semantical:
correctness/meaning) and enforced correctness + facilitates searching, progressing &
analyzing
Unstructured Data: long text documents, etc.
Semi-structured Data: certain structure but may be irregular or highly volatile (CV’s,
social media profiles)
+ Managing data redundancy
+ Concurrency control (database transaction)
- ACID
o Atomicity: executed entirely or not at all
o Consistency: transaction brings DB from one consistent state to another
o Isolation: effect of concurrent transactions same as I’d be executed in
isolation
o Durability: changes made by a transaction declared successful can be
made permanent always
+ Data security (access control; read only, etc. B2B, B2C, …)
+ Performance Utilities
- 3 KPI’s
o Response time
o Throughput rate (aantal transactions/time)
o Space utilization
2. Architecture and classification of DBMS
Architecture of a DBMS
- DDL statements: create data definitions stored in catalog
4
INHOUD
1. Fundamental Concepts of Database Management.......................................................................................2
2. Architecture and classification of DBMS.......................................................................................................4
Architecture of a DBMS................................................................................................................................4
Connection & security manager...................................................................................................................5
DDL Compiler................................................................................................................................................5
Query Processor...........................................................................................................................................5
Storage manager..........................................................................................................................................6
DBMS Utilities...............................................................................................................................................6
DBMS Interfaces...........................................................................................................................................6
Categorization of DBMS................................................................................................................................6
3. Conceptual Data Modeling using the (E)ER Model and UML Class Diagram.................................................8
Phases of Database Design...........................................................................................................................8
The ER model................................................................................................................................................8
The enhanced ER model (EER)......................................................................................................................9
The UML Class Diagram..............................................................................................................................10
4. Organizational Aspects of Data Management............................................................................................11
Data Management......................................................................................................................................11
5. Relational Databases (chapter 6 HB)...........................................................................................................13
The Relational Model..................................................................................................................................13
Normalization.............................................................................................................................................14
Mapping a Conceptual ER Model to a Relational Model............................................................................15
Mapping a Conceptual EER Model to a Relational Model...........................................................................16
6 & 7. SQL (chapter 7).....................................................................................................................................17
Relational Database Management Systems and SQL..................................................................................17
SQL Data Definition Language.....................................................................................................................17
SQL Data Manipulation Language...............................................................................................................18
SQL Views...................................................................................................................................................19
SQL Indexes................................................................................................................................................19
SQL Privileges..............................................................................................................................................20
SQL for Metadata Management.................................................................................................................20
8. Data Warehousing and Business Intelligence (chapter 17).........................................................................20
Decision-Making levels...............................................................................................................................20
Data Warehouse Definition........................................................................................................................20
Data Warehouse Schemas..........................................................................................................................20
The Extraction, Transformation and Loading (ETL) Process........................................................................22
1
, Data Marts..................................................................................................................................................22
Virtual Data Warehouses and Virtual Data Marts.......................................................................................22
Operational Data Store (ODS).....................................................................................................................23
Data Warehouses vs. Data Lakes................................................................................................................23
Business Intelligence...................................................................................................................................24
10. Basics of Transaction Management (chapter 14)......................................................................................25
Transactions, Recovery, and Concurrency Control.....................................................................................25
Transactions and Transaction Management...............................................................................................26
Recovery.....................................................................................................................................................27
Concurrency Control...................................................................................................................................28
ACID Properties of Transactions.................................................................................................................32
11. Accessing Databases and Database APIs (chapter 15)..............................................................................32
Database System Architectures..................................................................................................................32
Classification of Database APIs...................................................................................................................33
Universal Database APIs.............................................................................................................................33
1. Fundamental Concepts of Database Management
File Approach: every application has its own data file
- Duplicate data
- Danger of inconsistent data (updated in only one file)
- Structural change data file change in all applications that use it
- Hard to manage concurrency control (multiple users)
- Hard to integrate applications (cross-company)
Database Approach: applications directly interface with DBMS (superior)
2
, - Raw data
- Metadata: data definitions are stored in catalogue of the DBMS
metadata no longer included in applications, managed by DBMS
Database model: description of database data (…); specified during database design
and not changed frequently.
Conceptual data model
- Data items with their characteristics and relationships
- EER or object oriented
- Conceptual (user-friendly, implementation independent)
Logical data model
- Translation/mapping of conceptual data model towards specific implementation
environment
- Hierarchical, relational, object-oriented, XML, etc.
Internal data model (which data stored where, what format, etc. highly DBMS
specific)
External data (subsets of the data items in the logical model (=views) tailored
towards need of specific application or groups of users) ↓
Control data access and enforce security
Database state: current state of instances
Catalog:
- data definitions, metadata
- definitions of the views, logical and internal data models and synchronization
between these three data models
Database users:
- Information architect (conceptual data model)
- Database designer (logical and internal)
- Database administrator (DBA, implementation and monitoring)
3
, - Application developer (applications; Java/Python)
- Business user
Languages :
- Data Definition Language (DDL) for definitions stored in catalog (to express
external, logical & internal models)
- Data Manipulation Language (DML) to retrieve, insert, delete and modify data
- Structured Query Language (SQL) offers both DDL and DML statements for
relational DS
+ Data Independence, specifying integrity rules
- Changes in data definitions no impact on applications
- Physical: no changes in application/views/logical data model when data storage
specification are changed in internal data model
- Logical: software applications minimally affected by changes in
conceptual/logical data model
Structured Data: described to a formal logical data model (number, name, address,
etc); integrity rules (syntactical: string, integer, etc. or semantical:
correctness/meaning) and enforced correctness + facilitates searching, progressing &
analyzing
Unstructured Data: long text documents, etc.
Semi-structured Data: certain structure but may be irregular or highly volatile (CV’s,
social media profiles)
+ Managing data redundancy
+ Concurrency control (database transaction)
- ACID
o Atomicity: executed entirely or not at all
o Consistency: transaction brings DB from one consistent state to another
o Isolation: effect of concurrent transactions same as I’d be executed in
isolation
o Durability: changes made by a transaction declared successful can be
made permanent always
+ Data security (access control; read only, etc. B2B, B2C, …)
+ Performance Utilities
- 3 KPI’s
o Response time
o Throughput rate (aantal transactions/time)
o Space utilization
2. Architecture and classification of DBMS
Architecture of a DBMS
- DDL statements: create data definitions stored in catalog
4
