Databases 1

Programming with
Web Technologies
What is a Database

Data
Physical representation of some aspect of reality using some medium (text,
images, sound, video…)

Database
Set of interrelated data with a common purpose, user community and specific
applications (e.g. payroll, inventory, bank accounts, etc.)

Database Management system (DBMS)

Sometimes referred to as RDBMSs (relational DBMS) A set of programs
designed to define, create, and manipulate databases
2
What Database systems are available

● MS-Access
● SQL Base, MySQL, Postgres
● Derby
● Sybase, DB2, Oracle, Progress, SQL Server
● dBase, FoxPro
● Gemstone, O2, Illustra,…
● MongoDB, …

3
Why do we need a Database

Why not just use a text file or excel spreadsheet?

Problems Solutions
Redundancy / inconsistency Centralized, uniform storage control
Difficulty of access Query language, views
Integrity control Rule enforcement
Atomicity problems Transaction management
Concurrent access Concurrency control
Security Privilege schemes
Data loss Recovery mechanisms

4
Database System Overview

5
Database Application Example

6
Database Application Example

7
Layers of Abstraction

8
SQL

Structured Query Language, pronounced "Es-Kew-El" but often (technically

incorrectly) pronounced "sequel", was developed in the 1970’s by IBM to work
with the relational model described by E. F. Codd

SQL is the standard means of interacting with DBs, and was standardized by
ANSI in 1986, and by ISO under the standard ISO/IEC 9075 in 1987

While it is the de facto standard across database systems, each vendor has
specific additions, which can complicate moving to a different database
system

9
SQL

Although SQL has "query" as part of its name, data needs to be created and
stored before it can be interacted with

SQL has two major components

Data Definition Language (DDL), which includes table creation, modification

and management

Data Manipulation Language (DML), which includes querying and creation

of data

10
Data Definition
Language (DDL)
DDL: Creating a Database

A database contains definitions and data for all tables, indexes, user-defined
data types, functions, etc.

Simplest form:
CREATE DATABASE <dbname>;
Example:
CREATE DATABASE university;
Once created:
USE university;

12
DDL: Schema

Elements we need to create a schema:

1. Column data types

2. Basic create-table statements
3. Define keys
4. Define integrity constraints
5. Define foreign keys

All of these elements are needed for complete table definition. We will have a
look at each of these today

13
DDL: Data Types

SQL data types are used to specify the kind of data that may be stored in each
column of a table

For example

14
DDL: Data Types

Data types define the type of data that each column in a table will contain.
These data types can be system-defined or user-defined

The basic data types that you will encounter most often are

Integer numbers: int, -231 to 231 – 1 for T-SQL

Floating point: float, -1.79308 through 1.79308
Fixed-length character strings: char(size)
Variable-length character strings: varchar(size)

15
DDL: Data Types
There are a number of "standard" SQL types

char(n) strings of exactly n characters (8,000 max.)

varchar(n)strings of up to n characters (8,000 max.)
int integers
smallint small integers (machine-dependent)
numeric(p,d) e.g. numeric(3, 2): from -999.99 to 999.99
real real number, machine-dependent precision
float(n) real number, precision of n digits
date a date containing YYYY/MM/DD
time time in hours, minutes and seconds
timestamp date + time – e.g. 2007-03-12 13:15:17
16
Null Values

When a value is not available for a cell, the value can be null. Some columns
should never contain nulls such as primary keys (discussed later). The DBMS
can enforce 'non-nullability' with the NOT NULL keywords

17
DDL: Creating tables

Tables are the fundamental data structure in the relational model. In the
simplest form, they can be created with a statement in the following form

CREATE TABLE <table-name> (

<col-name1> data-type,
<col-name2> data-type,
<col-namen> data-type
);

Note that keys and constraints are not mentioned yet.

18
Basic Create-Table

A basic "create" statement could look as follows

CREATE TABLE students (

id INT NOT NULL,
fname VARCHAR(20),
lname VARCHAR(100),
country CHAR(2)
);

This is not complete yet!

19
DDL: Specifying Keys

A more complete "create" statement will include a PRIMARY KEY, used to

uniquely identify a row in the table. We can make the column "id" the key

CREATE TABLE students (

id INT NOT NULL,
fname VARCHAR(20),
lname VARCHAR(100),
country CHAR(2),
PRIMARY KEY (id)
);

