Lawrence Technological University
College of Arts and Science
Department of Mathematics and Computer Sciences

Handouts

The tables used in Ramakrishnan's textbook for Chapters 4 and 5:
Sailors1, Sailors2, Sailors3, Boats1, Reserves1 and Reserves2

Formats

  1. MySQL
  2. Access
  3. RDB
  4. Oracle
  5. PostgreSQL

MySQL Download sbr_mysql.sql or copy the following to a text editor:

# sbr_mysql.sql
# On Windows
#    CREATE database mcs5303
#  first, then
#    \mysql\bin\mysql -u your-account -p mcs5303 < sbr_mysql.sql
#  to create and load the tables
# On WWW5
#    mysql -u dbclass -p dbclass < sbr_mysql.sql
#  to create and load the tables
# NO key constraints because of 3 versions of sailors!
# Rev. 8/9/04 jmmiller@ltu.edu
# cleanup:
drop table if exists sailors1;
drop table if exists sailors2;
drop table if exists sailors3;
drop table if exists boats1;
drop table if exists reserves1;
drop table if exists reserves2;
# sailors 1
create table sailors1 (
  sid integer not null primary key,
  sname char(10),
  rating integer,
  age decimal(5,1)
);
insert into sailors1 values (22,'Dustin',7,45);
insert into sailors1 values (31,'Lubber',8,55.5);
insert into sailors1 values (58,'Rusty',10,35);
# sailors 2
create table sailors2 (
  sid integer not null primary key,
  sname char(10),
  rating integer,
  age decimal(5,1)
);
insert into sailors2 values (28,'Yuppy',9,35);
insert into sailors2 values (31,'Lubber',8,55.5);
insert into sailors2 values (44,'Guppy',5,35);
insert into sailors2 values (58,'Rusty',10,35);
# sailors 3
create table sailors3 (
  sid integer not null primary key,
  sname char(10),
  rating integer,
  age decimal(5,1)
);
insert into sailors3 values (22,'Dustin',7,45);
insert into sailors3 values (29,'Brutus',1,33);
insert into sailors3 values (31,'Lubber',8,55.5);
insert into sailors3 values (32,'Andy',8,25.5);
insert into sailors3 values (58,'Rusty',10,35);
insert into sailors3 values (64,'Horatio',7,35);
insert into sailors3 values (71,'Zorba',10,16);
insert into sailors3 values (74,'Horatio',9,35);
insert into sailors3 values (85,'Art',3,25.5);
insert into sailors3 values (95,'Bob',3,63.5);
# boats 1
create table boats1 (
  bid integer not null primary key,
  bname char(10),
  color enum('blue','red','green','red')
);
insert into boats1 values (101,'Interlake','blue');
insert into boats1 values (102,'Interlake','red');
insert into boats1 values (103,'Clipper','green');
insert into boats1 values (104,'Marine','red');
# reserves 1
create table reserves1 (
  sid integer not null,
  bid integer not null,
  day date not null,
  primary key(sid,bid,day)
);
insert into reserves1 values (22,101,'1996-10-10');
insert into reserves1 values (58,103,'1996-11-12');
# reserves 2
create table reserves2 (
  sid integer not null,
  bid integer not null,
  day date not null,
  primary key(sid,bid,day)
);
insert into reserves2 values (22,101,'1998-10-10');
insert into reserves2 values (22,102,'1998-10-10');
insert into reserves2 values (22,103,'1998-10-08');
insert into reserves2 values (22,104,'1998-10-07');
insert into reserves2 values (31,102,'1998-11-10');
insert into reserves2 values (31,103,'1998-11-06');
insert into reserves2 values (31,104,'1998-11-12');
insert into reserves2 values (64,101,'1998-09-05');
insert into reserves2 values (64,102,'1998-09-08');
insert into reserves2 values (74,103,'1998-09-08');

Access

