Description

You will upload 4 SQL scripts (must have .sql extension) to the Canvas dropbox (unzipped):

·         Script to drop, create and populate the tables

·         Script to drop and create the procedure

·         Script to drop and create the trigger

·         Script to drop and create the view

We will NOT be working on the Google Platform for this final homework.  The cost of the SQL Server licensing has made that prohibitive.  You will create your tables, procedure, view and trigger on your SQL Server database provided by me at 139.78.8.180,22.

You must have the scripts uploaded and everything made on my server by the deadline.  My primary source for grading will be your database.

Homework Content

The zip file contains a scenario, ERD and Excel file with data.  In case of any discrepancies between the scenario and the ERD the ERD wins. 

Tables:

Your first script must drop, create and populate all the tables on the ERD with the proper constraints.  Select the best fitting data type for each attribute based on the scenario.  Use the datatype “bit” for SIR.DogSuit.  All attributes that are neither part of the key or foreign are allowed to be null.

Trigger: 

Name it ArchiveLeases.  When a record in HumanLease is deleted copy all the data from that record into the "HumanLeaseArchive" table.  HumanLeaseArchive is not joined to any other table so there are no foreign key constraints (but it does need the primary key constraint). 

Inserting records using a select statement is a new process for you.  To do this you have to follow this format between "as" and "go":

insert into [tablename] (names(s) of columns)

select [names of column(s)] from deleted;

For example:

insert into ThisTable (CatID, Breed)

select CatID, Breed from deleted;

With this code in a deleted trigger on cat the cat record would effectively be moved to ThisTable.  This homework question is testing both your ability to write a trigger and your ability to apply something slightly new.  Do it as illustrated here but with the proper table and column names.

View:

Create a view named OpenLeases that shows the Irken name, Human ID and Lease Start for all open leases (end date > today).

Procedure:

Name your procedure TineCheck.  When an integer is passed to this procedure it should copy the HumanID, FirstName, LastName and Tines into a new table “TinyTines” for every Spork having a number of tines equal to or less than that integer.

In addition to these values the TinyTines table should have a unique primary key.  Here’s another new thing to implement:

            Set up the TinyTines primary key in this format: 

WhateverKeyNameYouChoose int identity(1,1) primary key,

HumanID int….etc, etc.

Your insert statement into a table like this follows the format:

Insert into TinyTines values (@yourvariableforhumanid, @yourvariableforFirstname, and two more variables for the last name and tines. 

The primary key value will be automatically added starting at 1 and incrementing by 1. This homework question is testing both your ability to write a procedure and your ability to apply something slightly new. 

Have your procedure create TinyTines each time it runs.