CASE STUDY 3: SHIPPING (SOLUTION) [33]

I. REQUIREMENTS/SPECIFICATION

A. Narrative

An order-entry system contains information about customers, items, and orders. For each customer (CUST# unique), there is ship-to address (ADDRESS, several per customer), balance (BAL), credit limit (CREDLIM), and discount (DISCOUNT).

For each order (ORD# unique), there is a CUST#, ADDRESS and DATE.

For each line of the order (LINE#, several per order), there is an ITEM# (unique), quantity ordered (QTYORD) and quantity outstanding (QTYOUT).

For each item, there is a description (DESCN).

For each plant (PLANT# unique) that manufactures the item, there is a quantity on hand (QTYOH) and a stock danger level (DANGER).

For each plant, there is a manager (MGR).

B. Inputs

The GUI collects CUST#, ADDRESS, BAL, CREDLIM, DISCOUNT, DATE, ITEM#, QTYORD, QTYOUT, PLANT#, QTYOH, DANGER, MGR.

C. Outputs

Report: find all of the plants related to a specified customer (C1).

II. ANALYSIS [34]

A. Modeling

1. Entity/Relationship Diagram

An item is manufactured in MANY plants.

A plant manufactures MANY items.

Need an INTERSECTION relation IP.

An item appears on MANY orders.

An order has MANY items.

Need an INTERSECTION relation Ordlines.

2. FD List [35]

CUST# $\rightarrow$ BAL, CREDLIM, DISCOUNT

ADDRESS $\rightarrow$ CUST#

ORD# $\rightarrow$ CUST#, ADDRESS, DATE

ORD#, LINE# $\rightarrow$ ITEM#, QTYORD, QTYOUT

ITEM# $\rightarrow$ DESCN

ITEM#, PLANT# $\rightarrow$ QTYOH, DANGER

PLANT# $\rightarrow$ MGR

3. FD Diagram

4. OO Set Diagram: UML [36]

III. DESIGN [37]

A. Preliminary Design

1. Data Design

a. Internal: OO Set Data Definitions in Java

class Customer {
  public String CUST_NO;
  public double BAL;
  public double CREDLIM;
  public double DISCOUNT;
  public Set addresses = new Set();
  public Customer (String CUST_NO, double BAL, 
                   double CREDLIM, double DISCOUNT) {
    this.CUST_NO = CUST_NO; this.BAL = BAL; this.CREDLIM = CREDLIM; 
    this.DISCOUNT = DISCOUNT;
    shipDB.customers.put(CUST_NO,this);
  }
  public String toString() {
    return (CUST_NO+" "+BAL+" "+CREDLIM+" "+DISCOUNT); }
}
class Address {
  public String ADDRESS;
  public Customer customer;
  public Set orderHeads = new Set();
  public Address(String ADDRESS, Customer customer) {
    this.ADDRESS = ADDRESS; this.customer = customer;
    customer.addresses.put(ADDRESS,this);
  }
}
class OrderHead {
  public String ORDER_NO;
  public Address address;
  public Date DATE;
  public Set orderLines = new Set();
  public OrderHead(String ORDER_NO, Address address, Date DATE) {
    this.ORDER_NO = ORDER_NO; this.address = address; this.DATE = DATE;
    address.orderHeads.put(ORDER_NO,this);
  }
}

Java Classes [38]

class OrderLine {
  public OrderHead orderHead;
  public String LINE_NO;
  public Item item;
  public int QTYORD;
  public int QTYOUT;
  public OrderLine (OrderHead orderHead, String LINE_NO, Item item, 
                    int QTYORD, int QTYOUT) {
    this.orderHead = orderHead; this.LINE_NO = LINE_NO;
    this.item = item; this.QTYORD = QTYORD; this.QTYOUT = QTYOUT;
    orderHead.orderLines.put(orderHead.ORDER_NO+LINE_NO,this);
    item.orderLines.put(orderHead.ORDER_NO+LINE_NO,this);
  }
}
class Item {
  public String ITEM_NO;
  public String DESCN;
  public Set orderLines = new Set();
  public Set ips = new Set();
  public Item(String ITEM_NO, String DESCN) {
    this.ITEM_NO = ITEM_NO; this.DESCN = DESCN;
    shipDB.items.put(ITEM_NO,this);
  }
}
class IP {
  public Item item;
  public Plant plant;
  public int QTYOH;
  public int DANGER;
  public IP(Item item, Plant plant, int QTYOH, int DANGER) {
    this.item = item; this.plant = plant; 
    this.QTYOH = QTYOH; this.DANGER = DANGER;
    item.ips.put(item.ITEM_NO+plant.PLANT_NO,this);
    plant.ips.put(item.ITEM_NO+plant.PLANT_NO,this);
  }
}

Java Class and Schema [39]

class Plant {
  public String PLANT_NO;
  public String MGR;
  public Set ips = new Set();
  public Plant(String PLANT_NO, String MGR) {
    this.PLANT_NO = PLANT_NO; this.MGR = MGR;
    shipDB.plants.put(PLANT_NO,this);
  }
  public String toString() { return (PLANT_NO+ " " + MGR); }
}

b. External: Relation Schema

Customers(CUST#, BAL, CREDLIM, DISCOUNT)

Addresses(ADDRESS, CUST# )

Ordheads(ORD#, ADDRESS, DATE)

Ordlines(ORD#, LINE#, ITEM#, QTYORD, QTYOUT)

Items(ITEM#, DESCN)

IP(ITEM#, PLANT#, QTYOH, DANGER )

Plants(Plant#, MGR)

B. Detail Design [40]

1. Detail Data Design

b. External: SQL Data Dictionary

create table Customers (
  CUST_NO char(8) primary key,
  BAL float(10,2),
  CREDLIM float(10,2),
  DISCOUNT float(10,2)
)
create table Addresses (
  ADDRESS char(20) primary key,
  CUST_NO char(8),
  foreign key(CUST_NO) references Customers
)
create table Ordheads (
  ORD_NO char(8) primary key,
  ADDRESS char(20),
  ODATE char(6),
  foreign key(ADDRESS) references Addresses
)
create table Ordlines (
  ORD_NO char(8),
  LINE_NO int,
  ITEM_NO char(8),
  QTYORD int,
  QTYOUT int,
  primary key(ORD_NO, LINE_NO),
  foreign key(ORD_NO) references Ordheads
)
create table Items (
  ITEM_NO char(8) primary key,
  DESCN char(8)
)
create table IP (
  ITEM_NO char(8),
  PLANT_NO char(8),
  QTYOH int,
  DANGER int,
  primary key(ITEM_NO, PLANT_NO),
  foreign key(ITEM_NO) references Items,
  foreign key(PLANT_NO) references Plants
)
create table Plants (
  PLANT_NO char(8) primary key,
  MGR char(8)
)

2. Procedural Design [41]

b. External: SQL Query

select Plants.PLANT_NO, Plants.MGR
from   Customers, Addresses, Ordheads, Ordlines, IP, Plants
where  Customers.CUST_NO = Addresses.CUST_NO
and    Addresses.ADDRESS = Ordheads.ADDRESS
and    Ordheads.ORD_NO = Ordlines.ORD_NO
and    Ordlines.ITEM_NO = IP.ITEM_NO
and    IP.PLANT_NO = Plants.PLANT_NO
and    Customers.CUST_NO = "C1"

IV. IMPLEMENTATION

A. Internal: Report using Java OO Sets

public class shipDB {
  public static Set customers = new Set();
  public static Set plants = new Set();
  public static Set items = new Set();
  public static void main(String args[]) {
    new shipDB();
    Customer c = (Customer)shipDB.customers.get("C1");
    c.addresses.reset();
    while (c.addresses.next()) {
      Address a = (Address)c.addresses.get();
      a.orderHeads.reset();
      while (a.orderHeads.next()) {
        OrderHead oH = (OrderHead)a.orderHeads.get();
        oH.orderLines.reset();
        while (oH.orderLines.next()) {
          OrderLine oL = (OrderLine)oH.orderLines.get();
          Item i = oL.item;
          i.ips.reset();
          while (i.ips.next()) {
            IP ip = (IP)i.ips.get();
            Plant p = (Plant)ip.plant;
            System.out.println(p.toString());
          }
        }
      }
    }
  }
}

V. TEST [42]

A. Data Sets

1. Internal

a. Java OO Sets

  public shipDB() {
    Customer c1 = new Customer("C1",100.00,200.00,0.10);
    Address a1 = new Address("Carlos Bee",c1);
    OrderHead oH1 = new OrderHead("O1",a1,new Date());
    Item i1 = new Item("I1","Elephant"); 
    OrderLine oL1 = new OrderLine(oH1,"1",i1,10,20);
    Plant p1 = new Plant("P1","Jones");
    IP ip1 = new IP(i1,p1,100,200);
  }

b. Report Results

P1 Jones

2. External

a. SQL Data Sets

insert into Customers values("C1",100.00,200.00,0.10)
insert into Addresses values("Carlos Bee","C1")
insert into Ordheads values("O1","Carlos Bee","970101")
insert into Items values("I1","Elephant")
insert into Ordlines values("O1","1","I1",10,20)
insert into Plants values("P1","Jones")
insert into IP values("I1","P1",100,200)

b. SQL Query Results

PLANT_NO        MGR
P1            Jones