Chapter 8 - Database Application Design

Case Application: View Ridge Gallery

Application Requirements
- track customers and their purchasing interests
- record customers' art purchases
- record gallery's purchases
- list artists and their works that have appeared in the gallery
- report how fast an artist's works have sold and at what margin
- list current inventory
- list product reports to be used by the gallery's bookkeeper/accountant

Database Design

CUSTOMER

_ID  Name
      Phone
      Address
        Street
        City
        State
        Zip
    WORK_0.N
    ARTIST_0.N

WORK

_ID  WorkID
        ARTIST_0.N
        Title
        Copy
      Description
      Transaction
    _ID  DateAcquired
        Acquisition Price
        PurchaseDate
    CUSTOMER_1.1
      SalesPrice

ARTIST

_ID  Name
      Nationality
      Dates
        Birth
        Death
    WORK_0.N
        CUSTOMER_0.N

Characteristics of Database Applications

Create, Update, Delete, and Display Objects
objects are transformed and stored as sets of relations; need to materialize objects from the relations
Provide Facilities for Controlling Processing
- command driven interface (SQL)
- menus
- GUIs
Protect Security and Integrity

A Continuum of Applications

Unrestricted Use of Command Language on Relations (e.g. SQL)	->	Controlled Access to Relations	->	Uncontrolled Access to Objects	->	Secure and Controlled Access to Object Views
Low	<---	Increasing Degree of Application Support An Application Continuum			--->	High

Materializing Objects (generate an object from underlying relations)
Object Views user views

Form Design (screen display used for data entry and edit)
1. The Form Structure Should Reflect the Object Structure
2. The Semantics of the Data Should be Graphically Evident (spacing between attributes)
3. The Form Structure Should Encourage Appropriate Action
4. Forms in a GUI Environment
  - Drop-down List Boxes
  - Radio (option) buttons
  - Check boxes
5. Cursor Movement and Pervasive Keys (function keys should be consistent in use)
Report Design
1. Report Structure
  structure of a report should reflect the structure of the underlying object
2. Implied Object (collections of objects)
  - Sorted by Object Identifier
  - Sorted by NonIdentifier, Nonobject Attributes
  - Sorted by Attributes Contained in Object Attributes

Application Program Design

The Structure of Application Program Logic (pseudocode)
structure of a program should mirror the structure of the data that it processes

Relationship Constaint Checking
fragment - row that exists inappropriately without a required parent or child
orphan - child row that exists without a mandatory parent

Types of Relationship Constraint for 1-1 and 1-M relationships
- mandatory-to-mandatory (M-M) (ex. HOTEL-BILL / DAILY-CHARGE)
- mandatory-to-optional (M-O) (ex. PROFESSOR / STUDENT)
- optional-to-mandatory (O-M) (ex. CLUB / STUDENT)
- optional-to-optional (O-O) (ex. PROJECT / EMPLOYEE)

Restrictions on Updates to Parent Rows

Proposed Action on Parent
	Insert	Modify(key)	Delete
M-M	Create at least one child	Change matching keys of all children	Delete all children OR reassign all children
M-O	OK	Change matching keys of all children	Delete all children OR Reassign all children
O-M	Insert new child OR Appropriate child exists	Change key of at least one child OR Appropriate child exists	OK
O-O	OK	OK	OK

Restrictions on Updating Child Rows

Proposed Action on Child
	Insert	Modify(key)	Delete
M-M	Parent Exists OR Create parent	Parent with new value exists (or create one) AND Sibling exists	Sibling exists
M-O	Parent Exists OR Create parent	Parent with new value exists OR Create parent	OK
O-M	OK	Sibling exists	Sibling exists
O-O	OK	OK	OK

Other Types of Constraint Checking (uniqueness, null or unknown values
The Use of Application Programs in Backup and Recovery atomicity - logical units of work