Hans-Pe2er

Halvorsen, Telemark University College, 2013.04.08

Database Lab Tips & Tricks

Design and Implement a System for Logging and PresentaSon of Weather Data

Weather Sta,on

Apps for Manipula,on

and Presenta,on
of Weather Data

Logging Weather Data

Database

Note! The Assignment is located here:

h2p://home.hit.no/~hansha/?lab=database
1

Table of Contents

1.Introduc,on
2.Database Systems
3.Database Design - Visio
4.SQL Server
5.LabVIEW
2

1. IntroducSon

System Overview
Server

Client

Visio 2012

Database

LabVIEW 2012

(Windows Server)

(Windows 7/8)

SQL Server 2012

Note! Normally the Database is installed on a
separate Server, but for simplicity, we install the SQL
Server on our local computer

SoXware
In this Assignment you need the following SoRware:

MicrosoR Visio 2010 (Visio 2013 is not recommended!)

MicrosoR SQL Server 2012 (Express)
LabVIEW 2012
SQL Toolkit

MicrosoX Visio 2010 and MicrosoR SQL Server 2012 are free to use as they are included in
the MicrosoX DreamSpark Premium SubscripSon available for students at Telemark
University College.

In our assignment it is recommended to use the MicrosoX SQL Server 2012 Express version
which is free of charge and can be downloaded here:
hZp://www.microsoR.com/en-us/download/details.aspx?id=29062
SQL Toolkit makes it easy to access Database Systems from LabVIEW. The Toolkit is
created by Hans-PeZer Halvorsen and is free of charge from my web site.
5

Task Overview

Database Design in MS Visio

Create Database and Tables in MS SQL Server
SQL Queries (Structured Query Language)
Create & Use a Database Views
Create & Use a Database Stored Procedures
Create & Use a Database Triggers
Create Applica,ons in LabVIEW that communicates
with the Database created

See the Assignment for detailed Task DescripSons:

h2p://home.hit.no/~hansha/?lab=database
6

Tutorials, etc.
Structured Query Language
Database CommunicaSon in LabVIEW
Use these Tutorials to get a beZer
understanding of the topics involved in the
assignment
Example Code
The Example Code help you solve the
assignment!
You nd Tutorials, Example Code and Addi,onal Resources here:

h2p://home.hit.no/~hansha/?lab=database

Hardware
We will use a NI USB-TC01 Thermocouple Measurement Device in order
to log Temperature Data into the Database

NI USB-TC01 Thermocouple
Measurement Device using LabVIEW
The USB-TC01 Device works similar as the USB-6008 DAQ device, i.e., you use
the DAQ Assistant

Temperature SimulaSon
If you dont have a TC01 device, you can create a simple Temperature Simulator instead.
Example of such as Simulator:
While Loop

While Loop

Case Structure

Case Structure

A simple SubVI that simulates a Temperature

value using a Random Generator

In this way you can easily switch

between the real Temperature
sensor (TC01) and the Simulator
10

2. Database
Systems
11

Database Systems
A Database is a structured way to store lots of
informa,on. The informa,on is stored in dierent
tables.
- Everything today is stored in databases!

Examples:
Bank/Account systems
Informa,on in Web pages such as Facebook,
Wikipedia, YouTube, etc.
Fronter, TimeEdit, etc.
lots of other examples!
12

Database Management Systems (DBMS)

MicrosoX 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
MicrosoR Access
IBM DB2
Sybase
etc.
We will use SQL server because it is very popular in the industry today, and we can use it for
free via the MicrosoX DreamSpark Premium SubscripSon which is available for the
students and sta at Telemark University College, or use the Express version which is available
for free for everybody.
13

MicrosoX 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 informa,on in the database. It can be installed on the server or on the
client (or both).

Note! You need to install both

on your local computer!

The newest version of MicrosoR SQL

Server is SQL Server 2012

14

3. Database
Design - Visio
15

