SQL Data Definition

jazzydoeSoftware and s/w Development

Oct 30, 2013 (3 years and 9 months ago)

61 views

SQL Data Definition

Stanislava Armstrong

http://www.cs.nott.ac.uk/~saw/teaching/G64DBS/lecture_slides.html


1

SQL Data Definition

Exercise

How can you find
the list of people
who are going on
a holiday to
India?

SQL Data Definition

2

ID

Code

Name

location

ID

Code

Bookings

Destination

Clients

By

takes

Let’s
solve


this!

SQL


Originally ‘Sequel’
-

S
tructured
E
nglish
que
ry
L
anguage, part of an IBM
project in the 70’s


Sequel was already taken,
so it became SQL
-

Structured Query Language


ANSI Standards


SQL
-
89


SQL
-
92 (SQL2)


SQL
-
99 (SQL3
)


...


SQL:2008


Most modern DBMS use a
variety of SQL


Most based on
SQL2 or higher


Few (if any) are true to the
standard

SQL Data Definition

3

SQL


SQL provides


A data definition language
(DDL)


A data manipulation language
(DML)


A data control language (DCL)


In addition SQL


Can be used from other
languages


Is often extended to provide
common programming
constructs (such as if
-
then
tests, loops, variables, etc
.)


SQL is a declarative (non
-
procedural) language

SQL Data Definition

4

Notes


SQL is (usually) not case
-
sensitive, but we’ll write
SQL keywords in upper case
for emphasis


SQL statements will be
written in
BOLD COURIER
FONT


Strings in SQL are
surrounded by single
quotes:

'I AM A STRING'


Single quotes within a string
are doubled:

'I''M A STRING'


The empty string:
''


SQL Data Definition

5

SQL, the Relational Model, and E/R Design


SQL is based on the
relational model


It has many of the same ideas


Databases that support SQL
are often described as
relational databases


It is not always true to the
model


E/R designs can be
implemented in SQL


Entities, attributes, and
relationships can all be
expressed in terms of SQL


Many
-
to
-
many relationships
are a problem, so should be
removed

SQL Data Definition

6

Relations, Entities, Tables

SQL Data Definition

Relational model


Relation

Tuple

Attribute

Foreign Key

Primary Key

E/R Diagram


Entity

Instance

Attribute

M:1 Relationship


SQL


Table

Row

Column or Field

Foreign Key

Primary Key


7

Implementing E/R Designs


Given an E/R design


The entities become SQL
tables


Attributes of an entity
become columns in the
corresponding table


Relationships may be
represented by foreign keys

SQL Data Definition

Price

fruitID

Variety

Purchase

Fruit

Kingdoms

for

Is

kingID

kingID

location

fruitID

name

8

CREATE TABLE

SQL Data Definition

CREATE TABLE

<name> (


<col
-
def
-
1>,


... ,


<
col
-
def
-
n>,


<constraint
-
1>,


...,


<constraint
-
k>

)[ENGINE = <
engine_choice
>];



Legend:

(...)

these brackets need to be included


in the actual code

<...>

substitute the … with the indicated


content. e.g.: <name>


Fruit

[...]

optional content



You supply


A name for the table


A list of column definitions


A list of constraints (such as keys)





e.g.:
9
9
9
9
9

CREATE TABLE Fruit (

fruitID

INT

AUTO_INCREMENT,

fruitPrice

INT DEFAULT 100,

fruitVariety

VARCHAR(20) UNIQUE,

CONSTRAINT
pkFruit




PRIMARY KEY (
fruitID
)

) ENGINE =
InnoDB
;


9

Table Name

SQL Data Definition

CREATE TABLE

<name>
(


<col
-
def
-
1>,


<col
-
def
-
2>,


:


<
col
-
def
-
n>,


<constraint
-
1>,


:


<constraint
-
k
>

)[ENGINE =
<
engine_choice
>];



The table name is entered after
the key words
CREATE TABLE



If you are working with more than
one database, you can specify the
database which the table should be
added to by writing the name in the
form:
database_name.table_name



If no database is specified, the
table will be added to the default
database (fine for the coursework!)


10

Example

SQL Data Definition

Purchase

for

Is

kingID

fruitID

CREATE TABLE

Purchase
(


<col
-
def
-
1>,


<col
-
def
-
2>,


:


<
col
-
def
-
n>,


<constraint
-
1>,


:


<constraint
-
k>

)[ENGINE =
<
engine_choice
>];

