Principles of Database Management
2023 – 2024
Contents
1 Fundamental Concepts of Database Management 4
1.1 The Three Layer Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
1.2 DB Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
1.3 Advantages of DBS & DBM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
2 Architecture and Classification of DBMSs 8
2.1 Architecture of a DBMS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
2.2 Categorization of DBMSs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
2.2.1 Categorization based on DM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
2.2.2 Categorization based upon degree of simultaneous access . . . . . . . . . . . . . . 11
2.2.3 Categorization based on architecture . . . . . . . . . . . . . . . . . . . . . . . . . . 11
2.2.4 Categorization based on usage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
3 Conceptual Data Modeling using the (E)Er model en UML Class Diagram 12
3.1 Phases of DB design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
3.2 Entity Relationship ER Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
3.3 Limitations of the ER model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
3.4 Enhanced Entity Relationship EER Model . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
3.4.1 Specialization/ Generalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
3.5 UML Class Diagram . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
3.6 Advanced UML Modeling Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
4 Organizational aspects of DM 21
4.1 Data Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
4.2 Metadata modelling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
4.3 Data Quality . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
4.4 Data Governance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
4.5 Roles in DM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
6 Relational Databases 24
6.1 Relational Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
6.2 Types of Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
6.2.1 Superkeys and Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
6.2.2 Candidate Keys, Primary Keys and Alternative Keys . . . . . . . . . . . . . . . . 25
6.2.3 Foreign Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
6.2.4 Relational Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
6.3 Normalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
6.3.1 Inertion, Deletion and Update Anomalies . . . . . . . . . . . . . . . . . . . . . . . 26
6.3.2 Information Normalization Guidelines . . . . . . . . . . . . . . . . . . . . . . . . . 26
6.4 Normalization Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
6.4.1 First Normal Form 1NF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
6.4.2 Second Normal Form 2NF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
6.4.3 Third Normal Form 3NF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
6.4.4 Boyce-Codd Normal Form BCNF . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
6.4.5 Fourth Normal Form 4NF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
6.5 Mapping a Conceptual ER Model to a Relational Model . . . . . . . . . . . . . . . . . . . 29
6.6 Mapping a Conceptual EER Model to a Relational Model . . . . . . . . . . . . . . . . . . 32
,7 Structured Query Language SQL 33
7.1 Relational DBMS and SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
7.2 SQL Data Definition Language DDL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
7.2.1 Referential Integrity Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
7.3 SQL Data Manipulation Language DML . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
17 Data Warehousing and Business Intelligence 36
17.1 Data Warehouse Definition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
17.2 Data Warehouse Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
17.2.1 Star Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
17.2.2 Snowflake Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
17.2.3 Fact Constellation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
17.2.4 Specific Schema Issues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
17.3 Data Marts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
17.3.1 Virtual Data Warehouses and Virtual Data Marts . . . . . . . . . . . . . . . . . . 41
17.4 Business Intelligence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
17.4.1 Query and reporting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
17.4.2 Pivot Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
17.4.3 On-Line Analystical Processing OLAP . . . . . . . . . . . . . . . . . . . . . . . . . 43
17.5 OLAP Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
17.6 OLAP Queries in SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
14 Basics of Transaction Management 46
14.1 Transactions, Recovery and Concurrency Control . . . . . . . . . . . . . . . . . . . . . . . 46
14.2 Transactions and Transaction Management . . . . . . . . . . . . . . . . . . . . . . . . . . 46
14.2.1 Delineating transactions and the transaction lifecycle . . . . . . . . . . . . . . . . . 46
14.2.2 DBMS components involved in transaction management . . . . . . . . . . . . . . . 47
14.2.3 Logfile . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
14.3 Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
14.3.1 Types of Failures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
14.3.2 System Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
14.3.3 Media Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
14.4 Concurrency Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
14.4.1 Typical concurrency problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
14.4.2 Schedules and serial schedules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
14.4.3 Serializable schedules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
14.4.4 Optimistic and pessimistic schedulers . . . . . . . . . . . . . . . . . . . . . . . . . 51
14.4.5 Locking and locking protocols . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
14.5 Cascade rollback . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
14.6 The ACID Properties of Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
15 Accessing Databases and Database APIs 56
15.1 Database System Architectures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
15.2 Database APIs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
15.2.1 Proprietary vs Universal APIs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
15.2.2 Embedded vs Call-level APIs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
15.2.3 Early Binding vs Late Binding . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
15.2.4 ODBC Open DataBase Connectivity . . . . . . . . . . . . . . . . . . . . . . . . . . 59
15.2.5 OLE DB and ADO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
15.2.6 JDBC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
15.2.7 SQLJ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
15.2.8 Language-Integrated Querying . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
15.3 Object Persistence and Object-Relational Mappers . . . . . . . . . . . . . . . . . . . . . . 62
15.3.1 Object Persistence and ORMs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
15.3.2 Object Persistence with EJB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
15.3.3 Object Persistence with JPA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
Principles of Database Management
2/70
, 15.3.4 Object Persistence with JDO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
15.4 DB API Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
15.5 Database Access in the World Wide Web . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
15.5.1 Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
15.5.2 Newer Technologies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
15.5.3 REST . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
Principles of Database Management
3/70
,1 Fundamental Concepts of Database Management
File vs DB Approach to Data Management
.
File approach: Database Approach:
∗ Duplicate or redundant information ∗ Superior to the file approach in terms of
stored efficiency, consistency and maintenance
∗ Danger of inconsistent data ∗ Loose coupling btw applications and data
∗ Strong coupling btw applications and ∗ Facilities provided for data querying and
data retrieval
∗ Hard to manage concurrency control
∗ Hard to integrate applications aimed at
providing cross-company services
Database model/ scheme provides description of database data at different levels of details and de-
fines the various data items, characteristics and relationships etc. (not expected to change to
frequently)
Database model is comprised of different data models e.g.
∗ Student (number, name, address, email)
∗ Course (number, name)
∗ Building (number, address)
Data model provides a clear and unambiguous description of the data items with their characteristics
and relationships
1. Conceptual DM provides a high-level description of the data items (including description of
relationships)
2. Logical DM is a mapping of the conceptuel DM towards a specific implementation environment
and sets the relationships between them
3. Physical DM describes the database-specific implementation of the data model (refining of the
early design)
→ We mostly look at Logical and Conceptual DM
Database state represents data in db at a particular moment (changes on an ongoing basis)
Principles of Database Management
4/70
, Figure 1: Database State
1.1 The Three Layer Architecture
Figure 2: 3-Layer-Architecture
The Layers can also be defined as followed:
1. Presentation Layer (external) e.g. tables etc.
Views are the visual components responsible for displaying information to a user
2. Application Layer (conceptual/logical)
3. Data Layer (internal) stores and manages all the data
Catalog is the heart of the DBMS and contains the data definition or metadata of de DB application.
– Stores the definitions of the views, logical and internal DM and synchronizes these 3 DM to
guarantee their consistency.
1.2 DB Users
In this order:
1. Information architect designs conceptual DM
2. DB designer translates conceptual DM → logical and internal DM
3. DB administrator (DBA) is responsible for implementation and monitoring of the DB
∗ Uses Data Definition Language (DDL) to express the DB’s external, logical and internal DM
– definitions are stored in the catalog
Principles of Database Management
5/70
, 4. Application Developer develops DB applications in a programming language such as Java or
Python
5. Business user runs these apps to perform specific DB operations
DML (Data Manipulation Language) is used to retrieve, insert, delete and modify data (can be embedded
in progr. lang. or entered through a front-end querying tool)
→ SQL offers both DDL & DML statements for relational DBS
1.3 Advantages of DBS & DBM
1. Data Independence
→ implies that changes in data definitions have minimal to no impact on the apps
Physical data independence implies that neither the apps, nor the views or logical DM must be
changed when changes are made to the data storage specifications in the internal model
− DBMS should provide interfaces between logical and internal DM
Logical data independence implies that software apps are minimally affected by changes in the
conceptual or logical DM
− Views in external DM act as a protective shield
− DBMS must provide interfaces between conceptual logical and external layer
2. Managing structured, semi-structured and unstructured data
∗ Structured data
– Described according to a formal logical DM
– Ability to express integrity rules and enforce correctness of data
– Also facilitates searching, processing and analyzing data
– E.g., number, name, address and email of student
∗ Unstructured data
– No finer grained components in a file or series of characters that can be interpreted in a
meaningful way by a DMS or app
– E.g, document with biographies of famous NY citizens
→ Volume of unstructured data > structured data
∗ Semi-structured data
– Data which does hav a certain structure but the structure may be very irregular or highly
volatile
– E.g., individual users’ webpages on a social media platform or resume documents in a HR
DB.
3. Managing Data Redundancy
∗ Duplication of data can be desired in distributed env. to improve data retrieval
∗ DBMS is now responsible for management of the redundancy by providing synchronization
facilities to safeguard data consistency
∗ Compared to the file approach the DBMS quarantees correctness of the data without user
intervention
4. Specifying Integrity Rules
∗ Syntactical rules specify how the data should be represented and stored
→ CustomerID is integer; birthdate should stored as month, day, year
Principles of Database Management
6/70
, Figure 3: Lost update problem; the balance+120 is lost as a result of inconsistency in the execution of
the operations
∗ Semantic rules focus on the semantic correctness or meaning of the data
→ CustomerID is unique, account balance should be > 0; customer cannot be deleted if he
has pending invoices
∗ Integrity rules are specified as part of the conceptual/ logical DM and stored in the catalog
→ Directly enforced by the DBMS instead of apps
5. Concurrency Control
Concurrency = gelijktijdigheid → parallel execution of de concurrent units
∗ DBMS has built in facilities to support concurrent or parallel execution of DB programs
∗ Key concept is a DB transaction
∗ Read/write operations can be executed at the same time by the DBMS
∗ DBMS should avoid inconsistencies
−→ DBMS must support ACID
A tomicity requires that a transaction should either be executed in its entirety or not at all
C onsistency assures that a transaction brings the database from one consistent state to another
I solation ensures that the effect of concurrent transaction should be the same as if they would
have been executed in isolation (no interaction)
D urability ensures that the DB changes made by a transaction declarred successful can be made
permanent under all circumstances
6. Backup and Recovery Facilities
∗ Backup and recovery facilities can be used to deal with the effect of loss of data due to hardware
or network errors etc.
∗ Backup facilities can either perform a full or incremental backup
∗ Recovery facilities allow to restore the data to a previous state after loss or damage occured
7. Data Security
∗ Some users have read access, whilst others have write access to the data (role-based function-
ality)
∗ Data access can be managed via logins and passwords assigned to users
∗ Each account has its own authorization rules that can be stored in the catalog
8. Performance Utilities 3 KPI’s (key performance indicators) are
Principles of Database Management
7/70
2023 – 2024
Contents
1 Fundamental Concepts of Database Management 4
1.1 The Three Layer Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
1.2 DB Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
1.3 Advantages of DBS & DBM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
2 Architecture and Classification of DBMSs 8
2.1 Architecture of a DBMS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
2.2 Categorization of DBMSs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
2.2.1 Categorization based on DM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
2.2.2 Categorization based upon degree of simultaneous access . . . . . . . . . . . . . . 11
2.2.3 Categorization based on architecture . . . . . . . . . . . . . . . . . . . . . . . . . . 11
2.2.4 Categorization based on usage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
3 Conceptual Data Modeling using the (E)Er model en UML Class Diagram 12
3.1 Phases of DB design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
3.2 Entity Relationship ER Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
3.3 Limitations of the ER model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
3.4 Enhanced Entity Relationship EER Model . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
3.4.1 Specialization/ Generalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
3.5 UML Class Diagram . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
3.6 Advanced UML Modeling Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
4 Organizational aspects of DM 21
4.1 Data Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
4.2 Metadata modelling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
4.3 Data Quality . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
4.4 Data Governance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
4.5 Roles in DM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
6 Relational Databases 24
6.1 Relational Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
6.2 Types of Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
6.2.1 Superkeys and Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
6.2.2 Candidate Keys, Primary Keys and Alternative Keys . . . . . . . . . . . . . . . . 25
6.2.3 Foreign Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
6.2.4 Relational Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
6.3 Normalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
6.3.1 Inertion, Deletion and Update Anomalies . . . . . . . . . . . . . . . . . . . . . . . 26
6.3.2 Information Normalization Guidelines . . . . . . . . . . . . . . . . . . . . . . . . . 26
6.4 Normalization Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
6.4.1 First Normal Form 1NF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
6.4.2 Second Normal Form 2NF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
6.4.3 Third Normal Form 3NF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
6.4.4 Boyce-Codd Normal Form BCNF . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
6.4.5 Fourth Normal Form 4NF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
6.5 Mapping a Conceptual ER Model to a Relational Model . . . . . . . . . . . . . . . . . . . 29
6.6 Mapping a Conceptual EER Model to a Relational Model . . . . . . . . . . . . . . . . . . 32
,7 Structured Query Language SQL 33
7.1 Relational DBMS and SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
7.2 SQL Data Definition Language DDL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
7.2.1 Referential Integrity Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
7.3 SQL Data Manipulation Language DML . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
17 Data Warehousing and Business Intelligence 36
17.1 Data Warehouse Definition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
17.2 Data Warehouse Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
17.2.1 Star Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
17.2.2 Snowflake Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
17.2.3 Fact Constellation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
17.2.4 Specific Schema Issues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
17.3 Data Marts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
17.3.1 Virtual Data Warehouses and Virtual Data Marts . . . . . . . . . . . . . . . . . . 41
17.4 Business Intelligence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
17.4.1 Query and reporting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
17.4.2 Pivot Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
17.4.3 On-Line Analystical Processing OLAP . . . . . . . . . . . . . . . . . . . . . . . . . 43
17.5 OLAP Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
17.6 OLAP Queries in SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
14 Basics of Transaction Management 46
14.1 Transactions, Recovery and Concurrency Control . . . . . . . . . . . . . . . . . . . . . . . 46
14.2 Transactions and Transaction Management . . . . . . . . . . . . . . . . . . . . . . . . . . 46
14.2.1 Delineating transactions and the transaction lifecycle . . . . . . . . . . . . . . . . . 46
14.2.2 DBMS components involved in transaction management . . . . . . . . . . . . . . . 47
14.2.3 Logfile . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
14.3 Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
14.3.1 Types of Failures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
14.3.2 System Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
14.3.3 Media Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
14.4 Concurrency Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
14.4.1 Typical concurrency problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
14.4.2 Schedules and serial schedules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
14.4.3 Serializable schedules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
14.4.4 Optimistic and pessimistic schedulers . . . . . . . . . . . . . . . . . . . . . . . . . 51
14.4.5 Locking and locking protocols . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
14.5 Cascade rollback . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
14.6 The ACID Properties of Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
15 Accessing Databases and Database APIs 56
15.1 Database System Architectures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
15.2 Database APIs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
15.2.1 Proprietary vs Universal APIs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
15.2.2 Embedded vs Call-level APIs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
15.2.3 Early Binding vs Late Binding . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
15.2.4 ODBC Open DataBase Connectivity . . . . . . . . . . . . . . . . . . . . . . . . . . 59
15.2.5 OLE DB and ADO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
15.2.6 JDBC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
15.2.7 SQLJ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
15.2.8 Language-Integrated Querying . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
15.3 Object Persistence and Object-Relational Mappers . . . . . . . . . . . . . . . . . . . . . . 62
15.3.1 Object Persistence and ORMs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
15.3.2 Object Persistence with EJB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
15.3.3 Object Persistence with JPA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
Principles of Database Management
2/70
, 15.3.4 Object Persistence with JDO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
15.4 DB API Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
15.5 Database Access in the World Wide Web . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
15.5.1 Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
15.5.2 Newer Technologies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
15.5.3 REST . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
Principles of Database Management
3/70
,1 Fundamental Concepts of Database Management
File vs DB Approach to Data Management
.
File approach: Database Approach:
∗ Duplicate or redundant information ∗ Superior to the file approach in terms of
stored efficiency, consistency and maintenance
∗ Danger of inconsistent data ∗ Loose coupling btw applications and data
∗ Strong coupling btw applications and ∗ Facilities provided for data querying and
data retrieval
∗ Hard to manage concurrency control
∗ Hard to integrate applications aimed at
providing cross-company services
Database model/ scheme provides description of database data at different levels of details and de-
fines the various data items, characteristics and relationships etc. (not expected to change to
frequently)
Database model is comprised of different data models e.g.
∗ Student (number, name, address, email)
∗ Course (number, name)
∗ Building (number, address)
Data model provides a clear and unambiguous description of the data items with their characteristics
and relationships
1. Conceptual DM provides a high-level description of the data items (including description of
relationships)
2. Logical DM is a mapping of the conceptuel DM towards a specific implementation environment
and sets the relationships between them
3. Physical DM describes the database-specific implementation of the data model (refining of the
early design)
→ We mostly look at Logical and Conceptual DM
Database state represents data in db at a particular moment (changes on an ongoing basis)
Principles of Database Management
4/70
, Figure 1: Database State
1.1 The Three Layer Architecture
Figure 2: 3-Layer-Architecture
The Layers can also be defined as followed:
1. Presentation Layer (external) e.g. tables etc.
Views are the visual components responsible for displaying information to a user
2. Application Layer (conceptual/logical)
3. Data Layer (internal) stores and manages all the data
Catalog is the heart of the DBMS and contains the data definition or metadata of de DB application.
– Stores the definitions of the views, logical and internal DM and synchronizes these 3 DM to
guarantee their consistency.
1.2 DB Users
In this order:
1. Information architect designs conceptual DM
2. DB designer translates conceptual DM → logical and internal DM
3. DB administrator (DBA) is responsible for implementation and monitoring of the DB
∗ Uses Data Definition Language (DDL) to express the DB’s external, logical and internal DM
– definitions are stored in the catalog
Principles of Database Management
5/70
, 4. Application Developer develops DB applications in a programming language such as Java or
Python
5. Business user runs these apps to perform specific DB operations
DML (Data Manipulation Language) is used to retrieve, insert, delete and modify data (can be embedded
in progr. lang. or entered through a front-end querying tool)
→ SQL offers both DDL & DML statements for relational DBS
1.3 Advantages of DBS & DBM
1. Data Independence
→ implies that changes in data definitions have minimal to no impact on the apps
Physical data independence implies that neither the apps, nor the views or logical DM must be
changed when changes are made to the data storage specifications in the internal model
− DBMS should provide interfaces between logical and internal DM
Logical data independence implies that software apps are minimally affected by changes in the
conceptual or logical DM
− Views in external DM act as a protective shield
− DBMS must provide interfaces between conceptual logical and external layer
2. Managing structured, semi-structured and unstructured data
∗ Structured data
– Described according to a formal logical DM
– Ability to express integrity rules and enforce correctness of data
– Also facilitates searching, processing and analyzing data
– E.g., number, name, address and email of student
∗ Unstructured data
– No finer grained components in a file or series of characters that can be interpreted in a
meaningful way by a DMS or app
– E.g, document with biographies of famous NY citizens
→ Volume of unstructured data > structured data
∗ Semi-structured data
– Data which does hav a certain structure but the structure may be very irregular or highly
volatile
– E.g., individual users’ webpages on a social media platform or resume documents in a HR
DB.
3. Managing Data Redundancy
∗ Duplication of data can be desired in distributed env. to improve data retrieval
∗ DBMS is now responsible for management of the redundancy by providing synchronization
facilities to safeguard data consistency
∗ Compared to the file approach the DBMS quarantees correctness of the data without user
intervention
4. Specifying Integrity Rules
∗ Syntactical rules specify how the data should be represented and stored
→ CustomerID is integer; birthdate should stored as month, day, year
Principles of Database Management
6/70
, Figure 3: Lost update problem; the balance+120 is lost as a result of inconsistency in the execution of
the operations
∗ Semantic rules focus on the semantic correctness or meaning of the data
→ CustomerID is unique, account balance should be > 0; customer cannot be deleted if he
has pending invoices
∗ Integrity rules are specified as part of the conceptual/ logical DM and stored in the catalog
→ Directly enforced by the DBMS instead of apps
5. Concurrency Control
Concurrency = gelijktijdigheid → parallel execution of de concurrent units
∗ DBMS has built in facilities to support concurrent or parallel execution of DB programs
∗ Key concept is a DB transaction
∗ Read/write operations can be executed at the same time by the DBMS
∗ DBMS should avoid inconsistencies
−→ DBMS must support ACID
A tomicity requires that a transaction should either be executed in its entirety or not at all
C onsistency assures that a transaction brings the database from one consistent state to another
I solation ensures that the effect of concurrent transaction should be the same as if they would
have been executed in isolation (no interaction)
D urability ensures that the DB changes made by a transaction declarred successful can be made
permanent under all circumstances
6. Backup and Recovery Facilities
∗ Backup and recovery facilities can be used to deal with the effect of loss of data due to hardware
or network errors etc.
∗ Backup facilities can either perform a full or incremental backup
∗ Recovery facilities allow to restore the data to a previous state after loss or damage occured
7. Data Security
∗ Some users have read access, whilst others have write access to the data (role-based function-
ality)
∗ Data access can be managed via logins and passwords assigned to users
∗ Each account has its own authorization rules that can be stored in the catalog
8. Performance Utilities 3 KPI’s (key performance indicators) are
Principles of Database Management
7/70