MicrosoX Visio
Tools for Design and Modeling Database Systems:
ERwin (very good! But expensive! ~$5000)
Note! CA ERwin Data Modeler Community Edi,on is a free edi,on that contains a subset of the
standard product
Toad Data Modeler
MS Visio
lots of other tools
We will use MS Visio because it has everything we need and it
is a very popular part of the MS Oce package (and you probably
already have it installed). We can also use it for free via the
MicrosoX DreamSpark Premium SubscripSon available for the
students and sta at Telemark University College
16

Database Design ER Diagram

ER Diagram (En,ty-Rela,onship Diagram)
Used for Design and Modeling of Databases.
Specify Tables and relaSonship between them (Primary Keys and
Foreign Keys)
Table Name
Example:
Table Name

Column
Names
Primary Key

Primary Key
Foreign Key

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

Database Design ER Diagram Example I

Table Name

Table Name

PK

PK
FK

This example stores all

informa,on about the
students grade, etc. in the
database
PK-FK RelaSonship
Table Name

Table Name
PK
FK

Table Name
PK

FK
FK

PK
FK

PK-FK RelaSonships
Table Name
PK
FK

Table Name
PK

FK
FK

PK Primary Key, FK Foreign Key

Table Name
PK
FK
FK

18

Database Design ER Diagram Example II

Library
Management
System

19

Database Design MicrosoX Visio

We will use Visio to
Design our Database

1
2

Select the proper Template

20

Table Name

Visio - Example

Primary Key (PK)

Foreign Key (FK)

Use the Rela,onship Tool in order to create

Primary Key Foreign Key Rela,onship between
two tables (lines with arrows)

Use the En,ty Tool in order to create Tables

21

Database - Best PracSce

Tables: Use upper case and singular form in table
names not plural, e.g., STUDENT (not students)
Columns: Use Pascal nota,on, e.g., StudentId
Primary Key:
If the table name is COURSE, name the Primary
Key column CourseId, etc.
Always use Integer and Iden,ty(1,1) for Primary
Keys
Specify Required Columns (NOT NULL) i.e., which
columns that need to have data or not
Standardize on these Data Types: int, oat,
varchar(x), date2me, bit
It is recommended that you follow these guidelines!

22

4. SQL Server

23

MicrosoX SQL Server Create a New Database

2

Name you database, e.g.,

WEATHER_SYSTEM

24

MicrosoX SQL Server

3
Your SQL Server

1
2

Write your Query here

Your Database

Your
Tables

The result from your Query

25

MicrosoX SQL Server

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

Make sure to uncheck

this op,on!

26

SQL Structured Query language

