Assignment05.sql

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

-- 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.

Assignment Task You need to also practice what you have learned in this module. To do so, you will write a SQL Script to create a new normalized database with stored procedures. Use the provide starter file, Assignment05.sql to get you started. Step 1: Create the assignment database -- Title: Assignment0!5 -Author: YourNameHere -Desc: This file demonstrates how to process data in a database -- Change Log WhenWho What -2017-01-01,YourNameHere , created File Use Master; go If Exists(Select Name from SvsDatabases, Where Name'Assignmente5DB_YourNameHere') Begin Alter Database [Assignmente5DB_YourNameHere] set Single user With Rollback Immediate; Drop Database Assignment05DB_YourNameHere; End go Create Database Assignment05DB_YourNameHere; go Use Assignment05DB YourNameHere -- Create Tables (Module 01) -- Create Table Categories [int] IDENTITY(1,1) NOT NULL CategoryName] [nvarchar (100) NOT NULL go Create Table (RredustJR] Products [3,0] IDENTITY (1,1) NOT NULL ProductName] [nvchar(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 Assignment Task You need to also practice what you have learned in this module. To do so, you will write a SQL Script to create a new normalized database with stored procedures. Use the provide starter file, Assignment05.sql to get you started. Step 1: Create the assignment database -- Title: Assignment0!5 -Author: YourNameHere -Desc: This file demonstrates how to process data in a database -- Change Log WhenWho What -2017-01-01,YourNameHere , created File Use Master; go If Exists(Select Name from SvsDatabases, Where Name'Assignmente5DB_YourNameHere') Begin Alter Database [Assignmente5DB_YourNameHere] set Single user With Rollback Immediate; Drop Database Assignment05DB_YourNameHere; End go Create Database Assignment05DB_YourNameHere; go Use Assignment05DB YourNameHere -- Create Tables (Module 01) -- Create Table Categories [int] IDENTITY(1,1) NOT NULL CategoryName] [nvarchar (100) NOT NULL go Create Table (RredustJR] Products [3,0] IDENTITY (1,1) NOT NULL ProductName] [nvchar(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