zeit2301 design of information systems sql: creating a database school of engineering and...

ZEIT2301Design of Information Systems

SQL: Creating a Database

School of Engineering and Information TechnologyUNSW@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

4

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)

Remember our bike example in 3NF

Bike name*

Number of riders*

Centre of mass height

Harley 1 0.724

Harley 2 0.775

Honda 1 0.831

Honda 2 0.881

Road conditions*

Coefficient of friction

Icy 0.1

Wet 0.5

Dry 0.9

Scenario ID*

Bike 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

Bike name* Wheelbase

Harley 1.588

Honda 1.458

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 ConstraintsConstraint Type Description

CONSTRAINT… PRIMARY KEY… • Supports entity integrity. • Defines the primary key column(s). Must

be NOT NULL and UNIQUE

CONSTRAINT…FOREIGN KEY… ON UPDATE … ON DELETE…

• 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