-- sbr_access.sql
-- NO key constraints because of 3 versions of sailors!
drop table sailors1;
drop table sailors2;
drop table sailors3;
drop table boats1;
drop table reserves1;
drop table reserves2;
-- sailors 1
create table sailors1 (
  sid integer not null primary key,
  sname char(10),
  rating integer,
  age single
);
insert into sailors1 values (22,'Dustin',7,45);
insert into sailors1 values (31,'Lubber',8,55.5);
insert into sailors1 values (58,'Rusty',10,35);
-- sailors 2
create table sailors2 (
  sid integer not null primary key,
  sname char(10),
  rating integer,
  age single
);
insert into sailors2 values (28,'Yuppy',9,35);
insert into sailors2 values (31,'Lubber',8,55.5);
insert into sailors2 values (44,'Guppy',5,35);
insert into sailors2 values (58,'Rusty',10,35);
-- sailors 3
create table sailors3 (
  sid integer not null primary key,
  sname char(10),
  rating integer,
  age single
);
insert into sailors3 values (22,'Dustin',7,45);
insert into sailors3 values (29,'Brutus',1,33);
insert into sailors3 values (31,'Lubber',8,55.5);
insert into sailors3 values (32,'Andy',8,25.5);
insert into sailors3 values (58,'Rusty',10,35);
insert into sailors3 values (64,'Horatio',7,35);
insert into sailors3 values (71,'Zorba',10,16);
insert into sailors3 values (74,'Horatio',9,35);
insert into sailors3 values (85,'Art',3,25.5);
insert into sailors3 values (95,'Bob',3,63.5);
-- boats 1
create table boats1 (
  bid integer not null primary key,
  bname char(10),
  color char(10)
);
insert into boats1 values (101,'Interlake','blue');
insert into boats1 values (102,'Interlake','red');
insert into boats1 values (103,'Clipper','green');
insert into boats1 values (104,'Marine','red');
-- reserves 1
create table reserves1 (
  sid integer not null,
  bid integer not null,
  day datetime not null,
  primary key(sid,bid,day)
);
insert into reserves1 values (22,101,'1996-10-10');
insert into reserves1 values (58,103,'1996-11-12');
-- reserves 2
create table reserves2 (
  sid integer not null,
  bid integer not null,
  day datetime not null,
  primary key(sid,bid,day)
);
insert into reserves2 values (22,101,'1998-10-10');
insert into reserves2 values (22,102,'1998-10-10');
insert into reserves2 values (22,103,'1998-10-08');
insert into reserves2 values (22,104,'1998-10-07');
insert into reserves2 values (31,102,'1998-11-10');
insert into reserves2 values (31,103,'1998-11-06');
insert into reserves2 values (31,104,'1998-11-12');
insert into reserves2 values (64,101,'1998-09-05');
insert into reserves2 values (64,102,'1998-09-08');
insert into reserves2 values (74,103,'1998-09-08');

RDB

