All Powder Board and Ski

Oracle 9i WorkbookChapter 1Jerry PostCopyright © 2003

Inventory

InventorySnowboards

Manufacturer Mfg ID Size Description Graphics List PriceQOH

FreestylePipeStandardExtreme

SkisManufacturer Mfg ID Size Description Graphics List Price

QOHSlalomCross country-skateCross country-trad.TelemarkJumpingFreestyleDownhill/race

Boots and Bindings

Boot-Binding CompatibilityManuf. Mfg. ID Board/Ski Binding/Style Color Price Cost

Size QOH34

35

36

…

Sales

SalesCustomer Sale DateFirst Name Last Name SalespersonPhone E-Mail DepartmentAddress Shipping AddressCity, State ZIP City, State ZIPMale/Female Ski/BoardAge/Date of Birth Style Skill LevelItem Description New/Used Size Quantity Price Subtotal

Item TotalTaxTotal DueMethod of Payment

Rentals

RentalsCustomer Rental DateFirst Name Last Name Expected ReturnPhone E-MailAddress Shipping AddressCity, State ZIP City, State ZIPMale/Female Ski/BoardAge/Date of Birth Style Skill LevelItem Description Size Fee Return Date Condition Charges

Item TotalTaxTotal Due Added ChargesMethod of Payment Signature

Project Structure

Project Title: Sales System for Boards and SkisCustomer: All Powder Board and Ski ShopPrimary Contact: KatyGoals:Project Description: Primary Forms:Primary Reports:Lead Developer:Estimated Development Time:Estimated Development Cost:Date Prepared:

Project Plan

Define the project and obtain approval.1. Analyze the user needs and identify all forms and reports.2. System Design

a. Determine the tables and relationships needed.b. Create the tables and load basic data.c. Create queries needed for forms and reports.d. Build forms and reports.e. Create transaction elements.f. Define security and access controls.

3. Additional Featuresa. Create data warehouse to analyze data as needed.b. Handle distributed database elements as needed.

4. System Implementationa. Convert and load data.b. Train users.c. Load testing.

5. System review

Feasibility

Assumptions      

Annual discount rate 0.03    

Project life/years 5    

       

Costs   Present Value Subtotal

One-time      

DBMS Software      

Hardware      

Development      

Data entry      

Training      

       

Feasibility-2Ongoing      

Personnel      

Upgrades/annual      

Supplies      

Support      

Maintenance      

       

Benefits      

Cost Savings      

Better inventory control      

Fewer clerks      

       

Strategic      

Increased sales      

Other?      

Net Present Value      

DBMS Update

Oracle academic initiative http://oai.oracle.com

Oracle technology network http://otn.oracle.com

Oracle documentation—requires OTN http://otn.oracle.com/documentation/content.html

Enterprise Manager

Practice: Create a table

Table Constraints and SQL

CREATE TABLE "POWDER"."CUSTOMER" ( "CUSTOMERID" NUMBER(10) NOT NULL, "LASTNAME" VARCHAR2(25), "FIRSTNAME" VARCHAR2(25), "PHONE" VARCHAR2(20), "ADDRESS" VARCHAR2(50), "CITY" VARCHAR2(35), "STATE" VARCHAR2(20), "ZIPCODE" VARCHAR2(20), CONSTRAINT "PK_CUSTOMER" PRIMARY KEY("CUSTOMERID"))

Practice: Add Data

Practice Design a Simple Form

Tools: Data block wizardLayout wizard

Save the form on the server and Program/Compile module

Practice: Create a Simple Form

Warning: On the server,You must either run full 9iASOr manually:Start OC4J Instance