2 of 14

Relational Model

A formal model for data
Data is organized as a set of relations, or tables

MID	Name	HomePhone	DoB
1224	Tom Morello	7577777777	30/05/1964
6719	Zack de la Rocha	8044444444	12/01/1970
2743	Brad Wilk	9199999999	05/09/1968

MUSICIAN

Relation (table)

Tuples (rows/records)

Attributes (columns/fields)

3 of 14

Relational Model

A table (relation) has columns (attributes)
Data in different tables are connected by the tables sharing a matching column(s)

MID	Name	HomePhone	DoB	BID
1224	Tom Morello	7577777777	30/05/1964	7373
6719	Zack de la Rocha	8044444444	12/01/1970	7373
2743	Brad Wilk	9199999999	05/09/1968	7373

MUSICIAN

BID	Name	Formed	City
7373	Rage Against The Machine	1991	Los Angeles
0201	The Beatles	1960	Liverpool

BAND

4 of 14

Relational Model

A formal model for data
Data is organized as a set of relations, or tables

PatID	Name	HomePhone	DoB
1224	Tom Morello	7577777777	30/05/1964
6719	Zack de la Rocha	8044444444	12/01/1970
2743	Brad Wilk	9199999999	05/09/1968

PATIENT

Relation (table)

Tuples (rows/records)

Attributes (columns/fields)

5 of 14

Create a table

DROP TABLE `PERSON`;

CREATE TABLE `PERSON` (

`PersonID` INT NOT NULL,

`TEAM_TeamName` VARCHAR(45) NOT NULL,

`PersonType` INT NOT NULL);

drop deletes the table and its data

W3 schools

6 of 14

CRUD

create, read, update, delete data
The SQL statements for manipulating data

7 of 14

insert

Basic syntax:

INSERT [INTO] tablename (col1, col2, …, coln)

VALUES (value1, value2, …, valuen)

Inserts a new row into a table

insert into TEAM

(TeamName, City, Division, PERSON_ManagerID)

values

('Crawford Cows', 'Crawford', 'West', NULL);

W3 schools

8 of 14

update

Basic syntax:

UPDATE tablename SET assignments

[WHERE condition]

Updates column(s) in a table for all rows, or just rows which meet the specified condition

update TEAM

set Division = 'Western'

where Division = 'West';

W3 schools

9 of 14

delete

Basic syntax:

DELETE FROM tablename

[WHERE condition]

Deletes all rows from a table, or just rows which meet the specified condition

delete from TEAM

where Division like 'West%';

W3 schools

10 of 14

SELECT statement

Basic syntax:

SELECT column(s)

FROM table(s)

WHERE condition

ORDER BY column(s)

LIMIT number

W3 schools

11 of 14

SQL aggregation functions

Usually go in the SELECT clause

count()
min()
max()
sum()
avg()

W3 schools

12 of 14

Examples (2019 dataset)

SELECT count(*) FROM person;

counts the total number of rows in the person table
1736379 people

SELECT avg(votes) FROM rating;

the average number of votes per movie title
1556.57 votes per movie

SELECT min(birth) FROM person;

the minimum birth year of all the people
43 (Ovid - a Roman poet)

13 of 14

Examples - more specific results

The shortest movie in 2020

SELECT min(runtime)

FROM title

WHERE year = 2020;�

The number of releases of Moana with a different title or region

SELECT count(*)

FROM title, aka

WHERE title.tid = aka.tid

AND title.title = 'Moana';

14 of 14

GROUP BY clause

Groups the results of a query by one or more columns
Aggregation functions apply to each group
Example: count the number of people per job category

SELECT category, count(*)

FROM castcrew

GROUP BY category

ORDER BY count(*) DESC;