FrontPage

Page history last edited by Yamil Karim 2 years, 9 months ago

UNIVERSITY OF CENTRAL FLORIDA

CET 4429 – APPLIED DATABASE II

Dr. Eaglin

Spring 2009

Group 1

Video & Audio Inventory Managment Database

Yamil Karim

Steven Finlay

Josh Walters

Bryan Gatenby

Background

Who are the stakeholders?

The stakeholders of this database management system are the staff at an up-and-coming media store that want to track sales in the store, orders placed with vendors, and manage their inventory to assist them in gaining a competitive advantage over the competition. Knowing what’s in stock and what’s on order is crucial information for the stakeholders to know, so they can better make business decisions to keep the product that the customers want on the shelf.

Why are you building this system?

We want to provide an inventory system that will keep real-time detailed information on the inventory within the company. Additionally, we will be able to maintain information on their vendors and any orders placed with the vendors. This will give management the tools they need to run the business more efficiently, while at the same, time providing the employees the information they need to provide better services to the customer.

Who will use it?

The management and staff of the media store looking for a system to track and order their inventory.

What does it do?

This database management system will allow the insertion of new products, updating of products already on hand, placing of an order, vendor information, track sales information, and the ability to search the database for a certain product and be able to see if it’s stock and or on order.

```
Return to Top
```

Data Dictionary

Sku Table - Stores the Sku of every item in the database, with an identity-specification enabled on Sku Field. Sku is an internal tracking system for the Store. Nothing is deleted from the database instead, a "1" is placed in the DeletedBit field for that specific item to represent it being deleted.

Field Name	Attributes	Data Type	Allow Nulls	Field Description	Data Life Cycle	Restricted Access
Sku	PK, Identity, Unique	Numeric (18,0)	No	Links all items in Database together	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
NumberonHand		Numeric (18,0)	No	Number of items on shelf and in warehouse	Deducted at each Sale and added to each Vendor Order Received	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
NumberonOrder		Numeric (18,0)	No	Number of items ordered with vendor	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
Price		Smallmoney	No	Price being charged for individual item (Seling Price)	Changed at Manager's Discretion	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
DeletedBit		Int	No	Represents an item being deleted with a 1 indicated in the record of an item	Will be reviewed after 1 year and can be modified based on current business needs. I Items that are marked for deletion will be removed after 1 year.	Administrator - Full Access, Employee - Read & Write Access, Manager - Read & Write Access
UnitPrice		Smallmoney	No	Price purchased from vendor	Updated as Vendor changes their prices	Administrator - Full Access, Employee - Read & WriteAccess, Manager - Read & Write Access

Games Table - Stores Video Game information, with an identity-specification enabled on GameID. A Foreign Key of Sku from the Sku Table is in this table to link to the Sku.

Field Name	Attributes	Data Type	Allow Nulls	Field Description	Data Life Cycle	Restricted Access
GameID	PK, Identity, Unique	Numeric (18,0)	No	Identifies the Game Table	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
Platform		NVarchar (50)	No	Video Game Platform (e.g. Xbox 360, PS3, PC, etc...)	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
Rating		Numeric (18,0)	Yes	Rating of Video Game	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
Sku	FK, Unique	Numeric (18,0)	No	FK of Sku table, Every item has a Sku	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
Genre		NVarchar (50)	Yes	Genre of the Game (e.g. RPG, Shooter, etc..)	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
ReleaseDate		DateTime	Yes	Date Video Game was Released	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
GameTitle	Unique	NVarchar (50)	No	Video Game Name	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
ProductionCompany		NVarchar (50)	Yes	Company that made game name	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access

Music Table - Stores Music information, with an identity-specification enabled on AlbumID. A Foreign Key of Sku from the Sku Table is in this table to link to the Sku.

Field Name	Attributes	Data Type	Allow Nulls	Field Description	Data Life Cycle	Restricted Access
AlbumID	PK, Identity, Unique	Numeric (18,0)	No	Identifies the Music Table	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
AlbumName		NVarchar (50)	No	Name of Album	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
TalentName		NVarchar (50)	No	Name of Music Artist or Band (e.g. The Band, Bon Jovi, Flo Rida, etc..)	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
Sku	FK, Unique	Numeric (18,0)	No	FK of Sku table, Every item has a Sku	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
ReleaseDate		DateTime	Yes	Date Music was Released	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
Producer		NVarchar (50)	Yes	Name of person that made the Album Name	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
ProductionCompany		NVarchar (50)	Yes	Company Name of which the producer is an employee (e.g. BMG Music, Nashville Records, Bad Boy Records, etc.,,)	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
Genre		NVarchar (50)	Yes	Type of Music (e.g. Country, R&B, Rap, Rock, etc..)	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
Explicit		NVarchar (50)	Yes	If Album Name has adult language / content	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
Format		NVarchar (50)	No	Media Type of Music (e.g. CD, AudioDVD, etc..)	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
Language		NVarchar (50)	Yes	Language of Music (e.g. English, French, Spanish, etc..)	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access

Movies Table - Stores Movie information, with an identity-specification enabled on MovieID. A Foreign Key of Sku from the Sku Table is in this table to link to the Sku.

Field Name	Attributes	Data Type	Allow Nulls	Field Description	Data Life Cycle	Restricted Access
MovieID	PK, Identity, Unique	Numeric (18,0)	No	Identifies the Movie Table	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
MovieName	Unique	NVarchar (50)	No	Name of Movie	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
Duration		NVarchar (50)	Yes	Length of Movie in minutes (e.g. The Band, Bon Jovi, Flo Rida, etc..)	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
Sku	FK, Unique	Numeric (18,0)	No	FK of Sku table, Every item has a Sku	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
ReleaseDate		DateTime	Yes	Date Movie was Released for purchase	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
AspectRatio		NVarchar (50)	Yes	Screen formatting of Movie (e.g. Wide Screen, Full Screen, etc..)	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
ProductionCompany		NVarchar (50)	Yes	Company Name that made Movie (e.g. Universal, Miramax, Disney, etc.,,)	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
Genre		NVarchar (50)	Yes	Type of Movie (e.g. Comedy, Horror, Drama, etc..)	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
Rating		NVarchar (50)	Yes	Rating of Movie (e.g. NC-17, PG-13, R, PG, etc..)	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
Format		NVarchar (50)	No	Media Type of Movie (e.g. DVD, BluRay etc..)	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
Language		NVarchar (50)	Yes	Language of Movie (e.g. English, French, Spanish, etc..)	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access

Categories Table - Stores Category information, with an identity-specification enabled on CategoryID. A Foreign Key of CategoryID from the CategoryTable is in this table to link to the ParentCateogoryID field. A Foreign Key of CodeID from the CodeTable is in this table to link to the ParentCodeID field. This table will hold all the Game Manufacturer Name's for Drop Down List's in the Game Insertion Form.

Field Name	Attributes	Data Type	Allow Nulls	Field Description	Data Life Cycle	Restricted Access
CategoryID	PK, Identity, Unique	Int	No	Identifies the Category Table	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
CategoryName		NVarchar (50)	No	Name of Video Game Manufacturer	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
CategoryDescription		NVarchar (50)	Yes	Describes CategoryName	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
ParentCategoryID	FK, Unique	Int	Yes	FK of CategoryID	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
ParentCodeID	FK, Unique	Int	Yes	FK of CodeID	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access

Codes Table - Stores Code information, with an identity-specification enabled on CodeID. A Foreign Key of CategoryID from the CategoryTable is in this table to link to the CateogoryID field. This table will hold all the Game System Name's for Drop Down List's in the Game Insertion Form.

Field Name	Attributes	Data Type	Allow Nulls	Field Description	Data Life Cycle	Restricted Access
CodeID	PK, Identity, Unique	Int	No	Identifies the Code Table	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
CategoryID	FK, Unique	Numeric (18,0)	No	FK from Categories Table	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
CodeDescription		NVarchar (50)	Yes	Describes CodeName	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
CodeName		Int	No	Name of Game System(e.g. PS3, Xbox 360, etc...)	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access

Vendors Table - Stores Vendor information, with an identity-specification enabled on VendorID. This table will hold all the Vendor Information for the company.

Field Name	Attributes	Data Type	Allow Nulls	Field Description	Data Life Cycle	Restricted Access
VendorID	PK, Identity, Unique	Int	No	Identifies the Vendor Table	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
VendorName		NVarchar (50)	No	Name of Vendor	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
ContactName		NVarchar (50)	Yes	Name of person at Vendor Company	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
ContactEmail		NVarchar (50)	Yes	Vendor Email (e.g. someone@company.com)	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
StreetNumber		NVarchar (50)	Yes	Street Number of Vendor (e.g. 1123, 4334, etc...)	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
StreetName		NVarchar (50)	Yes	Street Name of Vendor	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
StreetType		NVarchar (50)	Yes	Street Type of Vendor (e.g. Blvd, Rd, St, etc..)	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
City		NVarchar (50)	Yes	City of Vendor (e.g. New York, Orlando, etc..)	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
State		NVarchar (50)	Yes	State of Vendor (e.g. Florida, Georgia, etc..)	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
Zip		NVarchar (50)	Yes	Zip of Vendor (e.g. 34424, 45441, etc...)	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
PhoneNumber		Numeric (18,0)	No	Phone Number of Vendor (e.g. (000) 111-2345, etc..)	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access

VendorOrder Table - Stores Vendor information, with an identity-specification enabled on OrderID. A Foreign Key of VendorID from the Vendor Table is in this table to link to the VendorID field. A Foreign Key of Sku from the Sku Table is in this table to link to the Sku field. The Order being Fulfilled is tracked by the database by placing a "1" in the Fulfilled field for that specific order to represent the order being complete. This table will hold all the VendorOrder(s) that are place by the company.

Field Name	Attributes	Data Type	Allow Nulls	Field Description	Data Life Cycle	Restricted Access
OrderID	PK, Identity, Unique	Int	No	Identifies the VendorOrder Table	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
VendorID	FK, Unique	Int	No	FK from Vendors Table	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
Sku	FK, Unique	Numeric (18, 0)	No	FK from Sku Table	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
OrderQuantity		Int	No	Quantity ordered from Vendor	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
UnitPrice		Smallmoney	No	Unit Price from Vendor	Upated as Vendor Changes Price	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
Fulfilled		Int	No	Order Fulfilled (e.g. 0 or 1)	Updated as Orders are Completed	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access

Sales Table - Stores Sasle(s) information, with an identity-specification enabled on SalesID. A Foreign Key of Sku from the Sku Table is in this table to link to the Sku field. This table will hold all the Sale(s) for the company.