-- sbr_rdb.sql
-- Build Chapter 4 tables on Vax in Rdb
-- Save as sbr_rdb.sql
-- First, create the database by:
-- SQL> create database alias dbclass;
-- Then, run this script by:
-- $ sql @sbr_rdb.sql
-- After a long series of "1 row inserted", reply to
-- Would you like a chance to ROLLBACK these changes (No)?
-- with <Enter>
-- To avoid the warning about deferred constraints:
set warning nodeprecate;
attach 'filename dbclass';
-- NO key constraints because of 3 versions of sailors!
-- Rev. 1/31/04 jmmiller@ltu.edu
-- sailors 1
create table sailors1 (
  sid integer not null,
  sname char(10),
  rating integer,
  age integer(1),
  constraint pk_s1 primary key (sid)
);
insert into sailors1 values (22,'Dustin',7,45);
insert into sailors1 values (31,'Lubber',8,55.5);
insert into sailors1 values (58,'Rusty',10,35);
-- sailors 2
create table sailors2 (
  sid integer not null,
  sname char(10),
  rating integer,
  age integer(1),
  constraint pk_s2 primary key (sid)
);
insert into sailors2 values (28,'Yuppy',9,35);
insert into sailors2 values (31,'Lubber',8,55.5);
insert into sailors2 values (44,'Guppy',5,35);
insert into sailors2 values (58,'Rusty',10,35);
-- sailors 3
create table sailors3 (
  sid integer not null,
  sname char(10),
  rating integer,
  age integer(1),
  constraint pk_s3 primary key (sid)
);
insert into sailors3 values (22,'Dustin',7,45);
insert into sailors3 values (29,'Brutus',1,33);
insert into sailors3 values (31,'Lubber',8,55.5);
insert into sailors3 values (32,'Andy',8,25.5);
insert into sailors3 values (58,'Rusty',10,35);
insert into sailors3 values (64,'Horatio',7,35);
insert into sailors3 values (71,'Zorba',10,16);
insert into sailors3 values (74,'Horatio',9,35);
insert into sailors3 values (85,'Art',3,25.5);
insert into sailors3 values (95,'Bob',3,63.5);
-- boats 1
create table boats1 (
  bid integer not null,
  bname char(10),
  color char(6),
  constraint pk_b1 primary key(bid)
);
insert into boats1 values (101,'Interlake','blue');
insert into boats1 values (102,'Interlake','red');
insert into boats1 values (103,'Clipper','green');
insert into boats1 values (104,'Marine','red');
-- reserves 1
create table reserves1 (
  sid integer not null,
  bid integer not null,
  rday date ansi not null,
  constraint pk_r1 primary key(sid,bid,rday)
);
insert into reserves1 values (22,101,date ansi '1996-10-10');
insert into reserves1 values (58,103,date ansi '1996-11-12');
-- reserves 2
create table reserves2 (
  sid integer not null,
  bid integer not null,
  rday date ansi not null,
  constraint pk_r2 primary key(sid,bid,rday)
);
insert into reserves2 values (22,101,date ansi '1998-10-10');
insert into reserves2 values (22,102,date ansi '1998-10-10');
insert into reserves2 values (22,103,date ansi '1998-10-08');
insert into reserves2 values (22,104,date ansi '1998-10-07');
insert into reserves2 values (31,102,date ansi '1998-11-10');
insert into reserves2 values (31,103,date ansi '1998-11-06');
insert into reserves2 values (31,104,date ansi '1998-11-12');
insert into reserves2 values (64,101,date ansi '1998-09-05');
insert into reserves2 values (64,102,date ansi '1998-09-08');
insert into reserves2 values (74,103,date ansi '1998-09-08');
-- cleanup:
-- drop table sailors1;
-- drop table sailors2;
-- drop table sailors3;
-- drop table boats1;
-- drop table reserves1;
-- drop table reserves2;
-- drop database filename 'dbclass';
-- To Kill the database if drop does not work, manually delete it:
-- $ set protection dbclass.*.*/protection=owner:d
-- $ del dbclass.*.*

Oracle

-- sbr_oracle.sql
-- NO key constraints because of 3 versions of sailors!
-- Use the Windows clipboard to paste this into SQL Plus
drop table sailors1;
drop table sailors2;
drop table sailors3;
drop table boats1;
drop table reserves1;
drop table reserves2;
-- sailors 1
create table sailors1 (
  sid integer not null primary key,
  sname char(10),
  rating integer,
  age decimal(5,1)
);
insert into sailors1 values (22,'Dustin',7,45);
insert into sailors1 values (31,'Lubber',8,55.5);
insert into sailors1 values (58,'Rusty',10,35);
-- sailors 2
create table sailors2 (
  sid integer not null primary key,
  sname char(10),
  rating integer,
  age decimal(5,1)
);
insert into sailors2 values (28,'Yuppy',9,35);
insert into sailors2 values (31,'Lubber',8,55.5);
insert into sailors2 values (44,'Guppy',5,35);
insert into sailors2 values (58,'Rusty',10,35);
-- sailors 3
create table sailors3 (
  sid integer not null primary key,
  sname char(10),
  rating integer,
  age decimal(5,1)
);
insert into sailors3 values (22,'Dustin',7,45);
insert into sailors3 values (29,'Brutus',1,33);
insert into sailors3 values (31,'Lubber',8,55.5);
insert into sailors3 values (32,'Andy',8,25.5);
insert into sailors3 values (58,'Rusty',10,35);
insert into sailors3 values (64,'Horatio',7,35);
insert into sailors3 values (71,'Zorba',10,16);
insert into sailors3 values (74,'Horatio',9,35);
insert into sailors3 values (85,'Art',3,25.5);
insert into sailors3 values (95,'Bob',3,63.5);
-- boats 1
create table boats1 (
  bid integer not null primary key,
  bname char(10),
  color char(10)
);
insert into boats1 values (101,'Interlake','blue');
insert into boats1 values (102,'Interlake','red');
insert into boats1 values (103,'Clipper','green');
insert into boats1 values (104,'Marine','red');
-- reserves 1
create table reserves1 (
  sid integer not null,
  bid integer not null,
  day date not null,
  primary key(sid,bid,day)
);
insert into reserves1 values (22,101,TO_DATE('1996-10-10','YYYY-MM-DD'));
insert into reserves1 values (58,103,TO_DATE('1996-11-12','YYYY-MM-DD'));
-- reserves 2
create table reserves2 (
  sid integer not null,
  bid integer not null,
  day date not null,
  primary key(sid,bid,day)
);
insert into reserves2 values (22,101,TO_DATE('1998-10-10','YYYY-MM-DD'));
insert into reserves2 values (22,102,TO_DATE('1998-10-10','YYYY-MM-DD'));
insert into reserves2 values (22,103,TO_DATE('1998-10-08','YYYY-MM-DD'));
insert into reserves2 values (22,104,TO_DATE('1998-10-07','YYYY-MM-DD'));
insert into reserves2 values (31,102,TO_DATE('1998-11-10','YYYY-MM-DD'));
insert into reserves2 values (31,103,TO_DATE('1998-11-06','YYYY-MM-DD'));
insert into reserves2 values (31,104,TO_DATE('1998-11-12','YYYY-MM-DD'));
insert into reserves2 values (64,101,TO_DATE('1998-09-05','YYYY-MM-DD'));
insert into reserves2 values (64,102,TO_DATE('1998-09-08','YYYY-MM-DD'));
insert into reserves2 values (74,103,TO_DATE('1998-09-08','YYYY-MM-DD'));

