LECTURER: JOHN DOE
DATABASE MODELING AND
DATABASE SYSTEMS
INTRODUCTORY ROUND
Who are you?
− Name
− Employer
− Position/responsibilities
− Fun Fact
− Previous knowledge? Expectations?
TOPIC OUTLINE
Relational Database Basics 1
Database Queries to Exactly One Table
2
Conception and Modeling of Relational Databases
3
Creating Relational Databases
4
Complex Database Queries on Multiple Tables
5
TOPIC OUTLINE
Manipulating Records in Databases
6
NoSQL Database System 7
UNIT 1
RELATIONAL DATABASE BASICS
STUDY GOALS
− Explain the most important relational database
terms.
− Describe how data in relational databases is stored
and read in a structured way.
− Explain the meaning and the use of SQL.
− Compare among different commercial DBMSs.
EXPLAIN SIMPLY
1. How many times have you used a database in the last
week?
2. What is a database, and why is it used?
3. Why would we prefer to use a DBMS over a
spreadsheet for data storage?
BASIC CONCEPTS OF THE RELATIONAL DATA MODEL
− Storing data in databases is very
advantageous.
− Databases are optimized for processing
large amounts of data.
− Multiple users can access the data
simultaneously.
− Databases provide consistency rules.
Source of the images: earvine95, Pixabay; Hassan, pxhere
COMPONENTS OF A DATABASE SYSTEM
Source of the graphic: Course Book DLBCSDMDS01, p. 13
RELATIONAL DATABASES
− tables
− rows and columns
− Columns store a
specific property of
data.
− Rows contain data
about one entity.
Source of the graphic: Course Book DLBCSDMDS01, p. 14
RELATIONSHIPS IN RELATIONAL DATABASES
1:1 Relationships 1:N Relationships N:M Relationships
Insert the primary key of Insert the primary key of Break the relationship into
any of the tables into the the parent table into the two 1:N relationships using
other table as a foreign child table as a foreign a relationship table.
key. key.
Source of the images: Course Book DLBCSDMDS01, pp. 16, 18, 20
TERMS AND DEFINITIONS
− Relation: A relation R on the quantities A1, A2, ..., An is a subset of
the Cartesian product A1 XA2 ... X An, thus R ⊆ A1 X A2 X ... X An.
− Table: A table is an illustration of a relation.
− Data Record: All data in a table row are referred to as a data
record.
− Entity: An entity is something separate and distinct that can
be uniquely identified among other similar entities.
− Attribute (or Property): Concrete properties of entities are
described as attributes.
TERMS AND DEFINITIONS
− Primary key: A unique attribute that identifies a particular
record. It could be a single attribute or more, in which case it is
called a composite primary key.
− Artificial key (Surrogate key): A meaningless attribute added to a
relation only to identify records and used accordingly as a
primary key.
− Foreign key: An attribute of a table is called a foreign key if it
references a primary key of another table.
TERMS AND DEFINITIONS
− Referential integrity: Ensuring that each value of a foreign key in
a child table matches one or more primary key values in the
parent table.
− Database management system (DBMS): A software system for
operating databases is called a database management
system(DBMS).
FINDING AND DELETING RECORDS IN THE DATABASE
Source of the graphics: Aljiendi, 2022
SQL AND RELATIONAL DATABASE SYSTEMS
− SQL (Structured Query
Language)
− Data Manipulation Language
(DML)
− Data Definition Language (DDL)
− Transaction Control Language
(TCL)
− Data Control Language (DCL)
Source of the image: Mcmurryjulie, Pixabay
COMMERCIAL DBMSS
MySQL
Zinner, Wikimedia Commons; IJohnKennady, Wikimedia Commons
REVIEW STUDY GOALS
− Explain the most important relational database
terms.
− Describe how data in relational databases is stored
and read in a structured way.
− Explain the meaning and the use of SQL.
− Compare among different commercial DBMSs.
SESSION 1
TRANSFER TASK
TRANSFER TASK
TASKS
Convert the following part of a job offer letter into a record in a
relation by extracting the entities and attributes:
“Dear Mr. Johns,
We are delighted to offer you the sales manager position at ABC
bank, Branch 4 in Berlin, with an annual salary of €122,000 in
addition to the health benefits and vacation time. The start date
for this position will be January 23. Your supervisor will be Mme.
Franklin and her office number is 215.”
TRANSFER TASK
PRESENTATION OF THE RESULTS
Please present your
results.
The results will be
discussed in plenary.
LEARNING CONTROL QUESTIONS
1. What is the relationship that needs to be broken into
more two relationships to be able to implement in
DBMSs?
a) 1:1
b) 1:N
c) M:1
d) N:M
LEARNING CONTROL QUESTIONS
2. What is a surrogate key?
a) A meaningless artificial key inserted in a table to be used
as a primary key.
b) A primary key in the parent table inserted into a child
table.
c) A foreign key in a child table that also exists in many
other tables.
d) A composite primary key composed using several
attributes in the table.
LEARNING CONTROL QUESTIONS
3. SQL is …
a) … a programming language used to create web
applications.
b) … a database querying language used to process data in
databases.
c) … a pseudo-code language used to design and
graphically present databases.
d) … the data manipulation language used exclusively in
MariaDB.
LIST OF SOURCES
earvine95 (2017). 2394312 [illustration]. Pixabay. https://pixabay.com/de/illustrations/datenbank-blau-sql-server-lagerung-2394312/
Hassan, M. (2019). 1567615 [illustration]. Pxhere. https://pxhere.com/en/photo/1567615
IJohnKennady (2018). Oracle Red Logo [illustration]. Wikimedia Commons. https://commons.wikimedia.org/wiki/File:Oracle_redlogo.jpg
Mcmurryjulie (2016). 1895779 [illustration]. Pixabay. https://pixabay.com/de/vectors/datenbank-schema-datentabellen-1895779/
Zinner, M. (2012). MariaDB Seal Logo [illustration]. Wikimedia Commons. https://commons.wikimedia.org/wiki/File:Mariadb-seal-flat-browntext.png
© 2022 IU Internationale Hochschule GmbH
This content is protected by copyright. All rights reserved.
This content may not be reproduced and/or electronically edited, duplicated, or distributed in any kind of
form without written permission by the IU Internationale Hochschule GmbH.
LECTURER: JOHN DOE
DATABASE MODELING AND
DATABASE SYSTEMS
TOPIC OUTLINE
Relational Database Basics 1
Database Queries to Exactly One Table
2
Conception and Modeling of Relational Databases
3
Creating Relational Databases
4
Complex Database Queries on Multiple Tables
5
TOPIC OUTLINE
Manipulating Records in Databases
6
NoSQL Database System 7
UNIT 2
DATABASE QUERIES TO
EXACTLY ONE TABLE
STUDY GOALS
− Use the SQL SELECT statement to query data in an
RDBMS.
− Filter query results using the WHERE clause.
− Group query results using the GROUP BY and HAVING
clauses.
− Explain and use sub queries.
EXPLAIN SIMPLY
1. What is the difference between data and
information?
2. Why would you use queries instead of checking the
entire contents of a table (2 reasons)?
3. What is the general form of an SQL SELECT
statement?
TABLE EXAMPLE IN HEIDISQL
Source of the image: Aljendi, 2022
THE SELECT * STATEMENT
Source of the image: Aljendi, 2022
THE SELECT CERTAIN COLUMNS
Source of the image: Aljendi, 2022
ELIMINATION OF DUPLICATES
Source of the image: Aljendi, 2022
FILTERING RESULTS
Source of the image: Aljendi, 2022
FILTERING RESULTS WITH LOGICAL OPERATIONS
Source of the image: Aljendi, 2022
EMPTY QUERY RESULT
Source of the image: Aljendi, 2022
SORTING
Source of the image: Aljendi, 2022
GROUPING
Source of the image: Aljendi, 2022
SUBQUERIES
Source of the image: Aljendi, 2022
REVIEW STUDY GOALS
− Use the SQL SELECT statement to query data in an
RDBMS.
− Filter query results using the WHERE clause.
− Group query results using the GROUP BY and HAVING
clauses.
− Explain and use sub queries.
SESSION 2
TRANSFER TASK
TRANSFER TASK
TASKS
Given the following ACCOUNT table:
Source of the image: Aljendi, 2022
TRANSFER TASK
TASKS
Create queries that return:
1. Account type, account holder first name, account holder last
name and account balance of all records
2. Account types without duplicates
3. First and last names of savings account holders
4. First and last names of negative balance accounts
5. First and last names of negative balance Savings accounts
6. The content of the table sorted by account type
7. The content of the table sorted by account type and reverse
order of account balance
TRANSFER TASK
TASKS
8. The maximum, minimum and average of account balances
9. The sum and average of each account type
10. The sum of all acounts held by each customer (first and last
name)
11.The sum of all negative balanced acounts held by each
customer (first and last name)
12. Using a subquery, all data of negative balance accounts that
are higher than the average negative account balances
TRANSFER TASK
PRESENTATION OF THE RESULTS
Please present your
results.
The results will be
discussed in plenary.
LEARNING CONTROL QUESTIONS
1. Which SQL keyword is used in a query to return only
unique values
a) UNIQUE
b) DISTINCT
c) DIFFERENT
d) UNALIKE
LEARNING CONTROL QUESTIONS
2. Which SQL keyword is used to sort a query’s result?
a) SORT BY
b) ASCENDING
c) ORDER BY
d) ORGANIZED BY
LEARNING CONTROL QUESTIONS
3. What is a subquery?
a) A query nested in another query
b) A query that returns a subset of the data
c) A query that returns unimportant data
d) A query that returns all data in a table without filtering
© 2022 IU Internationale Hochschule GmbH
This content is protected by copyright. All rights reserved.
This content may not be reproduced and/or electronically edited, duplicated, or distributed in any kind of
form without written permission by the IU Internationale Hochschule GmbH.
LECTURER: JOHN DOE
DATABASE MODELING AND
DATABASE SYSTEMS
TOPIC OUTLINE
Relational Database Basics 1
Database Queries to Exactly One Table
2
Conception and Modeling of Relational Databases
3
Creating Relational Databases
4
Complex Database Queries on Multiple Tables
5
TOPIC OUTLINE
Manipulating Records in Databases
6
NoSQL Database System 7
UNIT 3
CONCEPTION AND MODELING OF
RELATIONAL DATABASES
STUDY GOALS
− Design a database using an ERD.
− Model relationships among tables within an ERD.
− Normalize the tables in a database.
EXPLAIN SIMPLY
1. Why is it necessary to design a database before
starting implementation?
2. Is data redundancy a desired or undesired feature in
a relational database?
3. Must a Primary Key be defined in each table in a
relational database?
THE ENTITY RELATIONSHIP MODEL
Sources of the images: Mcmurryjulie, pixabay; Cocoparisienne, pixabay
GRAPHICAL NOTATION OF ENTITIES AND ATTRIBUTES IN ER MODELS
Source of the graphics: Course Book DLBCSDMD01, pp. 59, 60
ERDS AND CARDINALITIES
− Maximum Cardinalities
− 1:1
− 1:M
− M:N
− Minimum Cardinalities
− optional
− mandatory
Source of the graphic: Aljiendi, 2022
NORMAL FORMS
− First Normal Form
− atomic attribute values
− Primary Key (PK)
− Second Normal Form
− In first normal form.
− No non-key attribute is identified by
part of the PK.
− Third Normal Form
− In second normal form.
− No non-key attribute is identified by
another non-key attribute.
Source of the graphic: Course Book DLBCSDMD01, pp. 69
REVIEW STUDY GOALS
− Design a database using an ERD.
− Model relationships among tables within an ERD.
− Normalize the tables in a database.
UNIT 4
CREATING RELATIONAL
DATABASES
STUDY GOALS
− Design a database through standard processes.
− Convert a conceptual design model into a physical
model.
− Use SQL to define database tables.
GRAPHICAL NOTATION OF ENTITIES AND ATTRIBUTES IN ER MODELS
Source of the graphic: Steiner, 2014, p. 83.
CONCEPTUAL AND PHYSICAL DATA MODELS
Source of the graphics: Course Book DLBCSDMD01, ps. 81, 83, 84
CONCEPTUAL AND PHYSICAL DATA MODELS
Source of the graphics: Course Book DLBCSDMD01, pp. 85, 86
CONCEPTUAL AND PHYSICAL DATA MODELS
Source of the graphics: Course Book DLBCSDMD01, pp. 87, 88, 89
CONCEPTUAL AND PHYSICAL DATA MODELS
Course Book DLBCSDMD01, ps. 89, 90
DATA TYPES
Form SQL Data Type Examples
Character strings with always exactly n CHAR(n)alternative: Country codes: DE, CN, FR, NL etc.:
characters CHARACTER(n) CHAR(2) ISBN 13 numbers:
9783836216999
CHAR(13)
UUIDs: 21EC2020–3AEA-1069-
A2DD-
08002B30309DCHAR(36)(German)
Postal codes: 45147CHAR(5)
Character strings with variable but VARCHAR (n)alternative: proper names, designations, short
limited length (max. n characters) CHARACTER descriptions: VARCHAR(55),
VARYING(20) VARCHAR(160),VARCHAR(500)
Very long character strings; the actual TEXT Texts, blog entries, detailed
allowed size is limited by technical descriptions: TEXT
parameters within the DBMS.
Source of the table: Course Book DLBCSDMD01, p. 95
DATA TYPES
Form SQL Data Type Examples
Integers; 32 bits, value INTEGER Number, index, numbering:
range from alternative: INTEGER
–2,147,483,648 to INT
2,147,483,647
Decimal numbers with a DECIMAL(n,m) Amounts of money: DECI-
maximum of n digits in total MAL(9,2),
and exactly m digits after the Key figures: DECIMAL(4,2),
decimal point.
Floating point numbers, 32bit REAL Measured values: REAL
Source of the graphic: Course Book DLBCSDMD01, p. 96
DATA TYPES
Form SQL Data Type Examples
Calendar data accurate to the DATE Date, calendar days: 2014–06–14
day, from year 1000 to year
9999 in YYYY-MM-DD format
Time data in the format TIME Time, to the second: 13:43:56
HH:MM:SS
Exact determination of a time, TIMESTAMP Selected times:
exact to the second, often in the 2014–06–14 14:00:02;
format YYYY-MM-DD Automatic saving of creation or
HH:MM:SS modification times
Source of the table: Course Book DLBCSDMD01, pp. 96-97
DATA TYPES
Form SQL Data Type Examples
Binary types with variable VARBINARY(n) Store binary data such as ima-
length, but not more than ges, audio, video, and other
n characters (n bits) binary files;
BINARY VARYING(50000);
often also called BLOB in DBMS
Boolean Values BOOLEAN Boolean Values
Source of the table: Course Book DLBCSDMD01, p. 97
CREATING TABLES
CREATE TABLE Name of the table (
Column definition,
Primary key definition,
[Integrity rules, background]
[Foreign Key Definition,]
[Specifying conditions for columns]
)
Source: Course Book DLBCSDMD01, p. 98
REVIEW STUDY GOALS
− Design a database through standard processes.
− Convert a conceptual design model into a physical
model.
− Use SQL to define database tables.
SESSION 3
TRANSFER TASK
TRANSFER TASK
TASKS
Given the following branch table:
Branch ID Branch Branch phone Department Department Employee ID Employee
address number ID name Name
1. Normalize the above table by putting it in the third normal
form.
2. Draw the ERD of the normalized tables.
3. Write the SQL code that creates the normalized tables.
TRANSFER TASK
TASKS
Source of the image: Aljiendi, 2022
TRANSFER TASK
PRESENTATION OF THE RESULTS
Please present your
results.
The results will be
discussed in plenary.
LEARNING CONTROL QUESTIONS
1. What is the graphical representation of a database
design called?
a) MS Access
b) Easy Design
c) QBE
d) ERD
LEARNING CONTROL QUESTIONS
2. What does normalization mean?
a) Remove as much data redundancy on the table level to
avoid data anomalies.
b) Add as much data redundancy on the table level to avoid
data anomalies.
c) Remove as much data redundancy on the table level to
allow data anomalies.
d) Add as much data redundancy on the table level to allow
data anomalies.
LEARNING CONTROL QUESTIONS
3. What is the particularity of the VARCHAR datatype?
a) Data occupies more memory when it is longer than the
defined length.
b) Data occupies less memory when it is longer than the
defined length.
c) Data occupies less memory when it is shorter the defined
length.
d) Data occupies more memory when it is longer than the
defined length.
LIST OF SOURCES
cocoparisiene (2014). 354233 [photo]. Pixabay. https://pixabay.com/photos/blueprint-floor-plan-architect-plan-354233/
mcmurryjulie (2016). 1895779 [graphic]. Pixabay. https://pixabay.com/vectors/database-schema-data-tables-schema-1895779/
Steiner, R. (2014). Grundkurs Relationale Datenbanken [Basic course relational databases] (8th ed.). Springer Vieweg.
© 2022 IU Internationale Hochschule GmbH
This content is protected by copyright. All rights reserved.
This content may not be reproduced and/or electronically edited, duplicated, or distributed in any kind of
form without written permission by the IU Internationale Hochschule GmbH.
LECTURER: JOHN DOE
DATABASE MODELING AND
DATABASE SYSTEMS
TOPIC OUTLINE
Relational Database Basics 1
Database Queries to Exactly One Table
2
Conception and Modeling of Relational Databases
3
Creating Relational Databases
4
Complex Database Queries on Multiple Tables
5
TOPIC OUTLINE
Manipulating Records in Databases
6
NoSQL Database System 7
UNIT 5
COMPLEX DATABASE QUERIES
ON MULTIPLE TABLES
STUDY GOALS
− Use composite quantities or JOINs to query more
than one table.
− Use set operations to gather combine the results of
more than one query.
− Use views to reuse the results of complex queries.
EXPLAIN SIMPLY
1. What is the main advantage of JOINs?
2. Why are there different types of JOINs?
3. What is a VIEW?
COMPOSITE QUANTITIES (JOIN)
Source of the image: Aljendi, 2022
THE BRANCH TABLE
Source of the image: Aljendi, 2022
THE FINANCIAL ADVISOR TABLE
Source of the image: Aljendi, 2022
THE CUSTOMER TABLE
Source of the image: Aljendi, 2022
EXAMPLE OF A COMPOUND SET
Source of the graphics: Aljiendi, 2022
EXAMPLE OF A COMPOUND SET
Source of the image: Aljendi, 2022
DIFFERENT TYPES OF JOINS
Source of the graphic: Arbeck, Wikimedia Commons
SET OPERATIONS
Source of the graphic: Course Book DLBCSDMD01, p. 113
EXAMPLE OF A SET OPERATION
Source of the graphics: Aljiendi, 2022
VIEWS
Source of the graphic: Course Book DLBCSDMD01, p.140
EXAMPLE OF CREATING AND USING A VIEW
Source of the image: Aljendi, 2022
REVIEW STUDY GOALS
− Use composite quantities or JOINs to query more
than one table.
− Use set operations to gather combine the results of
more than one query.
− Use views to reuse the results of complex queries.
SESSION 4
TRANSFER TASK
TRANSFER TASK
TASKS
Given the same database described previously in this session,
create queries that return:
1. The ranch address, the first and last names as well as the
phone number of the financial advisors working in the branch
that has the phone number +49 1123 5543.
2. A list of the branch id and first and last names of the financial
advisors working in the branch that has the phone number +49
1123 5443 as well as their phone numbers along with their
clients (first and last names)
TRANSFER TASK
TASKS
3. The branch ID, the first and last names as well as the phone
numbers of financial advisors whose last name is Schmidt along
with the first and last names of their clients.
4. The branch ID, the advisor‘s first and last names as well as their
phone numbers, the client‘s first and last names as well as their
phone numbers for any person (whether client or advisor)
whose last name is Muller.
TRANSFER TASK
TASKS
5. Using a view, the unique branch numbers of any person
(whether an advisor or a client) whose last name is Muller.
6. Number of clients in each branch address.
7. The branch phone number as well as the number of clients in
the branch of which the phone number is +49 1123 5543.
TRANSFER TASK
PRESENTATION OF THE RESULTS
Please present your
results.
The results will be
discussed in plenary.
LEARNING CONTROL QUESTIONS
1. A LEFT JOIN returns
a) All the records form the table on the left side of the JOIN
and only the matching records from the right table.
b) All the records form the table on the right side of the
JOIN and only the matching records from the left table.
c) All the records form the tables on both sides of the JOIN
d) All the records form the tables on both sides of the JOIN
and have matching vlues.
LEARNING CONTROL QUESTIONS
2. Which of the following is correct about the set
operations in SQL?
a) Both the number of the columns and the types of the
different queries might be different.
b) The number of the columns of the different queries
might be different but the types must be the same.
c) The number of the columns of the different queries must
be the same but the types might be different.
d) The number and types of the columns of the different
queries must be the same.
LEARNING CONTROL QUESTIONS
3. Which of the following is used to reuse the results of
complex queries?
a) SELECT AGAIN
b) CREATE VIEW
c) SET operations
d) OUTER JOIN
LIST OF SOURCES
Arbeck (2013) SQL joins [illustration]. Wikimedia Commons. https://commons.wikimedia.org/wiki/File:SQL_Joins.svg
© 2022 IU Internationale Hochschule GmbH
This content is protected by copyright. All rights reserved.
This content may not be reproduced and/or electronically edited, duplicated, or distributed in any kind of
form without written permission by the IU Internationale Hochschule GmbH.
LECTURER: JOHN DOE
DATABASE MODELING AND
DATABASE SYSTEMS
TOPIC OUTLINE
Relational Database Basics 1
Database Queries to Exactly One Table
2
Conception and Modeling of Relational Databases
3
Creating Relational Databases
4
Complex Database Queries on Multiple Tables
5
TOPIC OUTLINE
Manipulating Records in Databases
6
NoSQL Database System 7
UNIT 6
MANIPULATING RECORDS IN
DATABASES
STUDY GOALS
− Use SQL statements to insert new records.
− Use SQL statements to modify records and schemas.
− Explain what transactions are and how they operate.
EXPLAIN SIMPLY
1. What is a DEFAULT value in SQL?
2. Give an example on why a developer would need to
change a database schema.
3. What is a bank transaction?
INSERT STATEMENT
Sources of the graphics: Course Book DLBCSDMD01, p.118
Aljendi, 2022
INSERT EXAMPLES
Source of the image: Aljendi, 2022
COPYING DATA RECORDS
Source of the images: Aljendi, 2022
UPDATE EXAMPLE
Source of the graphic: Course Book DLBCSDMD01, p.123
UPDATE EXAMPLE
Source of the image: Aljendi, 2022
DELETING DATA (DELETE)
Source of the image: Aljendi, 2022
CHANGING TABLES (ALTER TABLE)
Source of the table: Course Book DLBCSDMD01, pp.124-125
ALTER EXAMPLES
Source of the graphics: Aljendi, 2022
TRANSACTIONS
Source of the graphic: Course Book DLBCSDMD01, p.128
REVIEW STUDY GOALS
− Use SQL statements to insert new records.
− Use SQL statements to modify records and schemas.
− Explain what are transactions and how they operate.
SESSION 6
TRANSFER TASK
TRANSFER TASK
TASKS
Given the following financial advisor table:
1. Write the SQL statements used to insert the data shown in the
above image in the table (named finencial_advisor).
2. Modify the FinencialAdvisorID field so that its value
automatically increases for each new inserted record.
TRANSFER TASK
TASKS
3. Insert a record of a new finencial advisor whose name is Susan
Huber and email address is
[email protected]. Check that
the new record‘s ID is 5.
4. Add a phone number to Susan‘s record using her ID. The phone
number is +49 1875 6632 ext. 2.
5. Add a constraint to ensure that the phone number cannot be
empty.
6. Delete all financial advisors working at branch 1.
TRANSFER TASK
TASKS
7. Add to the table a new integer column named
numberOfEmploymentYears and then set this value for all
employees to 3.5. Check the contents of the table.
8. Delete the branch_branchID from the table.
TRANSFER TASK
PRESENTATION OF THE RESULTS
Please present your
results.
The results will be
discussed in plenary.
LEARNING CONTROL QUESTIONS
1. In order to delete a value from a cell, which of the
following is used
a) DELETE value
b) DROP value
c) UPDATE table
d) DROP table
LEARNING CONTROL QUESTIONS
2. Which of the following is used to add a foreign key to
a table
a) ALTER table
b) ADD foreign key
c) ALERT CHANGE
d) CONSTRAINT foreign key
LEARNING CONTROL QUESTIONS
3. Which of the following is executed at the end of a
successful transaction?
a) COMMIT
b) EXECUTE
c) ROLLBACK
d) DO
© 2022 IU Internationale Hochschule GmbH
This content is protected by copyright. All rights reserved.
This content may not be reproduced and/or electronically edited, duplicated, or distributed in any kind of
form without written permission by the IU Internationale Hochschule GmbH.
LECTURER: JOHN DOE
DATABASE MODELING AND
DATABASE SYSTEMS
TOPIC OUTLINE
Relational Database Basics 1
Database Queries to Exactly One Table
2
Conception and Modeling of Relational Databases
3
Creating Relational Databases
4
Complex Database Queries on Multiple Tables
5
TOPIC OUTLINE
Manipulating Records in Databases
6
NoSQL Database System 7
UNIT 7
NoSQL DATABASE SYSTEMS
STUDY GOALS
− Explain the meaning and the need for NoSQL.
− Discuss the difference between constraints of
relation and non-relational data models.
− Explain the differences among different available
NoSQL system types.
EXPLAIN SIMPLY
1. List three drawbacks of the relational model.
2. What are the two modern data management needs
that led to the adoption of NoSQL paradigms?
3. Many NoSQL systems are open-source. What does
open-source mean?
WHY NOSQL?
− Not Only SQL
− Release much of the SQL constraints
− hundreds of thousands of users
− huge amounts of data
− flexibility
− schema-free or weak schema restrictions
− high availability
− scalability
NOSQL PROPERTIES
− The underlying data model is not relational.
− Distributed and horizontal scalability:
− growing data
− increasing users
− simple replication
− semi- and unstructured data
BASE VS. ACID
− ACID (Relational model) − BASE (Non-relational model)
− Atomicity − Basically available
− Consistency − Soft state
− Isolation − Eventual consistency
− Durability
NOSQL SYSTEM TYPES: KEY-VALUE SYSTEM
Source of the graphic: Course Book DLBCSDMD01, p.140
NOSQL SYSTEM TYPES: WIDE COLUMN STORES
Source of the graphic: Course Book DLBCSDMD01, p.141
NOSQL SYSTEM TYPES: DOCUMENT STORES- JSON
Source of the graphic: Course Book DLBCSDMD01, p.142
NOSQL SYSTEM TYPES: GRAPH DATABASES
Source of the graphic: Course Book DLBCSDMD01, p.143
REVIEW STUDY GOALS
− Explain the meaning and the need for NoSQL.
− Discuss the difference between constraints of
relation and non-relational data models.
− Explain the differences among different available
NoSQL system types.
SESSION 6
TRANSFER TASK
TRANSFER TASK
TASKS
Convert the following part of a job offer letter into a JSON
document:
“Dear Mr. Johns,
We are delighted to offer you the sales manager position at ABC
bank, Branch 4 in Berlin, with an annual salary of €122,000 in
addition to the health benefits and vacation time. The start date
for this position will be on January 23. Your supervisor will be Mme.
Franklin and her office number is 215.”
TRANSFER TASK
PRESENTATION OF THE RESULTS
Please present your
results.
The results will be
discussed in plenary.
LEARNING CONTROL QUESTIONS
1. Why NoSQL databases are considered more flexible
than relational databases?
a) NoSQL databases are schema-less.
b) NoSQL databases are open-source.
c) NoSQL databases are based on the ACID constraints.
d) NoSQL database schemas can be changed by the end-
user.
LEARNING CONTROL QUESTIONS
2. What does Eventual Consistency mean with the BASE
constraints?
a) The database might or might not reach a consistent state
b) The database will reach a consistent state eventually
c) The database will always be inconsistent
d) Consistency must be manually maintained by the
administrator
LEARNING CONTROL QUESTIONS
3. Which of the following is an example of document
stores:
a) Cassandra
b) MariaDB
c) MongoDB
d) Oracle
EVALUATION
How did you
like the course?
© 2022 IU Internationale Hochschule GmbH
This content is protected by copyright. All rights reserved.
This content may not be reproduced and/or electronically edited, duplicated, or distributed in any kind of
form without written permission by the IU Internationale Hochschule GmbH.
