h/p://home.hit.no/~hansha/?

page=soCware_development

Database Systems

S. Adams. Dilbert. Available: h/p://dilbert.com

Hans-Pe/er Halvorsen, M.Sc.

Old fashion Database (Data-storage) Systems

Not too long ago, this was the only data-storage device most
companies needed. Those days are over.

Deployment
Maintenance
TesPng

ImplementaPon

The SoCware
Development
Lifecycle

Design

Planning

Requirements
Analysis

Database Systems
A Database is a structured way to store lots of informaPon.
The informaPon is stored in dierent tables.
- Everything today is stored in databases!
Examples:
Bank/Account systems
InformaPon in Web pages such as Facebook, Wikipedia,
YouTube, etc.
Fronter, TimeEdit, etc.
lots of other examples!
4

Example:

Database ER Diagram

S. Adams. Dilbert. Available: h/p://dilbert.com

Database Management Systems (DBMS)

Microso5 SQL Server

Enterprise, Developer versions, etc. (Professional use)

Express version is free of charge

Oracle
MySQL (owned by Oracle, but previously owned by Sun Microsystems) -
MySQL can be used free of charge (open source license), Web sites that use
MySQL: YouTube, Wikipedia, Facebook
MicrosoC Access
IBM DB2
Sybase
MariaDB
MongoDB
etc.
7

Database Types
RelaPon Database/SQL Databases
MicrosC SQL Server
Oracle
MySQL
MariaDB
etc.

NoSQL Databases
MongoDB
etc.

SQL vs. NoSQL

Database Types

h/p://home.hit.no/~hansha/?page=soCware_development

Database Modelling
Hans-Pe/er Halvorsen, M.Sc.

10

Database Modelling
The logical structure of the database
ER Diagram (EnPty RelaPonship)

11

Database Design ER Diagram

ER Diagram (EnPty-RelaPonship Diagram)
Used for Design and Modeling of Databases.
Specify Tables and rela@onship between them (Primary Keys and Foreign Keys)
Table Name

Example:
Table Name

Column
Names
Primary Key

Primary Key
Foreign Key

RelaPonal Database. In a relaPonal database all the tables have one or more relaPon with each other using Primary Keys (PK) and Foreign Keys (FK). Note! You can
only have one PK in a table, but you may have several FKs.

Database Design Tools

Visio
PowerDesigner
CA ERwin

CA ERwin Data Modeler Community EdiPon

Community EdiPon is Free, 25 objects limit
Support for Oracle, SQL Server, MySQL, ODBC, Sybase

Toad Data Modeler

A Simple designer is also included with SQL Server
(physical model, not logical model)

13

Database - Best PracPce

Tables: Use upper case and singular form in table names not plural, e.g.,
STUDENT (not students)
Columns: Use Pascal notaPon, e.g., StudentId
Primary Key:
If the table name is COURSE, name the Primary Key column CourseId,
etc.
Always use Integer and IdenPty(1,1) for Primary Keys. Use UNIQUE
constraint for other columns that needs to be unique, e.g. RoomNumber
Specify Required Columns (NOT NULL) i.e., which columns that need to have
data or not
Standardize on few/these Data Types: int, oat, varchar(x), date5me, bit
Use English for table and column names
Avoid abbreviaPons! (Use RoomNumber not RoomNo, RoomNr, ...)

14

Database Design MicrosoC Visio

We will use Visio to
Design our Database
1
2

Select the proper Template

15

Table Name

ER Diagram Example - Visio

Primary Key (PK)

Foreign Key (FK)

17

ER DTable
iagram
Example using built-in Designer in MicrosoC SQL Server
Name
Table Name
PK
FK

PK

PK-FK Rela@onship
Table Name
PK
FK

Table Name
PK

FK
FK

Table Name
PK
FK

PK-FK Rela@onships
Table Name
PK
FK

Table Name
PK

FK
FK

PK Primary Key, FK Foreign Key

Table Name
PK
FK
FK
18

Exercise Database Modelling

Create a Database model (ER Diagram) of a
Library System
A Library System typically contains
informaPon about Books, Authors, Publisers,
etc.
Use a proper tool (Visio, ERwin, ...)

