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.
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
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
3.145.71.115