Field Name	Attributes	Data Type	Allow Nulls	Field Description	Data Life Cycle	Restricted Access
SalesID	PK, Identity, Unique	Int	No	Identifies the VendorOrder Table	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
Qty		Numeric (18, 0)	No	Quantity of product purchased	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
Sku	FK, Unique	Numeric (18, 0)	No	FK from Sku Table	Will be reviewed after 1 year and can be modified based on current business needs	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access
Price		Smallmoney	No	Selling Price	Changed at Manager's Discretion	Administrator - Full Access, Employee - Read & Write Acess, Manager - Read & Write Access

```
Return to Top
```

Database Diagram

```
Return to Top
```

Database Scripts

Sku Table Script

/****** Object: Table [dbo].[Sku] Script Date: 04/21/2009 14:08:41 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Sku](

[Sku] [numeric](18, 0) IDENTITY(1,1) NOT NULL,

[NumberonHand] [numeric](18, 0) NOT NULL,

[NumberonOrder] [numeric](18, 0) NOT NULL,

[UnitPrice] [smallmoney] NOT NULL,

[Price] [smallmoney] NOT NULL,

[Deletedbit] [int] NOT NULL,

CONSTRAINT [PK_Sku] PRIMARY KEY CLUSTERED

(

[Sku] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Music Table Script

/****** Object: Table [dbo].[Music] Script Date: 04/21/2009 14:08:41 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Music](

[AlbumID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,

[SKU] [numeric](18, 0) NOT NULL,

[AlbumName] [nvarchar](50) NOT NULL,

[TalentName] [nvarchar](50) NOT NULL,

[ReleaseDate] [datetime] NULL,

[Producer] [nvarchar](50) NULL,

[Genre] [nvarchar](50) NULL,

[Explicit] [nvarchar](50) NULL,

[Language] [nvarchar](50) NULL,

[Format] [nvarchar](50) NOT NULL,

[ProductionCompany] [nvarchar](50) NULL,

CONSTRAINT [PK_Music] PRIMARY KEY CLUSTERED

(

[AlbumID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Movies Table Script

/****** Object: Table [dbo].[Movies] Script Date: 04/21/2009 14:08:41 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Movies](

[MovieID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,

[SKU] [numeric](18, 0) NOT NULL,

[MovieName] [nvarchar](50) NOT NULL,

[Format] [nvarchar](50) NOT NULL,

[Genre] [nvarchar](50) NULL,

[AspectRatio] [nvarchar](50) NULL,

[Rating] [nvarchar](50) NULL,

[ProductionCompany] [nvarchar](50) NULL,

[ReleaseDate] [datetime] NULL,

[Language] [nvarchar](50) NULL,

[Duration] [nvarchar](50) NULL,

CONSTRAINT [PK_Movies] PRIMARY KEY CLUSTERED

(

[MovieID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Games Table Script

/****** Object: Table [dbo].[Games] Script Date: 04/21/2009 14:08:41 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Games](

[GameID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,

[SKU] [numeric](18, 0) NOT NULL,

[GameTitle] [nvarchar](50) NOT NULL,

[Platform] [nvarchar](50) NOT NULL,

[Rating] [nvarchar](50) NULL,

[ReleaseDate] [datetime] NULL,

[ProductionCompany] [nvarchar](50) NULL,

[Genre] [nvarchar](50) NULL,

CONSTRAINT [PK_Games] PRIMARY KEY CLUSTERED

(

[GameID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Vendor Table Script

/****** Object: Table [dbo].[Vendors] Script Date: 04/21/2009 14:08:41 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Vendors](

[VendorID] [int] IDENTITY(1,1) NOT NULL,

[VendorName] [nvarchar](50) NOT NULL,

[ContactName] [nvarchar](50) NULL,

[ContactEmail] [nvarchar](50) NULL,

[PhoneNumber] [numeric](18, 0) NOT NULL,

[StreetNumber] [nvarchar](50) NULL,

[StreetName] [nvarchar](50) NULL,

[StreetType] [nvarchar](50) NULL,

[City] [nvarchar](50) NULL,

[State] [nvarchar](50) NULL,

[ZipCode] [numeric](18, 0) NULL,

CONSTRAINT [PK_Vendors] PRIMARY KEY CLUSTERED

(

[VendorID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Vendor Order Table Script

/****** Object: Table [dbo].[VendorOrder] Script Date: 04/21/2009 14:08:41 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[VendorOrder](

[OrderID] [int] IDENTITY(1,1) NOT NULL,

[VendorID] [int] NOT NULL,

[Sku] [numeric](18, 0) NOT NULL,

[OrderQuantity] [int] NOT NULL,

[UnitPrice] [smallmoney] NOT NULL,

[Fulfilled] [int] NOT NULL,

CONSTRAINT [PK_VendorOrder] PRIMARY KEY CLUSTERED

(

[OrderID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Sales Table Script

/****** Object: Table [dbo].[Sales] Script Date: 04/21/2009 14:08:41 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Sales](

[SalesID] [int] IDENTITY(1,1) NOT NULL,

[Date] [date] NOT NULL,

[Sku] [numeric](18, 0) NOT NULL,

[Price] [smallmoney] NOT NULL,

[Qty] [numeric](18, 0) NOT NULL,

CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED

(

[SalesID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Categories Table Script

/****** Object: Table [dbo].[Categories] Script Date: 04/21/2009 14:08:40 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Categories](

[CategoryID] [int] IDENTITY(1,1) NOT NULL,

[CategoryName] [nvarchar](50) NOT NULL,

[CategoryDescription] [nvarchar](50) NULL,

[ParentCategoryID] [int] NULL,

[ParentCodeID] [int] NULL,

CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED

(

[CategoryID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Codes Table Script

/****** Object: Table [dbo].[Codes] Script Date: 04/21/2009 14:08:40 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Codes](

[CodeID] [int] IDENTITY(1,1) NOT NULL,

[CodeName] [nvarchar](50) NOT NULL,

[CodeDescription] [nvarchar](50) NULL,

[CategoryID] [int] NOT NULL,

CONSTRAINT [PK_Codes] PRIMARY KEY CLUSTERED

(

[CodeID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Foreign Key Relationship Scripts

/****** Object: ForeignKey [FK_Categories_Categories] Script Date: 04/21/2009 14:08:40 ******/

ALTER TABLE [dbo].[Categories] WITH CHECK ADD CONSTRAINT [FK_Categories_Categories] FOREIGN KEY([ParentCategoryID])

REFERENCES [dbo].[Categories] ([CategoryID])

ALTER TABLE [dbo].[Categories] CHECK CONSTRAINT [FK_Categories_Categories]

/****** Object: ForeignKey [FK_Categories_Codes] Script Date: 04/21/2009 14:08:40 ******/

ALTER TABLE [dbo].[Categories] WITH CHECK ADD CONSTRAINT [FK_Categories_Codes] FOREIGN KEY([ParentCodeID])

REFERENCES [dbo].[Codes] ([CodeID])

ALTER TABLE [dbo].[Categories] CHECK CONSTRAINT [FK_Categories_Codes]

/****** Object: ForeignKey [FK_Codes_Categories] Script Date: 04/21/2009 14:08:40 ******/

ALTER TABLE [dbo].[Codes] WITH CHECK ADD CONSTRAINT [FK_Codes_Categories] FOREIGN KEY([CategoryID])

REFERENCES [dbo].[Categories] ([CategoryID])

ALTER TABLE [dbo].[Codes] CHECK CONSTRAINT [FK_Codes_Categories]

/****** Object: ForeignKey [FK_Games_SKU] Script Date: 04/21/2009 14:08:41 ******/

ALTER TABLE [dbo].[Games] WITH CHECK ADD CONSTRAINT [FK_Games_SKU] FOREIGN KEY([SKU])

REFERENCES [dbo].[Sku] ([Sku])

ALTER TABLE [dbo].[Games] CHECK CONSTRAINT [FK_Games_SKU]

/****** Object: ForeignKey [FK_Movies_SKU] Script Date: 04/21/2009 14:08:41 ******/

ALTER TABLE [dbo].[Movies] WITH CHECK ADD CONSTRAINT [FK_Movies_SKU] FOREIGN KEY([SKU])

REFERENCES [dbo].[Sku] ([Sku])

ALTER TABLE [dbo].[Movies] CHECK CONSTRAINT [FK_Movies_SKU]

/****** Object: ForeignKey [FK_Music_SKU] Script Date: 04/21/2009 14:08:41 ******/

ALTER TABLE [dbo].[Music] WITH CHECK ADD CONSTRAINT [FK_Music_SKU] FOREIGN KEY([SKU])

REFERENCES [dbo].[Sku] ([Sku])

ALTER TABLE [dbo].[Music] CHECK CONSTRAINT [FK_Music_SKU]

/****** Object: ForeignKey [FK_Table_1_Sku] Script Date: 04/21/2009 14:08:41 ******/

ALTER TABLE [dbo].[Sales] WITH CHECK ADD CONSTRAINT [FK_Table_1_Sku] FOREIGN KEY([Sku])

REFERENCES [dbo].[Sku] ([Sku])

ALTER TABLE [dbo].[Sales] CHECK CONSTRAINT [FK_Table_1_Sku]

/****** Object: ForeignKey [FK_VendorOrder_VendorOrder] Script Date: 04/21/2009 14:08:41 ******/

ALTER TABLE [dbo].[VendorOrder] WITH CHECK ADD CONSTRAINT [FK_VendorOrder_VendorOrder] FOREIGN KEY([OrderID])

REFERENCES [dbo].[VendorOrder] ([OrderID])

ALTER TABLE [dbo].[VendorOrder] CHECK CONSTRAINT [FK_VendorOrder_VendorOrder]

/****** Object: ForeignKey [FK_VendorOrder_Vendors] Script Date: 04/21/2009 14:08:41 ******/

ALTER TABLE [dbo].[VendorOrder] WITH CHECK ADD CONSTRAINT [FK_VendorOrder_Vendors] FOREIGN KEY([Sku])

REFERENCES [dbo].[Sku] ([Sku])

ALTER TABLE [dbo].[VendorOrder] CHECK CONSTRAINT [FK_VendorOrder_Vendors]

```
Return to Top
```

Documentation of Stored Procedures

Games Table Index Search

