1drop database if exists demo; 2create database demo; 3use demo; 4create table ab_physical_person ( 5person_id integer, 6first_name VARCHAR(50), 7middle_initial CHAR, 8last_name VARCHAR(50), 9primary key (person_id)); 10create table ab_moral_person ( 11company_id integer, 12name VARCHAR(100), 13primary key (company_id)); 14create table in_inventory ( 15item_id integer, 16descr VARCHAR(50), 17stock integer, 18primary key (item_id)); 19create table po_order ( 20po_id integer auto_increment, 21cust_type char, /* arc relationship, see cust_id */ 22cust_id integer, /* FK to ab_physical_person *OR* ab_moral_person */ 23primary key (po_id)); 24create table po_order_line ( 25po_id integer, /* FK to po_order.po_id */ 26line_no integer, 27item_id integer, /* FK to in_inventory.item_id */ 28qty integer); 29# 30# Schema integrity enforcement 31# 32create procedure check_pk_person(in person_type char, in id integer) 33begin 34declare x integer; 35declare msg varchar(128); 36/* 37Test integrity constraints for an 'arc' relationship. 38Based on 'person_type', 'id' points to either a 39physical person, or a moral person. 40*/ 41case person_type 42when 'P' then 43begin 44select count(person_id) from ab_physical_person 45where ab_physical_person.person_id = id 46into x; 47if (x != 1) 48then 49set msg= concat('No such physical person, PK:', id); 50SIGNAL SQLSTATE '45000' SET 51MESSAGE_TEXT = msg, 52MYSQL_ERRNO = 10000; 53end if; 54end; 55when 'M' then 56begin 57select count(company_id) from ab_moral_person 58where ab_moral_person.company_id = id 59into x; 60if (x != 1) 61then 62set msg= concat('No such moral person, PK:', id); 63SIGNAL SQLSTATE '45000' SET 64MESSAGE_TEXT = msg, 65MYSQL_ERRNO = 10000; 66end if; 67end; 68else 69begin 70set msg= concat('No such person type:', person_type); 71SIGNAL SQLSTATE '45000' SET 72MESSAGE_TEXT = msg, 73MYSQL_ERRNO = 20000; 74end; 75end case; 76end 77$$ 78Warnings: 79Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 80Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 81create procedure check_pk_inventory(in id integer) 82begin 83declare x integer; 84declare msg varchar(128); 85select count(item_id) from in_inventory 86where in_inventory.item_id = id 87into x; 88if (x != 1) 89then 90set msg= concat('Failed integrity constraint, table in_inventory, PK:', 91id); 92SIGNAL SQLSTATE '45000' SET 93MESSAGE_TEXT = msg, 94MYSQL_ERRNO = 10000; 95end if; 96end 97$$ 98Warnings: 99Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 100create procedure check_pk_order(in id integer) 101begin 102declare x integer; 103declare msg varchar(128); 104select count(po_id) from po_order 105where po_order.po_id = id 106into x; 107if (x != 1) 108then 109set msg= concat('Failed integrity constraint, table po_order, PK:', id); 110SIGNAL SQLSTATE '45000' SET 111MESSAGE_TEXT = msg, 112MYSQL_ERRNO = 10000; 113end if; 114end 115$$ 116Warnings: 117Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 118create trigger po_order_bi before insert on po_order 119for each row 120begin 121call check_pk_person(NEW.cust_type, NEW.cust_id); 122end 123$$ 124create trigger po_order_bu before update on po_order 125for each row 126begin 127call check_pk_person(NEW.cust_type, NEW.cust_id); 128end 129$$ 130create trigger po_order_line_bi before insert on po_order_line 131for each row 132begin 133call check_pk_order(NEW.po_id); 134call check_pk_inventory(NEW.item_id); 135end 136$$ 137create trigger po_order_line_bu before update on po_order_line 138for each row 139begin 140call check_pk_order(NEW.po_id); 141call check_pk_inventory(NEW.item_id); 142end 143$$ 144# 145# Application helpers 146# 147create procedure po_create_order( 148in p_cust_type char, 149in p_cust_id integer, 150out id integer) 151begin 152insert into po_order set cust_type = p_cust_type, cust_id = p_cust_id; 153set id = last_insert_id(); 154end 155$$ 156create procedure po_add_order_line( 157in po integer, 158in line integer, 159in item integer, 160in q integer) 161begin 162insert into po_order_line set 163po_id = po, line_no = line, item_id = item, qty = q; 164end 165$$ 166# 167# Create sample data 168# 169insert into ab_physical_person values 170( 1, "John", "A", "Doe"), 171( 2, "Marry", "B", "Smith") 172; 173insert into ab_moral_person values 174( 3, "ACME real estate, INC"), 175( 4, "Local school") 176; 177insert into in_inventory values 178( 100, "Table, dinner", 5), 179( 101, "Chair", 20), 180( 200, "Table, coffee", 3), 181( 300, "School table", 25), 182( 301, "School chairs", 50) 183; 184select * from ab_physical_person order by person_id; 185person_id first_name middle_initial last_name 1861 John A Doe 1872 Marry B Smith 188select * from ab_moral_person order by company_id; 189company_id name 1903 ACME real estate, INC 1914 Local school 192select * from in_inventory order by item_id; 193item_id descr stock 194100 Table, dinner 5 195101 Chair 20 196200 Table, coffee 3 197300 School table 25 198301 School chairs 50 199# 200# Entering an order 201# 202set @my_po = 0; 203/* John Doe wants 1 table and 4 chairs */ 204call po_create_order("P", 1, @my_po); 205call po_add_order_line (@my_po, 1, 100, 1); 206call po_add_order_line (@my_po, 2, 101, 4); 207/* Marry Smith wants a coffee table */ 208call po_create_order("P", 2, @my_po); 209call po_add_order_line (@my_po, 1, 200, 1); 210# 211# Entering bad data in an order 212# 213call po_add_order_line (@my_po, 1, 999, 1); 214ERROR 45000: Failed integrity constraint, table in_inventory, PK:999 215# 216# Entering bad data in an unknown order 217# 218call po_add_order_line (99, 1, 100, 1); 219ERROR 45000: Failed integrity constraint, table po_order, PK:99 220# 221# Entering an order for an unknown company 222# 223call po_create_order("M", 7, @my_po); 224ERROR 45000: No such moral person, PK:7 225# 226# Entering an order for an unknown person type 227# 228call po_create_order("X", 1, @my_po); 229ERROR 45000: No such person type:X 230/* The local school wants 10 class tables and 20 chairs */ 231call po_create_order("M", 4, @my_po); 232call po_add_order_line (@my_po, 1, 300, 10); 233call po_add_order_line (@my_po, 2, 301, 20); 234select * from po_order; 235po_id cust_type cust_id 2361 P 1 2372 P 2 2383 M 4 239select * from po_order_line; 240po_id line_no item_id qty 2411 1 100 1 2421 2 101 4 2432 1 200 1 2443 1 300 10 2453 2 301 20 246select po_id as "PO#", 247( case cust_type 248when "P" then concat (pp.first_name, 249" ", 250pp.middle_initial, 251" ", 252pp.last_name) 253when "M" then mp.name 254end ) as "Sold to" 255 from po_order po 256left join ab_physical_person pp on po.cust_id = pp.person_id 257left join ab_moral_person mp on po.cust_id = company_id 258; 259PO# Sold to 2601 John A Doe 2612 Marry B Smith 2623 Local school 263select po_id as "PO#", 264ol.line_no as "Line", 265ol.item_id as "Item", 266inv.descr as "Description", 267ol.qty as "Quantity" 268 from po_order_line ol, in_inventory inv 269where inv.item_id = ol.item_id 270order by ol.item_id, ol.line_no; 271PO# Line Item Description Quantity 2721 1 100 Table, dinner 1 2731 2 101 Chair 4 2742 1 200 Table, coffee 1 2753 1 300 School table 10 2763 2 301 School chairs 20 277drop database demo; 278