CREATABS Command File

   /* 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;