Transcript DIS Topic 02: Project Initiation

ZEIT2301
Design of Information Systems
SQL: Creating a Database
School of Engineering and Information Technology
UNSW@ADFA
Dr Kathryn Merrick
Topic 10: SQL Basics

This lecture will introduce the SQL for
 Creating a database
 Inserting data
 Updating data
 Making simple queries

We will then compare how this is done in MS
Access


When can SQL be used?
When is it hidden?
Structured Query Language



SQL, the “Structured Query Language”, has become
the standard language for data manipulation in
relational databases
SQL is a non-procedural language - it specifies what is to
be done, not how it is to be done
Relatively easy to use for simple queries

some subtleties in more complex queries
Statements in SQL’s Data
Manipulation Language

INSERT – add data to a table

UPDATE – modify data in a table

DELETE – remove data from a table

SELECT – retrieve data from database

most commonly used statement (next lecture)
4
Remember our bike example in 3NF
Bike name*
Wheelbase
Harley
1.588
Honda
1.458
Coefficient
of friction
Bike
name*
Number of
riders*
Centre of
mass height
Harley
1
0.724
Harley
2
0.775
Honda
1
0.831
Road
conditions*
Honda
2
0.881
Icy
0.1
Wet
0.5
Dry
0.9
Scenario Bike
ID*
name
Number of
riders
Road
conditions
Can
stoppie
1
Harley
1
Dry
false
2
Harley
2
Dry
false
3
Honda
1
Dry
true
4
Honda
2
Dry
true
Creating Relations: Example 1
CREATE TABLE
Bikes (bikeName VARCHAR(60) NOT NULL UNIQUE,
wheelbase DECIMAL(4, 3) NOT NULL,
PRIMARY KEY (bikeName));
Common SQL Data Types
Data Type
Description
INTEGER
Up to 11 digits; –ve sign takes one place.
SMALLINT
Up to 6 digits, including sign.
NUMERIC(s,d)
Fractional numbers, with a maximum of s
digits and d decimal places; d can be omitted.
DECIMAL(s,d)
Fractional numbers, with a minimum of s
digits and d decimal places; d and s can be
omitted.
CHAR(s)
Character strings, with s places provided.
VARCHAR(s)
Unpadded character strings, up to length s.
DATE
Ten character date representation (year
month day) as digits.
BOOLEAN
Boolean
Creating Relations: Example 2
CREATE TABLE
CoMHeight( bikeName VARCHAR(60) NOT NULL UNIQUE,
numRiders INTEGER
NOT NULL
comHeight DECIMAL(4, 3) NOT NULL,
CONSTRAINT pk2 PRIMARY KEY (bikeName)
CONSTRAINT fk1 FOREIGN KEY (bikeName) REFERENCES Bikes
ON UPDATE CASCADE ON DELETE NO ACTION);
SQL Constraints
Constraint Type
Description
CONSTRAINT… PRIMARY KEY…
•
•
CONSTRAINT…FOREIGN KEY…
ON UPDATE … ON DELETE…
•
•
Supports entity integrity.
Defines the primary key column(s). Must
be NOT NULL and UNIQUE
Supports referential integrity
Regulates update/delete operations that
have implications to other relations
Foreign Key Subclause Values
CASCADE
Update/delete rows from parent table and
automatically update/delete matching rows
in child tables
SET NULL
Update/delete rows from parent table and
set the foreign key values in child table to
NULL
NO ACTION
Reject the update/delete operation
Inserting Records into a Relation
INSERT INTO Bikes VALUES (‘Harley’, 1.588);
INSERT INTO Bikes VALUES (‘Honda’, 1.458);
INSERT INTO Bikes VALUES(‘Mongoose Expert’, 0.932);
INSERT INTO CoMHeight VALUES(‘Harley’, 0, 0.724);
Inserting Records From a File

It may not be practical to write insert statements for every
record

To read multiple records from a file in bulk:
LOAD DATA LOCAL INFILE filename INTO TABLE Bikes
FIELDS TERMINATED BY ‘\t’;
Viewing Records: Simple
SELECT
SELECT * FROM Bikes;
SELECT scenarioID, canStoppie FROM Scenarios;
SELECT * FROM CoMHeight WHERE bikeName = ‘Harley’;
SELECT * FROM CoMHeight WHERE numRiders > 1;
Modifying Records
UPDATE Bikes SET wheelbase = 1.6
WHERE bikeName = ‘Harley’
UPDATE CoMHeight SET comHeight = 0.746
WHERE bikeName = ‘Harley’ AND numRiders = 1;
Deleting Records
DELETE FROM CoMHeight WHERE bikeName = ‘Honda’;
DELETE FROM CoMHeight WHERE numRiders = 2;
MS Access
Summary

After today’s lecture you should be able to:

Create and update a relational database using SQL