The CAP Database

The CAP database (for the table names customer, agent, and product) is used by a wholesale business to keep track of its customers, the products it sells to these customers, and the agents who place orders for products on behalf of these customers. The order table (ORDER is a SQL reserved word) is not a relationship because some triples of (cid, aid, pid) values occur more than once, and this is clearly the designer’s intention, since the customer can order the same product from the same agent on two different occasions.


⇓ Transforming (1-N relationship)
customer
cid cname city discnt
c001 TipTop Duluth 10.00
c002 Basics Dallas 12.00
c003 Allied Dallas 8.00
c004 ACME Duluth 8.00
c006 ACME Kyoto 0.00

agent
aid aname city percent
a01 Smith New York 6
a02 Jones Newark 6
a03 Brown Tokyo 7
a04 Gray New York 6
a05 Otasi Duluth 5
a06 Smith Dallas 5

product
pid pname city quantity price
p01 comb Dallas 111400 0.50
p02 brush Newark 203000 0.50
p03 razor Duluth 150600 1.00
p04 pen Duluth 125300 1.00
p05 pencil Dallas 221400 1.00
p06 folder Dallas 123100 2.00
p07 case Newark 100500 1.00
order3
orderno month cid aid pid qty dollar
1011 jan c001 a01 p01 1000 450.00
1012 jan c001 a01 p01 1000 450.00
1019 feb c001 a02 p02 400 180.00
1017 feb c001 a06 p03 600 540.00
1018 feb c001 a03 p04 600 540.00
1023 mar c001 a04 p05 500 450.00
1022 mar c001 a05 p06 400 720.00
1025 apr c001 a05 p07 800 720.00
1013 jan c002 a03 p03 1000 880.00
1026 may c002 a05 p03 800 704.00
1015 jan c003 a03 p05 1200 1104.00
1014 jan c003 a03 p05 1200 1104.00
1021 feb c004 a06 p01 1000 460.00
1016 jan c006 a01 p01 1000 500.00
1020 feb c006 a03 p07 600 600.00
1024 mar c006 a06 p01 800 400.00
⇓ Implementation (SQL commands) 
DROP TABLE order3;
DROP TABLE customer;
DROP TABLE agent;
DROP TABLE product;

CREATE TABLE customer (
  cid    CHAR(5) PRIMARY KEY,
  cname  VARCHAR(32) NOT NULL,
  city   VARCHAR(32),
  discnt REAL NOT NULL CHECK( discnt >= 0.00 AND discnt <= 20.00 ) );

INSERT INTO customer VALUES( 'c001', 'TipTop', 'Duluth', 10.00 );
INSERT INTO customer VALUES( 'c002', 'Basics', 'Dallas', 12.00 );
INSERT INTO customer VALUES( 'c003', 'Allied', 'Dallas',  8.00 );
INSERT INTO customer VALUES( 'c004', 'ACME',   'Duluth',  8.00 );
INSERT INTO customer VALUES( 'c006', 'ACME',   'Kyoto',   0.00 );

CREATE TABLE agent (
  aid     CHAR(5) PRIMARY KEY,
  aname   VARCHAR(32) NOT NULL,
  city    VARCHAR(32),
  percent INTEGER NOT NULL CHECK( percent >= 0 ) );

INSERT INTO agent VALUES( 'a01', 'Smith', 'New York', 6 );
INSERT INTO agent VALUES( 'a02', 'Jones', 'Newark',   6 );
INSERT INTO agent VALUES( 'a03', 'Brown', 'Tokyo',    7 );
INSERT INTO agent VALUES( 'a04', 'Gray',  'New York', 6 );
INSERT INTO agent VALUES( 'a05', 'Otasi', 'Duluth',   5 );
INSERT INTO agent VALUES( 'a06', 'Smith', 'Dallas',   5 );

CREATE TABLE product (
  pid      CHAR(5) PRIMARY KEY,
  pname    VARCHAR(32) NOT NULL,
  city     VARCHAR(32),
  quantity INTEGER NOT NULL CHECK( quantity >= 0 ),
  price    REAL NOT NULL CHECK( price >= 0.0 ) );

INSERT INTO product VALUES( 'p01', 'comb', 'Dallas', 111400, 0.50 );
INSERT INTO product VALUES( 'p02', 'comb', 'Newark', 203000, 0.50 );
INSERT INTO product VALUES( 'p03', 'comb', 'Duluth', 150600, 1.00 );
INSERT INTO product VALUES( 'p04', 'comb', 'Duluth', 125300, 1.00 );
INSERT INTO product VALUES( 'p05', 'comb', 'Dallas', 221400, 1.00 );
INSERT INTO product VALUES( 'p06', 'comb', 'Dallas', 123100, 2.00 );
INSERT INTO product VALUES( 'p07', 'comb', 'Newark', 100500, 1.00 );

-- order is a SQL reserved word.
CREATE TABLE order3 (
  orderno INTEGER PRIMARY KEY,
  month   CHAR(5),
  cid     CHAR(5) NOT NULL,
  aid     CHAR(5) NOT NULL,
  pid     CHAR(5) NOT NULL,
  qty     INTEGER NOT NULL CHECK( qty >= 0 ),
  dollar  REAL NOT NULL CHECK( dollar >= 0.0 ),
  FOREIGN KEY ( cid ) REFERENCES customer( cid ) ON DELETE CASCADE,
  FOREIGN KEY ( aid ) REFERENCES agent   ( aid ) ON DELETE CASCADE,
  FOREIGN KEY ( pid ) REFERENCES product ( pid ) ON DELETE CASCADE );

INSERT INTO order3 VALUES( 1011, 'jan', 'c001', 'a01', 'p01', 1000,  450.00 );
INSERT INTO order3 VALUES( 1012, 'jan', 'c001', 'a01', 'p01', 1000,  450.00 );
INSERT INTO order3 VALUES( 1019, 'feb', 'c001', 'a02', 'p02',  400,  180.00 );
INSERT INTO order3 VALUES( 1017, 'feb', 'c001', 'a06', 'p03',  600,  540.00 );
INSERT INTO order3 VALUES( 1018, 'feb', 'c001', 'a03', 'p04',  600,  540.00 );
INSERT INTO order3 VALUES( 1023, 'mar', 'c001', 'a04', 'p05',  500,  450.00 );
INSERT INTO order3 VALUES( 1022, 'mar', 'c001', 'a05', 'p06',  400,  720.00 );
INSERT INTO order3 VALUES( 1025, 'apr', 'c001', 'a05', 'p07',  800,  720.00 );
INSERT INTO order3 VALUES( 1013, 'jan', 'c002', 'a03', 'p03', 1000,  880.00 );
INSERT INTO order3 VALUES( 1026, 'may', 'c002', 'a05', 'p03',  800,  704.00 );
INSERT INTO order3 VALUES( 1015, 'jan', 'c003', 'a03', 'p05', 1200, 1104.00 );
INSERT INTO order3 VALUES( 1014, 'jan', 'c003', 'a03', 'p05', 1200, 1104.00 );
INSERT INTO order3 VALUES( 1021, 'feb', 'c004', 'a06', 'p01', 1000,  460.00 );
INSERT INTO order3 VALUES( 1016, 'jan', 'c006', 'a01', 'p01', 1000,  500.00 );
INSERT INTO order3 VALUES( 1020, 'feb', 'c006', 'a03', 'p07',  600,  600.00 );
INSERT INTO order3 VALUES( 1024, 'mar', 'c006', 'a06', 'p01',  800,  400.00 );


          Drop and create (using the above SQL commands)      

      List   customer     agent     product     order