Use the cis535 database you previously created.

Use T-SQL to alter the booth and machine tables. Add a foreign key constraint to each table for the product_id referencing product_id in the product table.

Use T-SQL to alter the booth and machine tables. Add a check constraint to each table for the booth_price in the booth table and the machine_price in the machine table. The check criteria should be booth_price > 0.00 and machine_price > 0.00 respectively.

Now, right click on the check constraint for booth price under constraints in the booth table. Select Script ToClipboard. You may now perform a paste (from the clipboard) into your submission document as verification of both the foreign key constraint add and the check constraint add.

Repeat the previous step for the check constraint in the machine table.

When you have successfully added the constraints to the table, ensure that you have copied and pasted all of the T-SQL statements and verification results into your assignment submission.

Previous Design for the cis535 database:

Use MSSQLS Management Studio to create a new database named cis535. Use the default settings. After the new database is created, use the Script Database AS function for the database to script it as a create to the clipboard. Once you have accomplished that action, paste the script into your assignment submission document for this part of your assignment.

See below for the script used for the cis535 database:

USE [master]

GO

/****** Object: Database [cis535] Script Date: 12/10/2016 1:32:05 AM ******/

CREATE DATABASE [cis535]

CONTAINMENT = NONE

ON PRIMARY

( NAME = N'cis535', FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLNEW\MSSQL\DATA\cis535.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N'cis535_log', FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLNEW\MSSQL\DATA\cis535_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

ALTER DATABASE [cis535] SET COMPATIBILITY_LEVEL = 110

GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

begin

EXEC [cis535].[dbo].[sp_fulltext_database] @action = 'enable'

end

GO

ALTER DATABASE [cis535] SET ANSI_NULL_DEFAULT OFF

GO

ALTER DATABASE [cis535] SET ANSI_NULLS OFF

GO

ALTER DATABASE [cis535] SET ANSI_PADDING OFF

GO

ALTER DATABASE [cis535] SET ANSI_WARNINGS OFF

GO

ALTER DATABASE [cis535] SET ARITHABORT OFF

GO

ALTER DATABASE [cis535] SET AUTO_CLOSE OFF

GO

ALTER DATABASE [cis535] SET AUTO_CREATE_STATISTICS ON

GO

ALTER DATABASE [cis535] SET AUTO_SHRINK OFF

GO

ALTER DATABASE [cis535] SET AUTO_UPDATE_STATISTICS ON

GO

ALTER DATABASE [cis535] SET CURSOR_CLOSE_ON_COMMIT OFF

GO

ALTER DATABASE [cis535] SET CURSOR_DEFAULT GLOBAL

GO

ALTER DATABASE [cis535] SET CONCAT_NULL_YIELDS_NULL OFF

GO

ALTER DATABASE [cis535] SET NUMERIC_ROUNDABORT OFF

GO

ALTER DATABASE [cis535] SET QUOTED_IDENTIFIER OFF

GO

ALTER DATABASE [cis535] SET RECURSIVE_TRIGGERS OFF

GO

ALTER DATABASE [cis535] SET DISABLE_BROKER

GO

ALTER DATABASE [cis535] SET AUTO_UPDATE_STATISTICS_ASYNC OFF

GO

ALTER DATABASE [cis535] SET DATE_CORRELATION_OPTIMIZATION OFF

GO

ALTER DATABASE [cis535] SET TRUSTWORTHY OFF

GO

ALTER DATABASE [cis535] SET ALLOW_SNAPSHOT_ISOLATION OFF

GO

ALTER DATABASE [cis535] SET PARAMETERIZATION SIMPLE

GO

ALTER DATABASE [cis535] SET READ_COMMITTED_SNAPSHOT OFF

GO

ALTER DATABASE [cis535] SET HONOR_BROKER_PRIORITY OFF

GO

ALTER DATABASE [cis535] SET RECOVERY FULL

GO

ALTER DATABASE [cis535] SET MULTI_USER

GO

ALTER DATABASE [cis535] SET PAGE_VERIFY CHECKSUM

GO

ALTER DATABASE [cis535] SET DB_CHAINING OFF

GO

ALTER DATABASE [cis535] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )

GO

ALTER DATABASE [cis535] SET TARGET_RECOVERY_TIME = 0 SECONDS

GO

ALTER DATABASE [cis535] SET READ_WRITE

GO

Use T-SQL to create two table structures based on the following entities:

Table name: Booth

Table name: Machine

Booth_Product

Booth_Price

Machine_Product

Machine_Price

Each table will have the two attributes indicated in the example above. The MACHINE_PRODUCT and BOOTH_PRODUCT will be character data type and the two price fields will be a money data type. Copy and paste the T-SQL table create statements into your assignment submission for this part of the assignment. Preceding your T-SQL create table statement use the following statements:

use cis535;

if object_id('dbo.booth', 'U') is not null

drop table dbo.booth;

T-SQL create table statement

AND

use cis535;

if object_id('dbo.MACHINE', 'U') is not null

drop table dbo.MACHINE;

Use the cis535 database you previously created. Create a new table named PRODUCT. The product table will have two attributes;

product ID as integer, not null, and a primary key constraint set to unique.

product description as varchar(30), not null specification.

When you have created the new table, copy and paste your T-SQL into your assignment submission.

Next, write T-SQL to alter the booth table and the machine table to add a product id attribute, type integer to each table. When the fields have been successfully added to each table, copy and paste your T-SQL into your assignment submission.

Lastly, use the following two sets of insert statements to insert data into the product table and the booth table.

use cis535;

insert into dbo.product(product_id, product_desc)

values(1, 'Chips');

insert into dbo.product(product_id, product_desc)

values(2, 'Cola')

insert into dbo.product(product_id, product_desc)

values(3, 'Energy Drink');

insert into dbo.product(product_id, product_desc)

values(4, 'Chocolate Bar');

go

insert into dbo.Booth(booth_product, booth_price, product_id)

values('Chips', 1.5, 1);

insert into dbo.Booth(booth_product, booth_price, product_id)

values('Cola', 1.25, 2);

insert into dbo.Booth(booth_product, booth_price, product_id)

values('Energy Drink', 2, 3);

Using the insert statements for the booth table, write insert statements to insert the following values into the machine table for the machine product, machine price, and product id fields respectively:

Chips, 1.25, 3

Chocolate Bar, 2.25, 4

Energy Drink, 2.95, 1