INTRODUCTION [1]

I. REQUIREMENTS/SPECIFICATION

A. Narrative: Description of the problem and features of the system

B. Inputs: GUI input fields

C. Outputs: GUI output fields and reports

II. ANALYSIS

A. Modeling

1. Entity/Relationship (E/R) Diagram

2. Functional Dependency (FD) List

3. FD Diagram

4. OO Set Diagram

B. Prototype: GUI with labels, fields, buttons, text

C. User's Manual

III. DESIGN

A. Preliminary Design

1. Data Design

a. Internal: OO Set Definitions in Java

b. External: Relation Schema

2. Architecture Design

a. Modular Structure

b. Control Relationships

3. User Interface Design

B. Detail Design

1. Detail Data Design

a. Internal: Refined Data Structures

b. External: SQL Data Dictionary

2. Procedural Design

a. Internal: Pseudocode

b. External: SQL Queries

IV. IMPLEMENTATION

A. Internal: Java OO Set Prototype

B. External: JDBC SQL

V. TEST

A. Data Sets

1. Internal

a. Java OO Sets

b. Report Results

2. External

a. SQL Inserts

b. Query Results

B. Unit Test (White Box)

C. Integration Test (Black Box)

INTRODUCTION: NORMAL FORMS [2]

Normal Forms: find database schema with `good' properties

BAD(S#,NAME,CITY,P#,XNAME,YCITY,QTY)

S#	NAME	CITY	P#	XNAME	YCITY	QTY
S1	smith	london	P1	nut	london	300
S1	smith	london	P2	bolt	paris	200
S1	smith	london	P3	screw	rome	400

• redundancy - CITY is determined by S# so appears many times

• update anomalies - CITY changed one place but not others
• insert anomalies - cannot record supplier unless shipments occur
• deletion anomalies - delete a shipment, may lose supplier
• S# and P# determine QTY
• decompose into three relations:

S(S#,NAME,CITY) Key: S#

P(P#,XNAME,YCITY) Key: P#

SP(S#,P#,QTY) Key: S#P#

• make small subschemas to eliminate redundancy
• but not too small so functional dependencies are preserved
• but not too small so that joins can put back together
• each relation has only its `key' dependency
• Third Normal Form (3NF) can always be done
• Boyce-Codd Normal Form (BCNF) is stronger (less redundancy, maybe more relations)

NewpageINTRODUCTION: KEYS

key: one or more attributes that uniquely identifies record

A

B

C

$\longrightarrow$

E

F

G

H

composite key: two or more attributes (ABC)

superkey: contains a key (ABCE)

subkey: part of a key (AC)

prime attribute: belongs to a key (A)

There may be several different candidate keys

primary key: designated from the set of candidate keys

foreign key: points to another relation (where it is prime)

INTRODUCTION: FUNCTIONAL DEPENDENCIES [3]

$AC\rightarrow B$

$AC$ determines $B$

``Any rows that agree on $AC$, will also agree on $B$''

Usually $AC$ is unique in the relation.

``You give me $AC$ and I can tell you $B$.

$S\char93 \rightarrow SNAME, STATUS, CITY$

$S\char93$ determines $SNAME$.

You give me S3, and I can tell you its Blake.

$P\char93 \rightarrow PNAME, COLOR, WEIGHT, CITY$

You give me P2, and I can tell you the COLOR is Red.

$S\char93 P\char93 \rightarrow QTY$

You give me S2 and P1 and I can tell you the QTY is 300.

INTRODUCTION: DECOMPOSITION INTO 3NF

Make three relations out of these FD's (Third Normal Form)

S(S#,SNAME,STATUS,CITY)

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

SP(S#,P#,QTY)

SP is an intersection relation

many-to-many relationship between S and P