11

Column Definitions


<
col
-
name> <type>


[NULL|NOT NULL
]


[DEFAULT <
val
>]


[AUTO_INCREMENT]


[constraint
-
1 [,


constraint
-
2[,


...]]]


Each column has a name
and a type


Common types


INT


REAL


CHAR(n)


VARCHAR(n)


DATE


If a constraint applies to a
single column it can be
added to the column's
definition

SQL Data Definition

12

Column Definitions


Columns can be specified as
NULL

or
NOT NULL


NOT NULL
columns
cannot have
NULL


If
neither is given then
NULL
is assumed


e.g.:
fruitID

INT NOT
NULL



Columns can be given a
default value


You just use the keyword
DEFAULT

followed by the
value

e.g.:
fruitName

VARCHAR(20)
DEFAULT ‘’

SQL Data Definition

13

Column Definitions


If a column is set to
AUTO_INCREMENT
, the value of
(
Previous_Value

+1) is automatically inserted in the column
when a new line is added


To take advantage of the auto increment you should enter
NULL
, 0 or no value at all for this column during data inserts


e.g.:
fruitID

int

AUTO_INCREMENT


SQL Data Definition

14

Example

CREATE
TABLE
Fruit (

fruitID

INT NOT NULL
AUTO_INCREMENT,

fruitPrice

INT DEFAULT 100,

fruitVariety

VARCHAR(20)


<constraint
-
1>,


:


<constraint
-
k>

)[ENGINE = <
engine_choice
>];



CREATE TABLE Purchase (

kingID

INT NOT NULL,

fruitID

INT NOT NULL

<constraint
-
1>,


:

<constraint
-
k>

)[ENGINE = <
engine_choice
>];


SQL Data Definition

Price

fruitID

Variety

Purchase

Fruit

for

Is

kingID

fruitID

15

Constraints

CONSTRAINT


<name>


<type>


<details>


Common
<type>
s


PRIMARY KEY


UNIQUE


FOREIGN KEY


INDEX



Each constraint is given a
name


MySQL

doesn’t
obligate you to specify
a
name, but some others
do


Constraints which refer to
single columns can be
included in their definition

SQL Data Definition

16

Primary Keys


Primary Keys are defined
through constraints


A
PRIMARY KEY

constraint
also includes a
UNIQUE

constraint and makes the
columns
involved
NOT
NULL


CONSTRAINT <name>


PRIMARY KEY


(col1, col2, …)



The
<details>

for a
primary key is a list of columns
which make up the key

e.g.:
CONSTRAINT
pkPurchase


PRIMARY KEY


(
fruitID
,
kingID
)


If the primary key is a single
column the constraint can be
written as part of the column
definition

e.g.:
fruitID

INT PRIMARY
KEY

SQL Data Definition

17

Unique Constraints


As well as a single primary key,
any set of columns can be
specified as
UNIQUE


This has the effect of making
candidate
keys
in the
table


CONSTRAINT <name>


UNIQUE


(col1, col2, …)



The
<details>

for a unique
constraint are a list of columns
which make up the candidate
key

e.g.:
CONSTRAINT
unPurchase


UNIQUE


(
fruitID
,
kingID
)


If the unique set contains a
single column the constraint
can be written as part of the
column definition

e.g.:
fruitVariety

VARCHAR(20) UNIQUE


SQL Data Definition

18

Example

CREATE
TABLE
Fruit (

fruitID

INT
NOT NULL
PRIMARY KEY
AUTO_INCREMENT,

fruitPrice

INT DEFAULT 100,

fruitVariety

VARCHAR(20) UNIQUE

)[ENGINE = <
engine_choice
>];



CREATE TABLE Purchase (

fruitID

INT
NOT NULL
,

kingID

INT
NOT NULL,


CONSTRAINT
pkFruit

PRIMARY KEY
(
fruitID
,
kingID
)

)[ENGINE = <
engine_choice
>];


SQL Data Definition

Price

fruitID

Variety

Purchase

Fruit

for

Is

kingID

fruitID

19

Relationships


Depends on the type


1:1 are usually not used, or
can be treated as a special
case of M:1


M:1 are represented as a
foreign key from the M
-
side
to the 1


M:M are split into two M:1
relationships

SQL Data Definition

Price

fruitID

Variety

Purchase

Fruit

Kingdoms

for

Is

kingID

kingID

location

fruitID

name

Sounds
familiar!

20

Representing Relationships


The
Purchase
table


