/****** SECTION ONE: CREATE THE STRAWBERRY DATABASE ******/
/****** Object: Database Strawberry Script Date: 11/9/00 ******/
USE master
GO
IF EXISTS (SELECT name FROM master.dbo.sysdatabases 
WHERE name = N'Strawberry')
 DROP DATABASE [Strawberry]
GO
CREATE DATABASE [Strawberry]  ON (NAME = N'Strawberry_Data', 
-- modify the following partition and path statements to fit your environment
FILENAME = N'd:\sql2000data\Strawberry.mdf' , SIZE = 50, 
  MAXSIZE = 200, FILEGROWTH = 10) 
LOG ON (NAME = N'Strawberry_log', 
-- modify the following partition and path statements to fit your environment
  FILENAME = N'd:\sql2000data\Strawberry.ldf' , 
  SIZE = 10, MAXSIZE = 20, FILEGROWTH = 5)
 COLLATE SQL_Latin1_General_CP1_CI_AS
GO

/****** SECTION TWO: CONFIGURE THE STRAWBERRY DATABASE ******/
exec sp_dboption N'Strawberry', N'autoclose', N'false'
GO
exec sp_dboption N'Strawberry', N'bulkcopy', N'false'
GO
exec sp_dboption N'Strawberry', N'trunc. log', N'false'
GO
exec sp_dboption N'Strawberry', N'torn page detection', N'true'
GO
exec sp_dboption N'Strawberry', N'read only', N'false'
GO
exec sp_dboption N'Strawberry', N'dbo use', N'false'
GO
exec sp_dboption N'Strawberry', N'single', N'false'
GO
exec sp_dboption N'Strawberry', N'autoshrink', N'false'
GO
exec sp_dboption N'Strawberry', N'ANSI null default', N'false'
GO
exec sp_dboption N'Strawberry', N'recursive triggers', N'false'
GO
exec sp_dboption N'Strawberry', N'ANSI nulls', N'false'
GO
exec sp_dboption N'Strawberry', N'concat null yields null', N'false'
GO
exec sp_dboption N'Strawberry', N'cursor close on commit', N'false'
GO
exec sp_dboption N'Strawberry', N'default to local cursor', N'false'
GO
exec sp_dboption N'Strawberry', N'quoted identifier', N'false'
GO
exec sp_dboption N'Strawberry', N'ANSI warnings', N'false'
GO
exec sp_dboption N'Strawberry', N'auto create statistics', N'true'
GO
exec sp_dboption N'Strawberry', N'auto update statistics', N'true'
GO

