USE [ACME_POS]
GO
/****** Object: User [acme_dw_user] Script Date: 06/04/2009 19:27:46 ******/
CREATE USER [acme_dw_user] FOR LOGIN [acme_dw_user] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object: Table [dbo].[Regions] Script Date: 06/04/2009 19:27:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Regions](
[REGIONS_KEY] [int] IDENTITY(1,1) NOT NULL,
[REGION_NAME] [varchar](50) NOT NULL,
[CONTINENT] [varchar](50) NULL,
[COUNTRY] [varchar](50) NULL,
CONSTRAINT [PK_Regions] PRIMARY KEY CLUSTERED
(
[REGIONS_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Vendors] Script Date: 06/04/2009 19:27:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Vendors](
[Vendors_key] [int] IDENTITY(1,1) NOT NULL,
[Vendor_name] [varchar](50) NULL,
[Vendor_address1] [varchar](60) NULL,
[Vendor_address2] [varchar](60) NULL,
[Vendor_city] [varchar](50) NULL,
[Vendor_state] [varchar](50) NULL,
[Vendor_zip] [varchar](50) NULL,
CONSTRAINT [PK_Vendors] PRIMARY KEY CLUSTERED
(
[Vendors_key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Stores] Script Date: 06/04/2009 19:27:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Stores](
[STORES_KEY] [int] IDENTITY(1,1) NOT NULL,
[STORE_NAME] [varchar](50) NOT NULL,
[STORE_ADDRESS1] [varchar](60) NULL,
[STORE_ADDRESS2] [varchar](60) NULL,
[STORE_CITY] [varchar](50) NULL,
[STORE_STATE] [varchar](50) NULL,
[STORE_ZIP] [varchar](50) NULL,
[REGION_LOCATED_IN] [int] NULL,
[STORE_NUMBER] [varchar](10) NOT NULL,
CONSTRAINT [PK_Stores] PRIMARY KEY CLUSTERED
(
[STORES_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Items] Script Date: 06/04/2009 19:28:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Items](
[ITEMS_KEY] [int] IDENTITY(1,1) NOT NULL,
[ITEM_NAME] [varchar](50) NULL,
[ITEM_CATEGORY] [varchar](50) NULL,
[ITEM_VENDOR] [int] NULL,
[ITEM_SKU] [varchar](50) NULL,
[ITEM_BRAND] [varchar](50) NULL,
[ITEM_LIST_PRICE] [decimal](6, 2) NULL,
[ITEM_DEPT] [varchar](50) NULL,
CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED
(
[ITEMS_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Registers] Script Date: 06/04/2009 19:28:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Registers](
[REGISTERS_KEY] [int] IDENTITY(1,1) NOT NULL,
[REGISTER_MANUFACTURER] [varchar](60) NULL,
[MODEL] [varchar](50) NULL,
[LOCATION] [int] NULL,
[SERIAL_NO] [varchar](50) NULL,
CONSTRAINT [PK_Registers] PRIMARY KEY CLUSTERED
(
[REGISTERS_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Employees] Script Date: 06/04/2009 19:28:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Employees](
[Employees_key] [int] IDENTITY(1,1) NOT NULL,
[Last_name] [varchar](50) NULL,
[First_name] [varchar](50) NULL,
[Middle_initial] [varchar](50) NULL,
[Emp_Address1] [varchar](60) NULL,
[Emp_Address2] [varchar](60) NULL,
[Emp_City] [varchar](50) NULL,
[Emp_State] [varchar](50) NULL,
[Emp_Zip] [varchar](50) NULL,
[Store_assigned_to] [int] NOT NULL,
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
[Employees_key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[POS_Transactions] Script Date: 06/04/2009 19:28:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[POS_Transactions](
[POS_TRANS_KEY] [int] IDENTITY(1,1) NOT NULL,
[SALES_QUANTITY] [int] NOT NULL,
[SALES_ASSOCIATE] [int] NOT NULL,
[REGISTER] [int] NOT NULL,
[ITEM_SOLD] [int] NOT NULL,
[DATE_SOLD] [datetime] NOT NULL,
[AMOUNT] [decimal](10, 2) NOT NULL,
CONSTRAINT [PK_POS_Transactions] PRIMARY KEY CLUSTERED
(
[POS_TRANS_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: ForeignKey [FK_Stores_Regions] Script Date: 06/04/2009 19:27:50 ******/
ALTER TABLE [dbo].[Stores] WITH CHECK ADD CONSTRAINT [FK_Stores_Regions] FOREIGN KEY([REGION_LOCATED_IN])
REFERENCES [dbo].[Regions] ([REGIONS_KEY])
GO
ALTER TABLE [dbo].[Stores] CHECK CONSTRAINT [FK_Stores_Regions]
GO
/****** Object: ForeignKey [FK_Items_Vendors] Script Date: 06/04/2009 19:28:08 ******/
ALTER TABLE [dbo].[Items] WITH CHECK ADD CONSTRAINT [FK_Items_Vendors] FOREIGN KEY([ITEM_VENDOR])
REFERENCES [dbo].[Vendors] ([Vendors_key])
GO
ALTER TABLE [dbo].[Items] CHECK CONSTRAINT [FK_Items_Vendors]
GO
/****** Object: ForeignKey [FK_Registers_Stores] Script Date: 06/04/2009 19:28:08 ******/
ALTER TABLE [dbo].[Registers] WITH CHECK ADD CONSTRAINT [FK_Registers_Stores] FOREIGN KEY([LOCATION])
REFERENCES [dbo].[Stores] ([STORES_KEY])
GO
ALTER TABLE [dbo].[Registers] CHECK CONSTRAINT [FK_Registers_Stores]
GO
/****** Object: ForeignKey [FK_Employees_Stores] Script Date: 06/04/2009 19:28:08 ******/
ALTER TABLE [dbo].[Employees] WITH CHECK ADD CONSTRAINT [FK_Employees_Stores] FOREIGN KEY([Store_assigned_to])
REFERENCES [dbo].[Stores] ([STORES_KEY])
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_Stores]
GO
/****** Object: ForeignKey [FK_POS_Transactions_Employees] Script Date: 06/04/2009 19:28:08 ******/
ALTER TABLE [dbo].[POS_Transactions] WITH CHECK ADD CONSTRAINT [FK_POS_Transactions_Employees] FOREIGN KEY([SALES_ASSOCIATE])
REFERENCES [dbo].[Employees] ([Employees_key])
GO
ALTER TABLE [dbo].[POS_Transactions] CHECK CONSTRAINT [FK_POS_Transactions_Employees]
GO
/****** Object: ForeignKey [FK_POS_Transactions_Items] Script Date: 06/04/2009 19:28:08 ******/
ALTER TABLE [dbo].[POS_Transactions] WITH CHECK ADD CONSTRAINT [FK_POS_Transactions_Items] FOREIGN KEY([ITEM_SOLD])
REFERENCES [dbo].[Items] ([ITEMS_KEY])
GO
ALTER TABLE [dbo].[POS_Transactions] CHECK CONSTRAINT [FK_POS_Transactions_Items]
GO
/****** Object: ForeignKey [FK_POS_Transactions_Registers] Script Date: 06/04/2009 19:28:08 ******/
ALTER TABLE [dbo].[POS_Transactions] WITH CHECK ADD CONSTRAINT [FK_POS_Transactions_Registers] FOREIGN KEY([REGISTER])
REFERENCES [dbo].[Registers] ([REGISTERS_KEY])
GO
ALTER TABLE [dbo].[POS_Transactions] CHECK CONSTRAINT [FK_POS_Transactions_Registers]
GO