A Database Computer Language designed for Managing Data in
Rela,onal 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: INSERT, SELECT, UPDATE and DELETE
27

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,
)
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[CLASS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

CREATE TABLE [CLASS]

(
[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

28

Create Tables using the Designer Tools

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

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

exis,ng tables or
create new Tables

Create New Table

3

Enter Columns, select Data Types,

Primary Keys, etc.

30

Get Data from mulSple 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

31

Create View:

CreaSng Views using SQL

IF EXISTS (SELECT name

FROM
sysobjects
WHERE name = 'CourseData'
AND
type = 'V')
DROP VIEW CourseData
GO
CREATE VIEW CourseData
AS
SELECT
SCHOOL.SchoolId,
SCHOOL.SchoolName,
COURSE.CourseId,
COURSE.CourseName,
COURSE.Description
FROM
SCHOOL
INNER JOIN COURSE ON SCHOOL.SchoolId = COURSE.SchoolId
GO

Using the View:

select * from CourseData

A View is a virtual table that

can contain data from mul,ple
tables
This part is not necessary but if you make any
changes, you need to delete the old version
before you can update it

The Name of the View

Inside the View you join the
dierent tables together using
the JOIN operator
You can Use the View as an
ordinary table in Queries :

32

CreaSng Views using the Editor

3

Graphical Interface where you can select columns you need

1

4
Add necessary tables
Save the View

33

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
This part is not necessary but if you make any
changes, you need to delete the old version
before you can update 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'

34

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

This part is not necessary but if you make any

changes, you need to delete the old version
before you can update it

Create the Trigger:

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

Name of the Trigger

Specify which Table the

Trigger shall work on
Specify what kind of opera,ons the Trigger
shall act on

CREATE TRIGGER CalcAvgGrade ON GRADE

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

Inside the
Trigger you can
use ordinary SQL
statements,
create variables,
etc.

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

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

35

5. LabVIEW

36

Weather System in LabVIEW

GUI/HMI Example:

App #1
Logg data from TC-01 Device and
save the data into the database
Code Example:

37

DAQ Assistant for NI USB-TC01

2
Acquire Signals Analog Input Temperature Thermocouple

Use default Proper,es

Select Physical Channel ai0

5
Click OK to Finish
38

Weather System in LabVIEW

Present weather data from the
database con,nuously

App #2

GUI/HMI Example:

39

Weather System in LabVIEW

App #3

Manage Weather Parameters

(Create New, Edit exis,ng or
Delete exis,ng Parameters)
GUI/HMI Example

GUI/HMI Example

40

ODBC
ODBC (Open Database Connec,vity) is a standardized interface (API) for accessing the
database from a client. You can use this standard to communicate with databases
from dierent vendors, such as Oracle, SQL Server, etc. The designers of ODBC aimed
to make it independent of programming languages, database systems, and opera,ng
systems.
Control Panel Administra,ve Tools Data Sources (ODBC)
We will use this ODBC Connec,on
later in LabVIEW in order to open
the Database Connec,on from
LabVIEW

41

ODBC Step by Step InstrucSons

The Name of your
ODBC Connec,on

The Name of your

SQL Server

Select the
Database you are
using for the
Library
Use either
Windows or SQL
Server
authen,ca,on
(Windows is
simplest to use!)

Test your
connec,on to see
if its works

42

SQL Server Congura,on Manager

Make sure to Enable these Protocols!

If not the Database Communica,on
from LabVIEW will not work properly!

43

MicrosoX Excel - Example

Select your ODBC connec,on

Select Tables and Columns

Finally, the data from the

database is in the Excel
sheet
44

LabVIEW SQL Toolkit

Easy Access to Database Systems from LabVIEW
Example 1: Get Data from Database into LabVIEW:
1

Your ODBC
Connec,on
Example 2: Write Data to Database from LabVIEW:

3
45

LabVIEW SQL Toolkit

1

GUI/HMI

If we want to save input data from the user

we can use the Format Into String func,on
The %s operator will be replaced by the text
from the TextBox on the Front Panel. For
Numbers we can use %d (Integer) or %f for
Floa,ng-point Number.
2

Code:

3 Resul,ng SQL Query:

execute CreateBook Lord of the Rings', J.R.R. Tolkien', Wiley', 32-2-333-56', Fantasy'
46

The following Window (SubVI)

Pops up when clicking the Add
Book BuZon

LabVIEW Example
This example shows is a Library
Management System, but a system for
manipula,ng Weather Parameters will
work the same way

Menu Ring

Mul,column Listbox
1

Show the Books available in the Database

using a View

2 Add more Books

3
Insert the Book
informa,on in the
Database using a Stored
Procedure

4 Update the List with the

latest informa,on from
the Database

47

LabVIEW Block Diagram State Machine

Download the Example Code and use the following Example:

ShiR Register

Error Handling using an

Error Clusters and ShiR
Register

When you click on the dierent

BuZons, etc., the code inside the
Event Structure will be
automa,cally executed

The Programming Technique shown above is called a State Machine and it is recommended that you use this method!

48

Grading/Submission - Checklist
Submission in Fronter (one .ZIP File Not .RAR!!!!!!)
The Report in PDF format is included in the ZIP File
The LabVIEW Code is included in the ZIP File
The ER diagram in Visio is included in the ZIP File
The SQL Code/SQL Scripts (Views, Triggers, Stored
Procedure, etc.) are included in the ZIP File
A Printed Copy of the Report is in the Mail Box outside my Oce
Note! The Database Lab counts 25% of the nal grade in the course

49

Hans-Pe2er Halvorsen, M.Sc.

Telemark University College
Faculty of Technology
Department of Electrical Engineering, InformaSon Technology and CyberneScs

E-mail: hans.p.halvorsen@hit.no
Blog: h2p://home.hit.no/~hansha/

50