USE master
GO

IF DB_ID('kungfu_panda_shop') IS NOT NULL
	DROP DATABASE kungfu_panda_shop
	
CREATE DATABASE kungfu_panda_shop
GO

USE [kungfu_panda_shop]
GO
/****** Object:  Table [dbo].[Categories]   
 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[categories](
	[categoryID] [int] NOT NULL,
	[categoryName] [varchar](255) NOT 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]
GO

/****** Object:  Table [dbo].[Products]     ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[products](
	[productID] [int] NOT NULL,
	[categoryID] [int] NOT NULL,
	[productCode] [varchar](10) NOT NULL,
	[productName] [varchar](255) NOT NULL,
	[imageFile] [varchar](30) NULL,
	[listPrice] [money] NOT NULL,
 CONSTRAINT [PK_products] PRIMARY KEY CLUSTERED 
(
	[productID] 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

INSERT [dbo].[categories] ([categoryID], [categoryName]) VALUES (1, N'Action Figures')
INSERT [dbo].[categories] ([categoryID], [categoryName]) VALUES (2, N'MP3 Songs')
INSERT [dbo].[categories] ([categoryID], [categoryName]) VALUES (3, N'DVD')


INSERT [dbo].[products] ([productID], [categoryID], [productCode], [productName], [imagefile], [listPrice]) VALUES (1, 1, N'po', N'Po Action Figure 1 foot', N'1po.jpg', 19.0000)
INSERT [dbo].[products] ([productID], [categoryID], [productCode], [productName], [imagefile], [listPrice]) VALUES (2, 1, N'master', N'Master Shifu Action Figure 6 inches', N'2master.jpg', 18.0000)
INSERT [dbo].[products] ([productID], [categoryID], [productCode], [productName], [imagefile], [listPrice]) VALUES (3, 1, N'tigress', N'Tigress Action Figure 11 inches', N'3tigress.jpg', 17.0000)
INSERT [dbo].[products] ([productID], [categoryID], [productCode], [productName], [imagefile], [listPrice]) VALUES (4, 1, N'monkey', N'Monkey Action Figure 11 inches', N'4monkey.jpg', 17.9900)
INSERT [dbo].[products] ([productID], [categoryID], [productCode], [productName], [imagefile], [listPrice]) VALUES (5, 1, N'viper', N'Viper Action Figure 11 inches', N'5viper.jpg', 19.0000)
INSERT [dbo].[products] ([productID], [categoryID], [productCode], [productName], [imagefile], [listPrice]) VALUES (6, 1, N'mantis', N'Mantis Action Figure 11 inches', N'6mantis.jpg', 16.0000)
INSERT [dbo].[products] ([productID], [categoryID], [productCode], [productName], [imagefile], [listPrice]) VALUES (7, 2, N'tournament', N'Let The Tournament Begin', N'7tournament.jpg', 9.9900)
INSERT [dbo].[products] ([productID], [categoryID], [productCode], [productName], [imagefile], [listPrice]) VALUES (8, 2, N'dragon', N'The Dragon Warrior Is Among Us', N'8dragon.jpg', 8.99)
INSERT [dbo].[products] ([productID], [categoryID], [productCode], [productName], [imagefile], [listPrice]) VALUES (9, 2, N'peach', N'Peach Tree Of Wisdom', N'9peach.jpg', 7.9900)
INSERT [dbo].[products] ([productID], [categoryID], [productCode], [productName], [imagefile], [listPrice]) VALUES (10, 3, N'kungfuwide', N'Kung Fu Panda Widescreen DVD', N'10kungfuwide.jpg', 11.9900)
INSERT [dbo].[products] ([productID], [categoryID], [productCode], [productName], [imagefile], [listPrice]) VALUES (11, 3, N'kungfu2', N'Kung Fu Panda II DVD', N'11kungfu2.jpg', 12.9900)

GO

ALTER TABLE [dbo].[products]  WITH NOCHECK ADD  CONSTRAINT [FK_Products_Categories] FOREIGN KEY([categoryID])
REFERENCES [dbo].[categories] ([categoryID])
ON UPDATE CASCADE
GO