next up previous contents
Next: CASE STUDY 2: EMPLOYEES Up: Contents Previous: INTRODUCTION   Contents

CASE STUDY 1: SUPPLIERS (SOLUTION) [4]

I. REQUIREMENTS

A. Narrative

A simple database system keeps track of suppliers and the parts available from each supplier.

Each supplier has a S# (unique), name (SNAME), status (STATUS), and location (CITY).

Each part has a P# (unique), name (PNAME), color (COLOR), weight (WEIGHT) and location (CITY).

When a supplier provides a part, there is a fixed quantity (QTY) in any shipment of that part from that supplier.

B. Inputs

The GUI collects the supplier name (or empty string).

C. Outputs

The GUI displays all information about suppliers, parts, and amounts.

Upon request, the GUI displays the ``Red'' parts for the specified supplier (or all suppliers).



II. ANALYSIS [5]

A. Modeling

1. Entity/Relationship (E/R) Diagram

S S# SNAME STATUS CITY

$\downarrow$

SP S# P# QTY

$\uparrow$

P P# PNAME COLOR WEIGHT CITY

one-to-many ($\downarrow$) relationship between S and SP

(usually there are double arrows but not in these notes)

hint: S# is a foreign key in the composite key of SP

one-to-many ($\uparrow$) relationship between P and SP

many-to-many relationship between S and P

SP is an intersection relation




2. Functional Dependency (FD) List

S# $\rightarrow$ SNAME STATUS CITY

P# $\rightarrow$ PNAME COLOR WEIGHT CITY

S#P# $\rightarrow$ QTY



3. FD Diagram [6]


\begin{picture}(500,400)(80,420) \thicklines\put(480,780){\framebox (100,40){}} ... ...505,435){\makebox(0,0)[lb]{\raisebox{0pt}[0pt][0pt]{\twlrm CITY}}} \end{picture}



4. OO Set Diagram: UML [7]

Modeling technique that integrates:

Object-Oriented Data Modeling

Entity-Relationship Diagram

Relation Schema




\begin{picture}(300,414)(60,380) \thicklines\put( 80,600){\line( 0,-1){ 20}} \pu... ...(0,0)[lb]{\raisebox{0pt}[0pt][0pt]{\twlrm inheritance: \lq\lq is a''}}} \end{picture}



4. OO Set Diagram: UML [8]


\begin{picture}(580,465)(20,375) \thicklines\put(140,720){\framebox (80,20){}} \... ...(505,500){\makebox(0,0)[lb]{\raisebox{0pt}[0pt][0pt]{\twlrm 0:m}}} \end{picture}



B.Prototype [9]

suppmain.java: 

import java.awt.*;
import java.applet.*;
import java.awt.event.*;
public class suppmain {

  public static void main(String args[]) {
     Applet applet = new suppApplet();
     Frame frame = new suppFrame(applet);
   }
}

class suppFrame extends Frame implements ActionListener {

  public suppFrame (Applet applet) {
    super("Supplier Database");
    MenuBar menubar = new MenuBar();
    Menu file = new Menu("File",true);
    menubar.add(file);
    file.add("Quit");
    this.setMenuBar(menubar);
    file.addActionListener(this); 
    this.add("Center",applet);
    this.setSize(620,680);
    applet.init();
    this.show();
  }

  public void actionPerformed (ActionEvent evt) {
    String arg = evt.getActionCommand();
    if (arg.equals("Quit")) {
        System.exit(0);
    }
  }
}



supp.html [10] 

<html>
<head>
<title>Supplier Database</title>
</head>
<body>
<applet
    code=suppApplet.class
    id=supp
    width=620
    height=840 >
</applet>
</html>



suppApplet.java [11] 

import java.awt.*;
import java.applet.*;
import java.awt.event.*;
public class suppApplet extends Applet implements ActionListener, ItemListener {
  Label  suppLabel;
  TextField suppField;
  Button partsButton;
  TextArea textArea1;
  Choice choice;
  suppDB db = new suppDB();
  suppReport rpt;
 
