Database Support

This section of deliverables deals with the Data Definition Language (DDL) statements necessary to support Microsoft or Sybase SQL Server, as well as Oracle. In most cases the differences between SQL Server and Oracle DDL, at least for the data types used in the Remulak project, are restricted to simple formatting issues and the use of DateTime in SQL Server versus Date in Oracle.

Note that the Java code written for the Remulak project and the accessing SQL statements found in the DAO modules will work on both platforms unmodified.

Microsoft SQL Server 7.0

CREATE TABLE T_Address (
       addressLine1 VARCHAR (30) NOT NULL,
       addressLine2 VARCHAR (30) NOT NULL,
       addressLine3 VARCHAR (30) NOT NULL,
       city VARCHAR (30) NOT NULL,
       state CHAR (2) NOT NULL,
       zip VARCHAR (15) NOT NULL,
       addressId int NOT NULL
)
GO

CREATE TABLE T_Customer (
       customerId int NOT NULL,
       customerNumber CHAR (14) NOT NULL,
       firstName CHAR (20) NOT NULL,
       lastName CHAR (20) NOT NULL,
       middleInitial CHAR (10) NOT NULL,
       prefix CHAR (4) NOT NULL,
       suffix CHAR (4) NOT NULL,
       phone1 CHAR (15) NOT NULL,
       phone2 CHAR (15) NOT NULL,
       eMail VARCHAR (30) NOT NULL
)
GO

CREATE TABLE T_Guitar (
       stringCount int NOT NULL,
       right CHAR (5) NOT NULL,
       fretLess CHAR (5) NOT NULL,
       make VARCHAR (20) NOT NULL,
       model VARCHAR (20) NOT NULL,
       age int NOT NULL,
       productId int NOT NULL,
       guitarId int NOT NULL
)
GO

CREATE TABLE T_Invoice (
       invoiceNumber int NOT NULL,
       invoiceId int NOT NULL,
       invoiceAmount DECIMAL(17,2) NOT NULL,
       invoiceDate DATETIME NOT NULL
)
GO

CREATE TABLE T_Role (
       role int NOT NULL,
       roleId int NOT NULL,
       addressId int NOT NULL,
       customerId int NOT NULL
)
GO

CREATE TABLE T_Order (
       orderId int NOT NULL,
       invoiceId int NULL,
       customerId int NOT NULL,
       orderNumber CHAR (10) NOT NULL
)
GO

CREATE TABLE T_OrderHeader (
       orderDateTime DATETIME NOT NULL,
       terms VARCHAR (30) NOT NULL,
       salesPerson VARCHAR (20) NOT NULL,
       orderHeaderId int NOT NULL,
       orderId int NOT NULL
)
GO

CREATE TABLE T_OrderLine (
       quantity int NOT NULL,
       iscount DECIMAL(17, 2) NOT NULL,
       dmextendedUnitPrice DECIMAL(17, 2) NOT NULL,
       orderLineId int NOT NULL,
       productId int NOT NULL,
       orderId int NOT NULL
) ON PRIMARY
GO

CREATE TABLE T_OrderSummary (
       orderSummaryId int NOT NULL,
       discount DECIMAL(17, 2) NOT NULL,
       courtesyMessage VARCHAR (50) NOT NULL,
       orderId int NOT NULL
)
GO
CREATE TABLE T_Payment (
       paymentId int NOT NULL,
       paymentAmount DECIMAL(17, 2) NOT NULL,
       paymentDate DATETIME NOT NULL
)
GO

CREATE TABLE T_Product (
       productId int NOT NULL,
       description VARCHAR (50) NOT NULL,
       price DECIMAL(17, 2) NOT NULL,
       discount DECIMAL(17, 2) NOT NULL,
       quantityOnHand int NOT NULL,
       eoq int NOT NULL,
       refproductId int NULL,
       productType int NULL
)
GO

CREATE TABLE T_SheetMusic (
       sheetId int NOT NULL,
       pages int NOT NULL,
       productId int NOT NULL
)
GO

CREATE TABLE T_Shipment (
       shipmentId int NOT NULL,
       shipmentDateTime DATETIME NOT NULL
)
GO

