 |
» |
|
|
|
/* The following commands create the Purchasing Department's DBEFileSet with two DBEFiles.*/
CREATE DBEFILESET PurchFS;
CREATE DBEFILE PurchDataF1
WITH PAGES = 50, NAME = 'PurchDF1',
TYPE = TABLE;
CREATE DBEFILE PurchIndxF1
WITH PAGES = 50, NAME = 'PurchXF1',
TYPE = INDEX;
ADD DBEFILE PurchDataF1
TO DBEFILESET PurchFS;
ADD DBEFILE PurchIndxF1
TO DBEFILESET PurchFS;
/* The following commands create the Warehouse Department's DBEFileSet with two DBEFiles. */
CREATE DBEFILESET WarehFS;
CREATE DBEFILE WarehDataF1
WITH PAGES = 50, NAME = 'WarehDF1',
TYPE = TABLE;
CREATE DBEFILE WarehIndxF1
WITH PAGES = 50, NAME = 'WarehXF1',
TYPE = INDEX;
ADD DBEFILE WarehDataF1
TO DBEFILESET WarehFS;
ADD DBEFILE WarehIndxF1
TO DBEFILESET WarehFS;
/* The following commands create the Receiving Department's DBEFileSet with two DBEFiles. */
CREATE DBEFILESET OrderFS;
CREATE DBEFILE OrderDataF1
WITH PAGES = 50, NAME = 'OrderDF1',
TYPE = TABLE;
CREATE DBEFILE OrderIndxF1
WITH PAGES = 50, NAME = 'OrderXF1',
TYPE = INDEX;
ADD DBEFILE OrderDataF1
TO DBEFILESET OrderFS;
ADD DBEFILE OrderIndxF1
TO DBEFILESET OrderFS;
/* The following commands create a DBEFileSet with one DBEFile for
storage of long field data in the PurchDB.Reports table .*/
CREATE DBEFILESET FileFS;
|
CREATE DBEFILE FileData
WITH PAGES=50, NAME='FileData',
TYPE=TABLE;
ADD DBEFILE FileData TO DBEFILESET FileFS;
/* The following commands create the two tables that comprise the ManufDB database. */
CREATE PUBLIC TABLE ManufDB.SupplyBatches
(VendPartNumber CHAR(16) NOT NULL,
BatchStamp DATETIME DEFAULT CURRENT_DATETIME
NOT NULL
PRIMARY KEY,
MinPassRate FLOAT)
IN WarehFS;
CREATE PUBLIC TABLE ManufDB.TestData
(BatchStamp DATETIME NOT NULL
REFERENCES ManufDB.SupplyBatches (BatchStamp),
TestDate DATE,
TestStart TIME,
TestEnd TIME,
LabTime INTERVAL,
PassQty INTEGER,
TestQty INTEGER)
IN WarehFS;
/* The following commands create the seven tables and two views
that comprise the PurchDB database. */
CREATE PUBLIC TABLE PurchDB.Parts
(PartNumber CHAR(16) NOT NULL,
PartName CHAR(30),
SalesPrice DECIMAL(10,2) )
IN WarehFS;
CREATE PUBLIC TABLE PurchDB.Inventory
(PartNumber CHAR(16) NOT NULL,
BinNumber SMALLINT NOT NULL,
QtyOnHand SMALLINT,
LastCountDate CHAR(8),
CountCycle SMALLINT,
AdjustmentQty SMALLINT,
ReorderQty SMALLINT,
ReorderPoint SMALLINT )
IN WarehFS;
CREATE PUBLIC TABLE PurchDB.SupplyPrice
(PartNumber CHAR(16) NOT NULL,
VendorNumber INTEGER NOT NULL,
VendPartNumber CHAR(16) NOT NULL,
UnitPrice DECIMAL(10,2),
DeliveryDays SMALLINT,
DiscountQty SMALLINT)
IN PurchFS;
CREATE PUBLIC TABLE PurchDB.Vendors
(VendorNumber INTEGER NOT NULL,
VendorName CHAR(30) NOT NULL,
ContactName CHAR(30),
PhoneNumber CHAR(15),
VendorStreet CHAR(30) NOT NULL,
VendorCity CHAR(20) NOT NULL,
VendorState CHAR(2) NOT NULL,
VendorZipCode CHAR(10) NOT NULL,
VendorRemarks VARCHAR(60) )
IN PurchFS;
|
CREATE PUBLIC TABLE PurchDB.Orders
(OrderNumber INTEGER NOT NULL,
VendorNumber INTEGER,
OrderDate CHAR(8) )
IN OrderFS;
CREATE PUBLIC TABLE PurchDB.OrderItems
(OrderNumber INTEGER NOT NULL,
ItemNumber INTEGER NOT NULL,
VendPartNumber CHAR(16),
PurchasePrice DECIMAL(10,2) NOT NULL,
OrderQty SMALLINT,
ItemDueDate CHAR(8),
ReceivedQty SMALLINT )
IN OrderFS;
CREATE PUBLIC TABLE PurchDB.Reports
(ReportName CHAR(20) NOT NULL,
ReportOwner CHAR(20) NOT NULL,
FileData LONG VARBINARY(100000)IN FileFS NOT NULL)
IN OrderFS;
CREATE VIEW PurchDB.PartInfo
(PartNumber,
PartName,
VendorNumber,
VendorName,
VendorPartNumber,
ListPrice,
Quantity) AS
SELECT PurchDB.SupplyPrice.PartNumber,
PurchDB.Parts.PartName,
PurchDB.SupplyPrice.VendorNumber,
PurchDB.Vendors.VendorName,
PurchDB.Supplyprice.VendPartNumber,
PurchDB.SupplyPrice.UnitPrice,
PurchDB.SupplyPrice.DiscountQty
FROM PurchDB.Parts,
PurchDB.SupplyPrice,
PurchDB.Vendors
WHERE PurchDB.SupplyPrice.PartNumber =
PurchDB.Parts.PartNumber
AND PurchDB.SupplyPrice.VendorNumber =
PurchDB.Vendors.VendorNumber;
CREATE VIEW PurchDB.VendorStatistics
(VendorNumber,
VendorName,
OrderDate,
OrderQuantity,
TotalPrice) AS
SELECT PurchDB.Vendors.VendorNumber,
PurchDB.Vendors.VendorName,
OrderDate,
OrderQty,
OrderQty * PurchasePrice
FROM PurchDB.Vendors,
PurchDB.Orders,
PurchDB.OrderItems
WHERE PurchDB.Vendors.VendorNumber =
PurchDB.Orders.VendorNumber
AND PurchDB.Orders.OrderNumber =
PurchDB.OrderItems.OrderNumber;
/* The following commands create the Recreation DBEFileSet
with one DBEFile. */
CREATE DBEFILESET RecFS;
CREATE DBEFILE RecDataF1
WITH PAGES = 50, NAME = 'RecDF1',
TYPE = MIXED;
ADD DBEFILE RecDataF1
TO DBEFILESET RecFS;
/* The following commands create three tables
that comprise the RecDB database. */
CREATE PUBLIC TABLE RecDB.Clubs
(ClubName CHAR(15) NOT NULL PRIMARY KEY CONSTRAINT Clubs_PK,
ClubPhone SMALLINT,
Activity CHAR(18) )
IN RecFS;
CREATE PUBLIC TABLE RecDB.Members
(MemberName CHAR(20) NOT NULL,
Club CHAR(15) NOT NULL,
MemberPhone SMALLINT,
PRIMARY KEY (MemberName, Club) CONSTRAINT Members_PK,
FOREIGN KEY (Club)
REFERENCES RecDB.Clubs (ClubName) CONSTRAINT Members_FK)
IN RecFS;
CREATE PUBLIC TABLE RecDB.Events
(SponsorClub CHAR(15),
Event CHAR(30),
Date DATE DEFAULT CURRENT_DATE,
Time TIME,
Coordinator CHAR(20),
FOREIGN KEY (Coordinator, SponsorClub)
REFERENCES RecDB.Members (MemberName, Club) CONSTRAINT Events_FK)
IN RecFS;
|
|