  public void init() {
    super.init();
    setLayout(null);
   
    suppLabel = new Label("Supplier #:");
    suppLabel.setLocation(20,10); suppLabel.setSize(70,30);
    add(suppLabel);
    
    suppField = new TextField(100);
    suppField.setLocation(100,10); suppField.setSize(100,30);
    add(suppField);
  
    partsButton = new Button("Show Red Parts");
    partsButton.setLocation(10,240); partsButton.setSize(120,25);
    add(partsButton);
    partsButton.addActionListener(this); 
    textArea1 = new TextArea();
    textArea1.setLocation(20,270); textArea1.setSize(580,300);
    Font font = new Font("Courier",Font.PLAIN,10);
    textArea1.setFont(font);
    add(textArea1);
     
    choice = new Choice();
    choice.addItem("red"); choice.addItem("blue");
    choice.setLocation(30,60); choice.setSize(40,100);
    add(choice);
    choice.addItemListener(this); 
    rpt = new suppReport(textArea1);
    rpt.showSuppliers(); 
    rpt.showParts(); 
    rpt.showAmounts(); 
  } 
  public void actionPerformed (ActionEvent evt) {
    String arg = evt.getActionCommand();
    if (arg.equals("Show Red Parts"))
       rpt.findParts(suppField.getText(),"Red");
  }
  public void itemStateChanged(ItemEvent event) {
    textArea1.append(choice.getSelectedItem() + "\n");      
  }
}



III. DESIGN [12]

A. Preliminary Design

1. Data Design

a. Internal: OO Set Definitions in Java 

class Supplier {
  public String S_NO;
  public String SNAME;
  public int STATUS;
  public String CITY;
  public Set amounts = new Set();
  public Supplier(String S_NO, String SNAME, int STATUS, String CITY) {
    this.S_NO = S_NO; this.SNAME = SNAME; this.STATUS = STATUS; 
    this.CITY = CITY;
    suppDB.suppliers.put(S_NO,this);
  }
  public String toString() { 
    return (S_NO+" "+SNAME+" "+STATUS+" "+CITY); 
  }
  public void remove() { 
    suppDB.suppliers.remove(this); 
  }
}
class Part {
  public String P_NO;
  public String PNAME;
  public String COLOR;
  public int WEIGHT;
  public String CITY;
  public Set amounts = new Set();
  public Part(String P_NO, String PNAME, String COLOR, int WEIGHT, 
              String CITY) {
    this.P_NO = P_NO; this.PNAME = PNAME; this.COLOR = COLOR; 
    this.WEIGHT = WEIGHT; this.CITY = CITY;
    suppDB.parts.put(P_NO,this);
  }
  public String toString() { 
    return (P_NO + " " + PNAME + " " + COLOR + " " + WEIGHT + " " + CITY); 
  }
  public void remove() { suppDB.parts.remove(this); }
}



Java Classes [13] 

class Amount {
  Supplier supplier;
  Part part;
  int QTY;
  public Amount(Supplier supplier, Part part, int QTY) {
    this.supplier = supplier; this.part = part; this.QTY = QTY;
    supplier.amounts.put(supplier.S_NO+part.P_NO,this);
    suppDB.amounts.put(supplier.S_NO+part.P_NO,this);
    part.amounts.put(supplier.S_NO+part.P_NO,this);
  }
  public String toString() { 
    return (supplier.S_NO + " " + part.P_NO + " " + QTY); 
  }
  public void remove() {
    supplier.amounts.remove(this);
    suppDB.amounts.remove(this);
    part.amounts.remove(this);
  }
}



b. External: Relation Schema [14]