/****** SECTION THREE: CREATE TABLES FOR THE STRAWBERRY DATABASE ******/
/****** Object:  Table [dbo].[tblEvent]    Script Date: 11/9/00 ******/
CREATE TABLE [dbo].[tblEvent] (
 [EventID] [int] IDENTITY (1000, 1) NOT NULL ,
 [EventName] [varchar] (40)  NOT NULL ,
 [Location] [varchar] (60)  NULL ,
 [Fees] [money] NULL ,
 [StartDate] [datetime] NULL ,
 [EndDate] [datetime] NULL ,
 [Hours] [varchar] (200)  NULL ,
 [EventNotes] [varchar] (200)  NULL 
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[tblInventory]    Script Date: 11/9/00 ******/
CREATE TABLE [dbo].[tblInventory] (
 [InventoryID] [int] IDENTITY (1000, 1) NOT NULL ,
 [VendorID] [int] NULL ,
 [InventoryDscr] [varchar] (40)  NOT NULL ,
 [QuantityOnHand] [numeric](6, 2) NULL ,
 [UnitOfMeasure] [varchar] (10)  NULL ,
 [ReorderPoint] [numeric](6, 2) NULL ,
 [InventoryNotes] [varchar] (2000)  NULL 
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[tblPerson]    Script Date: 11/9/00 ******/
CREATE TABLE [dbo].[tblPerson] (
 [PersonID] [int] IDENTITY (1000, 1) NOT NULL ,
 [LastName] [varchar] (20)  NOT NULL ,
 [FirstName] [varchar] (20)  NOT NULL ,
 [MailingAddr] [varchar] (40)  NULL ,
 [City] [varchar] (20)  NULL ,
 [State] [char] (2)  NULL ,
 [Zip] [char] (10)  NULL ,
 [PersonType] [char] (1)  NULL 
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[tblProduct]    Script Date: 11/9/00 ******/
CREATE TABLE [dbo].[tblProduct] (
 [ProductCode] [int] IDENTITY (1000, 1) NOT NULL ,
 [ProductDscr] [varchar] (40)  NOT NULL ,
 [ProductCost] [money] NULL ,
 [ProductNotes] [varchar] (200)  NULL 
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[tblVendor]    Script Date: 11/9/00  ******/
CREATE TABLE [dbo].[tblVendor] (
 [VendorID] [int] IDENTITY (1000, 1) NOT NULL ,
 [VendorName] [varchar] (40)  NOT NULL ,
 [Specialty] [varchar] (60)  NULL 
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[tblCommDevice]    Script Date: 11/9/00 ******/
CREATE TABLE [dbo].[tblCommDevice] (
 [CommDevID] [int] IDENTITY (1000, 1) NOT NULL ,
 [PersonID] [int] NOT NULL ,
 [CommType] [varchar] (20)  NULL ,
 [CommValue] [varchar] (60)  NOT NULL 
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[tblContact]    Script Date: 11/9/00 ******/
CREATE TABLE [dbo].[tblContact] (
 [PersonID] [int] NOT NULL ,
 [ContactTitle] [varchar] (20)  NOT NULL ,
 [OfficeLocation] [varchar] (60)  NULL ,
 [OfficeHours] [varchar] (60)  NULL 
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[tblEmployee]    Script Date: 11/9/00 ******/
CREATE TABLE [dbo].[tblEmployee] (
 [PersonID] [int] NOT NULL ,
 [JobTitle] [varchar] (20)  NOT NULL ,
 [SSN] [char] (12)  NULL ,
 [I9onFile] [char] (3)  NULL ,
 [W4onFile] [char] (3)  NULL ,
 [PayAmount] [money] NULL ,
 [PayPeriod] [varchar] (10)  NULL 
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[tblManufacturing]  Script Date: 11/9/00 ******/
CREATE TABLE [dbo].[tblManufacturing] (
 [ManufID] [int] IDENTITY (1000, 1) NOT NULL ,
 [InventoryID] [int] NOT NULL ,
 [ProductCode] [int] NOT NULL ,
 [AmountUsed] [numeric](6, 2) NULL ,
 [UnitUsed] [varchar] (10)  NULL 
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[tblSale]    Script Date: 11/9/00 ******/
CREATE TABLE [dbo].[tblSale] (
 [SaleNo] [int] IDENTITY (10000, 1) NOT NULL ,
 [EventID] [int] NOT NULL ,
 [SaleDate] [datetime] NOT NULL ,
 [SubTotal] [money] NULL ,
 [SalesTax] [money] NULL ,
 [SaleTotal] [money] NULL 
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[tblSalesPerson]    Script Date: 11/9/00 ******/
CREATE TABLE [dbo].[tblSalesPerson] (
 [PersonID] [int] NOT NULL ,
 [VendorID] [int] NOT NULL ,
 [OfficeLocation] [varchar] (60)  NULL ,
 [OfficeHours] [varchar] (60)  NULL 
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[tblSupply]    Script Date: 11/9/00 ******/
CREATE TABLE [dbo].[tblSupply] (
 [SupplyID] [int] IDENTITY (1000, 1) NOT NULL ,
 [VendorID] [int] NOT NULL ,
 [InventoryID] [int] NULL ,
 [SupplyDscr] [varchar] (40)  NULL ,
 [WholesalePrice] [money] NULL ,
 [WholesaleUnits] [varchar] (10)  NULL ,
 [DatePriced] [datetime] NULL ,
 [SupplyNotes] [varchar] (2000)  NULL 
) ON [PRIMARY]
GO
/**** Object:  Table [dbo].[tblEventContact]    Script Date: 11/9/00 ****/
CREATE TABLE [dbo].[tblEventContact] (
 [EventContactID] [int] IDENTITY (1000, 1) NOT NULL ,
 [EventID] [int] NOT NULL ,
 [PersonID] [int] NOT NULL ,
 [DateOfContact] [datetime] NULL ,
 [ContactNotes] [varchar] (2000)  NULL 
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[tblSaleItem]    Script Date: 11/9/00 ******/
CREATE TABLE [dbo].[tblSaleItem] (
 [SaleItemID] [int] IDENTITY (10000, 1) NOT NULL ,
 [SaleNo] [int] NOT NULL ,
 [ProductCode] [int] NOT NULL ,
 [QuantitySold] [int] NULL ,
 [UnitPrice] [money] NULL ,
 [ItemCost] [money] NULL 
) ON [PRIMARY]
GO
/**** Object:  Table [dbo].[tblWorkAssignment]    Script Date: 11/9/00 ****/
CREATE TABLE [dbo].[tblWorkAssignment] (
 [WorkID] [int] IDENTITY (1000, 1) NOT NULL ,
 [WorkAssignmentDscr] [varchar] (40)  NOT NULL ,
 [EventID] [int] NOT NULL ,
 [PersonID] [int] NOT NULL ,
 [ShiftDay] [varchar] (10)  NULL ,
 [ShiftStartTime] [datetime] NULL ,
 [ShiftEndTime] [datetime] NULL ,
 [HoursWorked] [numeric](6, 2) NULL ,
 [SpecialPay] [money] NULL 
) ON [PRIMARY]
GO
/****** End of Create Strawberry Script ******/

/****** SECTION ONE: CREATE PRIMARY KEY CONSTRAINTS ******/
ALTER TABLE [dbo].[tblEvent] WITH NOCHECK ADD 
 CONSTRAINT [tblEvent_PK] PRIMARY KEY  CLUSTERED 
 (
  [EventID]
 )  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[tblInventory] WITH NOCHECK ADD 
 CONSTRAINT [tblInventory_PK] PRIMARY KEY  CLUSTERED 
 (
  [InventoryID]
 )  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[tblPerson] WITH NOCHECK ADD 
 CONSTRAINT [tblPerson_PK] PRIMARY KEY  CLUSTERED 
 (
  [PersonID]
 )  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[tblProduct] WITH NOCHECK ADD 
 CONSTRAINT [tblProduct_PK] PRIMARY KEY  CLUSTERED 
 (
  [ProductCode]
 )  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[tblVendor] WITH NOCHECK ADD 
 CONSTRAINT [tblVendor_PK] PRIMARY KEY  CLUSTERED 
 (
  [VendorID]
 )  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[tblCommDevice] WITH NOCHECK ADD 
 CONSTRAINT [tblCommDevice_PK] PRIMARY KEY  CLUSTERED 
 (
  [CommDevID]
 )  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[tblContact] WITH NOCHECK ADD 
 CONSTRAINT [tblContact_PK] PRIMARY KEY  CLUSTERED 
 (
  [PersonID]
 )  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[tblEmployee] WITH NOCHECK ADD 
 CONSTRAINT [tblEmployee_PK] PRIMARY KEY  CLUSTERED 
 (
  [PersonID]
 )  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[tblManufacturing] WITH NOCHECK ADD 
 CONSTRAINT [tblManufacturing_PK] PRIMARY KEY  CLUSTERED 
 (
  [ManufID]
 )  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[tblSale] WITH NOCHECK ADD 
 CONSTRAINT [tblSale_PK] PRIMARY KEY  CLUSTERED 
 (
  [SaleNo]
 )  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[tblSalesPerson] WITH NOCHECK ADD 
 CONSTRAINT [tblSalesPerson_PK] PRIMARY KEY  CLUSTERED 
 (
  [PersonID]
 )  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[tblSupply] WITH NOCHECK ADD 
 CONSTRAINT [tblSupply_PK] PRIMARY KEY  CLUSTERED 
 (
  [SupplyID]
 )  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[tblEventContact] WITH NOCHECK ADD 
 CONSTRAINT [tblEventContact_PK] PRIMARY KEY  CLUSTERED 
 (
  [EventContactID]
 )  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[tblSaleItem] WITH NOCHECK ADD 
 CONSTRAINT [tblSaleItem_PK] PRIMARY KEY  CLUSTERED 
 (
  [SaleItemID]
 )  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[tblWorkAssignment] WITH NOCHECK ADD 
 CONSTRAINT [tblWorkAssignment_PK] PRIMARY KEY  CLUSTERED 
 (
  [WorkID]
 )  ON [PRIMARY] 
GO

/****** SECTION TWO: CREATE FOREIGN KEY CONSTRAINTS ******/
ALTER TABLE [dbo].[tblCommDevice] ADD 
 CONSTRAINT [tblPerson_tblCommDevice_FK1] FOREIGN KEY 
 (
  [PersonID]
 ) REFERENCES [dbo].[tblPerson] (
  [PersonID]
 ) NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[tblContact] ADD 
 CONSTRAINT [tblPerson_tblContact_FK1] FOREIGN KEY 
 (
  [PersonID]
 ) REFERENCES [dbo].[tblPerson] (
  [PersonID]
 ) NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[tblEmployee] ADD 
 CONSTRAINT [tblPerson_tblEmployee_FK1] FOREIGN KEY 
 (
  [PersonID]
 ) REFERENCES [dbo].[tblPerson] (
  [PersonID]
 ) NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[tblManufacturing] ADD 
 CONSTRAINT [tblInventory_tblManufacturing_FK1] FOREIGN KEY 
 (
  [InventoryID]
 ) REFERENCES [dbo].[tblInventory] (
  [InventoryID]
 ) NOT FOR REPLICATION ,
 CONSTRAINT [tblProduct_tblManufacturing_FK1] FOREIGN KEY 
 (
  [ProductCode]
 ) REFERENCES [dbo].[tblProduct] (
  [ProductCode]
 ) NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[tblSale] ADD 
 CONSTRAINT [tblEvent_tblSale_FK1] FOREIGN KEY 
 (
  [EventID]
 ) REFERENCES [dbo].[tblEvent] (
  [EventID]
 ) NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[tblSalesPerson] ADD 
 CONSTRAINT [tblPerson_tblSalesPerson_FK1] FOREIGN KEY 
 (
  [PersonID]
 ) REFERENCES [dbo].[tblPerson] (
  [PersonID]
 ) NOT FOR REPLICATION ,
 CONSTRAINT [tblVendor_tblSalesPerson_FK1] FOREIGN KEY 
 (
  [VendorID]
 ) REFERENCES [dbo].[tblVendor] (
  [VendorID]
 ) NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[tblSupply] ADD 
 CONSTRAINT [FK_tblSupply_tblInventory] FOREIGN KEY 
 (
  [InventoryID]
 ) REFERENCES [dbo].[tblInventory] (
  [InventoryID]
 ) ON UPDATE CASCADE  NOT FOR REPLICATION ,
 CONSTRAINT [FK_tblSupply_tblVendor] FOREIGN KEY 
 (
  [VendorID]
 ) REFERENCES [dbo].[tblVendor] (
  [VendorID]
 ) ON UPDATE CASCADE  NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[tblEventContact] ADD 
 CONSTRAINT [tblContact_tblEventContact_FK1] FOREIGN KEY 
 (
  [PersonID]
 ) REFERENCES [dbo].[tblContact] (
  [PersonID]
 ) NOT FOR REPLICATION ,
 CONSTRAINT [tblEvent_tblEventContact_FK1] FOREIGN KEY 
 (
  [EventID]
 ) REFERENCES [dbo].[tblEvent] (
  [EventID]
 ) NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[tblSaleItem] ADD 
 CONSTRAINT [tblProduct_tblSaleItem_FK1] FOREIGN KEY 
 (
  [ProductCode]
 ) REFERENCES [dbo].[tblProduct] (
  [ProductCode]
 ) NOT FOR REPLICATION ,
 CONSTRAINT [tblSale_tblSaleItem_FK1] FOREIGN KEY 
 (
  [SaleNo]
 ) REFERENCES [dbo].[tblSale] (
  [SaleNo]
 ) NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[tblWorkAssignment] ADD 
 CONSTRAINT [tblEmployee_tblWorkAssignment_FK1] FOREIGN KEY 
 (
  [PersonID]
 ) REFERENCES [dbo].[tblEmployee] (
  [PersonID]
 ) NOT FOR REPLICATION ,
 CONSTRAINT [tblEvent_tblWorkAssignment_FK1] FOREIGN KEY 
 (
  [EventID]
 ) REFERENCES [dbo].[tblEvent] (
  [EventID]
 ) NOT FOR REPLICATION 
GO

/****** SECTION THREE: CREATE INDEXES ******/
 CREATE  UNIQUE INDEX [idu_tblEmployee_SSNckey] ON [dbo].[tblEmployee]([SSN])
ON [PRIMARY]
GO
CREATE  INDEX [idx_tblPerson_FullName] ON [dbo].[tblPerson]([LastName],
[FirstName]) ON [PRIMARY]
GO
CREATE  INDEX [idx_tblPerson_FirstName] ON [dbo].[tblPerson]([FirstName]) 
ON [PRIMARY]
GO
CREATE  INDEX [idx_tblPerson_Zip] ON [dbo].[tblPerson]([Zip]) 
ON [PRIMARY]
GO
CREATE  INDEX [idx_tblSalesPerson_VendorID] ON
[dbo].[tblSalesPerson]([VendorID]) ON [PRIMARY]
GO
CREATE  INDEX [idx_tblVendor_Name] ON [dbo].[tblVendor]([VendorName]) 
ON [PRIMARY]
GO
CREATE  INDEX [idx_tblSupply_VendorID] ON [dbo].[tblSupply]([VendorID]) 
ON [PRIMARY]
GO
CREATE  INDEX [idx_tblSupply_InventoryID] 
ON [dbo].[tblSupply]([InventoryID]) ON [PRIMARY]
GO
CREATE  INDEX [idx_tblInventory_VendorID] ON [dbo].[tblInventory]([VendorID]) ON [PRIMARY]
GO
CREATE  INDEX [idx_tblInventory_VendorDscr] 
ON [dbo].[tblInventory]([InventoryDscr]) ON [PRIMARY]
GO
CREATE  INDEX [idx_tblCommDevice_PersonID] 
ON [dbo].[tblCommDevice]([PersonID]) ON [PRIMARY]
GO
CREATE  INDEX [idx_tblCommDevice_CommType] 
ON [dbo].[tblCommDevice]([CommType]) ON [PRIMARY]
GO
CREATE  INDEX [idx_tblEventContact_EventID] 
ON [dbo].[tblEventContact]([EventID]) ON [PRIMARY]
GO
CREATE  INDEX [idx_tblEventContact_PersonID] 
ON [dbo].[tblEventContact]([PersonID]) ON [PRIMARY]
GO
CREATE  INDEX [idx_tblEvent_EventName] ON [dbo].[tblEvent]([EventName]) 
ON [PRIMARY]
GO
CREATE  INDEX [idx_tblEvent_StartDate] ON [dbo].[tblEvent]([StartDate]) 
ON [PRIMARY]
GO
CREATE  INDEX [idx_tblSale_EventID] ON [dbo].[tblSale]([EventID]) 
ON [PRIMARY]
GO
CREATE  INDEX [idx_tblSale_SaleDate] ON [dbo].[tblSale]([SaleDate]) 
ON [PRIMARY]
GO
CREATE  INDEX [idx_tblSaleItem_SaleNo] ON [dbo].[tblSaleItem]([SaleNo]) 
ON [PRIMARY]
GO
CREATE  INDEX [idx_tblSaleItem_ProductCode] 
ON [dbo].[tblSaleItem]([ProductCode]) ON [PRIMARY]
GO
CREATE  INDEX [idx_tblEmployee_JobTitle] ON [dbo].[tblEmployee]([JobTitle]) ON [PRIMARY]
GO
CREATE  INDEX [idx_tblWorkAssign_WorkDscr] 
ON [dbo].[tblWorkAssignment]([WorkAssignmentDscr]) ON [PRIMARY]
GO
CREATE  INDEX [idx_tblWorkAssign_EventID] 
ON [dbo].[tblWorkAssignment]([EventID]) ON [PRIMARY]
GO
CREATE  INDEX [idx_tblWorkAssign_PersonID] 
ON [dbo].[tblWorkAssignment]([PersonID]) ON [PRIMARY]
GO
CREATE  INDEX [idx_tblProduct_ProdDscr] ON [dbo].[tblProduct]([ProductDscr]) ON [PRIMARY]
GO
CREATE  INDEX [idx_tblManuf_InvID] 
ON [dbo].[tblManufacturing]([InventoryID]) ON [PRIMARY]
GO
CREATE  INDEX [idx_tblManuf_ProdCode] 
ON [dbo].[tblManufacturing]([ProductCode]) ON [PRIMARY]
GO
/****** End of Modify Strawberry Script ******/