CREATE TABLE T_Supplies (
       supplyId int NOT NULL,
       independent CHAR (5) NOT NULL,
       productId int NOT NULL
)
GO

CREATE TABLE T_SysCode (
       sysCodeId int NOT NULL,
       name CHAR (12) NOT NULL,
       code CHAR (4) NOT NULL,
       description VARCHAR (20) NOT NULL
)
GO

CREATE VIEW Guitar_V(
    stringCount,
    right,
    fretLess,
    make,
    model,
    age,
    guitarId,
    productId,
    description,
    price,
    discount,
    quantityOnHand,
    eoq,
    productType)

    AS SELECT
    T_Guitar.stringCount,
    T_Guitar.right,
    T_Guitar.fretLess,
    T_Guitar.make,
    T_Guitar.model,
    T_Guitar.age,
    T_Guitar.guitarId,
    T_Product.productId,
    T_Product.description,
    T_Product.price,
    T_Product.discount,
    T_Product.quantityOnHand,
    T_Product.eoq,
    T_Product.productType

    FROM T_Guitar,T_Product
    WHERE T_Guitar.productId=T_Product.productId
GO

CREATE VIEW SheetMusic_V(
    pages,
    sheetId,
    productId,
    description,
    price,
    discount,
    quantityOnHand,
    eoq,
    productType)

    AS SELECT
    T_SheetMusic.pages,
    T_SheetMusic.sheetId,
    T_Product.productId,
    T_Product.description,
    T_Product.price,
    T_Product.discount,
    T_Product.quantityOnHand,
    T_Product.eoq,
    T_Product.productType

    FROM T_SheetMusic,T_Product
    WHERE T_SheetMusic.productId=T_Product.productId
GO

CREATE VIEW Supplies_V(
    independent,
    supplyId,
    productId,
    description,
    price,
    discount,
    quantityOnHand,
    eoq,
    productType)

    AS SELECT

    T_Supplies.independent,
    T_Supplies.supplyId,
    T_Product.productId,
    T_Product.description,
    T_Product.price,
    T_Product.discount,
    T_Product.quantityOnHand,
    T_Product.eoq,
    T_Product.productType

    FROM T_Supplies,T_Product
    WHERE T_Supplies.productId=T_Product.productId
GO

DDL for Oracle (Version 8.1.0)

CREATE TABLE T_Address (
      addressLine1 VARCHAR (30) NOT NULL,
      addressLine2 VARCHAR (30) NOT NULL,
      addressLine3 VARCHAR (30) NOT NULL,
      city VARCHAR (30) NOT NULL,
      state CHAR (2) NOT NULL,
      zip VARCHAR (15) NOT NULL,
      addressId int NOT NULL)

CREATE TABLE    T_Customer  (
       customerId   int  NOT NULL,
       customerNumber   CHAR  (14) NOT NULL,
       firstName   CHAR  (20) NOT NULL,
       lastName   CHAR  (20) NOT NULL,
       middleInitial   CHAR  (10) NOT NULL,
       prefix   CHAR  (4) NOT NULL,
       suffix   CHAR  (4) NOT NULL,
       phone1   CHAR  (15) NOT NULL,
       phone2   CHAR  (15) NOT NULL,
       eMail   VARCHAR  (30) NOT NULL)

CREATE TABLE    T_Guitar  (
       stringCount   int  NOT NULL,
       right   CHAR  (5) NOT NULL,
       fretLess   CHAR  (5) NOT NULL,
       make   VARCHAR  (20) NOT NULL,
       mmodel   VARCHAR  (20) NOT NULL,
       age   int  NOT NULL,
       productId   int  NOT NULL,
       guitarId   int  NOT NULL )

CREATE TABLE    T_Invoice  (
       invoiceNumber   int  NOT NULL,
       invoiceId   int  NOT NULL,
       invoiceAmount   DECIMAL (17, 2) NOT NULL,
       invoiceDate   date NOT NULL)

CREATE TABLE    T_Role  (
       role   int  NOT NULL,
       roleId   int  NOT NULL,
       addressId   int  NOT NULL,
       customerId   int  NOT NULL )