PostgreSQL Download sbr_postgresql.sql or copy the following to a text editor:

-- sbr_postgresql.sql
-- NO key constraints because of 3 versions of sailors!
drop table sailors1;
drop table sailors2;
drop table sailors3;
drop table boats1;
drop table reserves1;
drop table reserves2;
-- sailors 1
create table sailors1 (
  sid integer not null primary key,
  sname char(10),
  rating integer,
  age decimal(5,1)
);
insert into sailors1 values (22,'Dustin',7,45);
insert into sailors1 values (31,'Lubber',8,55.5);
insert into sailors1 values (58,'Rusty',10,35);
-- sailors 2
create table sailors2 (
  sid integer not null primary key,
  sname char(10),
  rating integer,
  age decimal(5,1)
);
insert into sailors2 values (28,'Yuppy',9,35);
insert into sailors2 values (31,'Lubber',8,55.5);
insert into sailors2 values (44,'Guppy',5,35);
insert into sailors2 values (58,'Rusty',10,35);
-- sailors 3
create table sailors3 (
  sid integer not null primary key,
  sname char(10),
  rating integer,
  age decimal(5,1)
);
insert into sailors3 values (22,'Dustin',7,45);
insert into sailors3 values (29,'Brutus',1,33);
insert into sailors3 values (31,'Lubber',8,55.5);
insert into sailors3 values (32,'Andy',8,25.5);
insert into sailors3 values (58,'Rusty',10,35);
insert into sailors3 values (64,'Horatio',7,35);
insert into sailors3 values (71,'Zorba',10,16);
insert into sailors3 values (74,'Horatio',9,35);
insert into sailors3 values (85,'Art',3,25.5);
insert into sailors3 values (95,'Bob',3,63.5);
-- boats 1
create table boats1 (
  bid integer not null primary key,
  bname char(10),
  color char(10)
);
insert into boats1 values (101,'Interlake','blue');
insert into boats1 values (102,'Interlake','red');
insert into boats1 values (103,'Clipper','green');
insert into boats1 values (104,'Marine','red');
-- reserves 1
create table reserves1 (
  sid integer not null,
  bid integer not null,
  day date not null,
  primary key(sid,bid,day)
);
insert into reserves1 values (22,101,'1996-10-10');
insert into reserves1 values (58,103,'1996-11-12');
-- reserves 2
create table reserves2 (
  sid integer not null,
  bid integer not null,
  day date not null,
  primary key(sid,bid,day)
);
insert into reserves2 values (22,101,'1998-10-10');
insert into reserves2 values (22,102,'1998-10-10');
insert into reserves2 values (22,103,'1998-10-08');
insert into reserves2 values (22,104,'1998-10-07');
insert into reserves2 values (31,102,'1998-11-10');
insert into reserves2 values (31,103,'1998-11-06');
insert into reserves2 values (31,104,'1998-11-12');
insert into reserves2 values (64,101,'1998-09-05');
insert into reserves2 values (64,102,'1998-09-08');
insert into reserves2 values (74,103,'1998-09-08');

Revised August 8, 2004