ALLBASE/SQL Reference Manual (36216-90216)

634 AppendixC
Sample DBEnvironment
CREATABS Command File
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;