####################################################
# CREATE THE DATABASE
####################################################
#drop database if exists test2;
#create database test2;
#use test2;
####################################################
# REINITIALIZE THE TABLES. ASSUME USER IS ALREADY
# CONNECTED TO THE PROPER DATABASE FROM WITHIN
# mysql OR psql.
####################################################
drop table if exists members;
drop table if exists families;
####################################################
# CREATE THE TWO TABLES FORMING A 1 TO MANY RELATIONSHIP.
# FAMILIES IS THE ONE, AND MEMBERS IS THE MANY.
# CREATE UNIQUE INDEX SUCH THAT FAMILY_ID PLUS NAME IN
# MEMBERS IS FORCED TO BE UNIQUE.
####################################################
create table families (
id int not null auto_increment,
name varchar(20) not null,
primary key (id)
);
show tables;
create table members (
id int not null auto_increment,
family_id int not null,
name varchar(16) not null,
primary key (id),
foreign key (family_id) references families
on delete restrict
on update cascade
);
create unique index familymember on members (family_id, name);
describe families;
describe members;
####################################################
# LOAD families WITH THREE ROWS
####################################################
insert into families (name) values ('Albertson');
insert into families (name) values ('Becker');
insert into families (name) values ('Cintez');
####################################################
# LOAD members WITH THREE ROWS FOR THE 'Albertson'
# FAMILY. USE MONOLITHIC SQL STATEMENTS TO ACCOMPLISH
# THIS.
####################################################
insert into members (family_id, name)
select families.id, 'Alvin' from families
where families.name = 'Albertson';
insert into members (family_id, name)
select families.id, 'Andrea' from families
where families.name = 'Albertson';
insert into members (family_id, name)
select families.id, 'Arthur' from families
where families.name = 'Albertson';
####################################################
# LOAD members WITH THREE ROWS FOR Becker and Cintez
# FAMILY. INSTEAD OF MONOLITHIC SQL STATEMENTS,
# LOOK UP families.id FROM families.name, AND THEN
# USE THAT id TO INSERT THE MEMBERS.
# SETTING @id TO NULL PREVENTS USAGE OF PREVIOUS VALUES WHEN
# THE SELECT''S WHERE CLAUSE FAILS
####################################################
select @id:=null;
select @id:=id from families where name='Becker';
insert into members (family_id, name) values(@id, 'Betty');
insert into members (family_id, name) values(@id, 'Ben');
insert into members (family_id, name) values(@id, 'Bob');
select @id:=null;
select @id:=id from families where name='Cintez';
insert into members (family_id, name) values(@id, 'Charles');
insert into members (family_id, name) values(@id, 'Christina');
insert into members (family_id, name) values(@id, 'Cindy');
####################################################
# SHOW EACH FAMILY AND ITS MEMBERS
####################################################
select families.id, families.name, members.name
from families, members where
(members.family_id = families.id);
|
|
Use this only if creating
a new database, otherwise
leave commented out
Drop the tables if they exist
to make room for new tables
of the same name
The families table is the 1 of
1 to many.
The members table is the many
of the 1 to many.
members.family_id matches
families.id.
Foreign key means you can't
delete a family that
still has members
This index prevent two family
members from having the same
first name
Show the structures of the two
tables just created
Load the families table.
Monolithic insert from select
Monolithic insert from select
Monolithic insert from select
The following inserts are
performed more procedurally,
by first finding families.id
based on families.name, and
then using that id as
members.family_id.
Prevent ghosts of selects past
Find id from families.name
Do the insert
Prevent ghosts of selects past
Find id from families.name
Do the insert
Join the tables in the
where clause, and find
all family members
|