Database Modeling and Database Systems - Notes

Introduction

This course book serves as the core content for the course. Additional learning materials are available on the learning platform.
The course book is divided into units, which are further divided into sections.
Each section focuses on one new key concept to facilitate efficient learning.
Self-check questions are provided at the end of each section to help students assess their understanding.
For modules with a final exam, students must complete knowledge tests on the learning platform.
A passing grade of 80% or higher on the knowledge tests for all units is required to finish the course.
Students must complete the evaluation prior to registering for the final assessment.

Basic Reading

The course refers to the following materials:

46th VLDB (2020). Proceedings of the International Conference on Very Large Data Bases (VLDB).
Date, C.J. (2019). Database design and relational theory: Normal forms and all that jazz (2nd ed.). Apress.
Documentation of Mondial Database (2010). Mondial Database.
Elmasri, R., Navathe, S. B. (2016). Fundamentals of database systems. Pearson Education Limited.Datenbank: ProQuest Ebook Central
Foster, E., Godbole, S. (2016). Database systems. A pragmatic approach. (2nd ed.). Apress.
Sumathi, S. et al (2010). Fundamentals of relational database management systems. Springer.
W3Schools (2020). SQL Tutorial.

Learning Objectives

Stored data is the base of value chains in a society focused on information and knowledge.
Data schema provide structure by organizing data that allows large amounts of information to be stored that enables easy retrieval and modification.
The goals of this course are:
- Design, model, and create relational data models.
- Use the SQL language to store and modify data in relational data models.
- Access data stored in relational databases.
- Understand properties and concepts of NoSQL Systems.

Unit 1: Relational Database Basics

Upon completing this unit, students will:
- Learn key relational database terms and their relationships.
- Understand structured storage and reading of data in relational databases.
- Identify SQL and typical relational database management systems (RDBMS).

Introduction

This unit provides a practical intro to relational databases.
It introduces key terms with real-world examples.

Basic Concepts of the Relational Data Model

Industrial information systems store data in databases within database systems.
Databases offer several advantages over simple files:
- Provide easier functions for data evaluation, that allows for complex evaluations across different data sets.
- Optimized for efficiency for evaluations and large amounts of data: reduces amount of read and written data.
- Allow simultaneous access by multiple users.
- Support fine-grained access protection by defining data access for specific users.
- Increase system reliability, restoring consistent states after write errors or crashes.
NoSQL systems de-emphasize data consistency to increase efficiency.
Key related terms:
- Databases: Contain data stored efficiently on storage media (e.g., hard disk).
- DBMS (Database Management Systems): Manage access to databases, applications access databases through DBMS.
- Database Systems: Combination of DBMS and database and sometimes applications.
The relational data model is the most common way to organize data storage.
RDBMSs (Relational Database Management Systems) administer databases organized relationally.

Structure of Relational Databases

Data records are arranged in a predefined structure for processing by information systems.
Example of customer data in a Word document. This data cannot be automatically read and modified by IT systems: “Mr. Emil Schulze has the email address e.schulze@gmail.com and is listed under customer number 00200. Ms. Silke Bauer with the customer number 00300 can be reached by email at silke334@byom.de.”
Data records are reduced to the most important information and brought into a uniform structure.
Customer data records may include customer number, title, last name, first name, and email address.
Customer data records are displayed in tables with columns and rows.
Data set is called an object.
A set of objects with the same attributes is stored in relation.
Objects are uniquely identified.
Technical attributes are primary keys.
Business IDs should be separate from technical IDs.
Table 1 contains the CUSTOMER relation with the Customer ID as the primary key.
A single relation is usually insufficient to store all required information.
Additional relations such as ADDRESS are used to store additional data.
Relations include:
- ADDRESS (ADDRESSID, STREET, HOUSENUMBER, POSTALCODE, CITY, COUNTRY).
- CUSTOMER (CUSTOMERNUMBER, TITLE, LASTNAME, FIRSTNAME, EMAIL).
Address data can be stored in structured way and customer data.
Classification of objects in the CUSTOMER relation to the ADDRESS relation uses relationships, that have names: 1:1, 1:N, and N:M.
1:1 Relationships: Connect a CUSTOMER data set with exactly one ADDRESS data set.
- Illustrated by the “primary residence” relationship.
- The CUSTOMER relation is extended by the ADDRESSID attribute identifying the ADDRESS record.
- The primary key of ADDRESS is used as foreign key in CUSTOMER.
1:N Relationships: Link one CUSTOMER object to several ADDRESS objects, such as delivery addresses.
- The ADDRESS relation is extended by the CUSTOMERID attribute.
- Values from the CUSTOMERID column of the CUSTOMER table are stored in this attribute.
N:M Relationships: Connect M objects of the CUSTOMER relation and N objects of the ADDRESS relation, such as billing addresses.
- Mapping N:M relationships require relationship tables.
- Relationship tables have the identifiers of correlating relations as attributes, such as ADDRESSID and CUSTOMERID.
- Data records consist only of foreign keys, and the primary key is a composite primary key.

