Learning Objectives

o Database Management Systems

o Data Models
o Introduction to SQL
o SQL Queries
o XML and JSON
Data and Management
● Data represents the traces of real-world
processes.
● Data is valuable but hard and costly to
manage
○ Collection, representation, storage
● Data management refers to the process of
collecting, storing, organizing, and
maintaining data to ensure its reliability,
accessibility, and security.
4
Data Management: Functionalities
● Describe real-world entities in terms of
stored data
● Create & persistently store large
datasets
● Efficiently query & update
○ Must handle complex questions
○ Must handle sophisticated

updates
○ Performance matters

5
Data Management: Functionalities
● Change structure (e.g., add
attributes)
● Concurrency control: enable
simultaneous queries, updates
etc.
● Crash recovery
● Access control, security, integrity

It is difficult and costly to

implement all these features!
6
DBMS
● Relational database management
systems (RDBMS)
● Hadoop Distributed File System
(HDFS)-based systems

● Stream management systems:

Apache Kafka

7
Database

What is a database?
• A collection of files storing related data

9
Database Management System
What is a Database Management System (DBMS)?
o A software package designed to store and
manage databases

o A large, integrated collection of data

o It models real-world enterprise.

o Entities (e.g., students, courses)
o Relationships (e.g., Jim is taking course
DA106)

10
Benefits of Database Management System

● Data independence and efficient access.

● Reduced application development time.
● Data integrity and security.
● Uniform data administration.
● Concurrent access, recovery from crash

11
DBMS Concepts: Data Models

A data model is a general,

conceptual way of structuring data.

Suppose we have book data: author,

title, publisher, pub date, price, etc.
● How should we organize such
data in files?

13
DBMS Concepts: Data Models

• Relational
• Data represented as relations
• Semi-structured (JSON)
• Data represented as trees
• Key-value pairs
• Used by NoSQL systems
• Graph
• Object-oriented

14
DBMS Concepts: Data Models
Elements of Data Model
● Instance
○ The actual data
● Schema
○ Describe what data is being stored
● Query language
○ How data can be retrieved and manipulated
The relational model of data is the most widely used.
● Main concept: relation, basically a table with rows and columns.
● Every relation has a schema, describes the columns
15
DBMS Concepts: Relational Data Models

● Instance
○ Organized as “table” or “relation”
consists of “column” aka “attribute”
aka “field” and “row” aka “tuple” aka
“record”
● Schema
○ “table name” aka “relation name”
○ “column name” aka “attribute
name”
○ Each attribute has a “type” aka
“domain” aka “data type”
17
DBMS Concepts: Relational Data Models

● “degree” of a relation
○ Number of attributes
● Example types:
○ Strings: CHAR(20), VARCHAR(50), TEXT
○ Numbers: INT, SMALLINT, FLOAT
○ MONEY, DATETIME, …
○ Usually vendor specific
○ Statically and strictly enforced

18
DBMS Concepts: Relational Data Models

Primary Key
● A Primary Key is the minimal set of
attributes of a table that has the task to
uniquely identify the rows
● It uniquely identifies a record

Foreign Key
● A foreign key is a key used to link two
tables together
● It is a column or a combination of
columns whose values match a Primary
Key in a different table.

19
DBMS Concepts: Relational Data Models

20
DBMS Concepts: Relational Data Models

Example: “type” in a Schema

cname country no_employees for_profit

Canon Japan 50000 Y

Hitachi Japan 30000 Y

Company(cname: varchar(30), country: char(20), no_employees: int, for_profit: char(1))

21
DBMS Concepts: Relational Data Models

Some key points:

● Tables in RDBMS are not ordered
○ The rows can be exchanged
● Tables don’t contain any nested attributes
○ Compare with semi structured data models
● Tables maintain data independence
○ Applications are insulated from how data are stored
○ Protection against the changes in logical and physical
structure of data
23
DBMS Concepts: Relational Data Models

Key aspects that we won’t cover (out of scope):

● Concurrency control
● Atomicity
● Logging
● Layered architecture

We will explore the basics of SQL (Structured

Query Language) used for query in a RDBMS in
the future lectures.

24
DBMS Concepts: Relational Data Models

25
What is SQL

● SQL stands for Structured Query Language

● SQL lets you access and manipulate
databases
● SQL became a standard of the American
National Standards Institute (ANSI) in 1986,
and of the International Organization for
Standardization (ISO) in 1987

27
What is SQL
What Can SQL do?
● SQL can execute queries against a database SQL can retrieve data
from a database
● SQL can insert records in a database
● SQL can update records in a database
● SQL can delete records from a database
● SQL can create new databases
● SQL can create new tables in a database
● SQL can create stored procedures in a database
● SQL can set permissions on tables, procedures…
28
What is SQL
● Although SQL is an ANSI/ISO standard,
there are different versions of the SQL
language.
● However, to be compliant with the ANSI
standard, they all support at least the
major commands (such as SELECT,
UPDATE, DELETE, INSERT, WHERE) in a
similar manner.
● Most of the SQL database programs also
have their own proprietary extensions in
addition to the SQL standard!
29
What is SQL
Example:
To build a website that shows data from a
database, you will need:
● An RDBMS database program (i.e. MS
Access, SQL Server, MySQL)
● To use a server-side scripting
language, like PHP or ASP
● To use SQL to get the data you want
● To use HTML / CSS to style the page

30
Quick SQL Examples

● SELECT * FROM Orders

● SELECT EmployeeID FROM Orders
● SELECT DISTINCT EmployeeID FROM Orders
● SELECT * FROM Customers
ORDER BY Country;

32
SQL Details
Structured Query Language(SQL)
commands are categorised into four
categories:

1. DDL – Data Definition Language

2. DQl – Data Query Language
3. DML – Data Manipulation Language
4. DCL – Data Control Language

33
SQL: DDL (Data Definition Language)
● Data Definition Language actually
consists of the SQL commands that
can be used to define the database
schema.
● DDL is a set of SQL commands used
to create, modify, and delete
database structures but not data.
● These commands are normally not
used by a general user, who should
be accessing the database via an
application.
34
SQL: DQL (Data Query Language)
● DQL statements are used for
performing queries on the data
within schema objects.
● It is a component of SQL statement
that allows getting data from the
database and imposing order upon
it.
● When a SELECT is fired against a
table or tables the result is compiled
into a further temporary table, which
is displayed or perhaps received by
the program i.e. a front-end.
35
SQL: DML(Data Manipulation Language)
● The SQL commands that deals with
the manipulation of data present in
the database belong to DML or Data
Manipulation

• INSERT: Adds new records to a table.

• UPDATE: Modifies existing records in a
table.
• DELETE: Removes records from a table.

37
SQL: DCL (Data Control Language)
● DCL includes commands such as
GRANT and REVOKE which mainly
deal with the rights, permissions,
and other controls of the database
system.

38
SQL: TCL

There is one more category known as

Transaction Control Language (TCL).
These commands are used to
manage transactions in the database.
These are used to manage the
changes made by DML-statements. It
also allows statements to be grouped
together into logical transactions.

39