/****** Object: StoredProcedure [dbo].[spGameIndexSearch] Script Date: 04/23/2009 09:42:33 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Yamil Karim, Joshua Walters, Steven Finlay, Bryan Gatenby

-- Create date: April 19, 2009

-- Description: Full Text Index Search on Games

-- =============================================

ALTER PROCEDURE [dbo].[spGameIndexSearch]

-- Add the parameters for the stored procedure here

@SearchString nvarchar(50)

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements

SET NOCOUNT ON;

-- Insert statements for procedure here

-- Inner join Games with Sku, then search Game Title

SELECT Games.Sku, Games.GameTitle, Games.[Platform], Games.Rating, Games.Genre,

Games.ProductionCompany,

Games.ReleaseDate, Sku.Price, Sku.NumberonHand, Sku.NumberonOrder

FROM Games inner join Sku on Games.sku = Sku.sku

WHERE CONTAINS ((Games.GameTitle,Games.[Platform],Games.Rating,

Games.Genre,Games.ProductionCompany),@SearchString)

END

Insert Sku and Game

/****** Object: StoredProcedure [dbo].[spInsertSkuandGame] Script Date: 04/23/2009 09:52:27 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Yamil Karim, Joshua Walters, Steven Finlay, Bryan Gatenby

-- Create date: April 15, 2009

-- Description: Creating sku in Sku Table and inserting values into Sku & Game Tables

-- =============================================

ALTER PROCEDURE [dbo].[spInsertSkuandGame]

-- Add the parameters for the stored procedure here

@Platform nvarchar(50),

@GameTitle nvarchar(50),

@Rating nvarchar(50),

@ProductionCompany nvarchar(100),

@ReleaseDate datetime,

@Price smallmoney,

@UnitPrice smallmoney,

@Genre nvarchar(50)

-- Declaring local variables for stored procedure

DECLARE @SkuID numeric

DECLARE @NumberonOrder numeric

DECLARE @NumberonHand numeric

DECLARE @Deletedbit int

DECLARE @PlatformCount smallint

DECLARE @GameTitleCount smallint

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements

SET NOCOUNT ON;

-- Insert statements for procedure here

-- Setting Deletedbit, NumberonOrder, and NumberonHand to Zero for initial sku creation

SET @Deletedbit = 0

SET @NumberonOrder = 0

SET @NumberonHand = 0

-- Selecting Platform Count

SELECT @PlatformCount = Count(*)

FROM dbo.Games

WHERE [Platform] = @Platform

-- Selecting Gametitle Count

SELECT @GameTitleCount = Count(*)

FROM dbo.Games

WHERE GameTitle = @GameTitle

-- Display error if count is greater than 1

IF @PlatformCount >=1 AND @GameTitleCount >=1

BEGIN

PRINT 'Game Already Exists in Database for the Platfrom entered'

RETURN

END

-- Insert Values into Sku Table

INSERT INTO dbo.Sku

(NumberonHand, NumberonOrder, Price, Deletedbit, UnitPrice)

VALUES

(@NumberonHand, @NumberonOrder, @Price, @Deletedbit, @UnitPrice)

-- Set Sku Scope Identitiy to use in insert for Games Table

SET @SkuID = Scope_Identity()

-- Insert Values into Games Table

INSERT INTO dbo.Games

(Sku, GameTitle, [Platform], Rating, ReleaseDate, ProductionCompany, Genre)

VALUES

(@SkuID, @GameTitle, @Platform, @Rating, @ReleaseDate, @ProductionCompany, @Genre)

END

Insert Sku and Movie

/****** Object: StoredProcedure [dbo].[spInsertSkuandMovies] Script Date: 04/23/2009 09:53:03 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Yamil Karim, Joshua Walters, Steven Finlay, Bryan Gatenby

-- Create date: April 15, 2009

-- Description: Creating sku in Sku Table and inserting values into Sku & Movies Tables

-- =============================================

ALTER PROCEDURE [dbo].[spInsertSkuandMovies]

-- Add the parameters for the stored procedure here

@MovieName nvarchar(50),

@ReleaseDate datetime,

@AspectRatio nvarchar(50),

@Genre nvarchar(50),

@Language nvarchar(50),

@Rating nvarchar(50),

@Duration nvarchar(50),

@ProductionCompany nvarchar(50),

@Format nvarchar(50),

@Price smallmoney,

@UnitPrice smallmoney

-- Declaring local variables for stored procedure

DECLARE @SkuID numeric

DECLARE @NumberonOrder numeric

DECLARE @NumberonHand numeric

DECLARE @Deletedbit int

DECLARE @MovieNameCount smallint

DECLARE @AspectRatioCount smallint

DECLARE @FormatCount smallint

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements

SET NOCOUNT ON;

-- Insert statements for procedure here

-- Setting Deletedbit, NumberonOrder, and NumberonHand to Zero for initial sku creation

SET @Deletedbit = 0

SET @NumberonOrder = 0

SET @NumberonHand = 0

-- Selecting MovieName Count

SELECT @MovieNameCount = Count(*)

FROM dbo.Movies

WHERE [MovieName] = @MovieName

-- Selecting AspectRatio Count

SELECT @AspectRatioCount = Count(*)

FROM dbo.Movies

WHERE AspectRatio = @AspectRatio

-- Selecting Format Count

SELECT @FormatCount = Count(*)

FROM dbo.Movies

WHERE Format = @Format

-- Display error if count is greater than 1

IF @MovieNameCount >=1 AND @AspectRatioCount >=1 AND @FormatCount >=1

BEGIN

PRINT 'Movie Already Exists in Database'

RETURN

END

-- Insert Values into Sku Table

INSERT INTO dbo.Sku

(NumberonHand, NumberonOrder, Price, Deletedbit, UnitPrice)

VALUES

(@NumberonHand, @NumberonOrder, @Price, @Deletedbit, @UnitPrice)

-- Set Sku Scope Identitiy to use in insert for Movies Table

SET @SkuID = Scope_Identity()

-- Insert Values into Movies Table

INSERT INTO dbo.Movies

(MovieName, Format, Genre, AspectRatio, Rating, ProductionCompany, ReleaseDate, [Language], Duration, sku)

VALUES

(@MovieName, @Format, @Genre, @AspectRatio, @Rating, @ProductionCompany, @ReleaseDate, @Language, @Duration, @skuID)

END

Insert Sku and Music

/****** Object: StoredProcedure [dbo].[spInsertSkuandMusic] Script Date: 04/23/2009 09:55:05 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Yamil Karim, Joshua Walters, Steven Finlay, Bryan Gatenby

-- Create date: April 15, 2009

-- Description: Creating sku in Sku Table and inserting values into Sku & Music Tables

-- =============================================

ALTER PROCEDURE [dbo].[spInsertSkuandMusic]

-- Add the parameters for the stored procedure here

@AlbumName nvarchar(50),

@TalentName nvarchar(50),

@ReleaseDate datetime,

@Producer nvarchar(50),

@Genre nvarchar(50),

@Explicit nvarchar(50),

@Language nvarchar(50),

@Format nvarchar(50),

@ProductionCompany nvarchar(50),

@Price smallmoney,

@UnitPrice smallmoney

-- Declaring local variables for stored procedure

DECLARE @SkuID numeric

DECLARE @NumberonOrder numeric

DECLARE @NumberonHand numeric

DECLARE @Deletedbit int

DECLARE @AlbumNameCount smallint

DECLARE @TalentNameCount smallint

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements

SET NOCOUNT ON;

-- Insert statements for procedure here

-- Setting Deletedbit, NumberonOrder, and NumberonHand to Zero for initial sku creation

SET @Deletedbit = 0

SET @NumberonOrder = 0

SET @NumberOnHand = 0

-- Selecting AlbumName Count

SELECT @AlbumNameCount = Count(*)

FROM dbo.Music

WHERE AlbumName = @AlbumName

-- Selecting TalentName Count

SELECT @TalentNameCount = Count(*)

FROM dbo.Music

WHERE TalentName = @TalentName

-- Display error if count is greater than 1

IF @AlbumNameCount >=1 AND @TalentNameCount >=1

BEGIN

PRINT 'Music Album and Artist Already Exists in Database'

RETURN

END

-- Insert Values into Sku Table

INSERT INTO dbo.Sku

(NumberonHand, NumberonOrder, Price, Deletedbit, UnitPrice)

VALUES

(@NumberonHand, @NumberonOrder, @Price, @Deletedbit, @UnitPrice)

-- Set Sku Scope Identitiy to use in insert for Music Table

SET @SkuID = Scope_Identity()

-- Insert Values into Music Table

INSERT INTO dbo.Music

(AlbumName, TalentName, ReleaseDate, Producer, Genre, [Explicit], [Language], Format, ProductionCompany, sku)

VALUES

(@AlbumName, @TalentName, @ReleaseDate, @Producer, @Genre, @Explicit, @Language, @Format, @ProductionCompany, @skuID)

END

Insert Sale

/****** Object: StoredProcedure [dbo].[spInsertSale] Script Date: 04/23/2009 09:51:39 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Josh Walters

-- Create date: 18 APR 2009

-- Description: To Insert a Sale

-- =============================================

ALTER PROCEDURE [dbo].[spInsertSale]

-- Add the parameters for the stored procedure here

--Gets Input from User for Qty of item for Sale

@Qty numeric (18,0),

--Gets Input from User for Sku of item for Sale

@Sku numeric (18,0)

--Declares Variables

DECLARE @GetDate datetime

DECLARE @GetPrice smallmoney

DECLARE @Price smallmoney

DECLARE @NewQty numeric

DECLARE @GetOnHandQty numeric

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements

SET NOCOUNT ON;

-- Insert statements for procedure here

-- Selects Price from sku table

SELECT @GetPrice = Price

FROM dbo.Sku

WHERE Sku = @Sku

-- Selects NumberonHand Qty from Sku Table

SELECT @GetOnHandQty = NumberOnHand

FROM dbo.sku

WHERE sku = @sku

-- Deducts from the NumberonHand from the Quantity entered for the Sale

SELECT @NewQty = @GetOnHandQty - @Qty

-- Sets the Date to the @GetDate Variable

SELECT @GetDate = GETDATE()

-- Inserts data into the Sales Table

INSERT INTO dbo.Sales

(Qty, Sku, [Date], Price)

VALUES

(@Qty, @Sku, @GetDate, @GetPrice)

-- Updates the NumberonHand to reflect proper changes form Sale & updates the sku table

UPDATE dbo.sku

SET NumberOnHand = @NewQty

WHERE sku = @Sku

END

Insert New Vendor

/****** Object: StoredProcedure [dbo].[spInsertVendors] Script Date: 04/23/2009 09:57:26 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Yamil Karim, Joshua Walters, Steven Finlay

-- Create date: April 15, 2009

-- Description: Insert Vendors into Vendor Table

-- =============================================

ALTER PROCEDURE [dbo].[spInsertVendors]

-- Add the parameters for the stored procedure here

@VendorName nvarchar(50),

@ContactName nvarchar(50),

@ContactEmail nvarchar(50),

@StreetNumber nvarchar(50),

@StreetName nvarchar(50),

@StreetType nvarchar(50),

@ZipCode numeric,

@City nvarchar(50),

@State nvarchar(50),

@PhoneNumber numeric

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements

SET NOCOUNT ON;

-- Insert statements for procedure here

-- Insert Values into Vendors Table

INSERT INTO dbo.Vendors

(VendorName, ContactName, ContactEmail, StreetNumber, StreetName, StreetType, ZipCode, City, [State], PhoneNumber)

VALUES

(@VendorName, @ContactName, @ContactEmail, @StreetNumber, @StreetName, @StreetType, @ZipCode, @City, @State, @PhoneNumber)

END

Insert New Vendor Order

/****** Object: StoredProcedure [dbo].[spInsertVendorOrder] Script Date: 04/23/2009 09:56:35 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Joshua Walters, Yamil Karim, Steven Finlay

-- Create date: April 19, 2009

-- Description: Inserts Vendor Orders into Vendor Table, Updates Number on Order & Rollsback if Deletedbit is Set

-- =============================================

ALTER PROCEDURE [dbo].[spInsertVendorOrder]

-- Add the parameters for the stored procedure here

@Sku numeric,

@OrderQty int,

@VendorID int

BEGIN

BEGIN TRAN

-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.

SET NOCOUNT ON;

-- Declaring local variables for stored procedure

DECLARE @GetVendorID int

DECLARE @GetUnitPrice smallmoney

DECLARE @Fulfilled int

DECLARE @Getsku numeric

DECLARE @GetDeletedBit int

DECLARE @GetOrderQty numeric

-- Insert statements for procedure here

-- Set Fulfilled to Zero

SET @Fulfilled = 0

-- Select Sku from Sku Table

SELECT @Getsku

FROM dbo.sku

WHERE @sku = sku

-- Get Number on Order

SELECT @GetOrderQty = numberonorder

FROM dbo.sku

WHERE @sku = sku

-- Get DeletedBit from Sku Table

SELECT @GetDeletedBit = DeletedBit

FROM dbo.Sku

WHERE sku = @GetSku

SELECT @GetUnitPrice = UnitPrice

FROM dbo.sku

WHERE @sku = sku

-- If DeletedBit is 1, then Display Error & Rollback

IF @GetDeletedBit = 1

BEGIN

PRINT 'Product is marked for Deletion, Cannot place Order, rollingback'

ROLLBACK

RETURN

END

-- Insert into Vendor Order Table Values

INSERT INTO dbo.VendorOrder

(VendorID, Sku, OrderQuantity, UnitPrice, Fulfilled)

VALUES

(@VendorID, @Sku, @OrderQty, @GetUnitPrice, @Fulfilled)

-- Update Sku Table Number on Order, with New Order Qty

UPDATE dbo.sku

SET numberonorder = @GetOrderQty + @OrderQty

WHERE sku = @sku

COMMIT TRAN

END

Movie Table Index Search

/****** Object: StoredProcedure [dbo].[spMovieIndexSearch] Script Date: 04/23/2009 09:57:44 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Yamil Karim, Joshua Walters, Steven Finlay, Bryan Gatenby

-- Create date: April 13, 2009

-- Description: Full Text Index Search on Movies

-- =============================================

ALTER PROCEDURE [dbo].[spMovieIndexSearch]

-- Add the parameters for the stored procedure here

@SearchString nvarchar(50)

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements

SET NOCOUNT ON;

-- Insert statements for procedure here

-- Inner join Movies with Sku, then search Movie Name, Select Displays Columns

SELECT Movies.Sku, Movies.MovieName, Movies.Format, Movies.Genre,

Movies.AspectRatio, Movies.Rating, Movies.ProductionCompany, Movies.ReleaseDate, Sku.Price, Sku.NumberonHand,

Sku.NumberonOrder

FROM Movies inner join sku on Movies.sku = Sku.sku

WHERE CONTAINS ((Movies.MovieName,Movies.Format, Movies.Genre, Movies.AspectRatio, Movies.Rating,

Movies.ProductionCompany),@SearchString );

END

Music Table Index Search

/****** Object: StoredProcedure [dbo].[spMusicIndexSearch] Script Date: 04/23/2009 09:58:03 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Yamil Karim, Joshua Walters, Steven Finlay, Bryan Gatenby

-- Create date: April 15, 2009

-- Description: Full Text Index Search on Music

-- =============================================

ALTER PROCEDURE [dbo].[spMusicIndexSearch]

-- Add the parameters for the stored procedure here

@SearchString nvarchar(50)

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements

SET NOCOUNT ON;

-- Insert statements for procedure here

-- Inner join Music with Sku, then search AlbumName and TalentName, Select Displays Columns

SELECT Music.SKU, Music.AlbumName, Music.TalentName, Music.Format, Music.Producer, Music.ProductionCompany,

Music.Genre, Music.ReleaseDate, Music.Explicit, Sku.Price ,Sku.NumberOnHand, Sku.NumberOnOrder

FROM Music INNER JOIN Sku ON Music.SKU = Sku.Sku

WHERE CONTAINS ((Music.TalentName, Music.AlbumName, Music.Producer, Music.ProductionCompany, Music.Genre, Music.Format), @SearchString);

END

Update Games Table

/****** Object: StoredProcedure [dbo].[spUpdateGames] Script Date: 04/23/2009 09:59:42 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Yamil Karim, Joshua Walters, Steven Finlay, Bryan Gatenby

-- Create date: April 20, 2009

-- Description: Update Games Table with Gametitle, Platform, Genre, Rating, Production Company, & Release Date

-- =============================================

ALTER PROCEDURE [dbo].[spUpdateGames]

-- Add the parameters for the stored procedure here

@GameTitle nvarchar(50),

@Platform nvarchar(50),

@Genre nvarchar(50),

@Rating nvarchar(50),

@ProductionCompany nvarchar(50),

@ReleaseDate DateTime,

@Sku Numeric(18,0)

-- Declaring local variables for stored procedure

DECLARE @GetGameID numeric (18,0)

BEGIN

-- Select GameID from Games Table inner join

SELECT @GetGameID = Games.GameID

FROM Games INNER JOIN Sku ON Games.SKU = Sku.Sku

WHERE Games.Sku = @Sku

-- Update Games Table from GameID

UPDATE Games

SET GameTitle = @GameTitle, Platform = @Platform, Genre = @Genre, Rating = @Rating,

ProductionCompany = @ProductionCompany, ReleaseDate = @ReleaseDate

WHERE Games.GameID = @GetGameID

End

Update Games Table Deleted Bit

/****** Object: StoredProcedure [dbo].[spUpdateGamesDeletedBit] Script Date: 04/23/2009 10:01:40 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Josh Walters

-- Create date: 20 APR 2009

-- Description: Adds DeletedBit to Game based on Platform & GameTitle

-- =============================================

ALTER PROCEDURE [dbo].[spUpdateGamesDeletedBit]

-- Add the parameters for the stored procedure here

@Platform nvarchar(50),

@GameTitle nvarchar(50)

-- Declaring local variables for stored procedure

DECLARE @DeletedBit numeric (18,0)

DECLARE @GetSku numeric (18,0)

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements

SET NOCOUNT ON;

-- Insert statements for procedure here

-- Selecting Sku from Games Table

SELECT @GetSku = Sku

FROM dbo.Games

WHERE GameTitle = @GameTitle AND [Platform] = @Platform

-- Updating Sku Table with Deletedbit

UPDATE dbo.Sku

SET Deletedbit = 1

WHERE sku = @getsku and DeletedBit = 0

END

Update Movie Table

/****** Object: StoredProcedure [dbo].[spUpdateMovie] Script Date: 04/23/2009 10:00:25 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Yamil Karim, Joshua Walters, Steven Finlay, Bryan Gatenby

-- Create date: April 20, 2009

-- Description: Update Movie Table with MovieName, Format, Genre, AspectRatio, Rating, Production Company & Rating

-- =============================================

ALTER PROCEDURE [dbo].[spUpdateMovie]

-- Stored Procedure Parameters

@MovieName nvarchar(50),

@Format nvarchar(50),

@AspectRatio nvarchar(50),

@Genre nvarchar(50),

@Rating nvarchar(50),

@ProductionCompany nvarchar(50),

@ReleaseDate DateTime,

@Sku Numeric(18,0)

-- Declaring local variables for stored procedure

DECLARE @GetMovieID numeric (18,0)

BEGIN

-- Select MovieID from Movies Table inner join

SELECT @GetMovieID = Movies.MovieID

FROM Movies INNER JOIN Sku ON Movies.SKU = Sku.Sku

WHERE Movies.Sku = @Sku

-- Update Movies Table from MovieID

UPDATE Movies

SET MovieName = @MovieName, Format = @Format, Genre = @Genre, AspectRatio = @AspectRatio,

Rating = @Rating, ProductionCompany = @ProductionCompany, ReleaseDate = @ReleaseDate

WHERE Movies.MovieID = @GetMovieID

End

Update Movie Table Deleted Bit

/****** Object: StoredProcedure [dbo].[spUpdateMoviesDeletedBit] Script Date: 04/23/2009 10:02:05 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Josh Walters

-- Create date: 20 APR 2009

-- Description: Adds DeletedBit to Movie based on Sku & MovieName

-- =============================================

ALTER PROCEDURE [dbo].[spUpdateMoviesDeletedBit]

-- Add the parameters for the stored procedure here

@MovieName nvarchar(50),

@Format nvarchar(50),

@AspectRatio nvarchar(50)

-- Declaring local variables for stored procedure

Declare @DeletedBit numeric (18,0)

Declare @GetSku numeric (18,0)

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements

SET NOCOUNT ON;

-- Insert statements for procedure here

-- Selecting Sku from Movies Table

SELECT @GetSku = Sku

FROM dbo.Movies

WHERE MovieName = @MovieName AND Format = @Format AND AspectRatio = @AspectRatio

-- Updating Sku Table with Deletedbit

UPDATE dbo.Sku

SET Deletedbit = 1

WHERE sku = @getsku and DeletedBit = 0

END

Update Music Table

/****** Object: StoredProcedure [dbo].[spUpdateMusic] Script Date: 04/23/2009 10:01:09 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Yamil Karim, Joshua Walters, Steven Finlay, Bryan Gatenby

-- Create date: April 20, 2009

-- Description: Update Music Table with Album Name, Talent Name, Genre, Explicit, Producer, Format, Production Company & Release Date

-- =============================================

ALTER PROCEDURE [dbo].[spUpdateMusic]

-- Stored Procedure Parameters

@AlbumName nvarchar(50),

@TalentName nvarchar(50),

@Genre nvarchar(50),

@Explicit nvarchar(50),

@Producer nvarchar(50),

@Format nvarchar(50),

@ProductionCompany nvarchar(50),

@ReleaseDate DateTime,

@Sku Numeric(18,0)

-- Declaring local variables for stored procedure

DECLARE @GetAlbumID numeric (18,0)

BEGIN

-- Select AlbumID from Albums Table inner join

SELECT @GetAlbumID = Music.AlbumID

FROM Music INNER JOIN Sku ON Music.SKU = Sku.Sku

WHERE Music.Sku = @Sku

-- Update Music Table from AlbumID

UPDATE Music

SET AlbumName = @AlbumName, TalentName = @TalentName, Genre = @Genre, Explicit = @Explicit,

Producer = @Producer, Format = @Format, ProductionCompany = @ProductionCompany, ReleaseDate = @ReleaseDate

WHERE Music.AlbumID = @GetAlbumID

End

Update Music Table Deleted Bit

/****** Object: StoredProcedure [dbo].[spUpdateMusicDeletedBit] Script Date: 04/23/2009 10:02:25 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Josh Walters

-- Create date: 20 APR 2009

-- Description: Adds DeletedBit to Music Table based on Album Name & Talent Name

-- =============================================

ALTER PROCEDURE [dbo].[spUpdateMusicDeletedBit]

-- Add the parameters for the stored procedure here

@AlbumName nvarchar(50),

@TalentName nvarchar(50)

-- Declaring local variables for stored procedure

Declare @DeletedBit numeric (18,0)

Declare @GetSku numeric (18,0)

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements

SET NOCOUNT ON;

-- Insert statements for procedure here

-- Selecting Sku from Music Table

SELECT @GetSku = Sku

FROM dbo.Music

WHERE AlbumName = @AlbumName AND TalentName = @TalentName

-- Updating Sku Table with Deletedbit

UPDATE dbo.Sku

SET Deletedbit = 1

WHERE sku = @getsku and DeletedBit = 0

END

Update Quantity

/****** Object: StoredProcedure [dbo].[spUpdateQty] Script Date: 04/23/2009 10:05:02 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Yamil Karim, Joshua Walters, Steven Finlay, Bryan Gatenby

-- Create date: April 22, 2009

-- Description: Updates Number on Hand and Number on Order, Rollback errors displayed

-- =============================================

ALTER PROCEDURE [dbo].[spUpdateQty]

-- Add the parameters for the stored procedure here

@GetSku nvarchar(50),

@AddedQty numeric(18,0)

-- Declaring Local Varaibles

DECLARE @NumberonHand numeric (18,0)

DECLARE @NumberonOrder numeric (18,0)

DECLARE @NewonHandQty numeric (18,0)

DECLARE @NewonOrderQty numeric (18,0)

DECLARE @GetDeletedBit int

BEGIN

BEGIN TRAN

-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT @GetDeletedBit = DeletedBit

FROM dbo.Sku

WHERE sku = @GetSku

-- Get Number on Hand from Sku Table

SELECT @NumberOnHand = NumberOnHand

FROM dbo.Sku

WHERE sku = @Getsku

-- Get Number on Order from Sku Table

SELECT @NumberOnOrder = NumberOnOrder

FROM dbo.sku

WHERE sku = @GetSku

-- Get New On Hand and On Order Quantities

SELECT @NewOnHandQty = @NumberOnHand + @AddedQty

SELECT @NewOnOrderQty = @NumberOnOrder - @AddedQty

-- If Inserting More Than On Order, Display Error and Rollback

IF @newonorderqty < 0

BEGIN

PRINT 'Inserting too many products, rollingback'

ROLLBACK

RETURN

END

-- If DeletedBit is 1, then Display Error & Rollback

IF @GetDeletedBit = 1

BEGIN

PRINT 'Product is marked for Deletion, rollingback'

ROLLBACK

RETURN

END

-- Updating Sku Table with New Number on Hand & New Number on Order

UPDATE dbo.Sku

SET NumberOnHand = @NewOnHandQty

WHERE Sku = @GetSku

UPDATE dbo.sku

SET NumberOnOrder = @NewOnOrderQty

WHERE sku = @GetSku

-- Selecting Updated Number On Order

SELECT @NumberOnOrder = NumberOnOrder

FROM dbo.Sku

WHERE sku = @Getsku

-- Setting Fulfilled Vendor Order

IF @numberonOrder = 0

BEGIN

UPDATE dbo.vendororder

SET fulfilled = 1

WHERE sku = @GetSku

END

COMMIT TRAN

END

Get Categories from Categories Table

/****** Object: StoredProcedure [dbo].[spGetCategories] Script Date: 04/23/2009 09:45:47 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Joshua Walters

-- Create date: April 19, 2009

-- Description: Get Categories From Categories Table

-- =============================================

ALTER PROCEDURE [dbo].[spGetCategories]

-- Add the parameters for the stored procedure here

@ParentCategoryID Int

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements

SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT *

FROM dbo.Categories

Where Categories.ParentCategoryID = @ParentCategoryID

END

Get Codes from Codes Table

/****** Object: StoredProcedure [dbo].[spGetCodes] Script Date: 04/23/2009 09:46:03 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Joshua Walters

-- Create date: April 19, 2009

-- Description: Get Codes From Codes Table

-- =============================================

ALTER PROCEDURE [dbo].[spGetCodes]

-- Add the parameters for the stored procedure here

@CategoryID INT

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT CodeName,CategoryID

FROM dbo.Codes

WHERE CategoryID = @CategoryID

END

Get Vendors

/****** Object: StoredProcedure [dbo].[spGetVendors] Script Date: 04/23/2009 09:49:07 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Bryan Gatenby, Yamil Karim, Steven Finlay, Joshua Walters

-- Create date: April 20, 2009

-- Description: Get Vendors from Vendors Table

-- =============================================

ALTER PROCEDURE [dbo].[spGetVendors]

-- Add the parameters for the stored procedure here

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements

SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT VendorID, VendorName, ContactName, ContactEmail, PhoneNumber, StreetNumber,

StreetName, StreetType, City, State, ZipCode

FROM Vendors

End

Get Order Information

/****** Object: StoredProcedure [dbo].[spGetOrders] Script Date: 04/23/2009 09:46:20 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Yamil Karim, Joshua Walters, Steven Finlay, Bryan Gatenby

-- Create date: April 23, 2009

-- Description: Get Order information from Vendor Orders

-- =============================================

ALTER PROCEDURE [dbo].[spGetOrders]

-- Add the parameters for the stored procedure here

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT OrderID, VendorID, Sku, OrderQuantity, UnitPrice, Fulfilled

FROM VendorOrder

END

Get System Make from Codes Table

/****** Object: StoredProcedure [dbo].[spGetSystemMake] Script Date: 04/23/2009 09:48:35 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Bryan Gatenby

-- Create date: April 20, 2009

-- Description: Get Codes for System Make

-- =============================================

ALTER PROCEDURE [dbo].[spGetSystemMake]

@SystemMake nvarchar(50)

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements

SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT Codes.CategoryID

FROM Codes

WHERE (Codes.CodeName = @SystemMake)

END

```
Return to Top
```

Documentation of Coded Fields and Code Plan

Our Coded Front End

Our Front End Allows For:

Full Index Searching of Movies, Games, and Music
Ability to Edit Data of the Database
Ability to Add New Items to the Database
Ability to Place Items on Order to a Vendor
Ability to Mark Placed Orders as Fulfilled and Update Inventory
Ability to Add Vendors to the System

Our Search Form

The Search Form takes the user's input and does a full index search of the Music, Movies, and Games tables. Depending on which radio button is selected we display that specific datagridview.

Search Button's Code

Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click

DataGridMovies.DataSource = SpMovieIndexSearchTableAdapter.GetData("""" & txtbSearch.Text & "*""") 'Calls SpMovieIndexSearch

DataGridGames.DataSource = SpGameIndexSearchTableAdapter.GetData("""" & txtbSearch.Text & "*""") 'Calls SpGameIndexSearch

DataGridMusic.DataSource = SpMusicIndexSearchTableAdapter.GetData("""" & txtbSearch.Text & "*""") 'Calls SpMusicIndexSearch

End Sub

Radio Buttons Code

This Code alters which grid is visible based on the radio buttons at the top of the form.

Private Sub rbMovies_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbMovies.CheckedChanged

If rbMovies.Checked = True Then

DataGridMovies.Visible = True 'Makes DataGridMovies Visible

DataGridGames.Visible = False 'Makes DataGridGames Invisible

DataGridMusic.Visible = False 'Makes DataGridMusic Invisible

End If

If rbGames.Checked = True Then

DataGridMovies.Visible = False 'Makes DataGridMovies Invisible

DataGridGames.Visible = True 'Makes DataGridGames Visible

DataGridMusic.Visible = False 'Makes DataGridMusic Invisible

End If

If rbMusic.Checked = True Then

DataGridMovies.Visible = False 'Makes DataGridMovies Invisible

DataGridGames.Visible = False 'Makes DataGridGames Invisible

DataGridMusic.Visible = True 'Makes DataGridMusic Visible

End If

End Sub

Our Inventory Management Form

The Inventory Management Form is used to edit and add items to the database. The same form is used for both functions and for all three types of items. Below are some screen shots of different displays of the same form.

Edit Button (Calls Edit Version of Inventory Manager)

Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click

Dim i As Integer ' Defines i as a variable to address the row selected

If rbMovies.Checked = True Then 'Checks to see if the Active Search Grid is DataGridMovies

If Not (DataGridMovies.SelectedRows Is Nothing OrElse DataGridMovies.SelectedRows.Count = 0) Then 'Checks to see if a Row is Selected

i = DataGridMovies.CurrentRow.Index 'Sets i to the selected row index

frmInventoryManager.rbGames.Visible = False 'Hides Set of Radio Buttons Used to Change form when being used to Add an Item

frmInventoryManager.rbMovies.Visible = False

frmInventoryManager.rbMusic.Visible = False

frmInventoryManager.btnUpdate.Visible = True 'Makes the Update button Visible

frmInventoryManager.btnSubmit.Visible = False 'Makes the Submit button Invisible

frmInventoryManager.txtSKU.Text = DataGridMovies.Item(0, i).Value ' Initializes the SKU text box based on selected datagridrow

frmInventoryManager.txtTitle.Text = DataGridMovies.Item(1, i).Value ' Initializes the Title text box based on selected datagridrow

frmInventoryManager.cBox1.DataSource = frmInventoryManager.SpGetCodesTableAdapter.GetData(1) 'Populates cbox1 from codes table

frmInventoryManager.cBox1.Text = DataGridMovies.Item(2, i).Value ' Initializes cbox1 based on selected datagridrow

frmInventoryManager.cBox3.DataSource = frmInventoryManager.SpGetCodesTableAdapter.GetData(2) 'Populates cbox3 from codes table

frmInventoryManager.cbox2.DataSource = frmInventoryManager.SpGetCodesTableAdapter.GetData(4) 'Populates cbox2 from codes table

frmInventoryManager.cbox2.Text = DataGridMovies.Item(3, i).Value 'Initializes cbox2 based on datagridrow

frmInventoryManager.cBox4.DataSource = frmInventoryManager.SpGetCodesTableAdapter.GetData(7) 'Populates cbox4 from codes table

frmInventoryManager.cBox4.Text = DataGridMovies.Item(5, i).Value 'Initializes cbox2 based on datagridrow

frmInventoryManager.txt2.Text = DataGridMovies.Item(6, i).Value 'Initializes txt2 based on datagridrow

frmInventoryManager.dtp1.Value = DataGridMovies.Item(7, i).Value 'Initializes dtp1 based on datagridrow

frmInventoryManager.txt3.Text = DataGridMovies.Item(8, i).Value 'Initializes txt3 based on datagridrow

frmInventoryManager.txt4.Text = DataGridMovies.Item(9, i).Value 'Initializes txt4 based on datagridrow

frmInventoryManager.txt5.Text = DataGridMovies.Item(10, i).Value 'Initializes txt5 based on datagridrow

frmInventoryManager.txtTitle.Visible = True 'The following change the visibility of items

frmInventoryManager.lblTitle.Visible = True

frmInventoryManager.cBox3.Visible = True

frmInventoryManager.lblRatio.Visible = True

frmInventoryManager.lblPlatform.Visible = False

frmInventoryManager.lblGameGenre.Visible = False

frmInventoryManager.cBox4.Visible = True

frmInventoryManager.lblRating.Visible = True

frmInventoryManager.lblTitle.Visible = True

frmInventoryManager.lblAlbum.Visible = False

frmInventoryManager.txtArtist.Visible = False

frmInventoryManager.cBox1.Visible = True

frmInventoryManager.lblArtist.Visible = False

frmInventoryManager.lblProducer.Visible = False

frmInventoryManager.txtProducer.Visible = False

frmInventoryManager.lblMusicFormat.Visible = False

frmInventoryManager.cBoxMusicFormat.Visible = False

frmInventoryManager.lblExplicit.Visible = False

frmInventoryManager.lblSystemMake.Visible = False

frmInventoryManager.cBoxSM.Visible = False

Me.Enabled = False 'Disables the Search Form

frmInventoryManager.Show() 'Shows the frmInventoryManager Form

Else

MsgBox("Error, Please Search and Select Item First", vbOKOnly) ' If no Item is selected before Edit is clicked

End If

If rbGames.Checked = True Then 'Checks to see if the Active Search Grid is DataGridGames

If Not (DataGridGames.SelectedRows Is Nothing OrElse DataGridGames.SelectedRows.Count = 0) Then 'Checks to see if a Row is Selected

i = DataGridGames.CurrentRow.Index 'Sets i to the selected row index

frmInventoryManager.rbGames.Visible = False 'Hides Set of Radio Buttons Used to Change form when being used to Add an Item

frmInventoryManager.rbMovies.Visible = False

frmInventoryManager.rbMusic.Visible = False

frmInventoryManager.btnUpdate.Visible = True 'Makes the Update button Visible

frmInventoryManager.btnSubmit.Visible = False 'Makes the Submit button Invisible

frmInventoryManager.txtSKU.Text = DataGridGames.Item(0, i).Value ' Initializes the SKU text box based on selected datagridrow

frmInventoryManager.txtTitle.Text = DataGridGames.Item(1, i).Value ' Initializes the Title text box based on selected datagridrow

frmInventoryManager.cBoxSM.DataSource = frmInventoryManager.SpGetCategoriesTableAdapter.GetData(12) 'Populates cboxSM

frmInventoryManager.cBoxSM.SelectedValue = (frmInventoryManager.QueriesTableAdapter1.spGetSystemMake(DataGridGames.Item(2, i).Value)) 'Initializes cboxSM by calling spGetSystemMake which is needed because System Make is not stored under the Games Table

frmInventoryManager.cbox2.DataSource = frmInventoryManager.SpGetCodesTableAdapter.GetData(frmInventoryManager.cBoxSM.SelectedValue)

'Populates cbox2 based on the selection of cboxSM

frmInventoryManager.cbox2.Text = DataGridGames.Item(2, i).Value 'Initializes cbox2 based on datagridview selection

frmInventoryManager.cBox3.DataSource = frmInventoryManager.SpGetCodesTableAdapter.GetData(5) 'Populates cbox3 from codes table

frmInventoryManager.cBox3.Text = DataGridGames.Item(4, i).Value 'Initializes cbox3 based on selected datagridrow

frmInventoryManager.cBox4.DataSource = frmInventoryManager.SpGetCodesTableAdapter.GetData(8) 'Populates cbox4 from codes table

frmInventoryManager.cBox4.Text = DataGridGames.Item(3, i).Value 'Initializes cbox4 based on datagridview selection

frmInventoryManager.txt2.Text = DataGridGames.Item(5, i).Value 'Initializes txt2 based on datagridview selection

frmInventoryManager.dtp1.Value = DataGridGames.Item(6, i).Value 'Initializes dtp1 based on datagridview selection

frmInventoryManager.txt3.Text = DataGridGames.Item(7, i).Value 'Initializes txt3 based on datagridview selection

frmInventoryManager.txt4.Text = DataGridGames.Item(8, i).Value 'Initializes txt4 based on datagridview selection

frmInventoryManager.txt5.Text = DataGridGames.Item(9, i).Value 'Initializes txt5 based on datagridview selection

frmInventoryManager.lblFormat.Visible = False 'The following change the visibility of items

frmInventoryManager.lblPlatform.Visible = True

frmInventoryManager.lblGameGenre.Visible = True

frmInventoryManager.lblRatio.Visible = False

frmInventoryManager.lblRating.Visible = True

frmInventoryManager.cBox4.Visible = True

frmInventoryManager.lblTitle.Visible = True

frmInventoryManager.lblAlbum.Visible = False

frmInventoryManager.txtArtist.Visible = False

frmInventoryManager.cBox1.Visible = False

frmInventoryManager.lblArtist.Visible = False

frmInventoryManager.lblProducer.Visible = False

frmInventoryManager.txtProducer.Visible = False

frmInventoryManager.lblMusicFormat.Visible = False

frmInventoryManager.cBoxMusicFormat.Visible = False

frmInventoryManager.lblExplicit.Visible = False

frmInventoryManager.lblGenre.Visible = False

frmInventoryManager.lblSystemMake.Visible = True

frmInventoryManager.cBoxSM.Visible = True

Me.Enabled = False 'Disables Search Form

frmInventoryManager.Show() 'Shows Inventory Manager Form

Else

MsgBox("Error, Please Search and Select Item First", vbOKOnly) ' If no Item is selected before Edit is clicked

End If

If rbMusic.Checked = True Then 'Checks to see if the Active Search Grid is DataGridMusic

If Not (DataGridMusic.SelectedRows Is Nothing OrElse DataGridMusic.SelectedRows.Count = 0) Then 'Checks to see if a Row is Selected

i = DataGridMusic.CurrentRow.Index 'Sets i to the selected row index

frmInventoryManager.rbGames.Visible = False 'Hides Set of Radio Buttons Used to Change form when being used to Add an Item

frmInventoryManager.rbMovies.Visible = False

frmInventoryManager.rbMusic.Visible = False

frmInventoryManager.btnUpdate.Visible = True 'Makes the Update button Visible

frmInventoryManager.btnSubmit.Visible = False 'Makes the Submit button Invisible

frmInventoryManager.txtSKU.Text = DataGridMusic.Item(0, i).Value ' Initializes the SKU text box based on selected datagridrow

frmInventoryManager.txtTitle.Text = DataGridMusic.Item(1, i).Value ' Initializes the Title text box based on selected datagridrow

frmInventoryManager.txtArtist.Text = DataGridMusic.Item(2, i).Value ' Initializes the Artist text box based on selected datagridrow

frmInventoryManager.cbox2.DataSource = frmInventoryManager.SpGetCodesTableAdapter.GetData(3) 'Populates cbox2 from codes table

frmInventoryManager.cbox2.Text = DataGridMusic.Item(5, i).Value ' Initializes the cbox2 based on selected datagridrow

frmInventoryManager.cBox3.DataSource = frmInventoryManager.SpGetCodesTableAdapter.GetData(11) 'Populates cbox3 from codes table

frmInventoryManager.cBox3.Text = DataGridMusic.Item(7, i).Value ' Initializes the cbox3 based on selected datagridrow

frmInventoryManager.txtProducer.Text = DataGridMusic.Item(4, i).Value ' Initializes the Producer text box based on selected datagridrow

frmInventoryManager.cBoxMusicFormat.DataSource = frmInventoryManager.SpGetCodesTableAdapter.GetData(1)

'Populates cboxMusicFormat from codes table

frmInventoryManager.cBoxMusicFormat.Text = DataGridMusic.Item(3, i).Value ' Initializes cboxMusicFormat based on selected datagridrow

frmInventoryManager.txt2.Text = DataGridMusic.Item(8, i).Value ' Initializes the txt2 text box based on selected datagridrow

frmInventoryManager.dtp1.Value = DataGridMusic.Item(6, i).Value ' Initializes dtp1 based on selected datagridrow

frmInventoryManager.txt3.Text = DataGridMusic.Item(9, i).Value ' Initializes the txt3 text box based on selected datagridrow

frmInventoryManager.txt4.Text = DataGridMusic.Item(10, i).Value ' Initializes the txt4 text box based on selected datagridrow

frmInventoryManager.txt5.Text = DataGridMusic.Item(11, i).Value ' Initializes the txt5 text box based on selected datagridrow

frmInventoryManager.lblTitle.Visible = False 'The following change the visibility of items

frmInventoryManager.lblAlbum.Visible = True

frmInventoryManager.txtArtist.Visible = True

frmInventoryManager.cBox1.Visible = False

frmInventoryManager.lblArtist.Visible = True

frmInventoryManager.lblPlatform.Visible = False

frmInventoryManager.lblFormat.Visible = False

frmInventoryManager.lblExplicit.Visible = True

frmInventoryManager.lblRating.Visible = False

frmInventoryManager.lblRatio.Visible = False

frmInventoryManager.lblRating.Visible = False

frmInventoryManager.lblProducer.Visible = True

frmInventoryManager.txtProducer.Visible = True

frmInventoryManager.cBox4.Visible = False

frmInventoryManager.lblMusicFormat.Visible = True

frmInventoryManager.cBoxMusicFormat.Visible = True

frmInventoryManager.lblRatio.Visible = False

frmInventoryManager.lblSystemMake.Visible = False

frmInventoryManager.cBoxSM.Visible = False

Me.Enabled = False 'Disables the Search Form

frmInventoryManager.Show() 'Shows the Inventory Management Form

Else

MsgBox("Error, Please Search and Select Item First", vbOKOnly) ' If no Item is selected before Edit is clicked

End If

End Sub

Add Button (Calls the Add Version of Inventory Manager)

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

frmInventoryManager.Show() 'Shows Inventory Manager

frmInventoryManager.rbGames.Visible = True 'These Set the rbButtons at the top of Inventory Manager to Visible

frmInventoryManager.rbMovies.Visible = True

frmInventoryManager.rbMusic.Visible = True

frmInventoryManager.txtSKU.Visible = False 'These set the starting conditions for Visibility for other items on the form

frmInventoryManager.txt4.Visible = False

frmInventoryManager.txt5.Visible = False

frmInventoryManager.lblSKU.Visible = False

frmInventoryManager.lblOnHand.Visible = False

frmInventoryManager.lblOnOrder.Visible = False

frmInventoryManager.btnUpdate.Visible = False

frmInventoryManager.btnSubmit.Visible = True

End Sub

Radio Buttons On Inventory Manager (These Toggle the Add Type)

Private Sub rbMovies_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbMovies.CheckedChanged

If rbMovies.Checked = True Then

Me.txtTitle.Text = "" 'Clears Value of txtTitle

Me.cBox1.DataSource = Me.SpGetCodesTableAdapter.GetData(1) 'These Populate Combo Boxes from Codes

Me.cBox3.DataSource = Me.SpGetCodesTableAdapter.GetData(2)

Me.cbox2.DataSource = Me.SpGetCodesTableAdapter.GetData(4)

Me.cBox4.DataSource = Me.SpGetCodesTableAdapter.GetData(7)

Me.txt2.Text = "" 'Clears Value of txt2

Me.txt3.Text = "" 'Clears Value of txt3

Me.txt4.Text = "" 'Clears Value of txt4

Me.txt5.Text = "" 'Clears Value of txtTitle

Me.txtTitle.Visible = True 'Toggle Visible Items For Movies

Me.lblTitle.Visible = True

Me.cBox3.Visible = True

Me.lblRatio.Visible = True

Me.lblPlatform.Visible = False

Me.lblGameGenre.Visible = False

Me.cBox4.Visible = True

Me.lblRating.Visible = True

Me.lblTitle.Visible = True

Me.lblAlbum.Visible = False

Me.txtArtist.Visible = False

Me.cBox1.Visible = True

Me.lblArtist.Visible = False

Me.lblProducer.Visible = False

Me.txtProducer.Visible = False

Me.lblMusicFormat.Visible = False

Me.cBoxMusicFormat.Visible = False

Me.lblExplicit.Visible = False

Me.lblSystemMake.Visible = False

Me.cBoxSM.Visible = False

frmSearch.Enabled = False 'Disables frmSearch

End If

If rbGames.Checked = True Then

Me.txtTitle.Text = "" 'Clears Value of txtTitle

Me.cBoxSM.DataSource = Me.SpGetCategoriesTableAdapter.GetData(12) 'Populates cBoxSM from Categories

Me.cbox2.DataSource = Me.SpGetCodesTableAdapter.GetData(Me.cBoxSM.SelectedValue) 'Populates from Codes

Me.cBox3.DataSource = Me.SpGetCodesTableAdapter.GetData(5) 'Populates from Codes

Me.cBox4.DataSource = Me.SpGetCodesTableAdapter.GetData(8) 'Populates from Codes

Me.txt2.Text = "" 'Clears Value of txt2

Me.txt3.Text = "" 'Clears Value of txt3

Me.lblFormat.Visible = False 'Toggle Visible Items for Games

Me.lblPlatform.Visible = True

Me.lblGameGenre.Visible = True

Me.lblRatio.Visible = False

Me.lblRating.Visible = True

Me.cBox4.Visible = True

Me.lblTitle.Visible = True

Me.lblAlbum.Visible = False

Me.txtArtist.Visible = False

Me.cBox1.Visible = False

Me.lblArtist.Visible = False

Me.lblProducer.Visible = False

Me.txtProducer.Visible = False

Me.lblMusicFormat.Visible = False

Me.cBoxMusicFormat.Visible = False

Me.lblExplicit.Visible = False

Me.lblGenre.Visible = False

Me.lblSystemMake.Visible = True

Me.cBoxSM.Visible = True

frmSearch.Enabled = False 'Disables frmSearch

End If

If rbMusic.Checked = True Then

Me.txtTitle.Text = "" 'Clears Value of txtTitle

Me.txtArtist.Text = "" 'Clears Value of txtArtist

Me.cbox2.DataSource = Me.SpGetCodesTableAdapter.GetData(3) 'Populates from Codes

Me.cBox3.DataSource = Me.SpGetCodesTableAdapter.GetData(11)

Me.cBoxMusicFormat.DataSource = Me.SpGetCodesTableAdapter.GetData(1)

Me.txtProducer.Text = "" 'Clears Value of txtProducer

Me.txt2.Text = "" 'Clears Value of txt2

Me.txt3.Text = "" 'Clears Value of txt3

Me.lblTitle.Visible = False 'Toggle Visible Items for Music

Me.lblAlbum.Visible = True

Me.txtArtist.Visible = True

Me.cBox1.Visible = False

Me.lblArtist.Visible = True

Me.lblPlatform.Visible = False

Me.lblFormat.Visible = False

Me.lblExplicit.Visible = True

Me.lblRating.Visible = False

Me.lblRatio.Visible = False

Me.lblRating.Visible = False

Me.lblProducer.Visible = True

Me.txtProducer.Visible = True

Me.cBox4.Visible = False

Me.lblMusicFormat.Visible = True

Me.cBoxMusicFormat.Visible = True

Me.lblRatio.Visible = False

Me.lblSystemMake.Visible = False

Me.cBoxSM.Visible = False

frmSearch.Enabled = False

End If

End Sub

Submit Button (Used to Add Item)

Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

If Me.rbMovies.Checked = True Then 'Checks to see if in Movies Mode

If (txtTitle.Text) = "" Then 'The Following Checks for Null Fields

MsgBox("Please Fill All Fields", vbOKOnly) 'Error for Nulls

ElseIf cBox1.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

ElseIf cbox2.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

ElseIf cBox3.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

ElseIf cBox4.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

ElseIf txt2.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

ElseIf txt3.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

Else

'If No Errors, This adds the Item by Calling spInsertSkuandMovies

QueriesTableAdapter1.spInsertSkuandMovies(txtTitle.Text, dtp1.Value, cBox3.Text, cbox2.Text, "English", cBox4.Text, "60 Min", txt2.Text, cBox1.Text, Val(txt3.Text), Val(txt3.Text))

Me.Close() 'Closes Inventory Manager Form

End If

If Me.rbMusic.Checked = True Then 'Checks for Music Mode

If (txtTitle.Text) = "" Then 'These Check for Nulls

MsgBox("Please Fill All Fields", vbOKOnly) 'Error Messages

ElseIf txtArtist.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

ElseIf cbox2.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

ElseIf cBox3.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

ElseIf txtProducer.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

ElseIf cBoxMusicFormat.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

ElseIf txt2.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

Else

'If No Errors, This Enters New Item by Calling spInsertSkuandMusic

QueriesTableAdapter1.spInsertSkuandMusic(txtTitle.Text, txtArtist.Text, dtp1.Value, txtProducer.Text, cbox2.Text, cBox3.Text, "English", cBoxMusicFormat.Text, txt2.Text, Val(txt3.Text), Val(txt3.Text))

Me.Close() 'Closes Inventory Manager

End If

If Me.rbGames.Checked = True Then 'Checks for Games Mode

If (txtTitle.Text) = "" Then 'Checks for Nulls

MsgBox("Please Fill All Fields", vbOKOnly) 'Error Messages

ElseIf cBoxSM.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

ElseIf cbox2.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

ElseIf cBox3.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

ElseIf cBox4.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

ElseIf txt2.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

Else

'If no Errors, This adds Item by Calling spInsertSkuandGame

QueriesTableAdapter1.spInsertSkuandGame(cbox2.Text, txtTitle.Text, cBox4.Text, txt2.Text, dtp1.Value, Val(txt3.Text), Val(txt3.Text), cBox3.Text)

Me.Close() 'Closes Inventory Manager Form

End If

End Sub

Update Button (This Updates the Values of an Item)

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

If frmSearch.rbMovies.Checked = True Then 'Checks for Movie Mode

If (txtTitle.Text) = "" Then 'Checks for Null

MsgBox("Please Fill All Fields", vbOKOnly) 'Error Messages

ElseIf cBox1.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

ElseIf cbox2.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

ElseIf cBox3.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

ElseIf cBox4.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

ElseIf txt2.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

ElseIf txt3.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

ElseIf txt4.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

ElseIf txt5.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

Else

'If no Errors, Edits Item by Calling spUpdateMovie

QueriesTableAdapter1.spUpdateMovie(txtTitle.Text, cBox1.Text, cBox3.Text, cbox2.Text, cBox4.Text, txt2.Text, dtp1.Value, Val(txtSKU.Text))

Me.Close() 'Closes Inventory Manager

End If

If frmSearch.rbMusic.Checked = True Then 'Checks for Music Mode

If (txtTitle.Text) = "" Then 'Checks for Nulls

MsgBox("Please Fill All Fields", vbOKOnly) 'Error Messages

ElseIf txtArtist.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

ElseIf cbox2.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

ElseIf cBox3.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

ElseIf txtProducer.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

ElseIf cBoxMusicFormat.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

ElseIf txt2.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

Else

'If no Errors, This Edits Item by Calling spUpdateMusic

QueriesTableAdapter1.spUpdateMusic(txtTitle.Text, txtArtist.Text, cbox2.Text, cBox3.Text, txtProducer.Text, cBoxMusicFormat.Text, txt2.Text, dtp1.Value, Val(txtSKU.Text))

Me.Close() 'Closes Inventory Manager

End If

If frmSearch.rbGames.Checked = True Then 'Checks For Games Mode

If (txtTitle.Text) = "" Then 'Checks For Nulls

MsgBox("Please Fill All Fields", vbOKOnly) 'Error messages

ElseIf cBoxSM.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

ElseIf cbox2.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

ElseIf cBox3.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

ElseIf cBox4.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

ElseIf txt2.Text = "" Then

MsgBox("Please Fill All Fields", vbOKOnly)

Else

'If No Errors, This Edits Item by Calling spUpdateGame

QueriesTableAdapter1.spUpdateGames(txtTitle.Text, cbox2.Text, cBox3.Text, cBox4.Text, txt2.Text, dtp1.Value, Val(txtSKU.Text))

Me.Close() 'Closes Inventory Manager Form

End If

End Sub

Our Order Form

The order form is used to place an order with a vendor and to mark it as being recieved/update our inventory.

Order Button (Calls Order Form in Place Order Mode)

Private Sub btnOrder_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOrder.Click

Dim j As Integer ' Defines j as an Integer

frmPlaceOrder.cBoxVendors.DataSource = frmPlaceOrder.SpGetVendorsTableAdapter.GetData() 'populates Vendors combobox

If Me.rbGames.Checked = True Then 'Checks for Games Mode

If Not (DataGridGames.SelectedRows Is Nothing OrElse DataGridGames.SelectedRows.Count = 0) Then 'Checks for Selection

j = DataGridGames.CurrentRow.Index 'Initializes j to Selected Row

frmPlaceOrder.txtSKUvo.Text = DataGridGames.Item(0, j).Value 'Initializes txtSku from DataGrid

frmPlaceOrder.txtOrderQuantity.Text = "" 'Clearts txtOrderQuantity

frmPlaceOrder.Show() 'Shows Order Form

Else

'Error Message for No Selection

MsgBox("Error, Please Search and Select Item You Wish To Order First", vbOKOnly)

End If

If Me.rbMovies.Checked = True Then 'Checks for Movie Mode

If Not (DataGridMovies.SelectedRows Is Nothing OrElse DataGridMovies.SelectedRows.Count = 0) Then 'Checks For Selection

j = DataGridMovies.CurrentRow.Index 'Initializes j to Selected Row

frmPlaceOrder.txtSKUvo.Text = DataGridMovies.Item(0, j).Value ' Initializes txtSku from DataGrid

frmPlaceOrder.txtOrderQuantity.Text = "" 'Clears txtOrderQuantity

frmPlaceOrder.Show() 'Shows Order Form

Else

'Error Message For No Selection

MsgBox("Error, Please Search and Select Item You Wish To Order First", vbOKOnly)

End If

If Me.rbMusic.Checked = True Then 'Checks for Music Mode

If Not (DataGridMusic.SelectedRows Is Nothing OrElse DataGridMusic.SelectedRows.Count = 0) Then 'Check For Selection

j = DataGridMusic.CurrentRow.Index 'Initializes j to Selected Row

frmPlaceOrder.txtSKUvo.Text = DataGridMusic.Item(0, j).Value 'Initializes txtSku from DataGrid

frmPlaceOrder.txtOrderQuantity.Text = "" 'Clears txtOrderQuantity

frmPlaceOrder.Show() 'Shows Order Form

Else

MsgBox("Error, Please Search and Select Item You Wish To Order First", vbOKOnly) 'Error Messag For Selection

End If

End Sub

Recieving Button (Calls Order Form in Recieving Mode)

Private Sub btnRecieving_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRecieving.Click

frmPlaceOrder.cBoxVendors.DataSource = frmPlaceOrder.SpGetVendorsTableAdapter.GetData() 'Populates Vendors Combobox

frmPlaceOrder.txtOrderQuantity.Text = "" 'Clears txtOrderQuantity

frmPlaceOrder.btnRecieved.Visible = True 'Shows Recieving Button

frmPlaceOrder.BtnPlaceOrder.Visible = False 'Hides Order

frmPlaceOrder.Show() 'Shows Order Form

End Sub

Enter Order Button (Places Order Based on Order Quantity, Previously Selected Item, and Selected Vendor)

Private Sub BtnPlaceOrder_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnPlaceOrder.Click

If txtOrderQuantity.Text = "" Then 'Checks For Nulls

MsgBox("Error, Please Enter Order Quantity", vbOKOnly)

ElseIf cBoxVendors.Text = "" Then

MsgBox("Error, Please Select, or Create Vendor", vbOKOnly)

Else

'If No Errors, Calls spInsertVendorOrder to Place Order

SpInsertVendorOrderTableAdapter1.GetData(Val(txtSKUvo.Text), Val(txtOrderQuantity.Text), cBoxVendors.SelectedValue)

End If

End Sub

Recieved Button (Updates Selected Order by Specified Quantity)

Private Sub btnRecieved_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRecieved.Click

Dim i As Integer 'Defines i as integer

If Not (DataGridOrders.SelectedRows Is Nothing OrElse DataGridOrders.SelectedRows.Count = 0) Then 'Checks for Selection

i = DataGridOrders.CurrentRow.Index 'initializes i based on Selected Row

MoviesTableAdapter1.spUpdateQty(Val(DataGridOrders.Item(2, i).Value), Val(txtOrderQuantity.Text)) 'Updates Order by calling spUpdateQty

DataGridOrders.DataSource = SpGetOrdersTableAdapter.GetData() 'Updates DataGridOrders

Else

MsgBox("Error, Please Select Order First", vbOKOnly) 'Error Message for no Selection

End If

End Sub

Our New Vendor Form

This form is used to enter a vendor to place orders too.

New Vendor Button (Opens NewVendor Form)

Private Sub btnAddVendor_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddVendor.Click

NewVendor.txtVendorName.Text = "" 'These Clear All Text Boxes

NewVendor.txtContactName.Text = ""

NewVendor.txtConEmail.Text = ""

NewVendor.txtStreetNumber.Text = ""

NewVendor.txtStreetName.Text = ""

NewVendor.txtStreetType.Text = ""

NewVendor.txtZip.Text = ""

NewVendor.txtState.Text = ""

NewVendor.txtPhoneNumber.Text = ""

NewVendor.Show() 'Shows New Vendor Form

End Sub

New Vendor Submit Button (Submits New Vendor Information to Vendor Form)

Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

If txtVendorName.Text = "" Then 'These Check For Nulls

MsgBox("Error, Please Fill all the Fields With Data", vbOKOnly) 'Error Message For Null

ElseIf txtContactName.Text = "" Then

MsgBox("Error, Please Fill all the Fields With Data", vbOKOnly)

ElseIf txtConEmail.Text = "" Then

MsgBox("Error, Please Fill all the Fields With Data", vbOKOnly)

ElseIf txtStreetNumber.Text = "" Then

MsgBox("Error, Please Fill all the Fields With Data", vbOKOnly)

ElseIf txtStreetName.Text = "" Then

MsgBox("Error, Please Fill all the Fields With Data", vbOKOnly)

ElseIf txtStreetType.Text = "" Then

MsgBox("Error, Please Fill all the Fields With Data", vbOKOnly)

ElseIf txtZip.Text = "" Then

MsgBox("Error, Please Fill all the Fields With Data", vbOKOnly)

ElseIf txtState.Text = "" Then

MsgBox("Error, Please Fill all the Fields With Data", vbOKOnly)

ElseIf txtPhoneNumber.Text = "" Then

MsgBox("Error, Please Fill all the Fields With Data", vbOKOnly)

Else

'If No Errors, This calls spInsertVendor to Insert Vendor Info From Text Boxes

VendorsTableAdapter1.spInsertVendors(txtVendorName.Text, txtContactName.Text, txtConEmail.Text, txtStreetNumber.Text, txtStreetName.Text, txtStreetType.Text, Val(txtZip.Text), txtCity.Text, txtState.Text, Val(txtPhoneNumber.Text))

Me.Close() 'This Closes New Vendor Form

frmPlaceOrder.cBoxVendors.DataSource = frmPlaceOrder.SpGetVendorsTableAdapter.GetData() 'Refreshes Order Data

End If

End Sub

```
Return to Top
```

Security Plan

(Definition of Roles & Access Level.)

1) Database Administrator(s)

The administrator has full administrative access to the entire database including all tables, view and stored procedures.
Since the database will be deployed in SQL server that runs on Windows, then the SQL server Authentication along with windows authentication, can be used to secure that database.
A login name and password will be needed to make any changes, or updates to the database.

2) Manager(s)

The Manager will be assigned a login and a password by the administrator.

The privileges will be set where the manager can modify (add, not delete) columns as needed to any table.

Manager(s) will be able to modify price, information and be able to run the DeletedBit Stored Procedure to mark something as Deleted.

3) Employee(s)

The Employee will be able to perform Searches
Perform Data Inserts into tables

How will access be enforced?

ADO.NET

We can create logins and passwords for Employee(s) and Manager(s) as the authenication method for the database, before anything can be done to the database. By using C# or Visual Basic programming language we will enforce this standard.

Windows Authentication

Windows Authentication will be used to enforce the Login / Password combinations required for the Manager(s) and Employee(s) to log into the system.

By using integrated security, it will be easier to enforce the Read/Write/Insert access for the Manager(s) and Read/Write access for the Employee(s).

```
Return to Top
```

Indexing & Maintenance Plan

What will be indexed?

We will be indexing all the main columns that have text, such as columns with nvarchar data type. This will allow us to do searches based on a wider range of options. Having the columns indexed with Full-Text Index will allow for faster search results as opposed to using a Like query. This will also setup this database for expansion as Full-Text Index is better suited for larger databases.

How will you monitor performance?

Performance of the database can be done via the Activity Monitor which is built into SQL server. The activity monitor can show processes running, open transactions, memory usage, and login times. These are separated per database as well. Reports can be run in SQL Server to see how disk usage is being used for tables, and to see the number of records in the tables.

Who will be responsible for maintaining?

Maintenance of the database will be handled by a trained database administrator. The administrator will look at reports that can be generated within SQL server. The administrator will be looking for errors in these reports and will also be checking that all backups are running properly to avoid the loss data.

What access will Maintenance have?

Maintenance will have access to the SQL Server Management Studio in order to setup any maintenance plans. These plans are setup in the SQL Server maintenance plan wizard which can allow the setup of maintenance tasks. Some of the tasks include checking database integrity, reorganize indexing, History cleanup, and either Full or Incremental Backups.