20
Creating Another Table

Another example

CREATE TABLE lecturers (

staff_no INT NOT NULL
, fname VARCHAR(32)
, lname VARCHAR(32)
, office CHAR(8)
, PRIMARY KEY (staff_no)
);

21
Another Example Table

Primary keys can span multiple columns, forming a composite primary key

CREATE TABLE courses (

dept CHAR(5) NOT NULL
, number INT NOT NULL
, description VARCHAR(100)
, coord_no INT
, rep_id INT
, PRIMARY KEY (dept, number)
);

22
DDL: Integrity constraints

Integrity constraints are used in SQL to ensure data quality. They come in a
number of forms, but the two that will be discussed today are

Check clauses, which ensures that data conforms to certain rules and
formats

Foreign keys, which ensure proper relationships between tables

23
DDL: Adding constraints to attributes

Imagine that the id column should be in the 1,001 to 10,000 range. We can use
the CHECK clause to enforce this

CREATE TABLE students (

id INT NOT NULL
, fname VARCHAR(32) NOT NULL
, lname VARCHAR(32) NOT NULL
, PRIMARY KEY (id)
, CHECK (id > 1000 AND id <= 10000)
);

24
Using Patterns

'Wildcards' can be used in SQL expressions. There are a few common

wildcards that you may want to use, or encounter in the wild

Symbol Meaning Example

% Any string of zero or more characters 'Uni%' matches Uni, Union and University

_ Any single character 'Ta_e' matches Take, Tale, Tame, …

[ ] Any single character in a range or set '[AB]' matches A and B, '[J-L]' matches J, K and L

[^] Any single character not in a range or set '[^0-9]' matches any non-numeric character

25
Patterns in constraints (using RLIKE)

If country codes must consist of two non-numeric characters:

CREATE TABLE students (

id INT NOT NULL
, fname VARCHAR(32) NOT NULL
, lname VARCHAR(32) NOT NULL
, country CHAR(2)
, PRIMARY KEY (id)
, CHECK (country RLIKE '^([[:alpha:]]){2}$')
);

26
Phone Numbers

These CHECK constraints can be very specific

CREATE TABLE one (

phone CHAR(12)
, CHECK (phone RLIKE
'^[(]0[[:digit:]][)]([[:digit:]]){3}-([[:digit:]]){4}$')
);

This would match a phone number like '(09)555-1234', but reject one like
'05 555-4458'

27
DDL: Foreign Keys

Tables can be related to each other, which is one of their major advantages
over alternatives like JSON or CSV. By definition, in the relational model, data
is organized in tables related by their contents. We need some way of
informing our database that columns in one table relate to a different column
in another. We can accomplish this with FOREIGN KEYs

28
Relationships in SQL: Foreign keys

Foreign keys make it explicit that attributes have been added because there is
a relationship with another table. Values for "foreign" attributes must exist in
the table they reference

Values will be validated upon insertion. The database will ensure that a row
with the correct value exists in the referenced table before inserting the new
row

29
Relationships in SQL: Foreign keys

Should be specified upon table creation:

CREATE TABLE courses (

dept CHAR(4) NOT NULL
, num CHAR(3) NOT NULL
, descrip VARCHAR(24)
, coord_no INT NOT NULL
, rep_id INT NOT NULL
, PRIMARY KEY (dept, num)
, FOREIGN KEY (coord_no) REFERENCES lecturers (staff_no)
, FOREIGN KEY (rep_id) REFERENCES students (id)
);

30
Relationships in SQL: Foreign keys

Like with PRIMARY KEYs, FOREIGN KEYs can be composite

CREATE TABLE attend (

id INT NOT NULL
, dept CHAR(4) NOT NULL
, num CHAR(3) NOT NULL
, semester CHAR(1)
, mark CHAR(2)
, PRIMARY KEY (id, dept, num)
, FOREIGN KEY (id) REFERENCES students (id)
, FOREIGN KEY (dept, num) REFERENCES courses (dept, num)
);

Note: A primary key can be a foreign key at the same time (see id)
31
Relationships in SQL: Foreign keys

Another example