Suppliers(S#, SNAME, STATUS, CITY)

Parts(P#, PNAME, COLOR, WEIGHT, CITY)

Amounts(S#, P#, QTY)




2. Architecture Design (Modular Structure and Control Relationships)


\begin{picture}(580,420)(20,400) \thicklines\put(470,735){\vector(-1, 0){140}} \... ...,640){\makebox(0,0)[lb]{\raisebox{0pt}[0pt][0pt]{\twlrm getText}}} \end{picture}

3. User Interface Design

See Prototype II.B.



B. Detail Design [15]

1. Detail Data Design

a. Internal: Refined Data Structures (NONE)

b. External: SQL Data Dictionary 

create table S (
  S_NO char(8) primary key,
  SNAME char(8),
  STATUS int,
  CITY char(8)
)
create table P (
  P_NO char(8) primary key,
  PNAME char(8),
  COLOR char(8),
  WEIGHT int,
  CITY char(8)
)
create table SP (
  S_NO char(8),
  P_NO char(8),
  QTY int,
  primary key (S_NO, P_NO),
  foreign key (S_NO) references S,
  foreign key (P_NO) references P
)



2. Procedural Design [16]

a. Internal: Pseudocode 

starting from one supplier (or a scan of all suppliers)
  find all of the parts they supply via the amounts
  if the part associated with the amount is the color ``Red'' then
    print

b. External: SQL Query 

select S.S_NO, S.SNAME, P.PNAME, P.COLOR 
from   S, P, SP
where  S.S_NO = SP.S_NO
and    SP.P_NO = P.P_NO
and    P.COLOR = "Red"



IV. IMPLEMENTATION [17]

A. Internal: Java OO Set Prototype

suppIntReport.java: 

import java.awt.*;
import java.applet.*;
public class suppReport {
  TextArea textArea1;
  public suppReport(TextArea textArea1) {
    this.textArea1 = textArea1;
  }
  public void showSuppliers() { 
    textArea1.appendText("Suppliers\n");
    while (suppDB.suppliers.next()) {
      Supplier s = (Supplier)suppDB.suppliers.get();
      textArea1.appendText(s.toString()+"\n");
    }
  }
  public void showParts() { 
    textArea1.appendText("Parts\n");
    while (suppDB.parts.next()) {
      Part p = (Part)suppDB.parts.get();
      textArea1.appendText(p.toString()+"\n");
    }
  }
  public void showAmounts() { 
    textArea1.appendText("Amounts\n");
    while (suppDB.amounts.next()) {
      Amount a = (Amount)suppDB.amounts.get();
      textArea1.appendText(a.toString()+"\n");
    }
  }



Java Reports [18] 

  public void findParts(String suppnum, String color) {
    textArea1.appendText("Report: " + color + " parts for supplier # " + 
                          suppnum+"\n");
    if (suppnum.equals(""))
      for (int i=0; i<suppDB.suppliers.size(); i++) {
        Supplier supplier = (Supplier)suppDB.suppliers.get(i); 
        scan_amounts(supplier, color);
      } else {
        Supplier supplier = (Supplier)suppDB.suppliers.get(suppnum);
        scan_amounts(supplier, color);
    }
  }
  void scan_amounts(Supplier supplier, String color) {
    supplier.amounts.reset();
    while ( supplier.amounts.next() ){
      Amount amount = (Amount)supplier.amounts.get();
      if (amount.part.COLOR.equals(color))
        textArea1.appendText(amount.supplier.S_NO+ " "+
                             amount.supplier.SNAME+" "+
                             amount.part.PNAME+" "+
                             amount.part.COLOR+"\n");    
    }
  }
}



B.External: JDBC SQL [19]

suppExtReport.java: 

import java.awt.*;
import java.applet.*;
import java.net.URL;
import exjava.sql.*;
import exgwe.sql.*;
public class suppReport {
  String driverName = "exgwe.sql.gweMysqlDriver";
  String url   = "jdbc:mysql://gold.mcs.csuhayward.edu:3333/suppliers"; 
  String user = "any";
  String dbpass = "any";
  TextArea textArea1;
  public suppReport(TextArea textArea1) {
    this.textArea1 = textArea1;
    try {
      Class.forName(driverName);
    } catch (Exception e) 
       textArea1.appendText("Cannot find Call for "+driverName);
  }
  public void showSuppliers() { 
    try {
      Connection con = DriverManager.getConnection(url,user,dbpass);
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery("select * from S");
      ResultSetMetaData md = rs.getMetaData();
      textArea1.appendText("\n"+SQLutil.getRow(rs,md,true)+"\n");
      while (rs.next())
        textArea1.appendText(SQLutil.getRow(rs,md,false)+"\n");

      textArea1.appendText("\n");
      rs.close();
      stmt.close();
      con.close();
    } catch (Exception e) {textArea1.appendText(e.getMessage()+ "\n");}
  }



SQL Reports [20] 

  public void showParts() { 
    try {
      Connection con = DriverManager.getConnection(url,user,dbpass);
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery("select * from P");
      ResultSetMetaData md = rs.getMetaData();
      textArea1.appendText("\n"+SQLutil.getRow(rs,md,true)+"\n");
      while (rs.next())
        textArea1.appendText(SQLutil.getRow(rs,md,false)+"\n");

      textArea1.appendText("\n");
      rs.close();
      stmt.close();
      con.close();
    } catch (Exception e) {textArea1.appendText(e.getMessage()+ "\n");}
  }
  
  public void showAmounts() { 
    try {
      Connection con = DriverManager.getConnection(url,user,dbpass);
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery("select * from SP");
      ResultSetMetaData md = rs.getMetaData();
      textArea1.appendText("\n"+SQLutil.getRow(rs,md,true)+"\n");
      while (rs.next())
        textArea1.appendText(SQLutil.getRow(rs,md,false)+"\n");

      textArea1.appendText("\n");
      rs.close();
      stmt.close();
      con.close();
    } catch (Exception e) {textArea1.appendText(e.getMessage()+ "\n");}
  }



SQL Reports [21] 

  public void findParts(String suppnum, String color) {
    textArea1.appendText("Report: " + color + 
                         " parts for supplier # " + suppnum+"\n");
    String query = "select S.S_NO, S.SNAME, P.PNAME, P.COLOR " +
                   "from S, SP, P " + 
                   "where S.S_NO = SP.S_NO and P.P_NO = SP.P_NO";
    query = query + " and P.COLOR = " + "\'"+color+"\'";
    if (!suppnum.equals(""))
      query = query + " and S.S_NO = " + "\'"+suppnum+"\'";
    try {
      Connection con = DriverManager.getConnection(url,user,dbpass);
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(query);
      ResultSetMetaData md = rs.getMetaData();
      textArea1.appendText("\n"+SQLutil.getRow(rs,md,true)+"\n");
      while (rs.next())
        textArea1.appendText(SQLutil.getRow(rs,md,false)+"\n");

      textArea1.appendText("\n");
      rs.close();
      stmt.close();
      con.close();
    } catch (Exception e) {textArea1.appendText(e.getMessage()+ "\n");}
  } 
}



V. TEST [22]

1. Internal

a. Java OO Sets 

public class suppDB {
  public static Set suppliers = new Set();
  public static Set parts = new Set();
  public static Set amounts = new Set();
  public suppDB() {
    new Supplier("S5","Adams",30,"Athens");
    new Supplier("S1","Smith",20,"London");
    new Supplier("S2","Jones",10,"Paris");
    new Supplier("S3","Blake",30,"Paris");
    new Supplier("S4","Clark",20,"London");

    new Part("P1","Nut","Red",12,"London");
    new Part("P2","Bolt","Green",17,"Paris");
    new Part("P3","Screw","Blue",17,"Rome");
    new Part("P4","Screw","Red",14,"London");
    new Part("P5","Cam","Blue",12,"Paris");
    new Part("P6","Cog","Red",19,"London");

    new Amount((Supplier)suppliers.get("S1"),
                                     (Part)parts.get("P6"),100);
    new Amount((Supplier)suppliers.get("S1"),
                                     (Part)parts.get("P1"),300);
    new Amount((Supplier)suppliers.get("S1"),
                                     (Part)parts.get("P2"),200);
    new Amount((Supplier)suppliers.get("S1"),
                                     (Part)parts.get("P3"),400);
    new Amount((Supplier)suppliers.get("S1"),
                                     (Part)parts.get("P4"),200);
    new Amount((Supplier)suppliers.get("S1"),
                                     (Part)parts.get("P5"),100);
    new Amount((Supplier)suppliers.get("S2"),
                                     (Part)parts.get("P1"),300);
    new Amount((Supplier)suppliers.get("S2"),
                                     (Part)parts.get("P2"),400);
    new Amount((Supplier)suppliers.get("S3"),
                                     (Part)parts.get("P2"),300);
    new Amount((Supplier)suppliers.get("S4"),
                                     (Part)parts.get("P2"),200);
    new Amount((Supplier)suppliers.get("S4"),
                                     (Part)parts.get("P4"),300);
    new Amount((Supplier)suppliers.get("S4"),
                                     (Part)parts.get("P5"),400);
  }
}



b. Report Results [23] 

Report: Red parts for supplier # S1
S1 Smith Nut Red
S1 Smith Screw Red
S1 Smith Cog Red

2. External

a. SQL Inserts 

insert into S values('S1','Smith',20,'London')
insert into S values('S2','Jones',10,'Paris')
insert into S values('S3','Blake',30,'Paris')
insert into S values('S4','Clark',20,'London')
insert into S values('S5','Adams',30,'Athens')
insert into P values('P1','Nut','Red',12,'London')
insert into P values('P2','Bolt','Green',17,'Paris')
insert into P values('P3','Screw','Blue',17,'Rome')
insert into P values('P4','Screw','Red',14,'London')
insert into P values('P5','Cam','Blue',12,'Paris')
insert into P values('P6','Cog','Red',19,'London')
insert into SP values('S1','P1',300)
insert into SP values('S1','P2',200)
insert into SP values('S1','P3',400)
insert into SP values('S1','P4',200)
insert into SP values('S1','P5',100)
insert into SP values('S1','P6',100)
insert into SP values('S2','P1',300)
insert into SP values('S2','P2',400)
insert into SP values('S3','P2',200)
insert into SP values('S4','P2',200)
insert into SP values('S4','P4',300)
insert into SP values('S4','P5',400)

b. Query Results 

Report: Red parts for supplier # S1
S_NO      SNAME     PNAME     COLOR
--------- --------- --------- ---------
S1        Smith     Nut       Red
S1        Smith     Screw     Red
S1        Smith     Cog       Red

next up previous contents
Next: CASE STUDY 2: EMPLOYEES Up: Contents Previous: INTRODUCTION   Contents
Ted Billard 2001-10-29