Will have columns for the
Fruit
and
Kingdom id
attributes


Will have a foreign key to
Fruit
for the
‘is’
relationship


Will have a foreign key to
Kingdom
for the
‘for’
relationship

SQL Data Definition

Price

fruitID

Variety

Purchase

Fruit

Kingdoms

for

Is

kingID

kingID

location

fruitID

name

21

Foreign Keys


Foreign Keys are also defined as
constraints


You need to give


The columns which make up the FK


The referenced table


The columns which are
referenced
by the FK


If the FK references the PK of
<table>

you don’t need to list the columns

CONSTRAINT <name>


FOREIGN KEY


(col1,col2,…)


REFERENCES


<table>


[(ref1,ref2
,…)]
[ON DELETE
ref_option
]
[ON UPDATE
ref_option
]



For

MySQL

the available
ref_options

are

RESTRICT,
CASCADE, SET NULL
and

NO
ACTION


e.g.:
CONSTRAINT
fkFruit

FOREIGN KEY (
fruitID
)
REFERENCES Fruit (
fruitID
)

SQL Data Definition

22

Example

CREATE
TABLE
Fruit (

fruitID

INT PRIMARY KEY

AUTO_INCREMENT,

fruitPrice

INT DEFAULT 100,

fruitVariety

VARCHAR(20) UNIQUE

)[ENGINE = <
engine_choice
>];



CREATE TABLE Purchase (

fruitID

INT,

kingID

INT,

CONSTRAINT
pkFruit

PRIMARY KEY (
fruitID
,
kingID
),

CONSTRAINT
fkFruit

FOREIGN KEY

(
fruitID
)
REFERENCES Fruit (
fruitID
)
ON UPDATE CASCADE,

CONSTRAINT
fkKingdom

FOREIGN KEY

(
kingID
)
REFERENCES Kingdom (
kingID
)
ON DELETE RESTRICT
ON UPDATE CASCADE

)[ENGINE = <
engine_choice
>];


SQL Data Definition

Price

fruitID

Variety

Purchase

Fruit

for

Is

kingID

fruitID

23

ENGINE


The selected engine
determines the method for
storage and manipulation of
the tables


Different storage engines
have different strengths


MyISAM

is the
defaulft



it
is fast, but does not support
foreign keys


InnoDB

supports foreign
keys and so it will be our
engine of choice for this
module


The choice of engine is
added at the end of the
table creation


e.g.:
ENGINE =
InnoDB

SQL Data Definition

24

Example

CREATE
TABLE
Fruit (

fruitID

INT PRIMARY KEY

AUTO_INCREMENT,

fruitPrice

INT DEFAULT 100,

fruitVariety

VARCHAR(20
) UNIQUE

)
ENGINE =
InnoDB
;



CREATE TABLE Purchase (

fruitID

INT,

kingID

INT,

CONSTRAINT
pkFruit

PRIMARY KEY (
fruitID
,
kingID
),

CONSTRAINT
fkFruit

FOREIGN KEY

(
fruitID
)
REFERENCES Fruit (
fruitID
)
ON DELETE SET NULL
ON UPDATE CASCADE,

CONSTRAINT
fkKingdom

FOREIGN KEY

(
kingID
)
REFERENCES Kingdom (
kingID
)
ON DELETE RESTRICT
ON UPDATE CASCADE

)
ENGINE =
InnoDB
;


SQL Data Definition

Price

fruitID

Variety

Purchase

Fruit

for

Is

kingID

fruitID

25

Exercise


Create table in
MySQL

from
the E/R diagram on the right
by identifying the:


Name of the tables


The columns (inc. data types
and attributes) for each table


Each table’s constraints

SQL Data Definition

price

destID

location

Bookings

Destination

Clients

make

for

cliID

cliID

address

destID

name

attractions

hotel

date

telephone

26

Reading Material


The
Manga

Guide to Databases


chapters 3


Database Systems


A Practical Approach to
Design, Implementation and Management by
Connolly and
Begg



chapters 6


Any other book


chapter on SQL data
definition


http://dev.mysql.com/doc/refman/5.1/en/sql
-
syntax
-
data
-
definition.html
-

MySQL

documentation for data definition

SQL Data Definition

27

Reminder


Lab sessions will begin today:


Room: A32

Time: 3pm


6pm

Exercises will be posted on:

http://www.cs.nott.ac.uk/~saw/teaching/G64D
BS/exercises.html


SQL Data Definition

28