DA113E
Database Techniques
Introduction
[Link]@[Link]
What is a databse?
Where are Databases used?
?
Where are Databases used?
Here are some examples of where databases may be used:
Banking
• Healthcare
• Web Applications
• Library Applications
• Map Applications
• Bank Systems
• Computer Games
• Shops and Ordering Systems
• Software Design Tools
• Statistical Applications
• Robotics
Why do we need a database?
?
Database vs File
• Size of data
• Updating
• Security
• Accuracy
• Saving changes
• …..
What is data?
Let us look at what data is.
Definition:
Information in raw or unorganized form (such as alphabets,
numbers, or symbols) that refer to, or represent, conditions,
ideas, or objects. Data is limitless and is present everywhere in
the universe.
[Link]
Data vs. Information
What is data and what is information?
He has black hair He has two legs
He is 180cm tall He is married
His weight is 80kg He is Swedish
He is 27 years old His name is Jhonny
Data vs. Information
“Data are mere numbers and figures while information gives meaning to these numbers and figures.”
([Link]
He has black hair He has two legs
He is 180cm tall He is married
His weight is 80kg He is Swedish
He is 27 years old His name is Jhonny
What is a database?
If we collect the data
about a lot of people in
one place, this is called a
database
Data or
Information?
Database
Example: Hospital Database
patients
patient_id patient_name address
760606-3140 John Doe Elm Street 6
540912-1818 Eve Smith Downing Street 32
831211-8923 Ron Lind Main Road 44
visits
visit_id patient_id date fee
10 760606-3140 2011-01- 300
11 540912-1818 2011-05- 300
12 831211-8923 2011-06- 300
diagnosis
visit_id diagnosis medication
10 pneumonia Pneusic
11 flue Tr Exo
12 cold NULL
What is a database?
• A database is a collection of data describing limited
and well defined part of the world.
• Data in a database is Persistent (Permanent).
• A database shall be consistent and shall not contain
contradictions.
Example of contradiction (inconsistency):
Bob is married to Sara.
Sara is not married.
What is a database?
Definition:
Collection of persistent data
Managed by a database management system (DBMS).
Example of DBMS
Examples of commonly used DBMS are:
– mySQL ([Link]
– Oracle ([Link]
– PostgreSQL ([Link]
– SQL Server ([Link]
– DB2 ([Link]
– H2 ([Link]
DBMS
• A DBMS, or "Database Management System", is a system or
software designed to manage databases.
• A DBMS is used to organize, analyze and modify information stored in
a database.
Why use Databases / DBMS?
1. Fast access to data
2. Easy to change data
3. Standardized interface for applications
4. Manage memory usage – Large amounts of data
cannot be saved in RAM only
5. Allow many users to access the same data
6. May control user privileges
7. May handle transactions to prevent multiple
users changing the same data at the same time.
Why use Databases / DBMS?
8. DBMS is a complex software system that
performs complex operations to ease data
handling.
9. Compare having data in text-files, that the applications
have to manually read/save. It is both unsafe and
It takes a lot of extra time.
10. DBMS makes application development easier!
Databases Illuminated, Page 9
RELATIONAL DATABASES
Different types of databases
The way data is stored in the database can differ.
1. Object databases
2. NoSQL databases (Big Data)
3. XML databases
4. Relational databases
Example: Hospital Database
patients
patient_id patient_name address
760606-3140 John Doe Elm Street 6
540912-1818 Eve Smith Downing Street 32
831211-8923 Ron Lind Main Road 44
visits
visit_id patient_id date fee
10 760606-3140 2011-01- 300
11 540912-1818 2011-05- 300
12 831211-8923 2011-06- 300
diagnosis
visit_id diagnosis medication
10 pneumonia Pneusic
11 flue Tr Exo
12 cold NULL
Terminology
• A Table holds the data and is also called relation
• A table consists of a number of rows and columns
• A row represents one entry in the table
• A column represents a field containing a value of specified type.
• Column may also be called attribute
• A row in the database is also called tuple or record
• The tuples in the database does not have any specific order
Terminology - example
A table named ”patients”
containing patient information
patients
patient_id patient_name adress Column names
Rows / 760606-3140 John Doe Elm Street 6
Tuples 540912-1818 Eve Smith Downing Street 32
The domain
depends on the
attribute. The
domain of
Columns / patient_id may be
Attributes all valid social
security numbers.
This specific table has the cardinality 2 since we have 2 tuples. The domain of the
whole patients
This specific table is of degree 3 since we have 3 columns. table may be all
people in Sweden.
Database schema
In relational databases we make a difference between:
• Data – The ”things” we store in the database when it is
created.
• Schema - The schema is the logical model of the database
that describes how the data are arranged.
patients ID
Name
visits Addres
Hospital
diagnosis
Database schema
This is the schema of the hospital
database.
The schema does not include any
data. It does only define the logical
structure of the database.
About relational databases
1. Data is stored in relations (tables). The relations can, and will often be
related to each other.
2. For tuples in a relation of a relational database the following must be
true:
– No duplicates.
– Tuples are unordered
– Attributes are unordered
– Only one value for each attribute in each tuple
3. All tuples in a relational database shall apply to the same domain
(you cannot store information about cartoon movies and hospital
patients in the same database because they are not related to each
other)
DESIGNING DATABASES
Database schema
• The database schema describes how a database is designed.
• To design a database is to define its schema.
Diagrams
• When designing (modeling) a database it is recommended to
draw a diagram of it:
– Diagrams are easy for everyone to understand
– Diagrams are a way to document your work
– Diagrams make you think through the design before
creating the database
Database modelling
Databases are modeled on different levels:
• Conceptual models, e.g. the ER Model (Entity
Relationship Model). Describes the concepts and the real
world. Independent of DBMS.
• Logical models or Relational models, describes the
database schema.
Specific for a DBMS.
•
Physical models, How the data is stored physically.
Specific for a DBMS.
The ER Model
The ER Model describes what data we will store and how the
data is connected. Independent of DBMS!
Patient Name
Date Fee
Patient ID
Address
1 n
Patient attends Visit
This model tells us that a patient has a name, id and address and that
one patient can attend to several visits.
DBMS and Tools
In this course we will use the following software:
– MySQL Server
– MySQL Workbench
• [Link]
Follow the installation guide on Canvas under the
Module ”Resources”.
Check for the latest version.
Make sure you download and install Both of them!
(Port 3306 must be open and don’t forget the root password)
