--*************************************************************************--

-- Title: Assignment05

-- Author: YourNameHere

-- Desc: This file demonstrates how to process data in a database

-- Change Log: When,Who,What

-- 2017-01-01,YourNameHere,Created File

--**************************************************************************--

-- Step 1: Create the assignment database

Use Master;

go

If Exists(Select Name from SysDatabases Where Name = 'Assignment05DB_YourNameHere')

Begin

Alter Database [Assignment05DB_YourNameHere] set Single_user With Rollback Immediate;

Drop Database Assignment05DB_YourNameHere;

End

go

Create Database Assignment05DB_YourNameHere;

go

Use Assignment05DB_YourNameHere;

go

-- Create Tables (Module 01)--

Create Table Categories

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

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

);

go

Create Table Products

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

,[ProductName] [nvarchar](100) NOT NULL

,[CategoryID] [int] NULL

,[UnitPrice] [money] NOT NULL

);

go

Create Table Inventories

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

,[InventoryDate] [Date] NOT NULL

,[ProductID] [int] NOT NULL

,[Count] [int] NOT NULL

);

go

-- Add Constraints (Module 02) --

Alter Table Categories

Add Constraint pkCategories

Primary Key (CategoryId);

go

Alter Table Categories

Add Constraint ukCategories

Unique (CategoryName);

go

Alter Table Products

Add Constraint pkProducts

Primary Key (ProductId);

go

Alter Table Products

Add Constraint ukProducts

Unique (ProductName);

go

Alter Table Products

Add Constraint fkProductsToCategories

Foreign Key (CategoryId) References Categories(CategoryId);

go

Alter Table Products

Add Constraint ckProductUnitPriceZeroOrHigher

Check (UnitPrice >= 0);

go

Alter Table Inventories

Add Constraint pkInventories

Primary Key (InventoryId);

go

Alter Table Inventories

Add Constraint dfInventoryDate

Default GetDate() For InventoryDate;

go

Alter Table Inventories

Add Constraint fkInventoriesToProducts

Foreign Key (ProductId) References Products(ProductId);

go

Alter Table Inventories

Add Constraint ckInventoryCountZeroOrHigher

Check ([Count] >= 0);

go

-- Show the Current data in the Categories, Products, and Inventories Tables

Select * from Categories;

go

Select * from Products;

go

Select * from Inventories;

go

-- Step 2: Add some starter data to the database

/* Add the following data to this database using inserts:

Category Product Price Date Count

Beverages Chai 18.00 2017-01-01 61

Beverages Chang 19.00 2017-01-01 17

Beverages Chai 18.00 2017-02-01 13

Beverages Chang 19.00 2017-02-01 12

Beverages Chai 18.00 2017-03-02 18

Beverages Chang 19.00 2017-03-02 12

*/

-- Step 3: Create transactional stored procedures for each table using the proviced template:

-- Step 4: Create code to test each transactional stored procedure.