Entering Data Records into Relational Databases

Data records are entered according to the following steps:
1. Select the relations/tables.
2. Insert the object with its values for it's attributes.
3. Make sure that the primary keys are set uniquely and that there are corresponding primary keys for foreign keys.
Example: Storing customer and address data for Mrs. Lisa Wagner. Requires changes to CUSTOMER, ADDRESS, and BILLINGADDRESS tables.

Terms and Definitions

Relation: A relation $R$ on the quantities $A1, A2, …, An$ is a subset of the Cartesian product $A1 X A2 … X An$ , thus $R ⊆ A1 X A2 X … X A_n$ . (e.g. ADDRESS ⊆ ADDRESSID X STREET X HOUSENUMBER X POSTALCODE X CITY X COUNTRY X CUSTOMERID)
Table: Represents a relation containing the values that belong to the relation.
Domain: The value range of an attribute.
Data Record: All data contained in a table row, in the element of the relation.
Entity: A separate and distinct item that can be uniquely identified among others. Each record stored in the database is an entity.
Attribute: Properties of entities described with name and a precisely defined value range. Each Column in a table stores value for a specific attribute with its name.
Entity set: Entities with the same properties are combined into entity sets. All records stored in a table are a entity set.
NULL Value: An attribute value of NULL means that no information has been saved for this attribute which usually requires as much memory as a valid value.
Primary key: Attribute or set of attributes of an entity that is used to uniquely distinguish from another. A primary key must never have a NULL Value.
Referential integrity: Property where all foreign keys used are actually used as primary keys in another table.
Artificial Key: Attribute added to a relation for use where there is no natural and unique primary key.
Foreign key: Attribute of a table that references a primary key of another table where there must be an actual primary key for each foreign key.
Consistency: The stored data records must be technically correct and consistent, and technical conditions must be fulfilled
DBMS: Typical relational database management systems include MySQL, Oracle, Microsoft SQL Server, and IBM DB2.

Finding and Deleting Records in the Database

Finding Records in Relational Databases

Steps to retrieving information:
1. Determine tables containing the desired information.
2. Identify attributes to be returned.
3. Restrict the output.
4. Sort the result.
Example: Determining locations and postal codes for shipments to David Schwarz.
Knowledge of the data model, technical meaning of foreign keys is necessary.

Deleting Records from Relational Databases

Deleting records is a operation.
Steps to deleting data:
1. Select the relations/tables to be changed.
2. Identify data records for deletion based on specified criteria.
3. Ensure all foreign keys can be resolved to maintain technical consistency.
Example: Deleting customer Silke Bauer and associated address data.

SQL and Relational Database Systems

SQL (Structured Query Language) is the internationally standardized programming language for data queries (ISO/IEC 9075).
Sublanguages of SQL:
- DML
- DDL
- TCL
- DCL
SQL is universal interface for relational database systems.
It gives base that should make developing and operating an application that doesn't depend on an DBMS.
DBMS implementations vary, so a simple DBMS exchange is not possible.
Individual DBMS have varying execution speeds.
Typical DBMSs include MariaDB, Oracle Database, IBM DB2, and Microsoft SQL Server.

Database Modeling and Database Systems - Notes

Introduction

Basic Reading

Further Reading

Learning Objectives

Unit 1: Relational Database Basics

Introduction

Basic Concepts of the Relational Data Model

Structure of Relational Databases

Entering Data Records into Relational Databases

Terms and Definitions

Finding and Deleting Records in the Database

Finding Records in Relational Databases

Deleting Records from Relational Databases

SQL and Relational Database Systems