h/p://home.hit.no/~hansha/?page=soCware_development

Hans-Pe/er Halvorsen, M.Sc.

20

SQL Server
Main parts: SQL Server Engine + Management
Studio
Latest version: SQL Server 2014
SQL Server Standard, Developer, Web,
Enterprise, Datacenter, ...
Free AlternaPve: SQL Server Express
SQL Azure Database Cloud-based version
21

MicrosoC SQL Server

SQL Server consists of a Database Engine and a Management Studio. The Database Engine has no graphical interface - it is just a service
running in the background of your computer (preferable on the server). The Management Studio is graphical tool for conguring and viewing
the informaPon in the database. It can be installed on the server or on the client (or both).

The newest version of MicrosoC SQL

Server is SQL Server 2014

22

MicrosoC SQL Server

3

Your SQL Server

2

Write your Query here

Your Database

Your
Tables
5

The result from your Query

23

MicrosoC SQL Server Create a New Database

2

Name you database, e.g.,

WEATHER_SYSTEM

24

MicrosoC SQL Server Tips and Tricks

Do you get an error
when trying to
change your tables?

Make sure to uncheck

this opPon!

25

Exercise SQL Server

Create a new Database (LIBRARY)
Implement the dierent tables based on the
ER diagram you have created (BOOK,
AUTHOR, PUBLISHER, etc.)

h/p://home.hit.no/~hansha/?page=soCware_development

SQL

Structured Query Language

Hans-Pe/er Halvorsen, M.Sc.

27

SQL Structured Query language

A Database Computer Language designed for Managing Data in
RelaPonal Database Management Systems (RDBMS)
Query Examples:

insert into STUDENT (Name , Number, SchoolId)

values ('John Smith', '100005', 1)

select SchoolId, Name from SCHOOL

select * from SCHOOL where SchoolId > 100

update STUDENT set Name='John Wayne' where StudentId=2

delete from STUDENT where SchoolId=3

We have 4 dierent Query Types (CRUD): INSERT, SELECT, UPDATE and DELETE

CRUD Create (Insert), Read (Select), Update and Delete

28

Create Tables using the Designer Tools in SQL Server

Even if you can do everything using the SQL language, it is somePmes easier to do
something in the designer tools in the Management Studio in SQL Server.
Instead of creaPng a script you may as well easily use the designer for creaPng tables,
constraints, inserPng data, etc.
1

Select New Table :

2
Next, the table designer pops up where you
can add columns, data types, etc.

In this designer we may also specify constraints, such as

primary keys, unique, foreign keys, etc.

29

Create Tables with the Database Diagram

2

You may select

exisPng tables or
create new Tables
Create New Table
3

Enter Columns, select Data Types,

Primary Keys, etc.

30

Create Tables using SQL

