SQL DATABASE
budsimpson
chapt05.pdf
The Many-to-Many
Relationship
Fearful concatenation of circumstances
Daniel Webster
2
3
A sales form
4
The many-to-many
relationship Create a third entity to map an m:m relationship
An associative entity
The + on the crow's foot indicates that LINEITEM is identified by concatenating saleno and lineno
LINEITEM is known as a weak entity, and it has an identifying
relationship with SALE
Preference settings
5
Foreign key same name as primary key
Associative table name of form tableA_tableB
6
The many-to-many
relationship
MySQL Workbench
m:m
symbol
7
The many-to-many
relationship
MySQL Workbench
Identifying
relationship
Non-identifying
relationship
8
Why a third entity?
Store data about the relationship
Think of an m:m as two 1:m relationships
9
Creating a relational
database
Same rules apply
The associative table has two foreign keys
One for each of the entities in the m:m relationship
A foreign key can also be part of the primary key of an associative entity
lineitem
lineno lineqty lineprice saleno itemno
1 1 4.50 1 2
1 1 25.00 2 6
2 1 20.00 2 16
3 1 25.00 2 19
10
Creating a relational
database
CREATE TABLE sale (
saleno INTEGER,
saledate DATE NOT NULL,
saletext VARCHAR(50),
PRIMARY KEY(saleno));
CREATE TABLE item (
itemno INTEGER,
itemname VARCHAR(30) NOT NULL,
itemtype CHAR(1) NOT NULL,
itemcolor VARCHAR(10),
PRIMARY KEY(itemno));
CREATE TABLE lineitem (
lineno INTEGER,
lineqty INTEGER NOT NULL,
lineprice DECIMAL(7,2) NOT NULL,
saleno INTEGER,
itemno INTEGER,
PRIMARY KEY(lineno,saleno),
CONSTRAINT fk_has_sale FOREIGN KEY(saleno) REFERENCES sale(saleno),
CONSTRAINT fk_has_item FOREIGN KEY(itemno) REFERENCES item(itemno));
Exercise
A keen field hockey fan wants to keep track of which countries won which medals in the various summer Olympics for both the men’s and women’s events
Design a data model
Create the database
Populate with data for the last two Olympics
• http://en.wikipedia.org/wiki/Field_hockey_at _the_Summer_Olympics
11
12
A three table join
Specify two matching conditions with the associative table in both join conditions
SELECT * FROM sale JOIN lineitem
ON sale.saleno = lineitem.saleno
JOIN item ON item.itemno = lineitem.itemno;
13
A three table join
List the names of items, quantity, and value of items sold on January 16, 2011
SELECT itemname, lineqty, lineprice, lineqty*lineprice
AS total FROM sale JOIN lineitem
ON lineitem.saleno = sale.saleno
JOIN item ON item.itemno = lineitem.itemno
WHERE saledate = '2011-01-16';
itemname lineqty lineprice total
Pocket knife—Avon 1 0.00 0.00
Safari chair 50 36.00 1800.00
Hammock 50 40.50 2025.00
Tent—8 person 8 153.00 1224.00
Tent—2 person 1 60.00 60.00
14
EXISTS
Existential quantifier
Returns true or false
Returns true if the table contains at least one row satisfying the specified condition
Report all clothing items (type “C”) for which a sale is recorded
SELECT itemname, itemcolor FROM item
WHERE itemtype = 'C'
AND EXISTS (SELECT * FROM lineitem
WHERE lineitem.itemno = item.itemno);
itemname itemcolor Hat—Polar Explorer Red
Boots—snake proof Black
Pith helmet White
Stetson Black
15
lineno lineqty lineprice saleno itemno
1 1 4.5 1 2
1 1 25 2 6
2 1 20 2 16
3 1 25 2 19
4 1 2.25 2 2
1 1 500 3 4
2 1 2.25 3 2
1 1 500 4 4
2 1 65 4 9
3 1 60 4 13
4 1 75 4 14
5 1 10 4 3
6 1 2.25 4 2
1 50 36 5 10
2 50 40.5 5 11
3 8 153 5 12
4 1 60 5 13
5 1 0 5 2
itemno itemname itemtype itemcolor
1 Pocket knife — Nile E Brown
2 Pocket knife — Avon E Brown
3 Compass N —
4 Geopositioning system N —
5 Map measure N —
6 Hat — Polar Explorer C Red
7 Hat — Polar Explorer C White
8 Boots — snake proof C Green
9 Boots — snake proof C Black
10 Safari chair F Khaki
11 Hammock F Khaki
12 Tent — 8 person F Khaki
13 Tent — 2 person F Khaki
14 Safari cooking kit E —
15 Pith helmet C Khaki
16 Pith helmet C White
17 Map case N Brown
18 Sextant N —
19 Stetson C Black
20 Stetson C Brown
SELECT itemname,
itemcolor FROM item
WHERE itemtype = 'C’
AND EXISTS (SELECT *
FROM lineitem
WHERE lineitem.itemno
= item.itemno);
itemname itemcolor
Hat—Polar Explorer Red
Boots—snake proof Black
Pith helmet White
Stetson Black
16
NOT EXISTS
Returns true if the table contains no rows satisfying the specified condition
Report all clothing items (type “C”) that have not been sold
SELECT itemname, itemcolor FROM item
WHERE itemtype = 'C'
AND NOT EXISTS
(SELECT * FROM lineitem
WHERE item.itemno = lineitem.itemno);
itemname itemcolor
Hat—Polar Explorer White
Boots—snake proof Green
Pith helmet Khaki
Stetson Brown
17
lineno lineqty lineprice saleno itemno
1 1 4.5 1 2
1 1 25 2 6
2 1 20 2 16
3 1 25 2 19
4 1 2.25 2 2
1 1 500 3 4
2 1 2.25 3 2
1 1 500 4 4
2 1 65 4 9
3 1 60 4 13
4 1 75 4 14
5 1 10 4 3
6 1 2.25 4 2
1 50 36 5 10
2 50 40.5 5 11
3 8 153 5 12
4 1 60 5 13
5 1 0 5 2
itemno itemname itemtype itemcolor
1 Pocket knife — Nile E Brown
2 Pocket knife — Avon E Brown
3 Compass N —
4 Geopositioning system N —
5 Map measure N —
6 Hat — Polar Explorer C Red
7 Hat — Polar Explorer C White
8 Boots — snake proof C Green
9 Boots — snake proof C Black
10 Safari chair F Khaki
11 Hammock F Khaki
12 Tent — 8 person F Khaki
13 Tent — 2 person F Khaki
14 Safari cooking kit E —
15 Pith helmet C Khaki
16 Pith helmet C White
17 Map case N Brown
18 Sextant N —
19 Stetson C Black
20 Stetson C Brown
SELECT itemname, itemcolor
FROM item
WHERE itemtype = 'C'
AND NOT EXISTS
(SELECT * FROM lineitem
WHERE item.itemno =
lineitem.itemno);
itemname itemcolor
Hat—Polar Explorer White
Boots—snake proof Green
Pith helmet Khaki
Stetson Brown
Exercise
Report all brown items that have been sold
Report all brown items that have not been sold
18
19
Divide
The universal quantifier
forall
Not directly mapped into SQL
Implement using NOT EXISTS
Find all items that have appeared in all sales
becomes
Find items such that there does not exist a sale in which this item does not appear
20
Divide
Find the items that have appeared in all sales
SELECT itemname FROM item
WHERE NOT EXISTS
(SELECT * FROM sale
WHERE NOT EXISTS
(SELECT * FROM lineitem
WHERE lineitem.itemno = item.itemno
AND lineitem.saleno = sale.saleno));
itemname
Pocket knife—Thames See the book’s web site for a detailed explanation of how divide works (Support/SQL
Divide)
21
A template for divide
Find the target1 that have appeared in all sources
SELECT target1 FROM target
WHERE NOT EXISTS
(SELECT * FROM source
WHERE NOT EXISTS
(SELECT * FROM target-source
WHERE target-source.target# = target.target#
AND target-source.source# = source.source#));
22
Beyond the great divide
Find the items that have appeared in all sales can be rephrased as
Find all the items for which the number of sales that include this item is equal to the total number of sales.
SELECT item.itemno, item.itemname
FROM item JOIN lineitem
ON item.itemno = lineitem.itemno
GROUP BY item. itemno, item.itemname
HAVING COUNT(DISTINCT saleno)
= (SELECT COUNT(DISTINCT saleno) FROM sale);
First determine the number of sales in which an item has appeared
Second compare the number of sales to the total number of sales
23
Set operations
UNION
Equivalent to OR
INTERSECT
Equivalent to AND
24
UNION
List all items that were sold on January 16, 2011, or are brown.
SELECT itemname FROM item JOIN lineitem
ON item.itemno = lineitem.itemno
JOIN sale ON lineitem.saleno = sale.saleno
WHERE saledate = '2011-01-16'
UNION
SELECT itemname FROM item WHERE itemcolor = 'Brown';
itemname
Hammock
Map case
Pocket knife—Avon
Pocket knife—Nile
Safari chair
Stetson
Tent—2 person
Tent—8 person
25
INTERSECT
List all items that were sold on January 16, 2011, and are brown.
SELECT itemname FROM item JOIN lineitem
ON item.itemno = lineitem.itemno
JOIN sale ON lineitem.saleno = sale.saleno
WHERE saledate = '2011-01-16'
INTERSECT
SELECT itemname FROM item WHERE itemcolor = 'Brown';
itemname
Pocket knife—Avon INTERSECT not supported by MySQL
26
Conclusion
Introduced
m:m relationship
Associative entity
Weak entity
EXISTS
Divide
Set operations