CREATE TABLE    T_Order  (
       orderId   int  NOT NULL,
       invoiceId   int  NULL,
       customerId   int  NOT NULL,
       orderNumber   CHAR  (10) NOT NULL )

CREATE TABLE    T_OrderHeader  (
       orderDateTime   date  NOT NULL,
       terms   VARCHAR  (30) NOT NULL,
       salesPerson   VARCHAR  (20) NOT NULL,
       orderHeaderId   int  NOT NULL,
       orderId   int  NOT NULL )

CREATE TABLE    T_OrderLine  (
       quantity   int  NOT NULL,
       discount   DECIMAL (17, 2) NOT NULL,
       extendedUnitPrice   DECIMAL (17, 2) NOT NULL,
       orderLineId   int  NOT NULL,
       productId   int  NOT NULL,
       orderId   int  NOT NULL )

CREATE TABLE    T_OrderSummary  (
       orderSummaryId   int  NOT NULL,
       discount   DECIMAL (17, 2) NOT NULL,
       courtesyMessage   VARCHAR  (50) NOT NULL,
       orderId   int  NOT NULL )

CREATE TABLE    T_Payment  (
       paymentId   int  NOT NULL,
       paymentAmount   DECIMAL (17, 2) NOT NULL,
       paymentDate   date NOT NULL)

CREATE TABLE    T_Product  (
       productId   int  NOT NULL,
       description   VARCHAR  (50) NOT NULL,
       price   DECIMAL (17, 2) NOT NULL,
       discount   DECIMAL (17, 2) NOT NULL,
       quantityOnHand   int  NOT NULL,
       eoq   int  NOT NULL,
       refproductId   int  NULL,
       productType   int  NULL )

CREATE TABLE    T_SheetMusic  (
       sheetId   int  NOT NULL,
       pages   int  NOT NULL,
       productId   int  NOT NULL )
CREATE TABLE    T_Shipment  (
       shipmentId   int  NOT NULL,
       shipmentDateTime   date  NOT NULL )

CREATE TABLE    T_Supplies  (
       supplyId   int  NOT NULL,
       independent   CHAR  (5) NOT NULL,
       productId   int  NOT NULL )

CREATE TABLE    T_SysCode  (
       sysCodeId   int  NOT NULL,
       name   CHAR  (12) NOT NULL,
       code   CHAR  (4) NOT NULL,
       description   VARCHAR  (20) NOT NULL )

CREATE VIEW Guitar_V(
    stringCount,
    right,
    fretLess,
    make,
    model,
    age,
    guitarId,
    productId,
    description,
    price,
    discount,
    quantityOnHand,
    eoq,
    productType)

    AS SELECT
    T_Guitar.stringCount,
    T_Guitar.right,
    T_Guitar.fretLess,
    T_Guitar.make,
    T_Guitar.model,
    T_Guitar.age,
    T_Guitar.guitarId,
    T_Product.productId,
    T_Product.description,
    T_Product.price,
    T_Product.discount,
    T_Product.quantityOnHand,
    T_Product.eoq,
    T_Product.productType
    FROM T_Guitar,T_Product
    WHERE T_Guitar.productId=T_Product.productId

CREATE VIEW SheetMusic_V(
    pages,
    sheetId,
    productId,
    description,
    price,
    discount,
    quantityOnHand,
    eoq,
    productType)

    AS SELECT
    T_SheetMusic.pages,
    T_SheetMusic.sheetId,
    T_Product.productId,
    T_Product.description,
    T_Product.price,
    T_Product.discount,
    T_Product.quantityOnHand,
    T_Product.eoq,
    T_Product.productType

    FROM T_SheetMusic,T_Product
    WHERE T_SheetMusic.productId=T_Product.productId

CREATE VIEW Supplies_V(
    independent,
    supplyId,
    productId,
    description,
    price,
    discount,
    quantityOnHand,
    eoq,
    productType)

    AS SELECT

    T_Supplies.independent,
    T_Supplies.supplyId,
    T_Product.productId,
    T_Product.description,
    T_Product.price,
    T_Product.discount,
    T_Product.quantityOnHand,
    T_Product.eoq,
    T_Product.productType

    FROM T_Supplies,T_Product
    WHERE T_Supplies.productId=T_Product.productId

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.140.197.136