CREATE TABLE teach (

dept CHAR(4) NOT NULL
, num CHAR(3) NOT NULL
, staff_no INT NOT NULL
, PRIMARY KEY (dept, num, staff_no)
, FOREIGN KEY (dept, num) REFERENCES courses (dept, num)
, FOREIGN KEY (staff_no) REFERENCES lecturers (staff_no)
);

32
DDL: Changing tables

Tables can be ALTERed or DROPped after they have been created

Add a column:
ALTER TABLE students ADD passport CHAR(32)
Change a column
ALTER TABLE students ALTER COLUMN id CHAR(16)
Delete a column
ALTER TABLE students DROP COLUMN passport
Delete a table (and its contents)
DROP TABLE students

33
Other types of queries

There are also a range of other commonly used queries like UPDATE queries
and DELETE queries. We do not cover all types of queries in the lecture slides
so you will need to make use of reference materials.

There is a lot of reference material for SQL online; Tutorials Point and
w3schools are good places to start:
http://www.tutorialspoint.com/sql/

https://www.w3schools.com/sql/

34
DDL: So far

35
Data Manipulation
Language (DML)
Data Manipulation Language

Typically, data is manipulated within databases through application programs

via DML statements. We will be looking at how to do this in some of the next
lectures

Database Administrators (DBA), Programmers and Advanced Users may

interact directly with the DBMS via graphical interfaces which allow them to
execute DML statements directly on the data, and view the results.

There are many different interface options including Squirrel-SQL,

phpMyAdmin, SQL Management Studio or DataGrip. We will be using
phpMyAdmin and IntelliJ's built-in DataGrip module
37
Populating databases: Insert

Once a table has been created, rows need to be INSERTed to populate the
database. The basic syntax of the DML INSERT statement is

INSERT INTO <table> VALUES (…)

INSERT INTO students VALUES (1667, 'John', 'Doe', 'AU');

INSERT INTO students VALUES (1668, 'Jane', 'Doe', 'NZ');
INSERT INTO students VALUES
(1669, 'Jay', 'Walker', 'NZ'),
(1670, 'Bob', 'Jones', 'NZ');

38
Populating databases: Insert

When inserting rows into a table with FOREIGN KEY constraints, the DBMS
checks for integrity

When everything is ok
INSERT INTO courses VALUES ('comp', '219', 'databases', 123, 1667);
(1 row(s) affected)

If the constraint is violated, the DBMS will let you know

INSERT INTO courses VALUES ('comp', '425', 'HCI', 456, 1010);
INSERT statement conflicted with COLUMN FOREIGN KEY constraint—table
'students', column 'id'.

39
Retrieving data in SQL

Information can be retrieved from a database using a SELECT statement. The

simplest form of a SELECT statement is

SELECT <columns>
FROM <table>
WHERE <expression>;

The result is always a table, regardless of how many rows or columns it

returns

40
DML: SELECT examples

If you wanted to retrieve all students, you could use the query

SELECT id, fname, lname, country FROM students;

or

SELECT * FROM students;

If you want all of the available columns in a query, you can use the asterisk
symbol to match all columns. In this case, the result has exactly the same
structure and content as the students table

41
DML: SELECT examples

Get student ids followed by country ID |COU&

----------------
of origin
1661 |US
1668 |US
SELECT id, country 1713 |NZ
FROM students; 1715 |NZ
1717 |AU
1824 |MX
1828 |FR
1970 |SC
1971 |SC

9 rows selected

42
DML: SELECT examples

We can filter our results using the ID |LNAME

WHERE clause of a query. To get the ---------------------------
ids and last names of Kiwi students 1713 |Speight
1715 |Wagstaff
SELECT id, lname
FROM students 2 rows selected
WHERE country = 'NZ';

43
DML: SELECT Examples

Get names of lecturers with offices FNAME | LNAME

in G-Block ---------------------------
Annika | Hinze
SELECT fname, lname Te Taka | Keegan
FROM lecturers David | Bainbridge
WHERE office LIKE 'G%';

44
Summary

● Foreign keys are used to relate tables

● Databases can be populated using INSERT
● Attribute constraints are verified by DBMSs
● SELECT is used to retrieve data from databases
● SQL includes DDL and DML
● Data types define what kind of data each table holds
● Basic DDL statements include CREATE database, USE database, CREATE
TABLE and ALTER TABLE
● SQL is the cross-language DB access tool

45