if not exists (select * from dbo.sysobjects where id = object_id(N'[SCHOOL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

CREATE TABLE [SCHOOL]

(
[SchoolId] [int] IDENTITY(1, 1) NOT NULL PRIMARY KEY,
[SchoolName] [varchar](50) NOT NULL UNIQUE,
[Description] [varchar](1000) NULL,
[Address] [varchar](50) NULL,
[Phone] [varchar](50) NULL,
[PostCode] [varchar](50) NULL,
[PostAddress] [varchar](50) NULL,
if not exists (select * from dbo.sysobjects where id = object_id(N'[CLASS]') and OBJECTPROPERTY(id,
)
CREATE TABLE [CLASS]
GO
(

N'IsUserTable') = 1)

[ClassId] [int] IDENTITY(1, 1) NOT NULL PRIMARY KEY,

[SchoolId] [int] NOT NULL FOREIGN KEY REFERENCES [SCHOOL] ([SchoolId]),
[ClassName] [varchar](50) NOT NULL,
[Description] [varchar](1000) NULL,
)
GO

31

Get Data from mulPple tables in a single

Query using Joins
Example:

select
SchoolName,
CourseName
from
You link Primary Keys and Foreign Keys together
SCHOOL
inner join COURSE on SCHOOL.SchoolId = COURSE.SchoolId

32

CreaPng Views using the Editor

3

Graphical Interface where you can select columns you need

1

2
4
Add necessary tables
Save the View

33

Create View:

CreaPng Views using SQL

A View is a virtual table that

can contain data from mulPple
tables
The Name of the View

IF EXISTS (SELECT name

FROM
sysobjects
WHERE name = 'CourseData'
AND
type = 'V')
DROP VIEW CourseData
GO

Inside the View you join the

dierent tables together using
the JOIN operator

CREATE VIEW CourseData

AS
SELECT
SCHOOL.SchoolId,
SCHOOL.SchoolName,
COURSE.CourseId,
COURSE.CourseName,
COURSE.Description
FROM the View:
Using
SCHOOL
select
* from
CourseData =
INNER
JOIN COURSE
ON SCHOOL.SchoolId

You can Use the View as an

ordinary table in Queries :
COURSE.SchoolId

GO

34

Create Stored Procedure:

Stored Procedure

IF EXISTS (SELECT name

FROM sysobjects
WHERE name = 'StudentGrade'
AND
type = 'P')
DROP PROCEDURE StudentGrade
OG

CREATE PROCEDURE StudentGrade

@Student varchar(50),
@Course varchar(10),
@Grade varchar(1)

AS

DECLARE
@StudentId int,
@CourseId int

select StudentId from STUDENT where StudentName = @Student

select CourseId from COURSE where CourseName = @Course

insert into GRADE (StudentId, CourseId, Grade)
values (@StudentId, @CourseId, @Grade)
GO

A Stored Procedure is like Method in C#

- it is a piece of code with SQL
commands that do a specic task and
you reuse it
Procedure Name
Input Arguments
Internal/Local Variables
Note! Each variable starts with @
SQL Code (the body of the
Stored Procedure)

Using the Stored Procedure:

execute StudentGrade 'John Wayne', 'SCE2006', 'B'

35

Trigger
A Trigger is executed when you insert, update or delete data in a Table specied in the Trigger.

Create the Trigger:

IF EXISTS (SELECT name
FROM
sysobjects
WHERE name = 'CalcAvgGrade'
AND
type = 'TR')
DROP TRIGGER CalgAvgGrade
GO

CREATE TRIGGER CalcAvgGrade ON GRADE

FOR UPDATE, INSERT, DELETE
AS
DECLARE
@StudentId int,
@AvgGrade float

Name of the Trigger

Specify which Table the
Trigger shall work on

Specify what kind of operaPons the Trigger

shall act on
Internal/Local Variables

select @StudentId = StudentId from INSERTED

select @AvgGrade = AVG(Grade) from GRADE where StudentId = @StudentId
update STUDENT set TotalGrade = @AvgGrade where StudentId = @StudentId

Inside the
Trigger you can
use ordinary SQL
statements,
create variables,
etc.
SQL Code
(The body
of the Trigger)

GO

Note! INSERTED is a temporarily table containing the latest inserted data, and it is very handy to
use inside a trigger

36

Exercise SQL
Use SQL queries to implemnt data into your
LIBRARY database
WeTest the dierent Query Types (CRUD):
INSERT, SELECT, UPDATE and DELETE

Summary
DBMS Database Management System
SQL Structured Query Language. A Database
Computer Language designed for Managing Data in
RelaPonal Database Management Systems (RDBMS)
ER Diagram EnPty RelaPonship. Used for Design and
Modeling of Databases. Specify Tables and relaPonship
between them (Primary Keys and Foreign Keys)
38

References
NTNU. (2013). TDT4140 Systemutvikling. Available:
h/p://www.ntnu.no/studier/emner/TDT4140
UiO. (2013). INF1050 - Systemutvikling. Available:
h/p://www.uio.no/studier/emner/matnat/i/INF1050/
O. Widder. (2013). geek&poke. Available: h/p://geek-and-poke.com
B. Lund. (2013). Lunch. Available:
h/p://www.lunchstriper.no, h/p://www.dagbladet.no/tegneserie/lunch/
S. Adams. Dilbert. Available: h/p://dilbert.com

39

Hans-Pecer Halvorsen, M.Sc.

Telemark University College
Faculty of Technology

Department of Electrical Engineering, Informa@on Technology and Cyberne@cs

E-mail: [email protected]
Blog: hcp://home.hit.no/~hansha/

40