Use CommunityAssist --trigger to flag all donations above 1000 --to be checked out by somebody --will write them into a temp table -- for or after trigger lets the operation --occur and then fires the trigger --instead of triggers intercept the operation --and do the content of the trigger instead of --the insert, update or delete Go --triggers are usually on a particular table Create trigger tr_BigDonations on Donation for insert --this will fire with every insert As --beginning of the body of the trigger If not Exists --check to see if our temp table exists (Select name from sys.tables where name = 'BigDonations') Begin --begin of the if block --if the table doesn't exist create it Create table BigDonations ( DonationKey int, DonationDate datetime, DonationAmount money, PersonKey int, EmployeeKey int ) End --end of if exists block --create a variable Declare @DonationAmount money --set a value for the variable based --on the inserted table (a temp table that --keeps all insertions for the duration of --the transaction (less than a nano second) --there is also a deleted table that keeps --updates and deletes Select @DonationAmount=DonationAmount From Inserted --check the value of the donation If @DonationAmount >= 1000 Begin --begin if --if it is 1000 or larger insert it also --into the BigDonations Table Insert into BigDonations (DonationKey, DonationDate, DonationAmount, PersonKey, EmployeeKey) Select DonationKey, DonationDate, DonationAmount, PersonKey, EmployeeKey From Inserted End --end if condition --also end of trigger --Test the trigger Insert into Donation (DonationDate, DonationAmount, PersonKey, EmployeeKey) Values(GETDATE(), 1002,1,1) --should be inserted into donation Select * from Donation --should also be inserted into Bigdonations Select * from BigDonations --this stored procedure will also fire the trigger Exec usp_NewDonation @lastname ='Lawrence', @firstname='Bob', @street='10000 nowhere', @city='seattle', @state='wa', @zip='98100', @homephone='2065551111', @email='boblawrence@yahoo.com', @donationAmount=1500.96
Wednesday, February 29, 2012
Trigger
Thursday, February 23, 2012
SQL
Use CommunityAssist
--Simple Selects
Select lastname, Firstname from Person
Select * From Person
/*
this is a
multiline
comment
*/
--alias and sort
Select lastname as [Last name], firstname as [First Name]
From Person
order by lastname
--sort descending
Select lastname as [Last name], firstname as [First Name]
From Person
order by lastname Desc
Select * From PersonAddress
Order by City Desc, zip
Select * From Donation
Select Distinct YEAR(DonationDate) as [Year], Month(DonationDate) from Donation
Select LastName, firstname from Person
Where PersonKey=15
Select * From PersonAddress
Where City='Seattle'
Select * From PersonAddress
Where not City = 'seattle'
-- > < >= <= !=
Select * From Donation
where DonationAmount > 500
Select * from Donation
Where DonationDate Between '3/1/2010' and '3/31/2010'
Select * from Donation
Where DonationDate >= '3/1/2010' and DonationDate <='3/31/2010'
--aggregate function count, max, min, sum,avg
Select COUNT(DonationAmount) as Number from Donation
Select SUM(DonationAmount) as Total from donation
Select AVG(DonationAmount) as Average from Donation
Select MAX(donationAmount) as biggest from donation
Select Year(donationDate) as [Year], Month(DonationDate) as [Month],
SUM(DonationAmount) as Total
From Donation
Group by YEAR(DonationDate), MONTH(donationDate)
Having SUM(donationAmount) > 5000
Order by Year(donationDate)
Select * from Person
Select * From PersonAddress
--inner joins
Select LastName, Firstname, Street, Apartment,City, [State], zip
From Person
Inner join PersonAddress
on Person.PersonKey=PersonAddress.PersonKey
Select LastName, Firstname, Street, Apartment,City, [State],
zip, donationDate, DonationAmount
From Person p
Inner join PersonAddress pa
on p.PersonKey=pa.PersonKey
inner join Donation d
on p.PersonKey=d.PersonKey
Select Servicename, GrantKey
From [Service] s
Left Outer join ServiceGrant sg
on s.ServiceKey=sg.ServiceKey
Where GrantKey is null
Select * from PersonAddress
Where Apartment is not null
Insert into Person(LastName, FirstName)
Values('Smith', 'Joe'),
('Doe', 'Jane')
Select * from Person
--explicitly setting up a transaction
--can allow you to have an undo
Begin tran
Update Person
Set Firstname='fsdakfsdahk'
Where PersonKey=1
Rollback tran --undoes transaction
Commit Tran -- commits and writes the transaction
Delete from PersonAddress --will delete all records in the table
Select * from PersonAddress
Inserting with Linq
First Drag the tables onto the LINQ Designer
Here is the Source code with the validation
<%@ Page Language="C#" AutoEventWireup="true"
CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0
Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<p>
<asp:Label ID="Label1" runat="server" Text="Enter First Name"></asp:Label>
<asp:TextBox ID="txtFirstname" runat="server"></asp:TextBox> <br />
<asp:Label ID="Label2" runat="server" Text="Enter Last Name"></asp:Label>
<asp:TextBox ID="txtLastName" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="txtLastName" Display="None"
ErrorMessage="Last Name is required"></asp:RequiredFieldValidator>
<br />
<asp:Label ID="Label3" runat="server" Text="Enter Street Address"></asp:Label>
<asp:TextBox ID="txtStreet" runat="server"></asp:TextBox> <br />
<asp:Label ID="Label5" runat="server" Text="Enter City"></asp:Label>
<asp:TextBox ID="txtCity" runat="server"></asp:TextBox> <br />
<asp:Label ID="Label4" runat="server" Text="Enter State (2 Characters) "></asp:Label>
<asp:TextBox ID="txtState" runat="server"></asp:TextBox> <br />
<asp:Label ID="Label6" runat="server" Text="Enter Zip Code"></asp:Label>
<asp:TextBox ID="txtZip" runat="server"></asp:TextBox>
<asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server"
ControlToValidate="txtZip" Display="None" ErrorMessage="Invalid Zip Code"
ValidationExpression="\d{5}(-\d{4})?"></asp:RegularExpressionValidator>
<br />
<asp:Label ID="Label7" runat="server" Text="Enter Home Phone"></asp:Label>
<asp:TextBox ID="txtPhone" runat="server"></asp:TextBox> <br />
<asp:Label ID="Label8" runat="server" Text="Enter Email"></asp:Label>
<asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>
<asp:RegularExpressionValidator ID="RegularExpressionValidator2" runat="server"
ControlToValidate="txtEmail" Display="None" ErrorMessage="Invalid email"
ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*">
</asp:RegularExpressionValidator>
<br />
<asp:Label ID="Label9" runat="server" Text="Enter Donation Amount"></asp:Label>
<asp:TextBox ID="txtDonation" runat="server"></asp:TextBox>
<asp:CompareValidator ID="CompareValidator1" runat="server"
ControlToValidate="txtDonation" Display="None"
ErrorMessage="Enter a valid Donation" Operator="DataTypeCheck"
Type="Currency"></asp:CompareValidator>
<br />
<asp:Button ID="btnDonation" runat="server" Text="EnterDonation"
onclick="btnDonation_Click" />
</p>
</div>
<asp:ValidationSummary ID="ValidationSummary1" runat="server" />
</form>
</body>
</html>
Here is the LINQ code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnDonation_Click(object sender, EventArgs e)
{
DonorDataContext dc = new DonorDataContext();
Person p = new Person();
p.LastName = txtLastName.Text;
p.FirstName = txtFirstname.Text;
dc.Persons.InsertOnSubmit(p);
PersonAddress pa = new PersonAddress();
pa.Person= p;
pa.Street = txtStreet.Text;
pa.City = txtCity.Text;
pa.State = txtState.Text;
pa.Zip = txtZip.Text;
dc.PersonAddresses.InsertOnSubmit(pa);
PersonContact pc = new PersonContact();
pc.Person = p;
pc.ContactTypeKey = 1;
pc.ContactInfo = txtPhone.Text;
dc.PersonContacts.InsertOnSubmit(pc);
PersonContact pc2 = new PersonContact();
pc2.Person = p;
pc2.ContactTypeKey =6;
pc2.ContactInfo = txtEmail.Text;
dc.PersonContacts.InsertOnSubmit(pc2);
Donation d = new Donation();
d.Person = p;
d.DonationDate = DateTime.Now;
d.DonationAmount = decimal.Parse(txtDonation.Text);
dc.Donations.InsertOnSubmit(d);
dc.SubmitChanges();
}
}
Wednesday, February 22, 2012
Stored Procedure
--Stored procedure to process a new donation
--insert a person
--insert a address for person
--insert a home phone and an email
--insert the actual donation
---Check to make sure the person doesn't already exist
--if the do exist we still do donation, but don't enter person etc
--put all in transaction
--add a try catch
Use CommunityAssist
go
Alter Proc usp_NewDonation
--enter parameters
@lastname nvarchar(255),
@firstname nvarchar(255),
@street nvarchar(255),
@city nvarchar(255),
@state nvarchar(2),
@zip nvarchar(10),
@homephone nvarchar(255),
@email nvarchar(255),
@donationAmount money,
@EmployeeKey int = 3
As
Begin tran
Begin try
--test to make sure the customer does not exist
--by seeing if any values in the database
--match the parameters
If not exists
(Select lastname, firstname, ContactInfo
From Person p
inner join PersonAddress pa
on p.PersonKey=pa.PersonKey
inner join PersonContact pc
on p.PersonKey=pc.PersonKey
Where LastName=@lastname
And FirstName=@firstname
And ContactInfo=@email
And ContactTypeKey=6)
Begin --if person doesn't exist
--inserts if the person doesn't exist
Insert into Person(LastName, FirstName)
Values(@lastname, @firstname)
Declare @personKey int
--this gets the last identity created for the table in parenthesis
Set @personKey=IDENT_CURRENT('Person')
Insert into PersonAddress(Street, Apartment, [State], City, Zip, PersonKey)
Values(@street, null, @state, @city, @zip, @personKey)
Insert into dbo.PersonContact(ContactInfo, PersonKey, ContactTypeKey)
Values(@homephone, @personKey, 1)
Insert into dbo.PersonContact(ContactInfo, PersonKey, ContactTypeKey)
Values(@email, @personKey, 6)
End--end of 'if person doesn't exist'
--we need a new person key, because they may
--have an existing key
Declare @Personkeyb int
--same query as above but now the person
--does exist
Select @Personkeyb=p.PersonKey From Person p
Inner join PersonContact pc
on p.PersonKey=pc.PersonKey
Where FirstName=@firstname
And LastName=@lastname
And ContactInfo =@email
And ContactTypekey=6
--insert the donation
Insert into Donation(DonationDate, DonationAmount, PersonKey, EmployeeKey)
Values(GETDATE(), @donationAmount, @personKeyb, @EmployeeKey)
Commit tran --if it gets here commit the transaction
End try --end the try part
Begin Catch --begin the catch
Rollback Tran --roll back the transaction (all or nothing)
--the print only works in the sql environment
print 'The insertion of the donation failed'
End Catch
--here is the test
Go
Exec usp_NewDonation
@lastname ='John',
@firstname='Smith' ,
@street='somewhere 101',
@city ='Seattle',
@state ='WA',
@zip='98000' ,
@homephone ='2065551000',
@email ='js@yahoo.com',
@donationAmount=150,
@Employeekey=1
--selects to check on the results
Select * From Person
Select * from PersonAddress Where PersonKey=53
Select * from PersonContact Where PersonKey=53
Select * from Donation where PersonKey=53
Wednesday, February 15, 2012
LINQ ADO and Other Stuff
here is the code for the ListView, but remember to look at chapter 11
<%@ Page Language="C#" AutoEventWireup="true"
CodeFile="Default.aspx.cs" Inherits="_Default"
ViewStateMode="Enabled" ViewStateEncryptionMode="Always"%>
<!DOCTYPE html
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<asp:ListView ID="ListView1" runat="server">
<ItemTemplate>
<tr>
<td><%#Eval("ServiceName")%></td>
<td><%#Eval("ServiceDescription")%></td>
</tr>
</ItemTemplate>
</asp:ListView>
</table>
</div>
</form>
</body>
</html>
Here is the ADO code behind for that page
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection connect = new SqlConnection
("Data Source=Localhost;initial catalog=CommunityAssist;integrated security=true");
string sql = "Select ServiceName, ServiceDescription from [Service]";
SqlCommand cmd = new SqlCommand(sql, connect);
SqlDataReader reader=null;
try
{
connect.Open();
reader = cmd.ExecuteReader();
ListView1.DataSource = reader;
ListView1.DataBind();
}
catch (SqlException )
{
Session["error"] = "There is an error in the SQL String";
Response.Redirect("Default2.aspx");
}
catch (Exception)
{
Session["error"] = "Something went wrong, sorry";
Response.Redirect("Default2.aspx");
}
finally
{
reader.Dispose();
connect.Close();
}
}
}
Here is the source for the Error page
<%@ Page Language="C#" AutoEventWireup="true"
CodeFile="Default2.aspx.cs" Inherits="Default2" %>
<!DOCTYPE html PUBLIC
"-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
</div>
</form>
</body>
</html>
And here is the c# from the error page
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (Session["error"] != null)
{
//Exception ex = (Exception)Session["error"];
//Label1.Text = ex.Message;
Label1.Text = Session["Error"].ToString();
}
}
}
Linq
Remember with LINQ you need to add a new a LINQ to SQL object.
It adds the LINQ Designer
From the solution explored drag tables from a connection onto the designer. This causes Visual Studio to generate classes we can access.
Here is the code for the Linq example
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
try
{
CommunityAssistDataContext dc = new CommunityAssistDataContext();
var donors = from d in dc.PersonContacts
where d.ContactTypeKey == 6
orderby d.Person.LastName
select new
{
d.Person.LastName,
d.Person.FirstName,
d.ContactInfo
};
GridView1.DataSource = donors.ToList();
GridView1.DataBind();
}
catch (Exception ex)
{
}
}
}
Functions and Simple Procedures
--a simple function
Create Function fx_cube
(@number int) --parameter provided by the user
returns int --what data type the function returns
As -- function is an alias for the content below
Begin
--result to return
return @number * @number * @number
End
--using the function (always needs the schema "dbo"
--or whatever schema owns it
Select dbo.fx_Cube(3)
--using the function with a field
Select EmployeeKey, dbo.fx_cube(EmployeeKey) From Employee
/***********************
always plot out the function or procedure first
the function will return the price of a subscription
parameters magid and subscriptionTypeID
use MagazineId and SubscriptionTypeID to look up price
********************************/
Use MagazineSubscription
Go
Create function fx_GetSubscriptionPrice
(@magid int, @subscriptTypeID int) --parameters
Returns money --return type
AS
Begin
Declare @Price money --internal variable
--assign the value from a select statement
Select @Price=SubscriptionPrice
From MagazineDetail
Where MagID=@MagID
AND SubscriptTypeID=@subscriptTypeID
Return @Price --return the value
End
Select * From Magazine
Select * From magazineDetail where MagID=2
--using the function
Select MagName, SubscriptTypeName,
dbo.fx_GetSubscriptionPrice(m.MagID, st.SubscriptTypeID)
From Magazine m
Inner Join MagazineDetail md
on m.MagID=md.MagID
inner join SubscriptionType st
on st.SubscriptTypeID=md.SubscriptTypeID
Where m.MagID=2
--another way to declare and set a value
--for a variable
Declare @Today DateTime
Set @Today=GETDATE()
Select @Today
Declare @ID int
set @ID=@@Identity --retursn the last identity
--created in the database
Declare @ID2 int
--returns the last identity created in the table
set @ID2=IDENT_CURRENT('MagazineDetail')
Select @ID2
Select * from MagazineDetail
Use CommunityAssist
--simple stored procedure
--really a parameterized view
Create proc usp_DonorContact
@contactType int --parameter
As
Select Distinct lastname [Last Name],
Firstname [First Name],
ContactInfo [Contact]
From Person p
inner join PersonContact pc
on p.PersonKey=pc.PersonKey
inner join Donation d
on p.PersonKey=d.PersonKey
Where ContactTypeKey=@ContactType
--calling the procedure
--the exec is optional
--6 is the contact type
Exec usp_DonorContact 6
Monday, February 13, 2012
Indexes and Views
--views Use CommunityAssist Go If exists (Select name from sys.views where name='vw_Donors') Begin Drop View vw_Donors end Go --create a view Create view vw_Donors As Select Distinct lastname [Last Name], Firstname [First Name], ContactInfo [Email] From Person p inner join PersonContact pc on p.PersonKey=pc.PersonKey inner join Donation d on p.PersonKey=d.PersonKey Where ContactTypeKey=6 Go --use the view just like a table Select * from vw_Donors where [Last Name] Like 'Mann%' --clustered index primary keys have a clustered index by default Create index ix_Lastname on Person(LastName) --Force a query to use an index Select * from Person with (index(ix_LastName)) --a filtered index Create index ix_City on PersonAddress(City) Where City != 'Seattle' --a unique index Create unique index ix_contact on PersonContact(contactInfo)
Sunday, February 12, 2012
Final Project
The final project is based on the BitbyBitEbooks database. You will install this database and then write the SQL to answer a set of questions. You can work on this in groups of two to four people, or you can do it alone. I would suggest a group. A group often has more ideas and better ideas about how to solve certain problems.
First here is the ERD for the database:
Here is the code to create the database
USE MASTER GO IF EXISTS (SELECT Name FROM sys.databases WHERE name='BitbyBitEBooks') BEGIN DROP DATABASE BitbyBitEBooks END GO CREATE DATABASE BitbyBitEbooks GO USE [BitbyBitEbooks] GO /****** Object: Table [dbo].[Project] Script Date: 02/12/2012 11:21:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Project]( [ProjectKEy] [int] IDENTITY(1,1) NOT NULL, [ProjectName] [nvarchar](255) NOT NULL, [ProjectStart] [date] NOT NULL, [ProjectEnd] [date] NULL, [ProjectDescription] [nvarchar](255) NOT NULL, [ProjectAdvance] [money] NULL, [ProjectAuthorPercent] [decimal](3, 2) NULL, CONSTRAINT [PK_Project] PRIMARY KEY CLUSTERED ( [ProjectKEy] 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 SET IDENTITY_INSERT [dbo].[Project] ON INSERT [dbo].[Project] ([ProjectKEy], [ProjectName], [ProjectStart], [ProjectEnd], [ProjectDescription], [ProjectAdvance], [ProjectAuthorPercent]) VALUES (1, N'WordPress', CAST(0xED300B00 AS Date), CAST(0xCF320B00 AS Date), N'Beginning WordPress', 2000.0000, CAST(0.07 AS Decimal(3, 2))) INSERT [dbo].[Project] ([ProjectKEy], [ProjectName], [ProjectStart], [ProjectEnd], [ProjectDescription], [ProjectAdvance], [ProjectAuthorPercent]) VALUES (2, N'Snacks for Gamers', CAST(0x42310B00 AS Date), CAST(0x01330B00 AS Date), N'A recipe book for snacks', 800.0000, CAST(0.05 AS Decimal(3, 2))) INSERT [dbo].[Project] ([ProjectKEy], [ProjectName], [ProjectStart], [ProjectEnd], [ProjectDescription], [ProjectAdvance], [ProjectAuthorPercent]) VALUES (4, N'SQL Server 2008 R2', CAST(0x6F320B00 AS Date), CAST(0x8A330B00 AS Date), N'Administering SQL Server', 2500.0000, CAST(0.07 AS Decimal(3, 2))) INSERT [dbo].[Project] ([ProjectKEy], [ProjectName], [ProjectStart], [ProjectEnd], [ProjectDescription], [ProjectAdvance], [ProjectAuthorPercent]) VALUES (5, N'SQL', CAST(0x6E320B00 AS Date), CAST(0xE1330B00 AS Date), N'Basic SQL', 2500.0000, CAST(0.08 AS Decimal(3, 2))) INSERT [dbo].[Project] ([ProjectKEy], [ProjectName], [ProjectStart], [ProjectEnd], [ProjectDescription], [ProjectAdvance], [ProjectAuthorPercent]) VALUES (6, N'Java for Android', CAST(0xA6320B00 AS Date), NULL, N'Introduction to Java for Android Development', 3000.0000, CAST(0.08 AS Decimal(3, 2))) INSERT [dbo].[Project] ([ProjectKEy], [ProjectName], [ProjectStart], [ProjectEnd], [ProjectDescription], [ProjectAdvance], [ProjectAuthorPercent]) VALUES (7, N'WPF Development', CAST(0xC6320B00 AS Date), NULL, N'windows Presentation foundation and silverlight', 2800.0000, CAST(0.08 AS Decimal(3, 2))) SET IDENTITY_INSERT [dbo].[Project] OFF /****** Object: Table [dbo].[Employee] Script Date: 02/12/2012 11:21:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Employee]( [EmployeeKey] [int] IDENTITY(1,1) NOT NULL, [EmployeeLasName] [nvarchar](50) NOT NULL, [EmployeeFirstName] [nvarchar](50) NOT NULL, [EmployeePosition] [nvarchar](50) NOT NULL, [EmployeeHireDate] [date] NOT NULL, [EmployeeLeaveDate] [date] NULL, CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ( [EmployeeKey] 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 SET IDENTITY_INSERT [dbo].[Employee] ON INSERT [dbo].[Employee] ([EmployeeKey], [EmployeeLasName], [EmployeeFirstName], [EmployeePosition], [EmployeeHireDate], [EmployeeLeaveDate]) VALUES (1, N'Brown', N'Paul', N'Chief Editor', CAST(0x48270B00 AS Date), NULL) INSERT [dbo].[Employee] ([EmployeeKey], [EmployeeLasName], [EmployeeFirstName], [EmployeePosition], [EmployeeHireDate], [EmployeeLeaveDate]) VALUES (2, N'Gardner', N'Susan', N'Copy Editor', CAST(0x48270B00 AS Date), NULL) INSERT [dbo].[Employee] ([EmployeeKey], [EmployeeLasName], [EmployeeFirstName], [EmployeePosition], [EmployeeHireDate], [EmployeeLeaveDate]) VALUES (3, N'West', N'Melissa', N'Design Editor', CAST(0x352A0B00 AS Date), NULL) INSERT [dbo].[Employee] ([EmployeeKey], [EmployeeLasName], [EmployeeFirstName], [EmployeePosition], [EmployeeHireDate], [EmployeeLeaveDate]) VALUES (4, N'Long', N'Mark', N' Design Editor', CAST(0x79280B00 AS Date), CAST(0xD9290B00 AS Date)) INSERT [dbo].[Employee] ([EmployeeKey], [EmployeeLasName], [EmployeeFirstName], [EmployeePosition], [EmployeeHireDate], [EmployeeLeaveDate]) VALUES (5, N'Peterson', N'Fred', N'Sales Manager', CAST(0xCA290B00 AS Date), NULL) SET IDENTITY_INSERT [dbo].[Employee] OFF /****** Object: Table [dbo].[Customer] Script Date: 02/12/2012 11:21:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Customer]( [CustomerKey] [int] IDENTITY(1,1) NOT NULL, [CustomerLastName] [nvarchar](50) NULL, [CustomerFirstName] [nvarchar](50) NULL, [CustomerEmail] [nvarchar](50) NULL, CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED ( [CustomerKey] 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 SET IDENTITY_INSERT [dbo].[Customer] ON INSERT [dbo].[Customer] ([CustomerKey], [CustomerLastName], [CustomerFirstName], [CustomerEmail]) VALUES (1, N'Martin', N'Jeff', N'jMartin@yahoo.com') INSERT [dbo].[Customer] ([CustomerKey], [CustomerLastName], [CustomerFirstName], [CustomerEmail]) VALUES (2, N'Lawrence', N'Larry', N'll@gmail.com') INSERT [dbo].[Customer] ([CustomerKey], [CustomerLastName], [CustomerFirstName], [CustomerEmail]) VALUES (3, N'Towne', N'Peter', N'pTowne@msn.com') INSERT [dbo].[Customer] ([CustomerKey], [CustomerLastName], [CustomerFirstName], [CustomerEmail]) VALUES (4, N'Bradely', N'Susan', N'sBradely@hotmail.com') INSERT [dbo].[Customer] ([CustomerKey], [CustomerLastName], [CustomerFirstName], [CustomerEmail]) VALUES (5, N'Anderson', N'Laura', N'landerson@hotmail.com') INSERT [dbo].[Customer] ([CustomerKey], [CustomerLastName], [CustomerFirstName], [CustomerEmail]) VALUES (6, N'Harding', N'Melanie', N'mHarding@gmail.com') INSERT [dbo].[Customer] ([CustomerKey], [CustomerLastName], [CustomerFirstName], [CustomerEmail]) VALUES (7, N'Nelson', N'Sam', N'sNelson@harvard.edu') INSERT [dbo].[Customer] ([CustomerKey], [CustomerLastName], [CustomerFirstName], [CustomerEmail]) VALUES (8, N'Smith', N'Jason', N'jSmith405@yahoo.com') INSERT [dbo].[Customer] ([CustomerKey], [CustomerLastName], [CustomerFirstName], [CustomerEmail]) VALUES (9, N'Simpson', N'Maggie', N'mSimpson@hotmail.com') INSERT [dbo].[Customer] ([CustomerKey], [CustomerLastName], [CustomerFirstName], [CustomerEmail]) VALUES (10, N'Zilla ', N'God', N'godzilla@gmail.com') SET IDENTITY_INSERT [dbo].[Customer] OFF /****** Object: Table [dbo].[Author] Script Date: 02/12/2012 11:21:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Author]( [AuthorKey] [int] IDENTITY(1,1) NOT NULL, [AuthorName] [nvarchar](50) NOT NULL, [AuthorEmail] [nvarchar](50) NOT NULL, CONSTRAINT [PK_Author] PRIMARY KEY CLUSTERED ( [AuthorKey] 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 SET IDENTITY_INSERT [dbo].[Author] ON INSERT [dbo].[Author] ([AuthorKey], [AuthorName], [AuthorEmail]) VALUES (1, N'Robert Jakobs', N'rj@hotmail.com') INSERT [dbo].[Author] ([AuthorKey], [AuthorName], [AuthorEmail]) VALUES (2, N'Linda Manning', N'linda@manning.com') INSERT [dbo].[Author] ([AuthorKey], [AuthorName], [AuthorEmail]) VALUES (3, N'Bradley Stevens', N'bradlys@yahoo.com') INSERT [dbo].[Author] ([AuthorKey], [AuthorName], [AuthorEmail]) VALUES (4, N'Mark Lewis', N'ml@Lewis.org') INSERT [dbo].[Author] ([AuthorKey], [AuthorName], [AuthorEmail]) VALUES (5, N'Tabitha King', N'King@gmail.com') INSERT [dbo].[Author] ([AuthorKey], [AuthorName], [AuthorEmail]) VALUES (6, N'Neil Hart', N'harttohart@msn.com') INSERT [dbo].[Author] ([AuthorKey], [AuthorName], [AuthorEmail]) VALUES (7, N'Melanie Dodge', N'melanie@gmail.com') INSERT [dbo].[Author] ([AuthorKey], [AuthorName], [AuthorEmail]) VALUES (8, N'Beth Charity', N'charity@yahoo.com') INSERT [dbo].[Author] ([AuthorKey], [AuthorName], [AuthorEmail]) VALUES (9, N'Gerald Norris', N'gnorris@hotmail.com') INSERT [dbo].[Author] ([AuthorKey], [AuthorName], [AuthorEmail]) VALUES (10, N'Abby Kingston', N'ak@kingston.net') SET IDENTITY_INSERT [dbo].[Author] OFF /****** Object: Table [dbo].[Sale] Script Date: 02/12/2012 11:21:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Sale]( [SaleKey] [int] IDENTITY(1,1) NOT NULL, [SaleDate] [date] NULL, [CustomerKey] [int] NULL, CONSTRAINT [PK_Sale] PRIMARY KEY CLUSTERED ( [SaleKey] 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 SET IDENTITY_INSERT [dbo].[Sale] ON INSERT [dbo].[Sale] ([SaleKey], [SaleDate], [CustomerKey]) VALUES (1, CAST(0xEE320B00 AS Date), 1) INSERT [dbo].[Sale] ([SaleKey], [SaleDate], [CustomerKey]) VALUES (2, CAST(0xEF320B00 AS Date), 2) INSERT [dbo].[Sale] ([SaleKey], [SaleDate], [CustomerKey]) VALUES (3, CAST(0x20330B00 AS Date), 3) INSERT [dbo].[Sale] ([SaleKey], [SaleDate], [CustomerKey]) VALUES (4, CAST(0x0C330B00 AS Date), 4) INSERT [dbo].[Sale] ([SaleKey], [SaleDate], [CustomerKey]) VALUES (5, CAST(0x0F330B00 AS Date), 1) INSERT [dbo].[Sale] ([SaleKey], [SaleDate], [CustomerKey]) VALUES (6, CAST(0x2C330B00 AS Date), 5) INSERT [dbo].[Sale] ([SaleKey], [SaleDate], [CustomerKey]) VALUES (7, CAST(0x8A330B00 AS Date), 6) INSERT [dbo].[Sale] ([SaleKey], [SaleDate], [CustomerKey]) VALUES (8, CAST(0x93330B00 AS Date), 3) INSERT [dbo].[Sale] ([SaleKey], [SaleDate], [CustomerKey]) VALUES (9, CAST(0xDC330B00 AS Date), 7) INSERT [dbo].[Sale] ([SaleKey], [SaleDate], [CustomerKey]) VALUES (10, CAST(0xF6330B00 AS Date), 9) INSERT [dbo].[Sale] ([SaleKey], [SaleDate], [CustomerKey]) VALUES (11, CAST(0x3D340B00 AS Date), 10) INSERT [dbo].[Sale] ([SaleKey], [SaleDate], [CustomerKey]) VALUES (12, CAST(0x3F340B00 AS Date), 3) SET IDENTITY_INSERT [dbo].[Sale] OFF /****** Object: Table [dbo].[ProjectDetail] Script Date: 02/12/2012 11:21:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ProjectDetail]( [ProjectDetailKey] [int] IDENTITY(1,1) NOT NULL, [ProjectKey] [int] NOT NULL, [ProjectTask] [nvarchar](50) NOT NULL, [EmployeeKey] [int] NOT NULL, [ProjectChargedAmount] [money] NULL, [Notes] [nvarchar](max) NULL, [ProjectDetailDate] [date] NULL, CONSTRAINT [PK_ProjectDetail] PRIMARY KEY CLUSTERED ( [ProjectDetailKey] 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 SET IDENTITY_INSERT [dbo].[ProjectDetail] ON INSERT [dbo].[ProjectDetail] ([ProjectDetailKey], [ProjectKey], [ProjectTask], [EmployeeKey], [ProjectChargedAmount], [Notes], [ProjectDetailDate]) VALUES (1, 1, N'Initial Review and Contract', 1, 300.0000, N'lunch and contract', CAST(0xED300B00 AS Date)) INSERT [dbo].[ProjectDetail] ([ProjectDetailKey], [ProjectKey], [ProjectTask], [EmployeeKey], [ProjectChargedAmount], [Notes], [ProjectDetailDate]) VALUES (3, 1, N'First outline', 2, 150.0000, N'reviewed outline with author', CAST(0x07310B00 AS Date)) INSERT [dbo].[ProjectDetail] ([ProjectDetailKey], [ProjectKey], [ProjectTask], [EmployeeKey], [ProjectChargedAmount], [Notes], [ProjectDetailDate]) VALUES (4, 2, N'Conceptual meeting', 1, 200.0000, N'reviewed outline and concpt', CAST(0x64310B00 AS Date)) INSERT [dbo].[ProjectDetail] ([ProjectDetailKey], [ProjectKey], [ProjectTask], [EmployeeKey], [ProjectChargedAmount], [Notes], [ProjectDetailDate]) VALUES (5, 4, N'Initial review', 1, 120.0000, NULL, CAST(0xCD320B00 AS Date)) INSERT [dbo].[ProjectDetail] ([ProjectDetailKey], [ProjectKey], [ProjectTask], [EmployeeKey], [ProjectChargedAmount], [Notes], [ProjectDetailDate]) VALUES (6, 5, N'Initial Review', 1, 130.0000, N'Included dinner', CAST(0xAB320B00 AS Date)) INSERT [dbo].[ProjectDetail] ([ProjectDetailKey], [ProjectKey], [ProjectTask], [EmployeeKey], [ProjectChargedAmount], [Notes], [ProjectDetailDate]) VALUES (7, 1, N'Design review', 3, 85.0000, N'Discuss design Options', CAST(0x59310B00 AS Date)) INSERT [dbo].[ProjectDetail] ([ProjectDetailKey], [ProjectKey], [ProjectTask], [EmployeeKey], [ProjectChargedAmount], [Notes], [ProjectDetailDate]) VALUES (8, 2, N'Copy Editing', 2, 1200.0000, N'editing text', CAST(0x5F320B00 AS Date)) INSERT [dbo].[ProjectDetail] ([ProjectDetailKey], [ProjectKey], [ProjectTask], [EmployeeKey], [ProjectChargedAmount], [Notes], [ProjectDetailDate]) VALUES (9, 1, N'Copy Editing', 2, 500.0000, NULL, CAST(0x6F320B00 AS Date)) INSERT [dbo].[ProjectDetail] ([ProjectDetailKey], [ProjectKey], [ProjectTask], [EmployeeKey], [ProjectChargedAmount], [Notes], [ProjectDetailDate]) VALUES (10, 4, N'Design Review', 4, 120.0000, N'Reviewed Layout', CAST(0x91320B00 AS Date)) INSERT [dbo].[ProjectDetail] ([ProjectDetailKey], [ProjectKey], [ProjectTask], [EmployeeKey], [ProjectChargedAmount], [Notes], [ProjectDetailDate]) VALUES (11, 1, N'Sales Plan', 5, 100.0000, N'Discussed price and sales plan', CAST(0xB7320B00 AS Date)) INSERT [dbo].[ProjectDetail] ([ProjectDetailKey], [ProjectKey], [ProjectTask], [EmployeeKey], [ProjectChargedAmount], [Notes], [ProjectDetailDate]) VALUES (12, 5, N'Copy editing', 2, 400.0000, NULL, CAST(0xB9320B00 AS Date)) INSERT [dbo].[ProjectDetail] ([ProjectDetailKey], [ProjectKey], [ProjectTask], [EmployeeKey], [ProjectChargedAmount], [Notes], [ProjectDetailDate]) VALUES (13, 5, N'Design Review', 4, 450.0000, N'Included hotel', CAST(0xC6320B00 AS Date)) INSERT [dbo].[ProjectDetail] ([ProjectDetailKey], [ProjectKey], [ProjectTask], [EmployeeKey], [ProjectChargedAmount], [Notes], [ProjectDetailDate]) VALUES (14, 6, N'Initial Review', 1, 300.0000, NULL, CAST(0xC6320B00 AS Date)) INSERT [dbo].[ProjectDetail] ([ProjectDetailKey], [ProjectKey], [ProjectTask], [EmployeeKey], [ProjectChargedAmount], [Notes], [ProjectDetailDate]) VALUES (15, 7, N'Initial Review', 1, 250.0000, N'Lunch and contract', CAST(0xD2320B00 AS Date)) INSERT [dbo].[ProjectDetail] ([ProjectDetailKey], [ProjectKey], [ProjectTask], [EmployeeKey], [ProjectChargedAmount], [Notes], [ProjectDetailDate]) VALUES (16, 2, N'Design Review', 3, 450.0000, NULL, CAST(0xCD320B00 AS Date)) INSERT [dbo].[ProjectDetail] ([ProjectDetailKey], [ProjectKey], [ProjectTask], [EmployeeKey], [ProjectChargedAmount], [Notes], [ProjectDetailDate]) VALUES (17, 4, N'Sales Plan', 4, 200.0000, NULL, CAST(0xCE320B00 AS Date)) SET IDENTITY_INSERT [dbo].[ProjectDetail] OFF /****** Object: Table [dbo].[ProjectAuthor] Script Date: 02/12/2012 11:21:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ProjectAuthor]( [AuthorKey] [int] NOT NULL, [ProjectKey] [int] NOT NULL, CONSTRAINT [PK_ProjectAuthor] PRIMARY KEY CLUSTERED ( [AuthorKey] ASC, [ProjectKey] 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].[ProjectAuthor] ([AuthorKey], [ProjectKey]) VALUES (1, 1) INSERT [dbo].[ProjectAuthor] ([AuthorKey], [ProjectKey]) VALUES (2, 2) INSERT [dbo].[ProjectAuthor] ([AuthorKey], [ProjectKey]) VALUES (3, 2) INSERT [dbo].[ProjectAuthor] ([AuthorKey], [ProjectKey]) VALUES (4, 4) INSERT [dbo].[ProjectAuthor] ([AuthorKey], [ProjectKey]) VALUES (5, 4) INSERT [dbo].[ProjectAuthor] ([AuthorKey], [ProjectKey]) VALUES (6, 4) INSERT [dbo].[ProjectAuthor] ([AuthorKey], [ProjectKey]) VALUES (7, 5) INSERT [dbo].[ProjectAuthor] ([AuthorKey], [ProjectKey]) VALUES (8, 6) INSERT [dbo].[ProjectAuthor] ([AuthorKey], [ProjectKey]) VALUES (9, 7) INSERT [dbo].[ProjectAuthor] ([AuthorKey], [ProjectKey]) VALUES (10, 7) /****** Object: Table [dbo].[EBook] Script Date: 02/12/2012 11:21:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[EBook]( [EBookKey] [int] IDENTITY(1,1) NOT NULL, [EbookTitle] [nvarchar](255) NOT NULL, [EbookPrice] [money] NOT NULL, [EbookDescription] [nvarchar](255) NOT NULL, [ProjectKey] [int] NOT NULL, CONSTRAINT [PK_EBook] PRIMARY KEY CLUSTERED ( [EBookKey] 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 SET IDENTITY_INSERT [dbo].[EBook] ON INSERT [dbo].[EBook] ([EBookKey], [EbookTitle], [EbookPrice], [EbookDescription], [ProjectKey]) VALUES (1, N'Wordpress Made Easy', 33.5000, N'All you need to know to get started with word Press', 1) INSERT [dbo].[EBook] ([EBookKey], [EbookTitle], [EbookPrice], [EbookDescription], [ProjectKey]) VALUES (2, N'Snacks for Gamers', 15.7000, N'How to keep from dying of malnutrition while beating Halo', 2) INSERT [dbo].[EBook] ([EBookKey], [EbookTitle], [EbookPrice], [EbookDescription], [ProjectKey]) VALUES (3, N'SQL Server 2008 R2', 65.5000, N'A complete guide ', 4) INSERT [dbo].[EBook] ([EBookKey], [EbookTitle], [EbookPrice], [EbookDescription], [ProjectKey]) VALUES (4, N'SQL', 35.7500, N'All the SQL you will ever need to know', 5) SET IDENTITY_INSERT [dbo].[EBook] OFF /****** Object: Table [dbo].[SaleDetail] Script Date: 02/12/2012 11:21:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[SaleDetail]( [SaleDetailKey] [int] IDENTITY(1,1) NOT NULL, [SaleKey] [int] NULL, [EbookKey] [int] NULL, [Quantity] [int] NULL, [Discount] [decimal](3, 2) NULL, CONSTRAINT [PK_SaleDetail] PRIMARY KEY CLUSTERED ( [SaleDetailKey] 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 SET IDENTITY_INSERT [dbo].[SaleDetail] ON INSERT [dbo].[SaleDetail] ([SaleDetailKey], [SaleKey], [EbookKey], [Quantity], [Discount]) VALUES (1, 1, 1, 3, NULL) INSERT [dbo].[SaleDetail] ([SaleDetailKey], [SaleKey], [EbookKey], [Quantity], [Discount]) VALUES (2, 2, 1, 1, NULL) INSERT [dbo].[SaleDetail] ([SaleDetailKey], [SaleKey], [EbookKey], [Quantity], [Discount]) VALUES (3, 3, 1, 30, CAST(0.05 AS Decimal(3, 2))) INSERT [dbo].[SaleDetail] ([SaleDetailKey], [SaleKey], [EbookKey], [Quantity], [Discount]) VALUES (4, 4, 1, 5, NULL) INSERT [dbo].[SaleDetail] ([SaleDetailKey], [SaleKey], [EbookKey], [Quantity], [Discount]) VALUES (5, 4, 2, 1, NULL) INSERT [dbo].[SaleDetail] ([SaleDetailKey], [SaleKey], [EbookKey], [Quantity], [Discount]) VALUES (6, 5, 2, 1, NULL) INSERT [dbo].[SaleDetail] ([SaleDetailKey], [SaleKey], [EbookKey], [Quantity], [Discount]) VALUES (7, 6, 1, 15, CAST(0.05 AS Decimal(3, 2))) INSERT [dbo].[SaleDetail] ([SaleDetailKey], [SaleKey], [EbookKey], [Quantity], [Discount]) VALUES (8, 7, 4, 1, NULL) INSERT [dbo].[SaleDetail] ([SaleDetailKey], [SaleKey], [EbookKey], [Quantity], [Discount]) VALUES (9, 8, 4, 25, CAST(0.05 AS Decimal(3, 2))) INSERT [dbo].[SaleDetail] ([SaleDetailKey], [SaleKey], [EbookKey], [Quantity], [Discount]) VALUES (10, 8, 1, 20, CAST(0.05 AS Decimal(3, 2))) INSERT [dbo].[SaleDetail] ([SaleDetailKey], [SaleKey], [EbookKey], [Quantity], [Discount]) VALUES (11, 9, 2, 5, CAST(0.03 AS Decimal(3, 2))) INSERT [dbo].[SaleDetail] ([SaleDetailKey], [SaleKey], [EbookKey], [Quantity], [Discount]) VALUES (12, 9, 1, 1, NULL) INSERT [dbo].[SaleDetail] ([SaleDetailKey], [SaleKey], [EbookKey], [Quantity], [Discount]) VALUES (15, 10, 4, 20, CAST(0.05 AS Decimal(3, 2))) INSERT [dbo].[SaleDetail] ([SaleDetailKey], [SaleKey], [EbookKey], [Quantity], [Discount]) VALUES (16, 10, 1, 20, CAST(0.05 AS Decimal(3, 2))) INSERT [dbo].[SaleDetail] ([SaleDetailKey], [SaleKey], [EbookKey], [Quantity], [Discount]) VALUES (17, 10, 2, 1, NULL) INSERT [dbo].[SaleDetail] ([SaleDetailKey], [SaleKey], [EbookKey], [Quantity], [Discount]) VALUES (18, 11, 3, 2, NULL) INSERT [dbo].[SaleDetail] ([SaleDetailKey], [SaleKey], [EbookKey], [Quantity], [Discount]) VALUES (19, 12, 3, 30, CAST(0.05 AS Decimal(3, 2))) INSERT [dbo].[SaleDetail] ([SaleDetailKey], [SaleKey], [EbookKey], [Quantity], [Discount]) VALUES (20, 12, 1, 25, CAST(0.05 AS Decimal(3, 2))) SET IDENTITY_INSERT [dbo].[SaleDetail] OFF /****** Object: ForeignKey [FK_EBook_Project] Script Date: 02/12/2012 11:21:24 ******/ ALTER TABLE [dbo].[EBook] WITH CHECK ADD CONSTRAINT [FK_EBook_Project] FOREIGN KEY([ProjectKey]) REFERENCES [dbo].[Project] ([ProjectKEy]) GO ALTER TABLE [dbo].[EBook] CHECK CONSTRAINT [FK_EBook_Project] GO /****** Object: ForeignKey [FK_ProjectAuthor_Author] Script Date: 02/12/2012 11:21:24 ******/ ALTER TABLE [dbo].[ProjectAuthor] WITH CHECK ADD CONSTRAINT [FK_ProjectAuthor_Author] FOREIGN KEY([AuthorKey]) REFERENCES [dbo].[Author] ([AuthorKey]) GO ALTER TABLE [dbo].[ProjectAuthor] CHECK CONSTRAINT [FK_ProjectAuthor_Author] GO /****** Object: ForeignKey [FK_ProjectAuthor_Project] Script Date: 02/12/2012 11:21:24 ******/ ALTER TABLE [dbo].[ProjectAuthor] WITH CHECK ADD CONSTRAINT [FK_ProjectAuthor_Project] FOREIGN KEY([ProjectKey]) REFERENCES [dbo].[Project] ([ProjectKEy]) GO ALTER TABLE [dbo].[ProjectAuthor] CHECK CONSTRAINT [FK_ProjectAuthor_Project] GO /****** Object: ForeignKey [FK_ProjectDetail_Employee] Script Date: 02/12/2012 11:21:24 ******/ ALTER TABLE [dbo].[ProjectDetail] WITH CHECK ADD CONSTRAINT [FK_ProjectDetail_Employee] FOREIGN KEY([EmployeeKey]) REFERENCES [dbo].[Employee] ([EmployeeKey]) GO ALTER TABLE [dbo].[ProjectDetail] CHECK CONSTRAINT [FK_ProjectDetail_Employee] GO /****** Object: ForeignKey [FK_ProjectDetail_Project] Script Date: 02/12/2012 11:21:24 ******/ ALTER TABLE [dbo].[ProjectDetail] WITH CHECK ADD CONSTRAINT [FK_ProjectDetail_Project] FOREIGN KEY([ProjectKey]) REFERENCES [dbo].[Project] ([ProjectKEy]) GO ALTER TABLE [dbo].[ProjectDetail] CHECK CONSTRAINT [FK_ProjectDetail_Project] GO /****** Object: ForeignKey [FK_Sale_Customer] Script Date: 02/12/2012 11:21:24 ******/ ALTER TABLE [dbo].[Sale] WITH CHECK ADD CONSTRAINT [FK_Sale_Customer] FOREIGN KEY([CustomerKey]) REFERENCES [dbo].[Customer] ([CustomerKey]) GO ALTER TABLE [dbo].[Sale] CHECK CONSTRAINT [FK_Sale_Customer] GO /****** Object: ForeignKey [FK_SaleDetail_EBook] Script Date: 02/12/2012 11:21:24 ******/ ALTER TABLE [dbo].[SaleDetail] WITH CHECK ADD CONSTRAINT [FK_SaleDetail_EBook] FOREIGN KEY([EbookKey]) REFERENCES [dbo].[EBook] ([EBookKey]) GO ALTER TABLE [dbo].[SaleDetail] CHECK CONSTRAINT [FK_SaleDetail_EBook] GO /****** Object: ForeignKey [FK_SaleDetail_Sale] Script Date: 02/12/2012 11:21:24 ******/ ALTER TABLE [dbo].[SaleDetail] WITH CHECK ADD CONSTRAINT [FK_SaleDetail_Sale] FOREIGN KEY([SaleKey]) REFERENCES [dbo].[Sale] ([SaleKey]) GO ALTER TABLE [dbo].[SaleDetail] CHECK CONSTRAINT [FK_SaleDetail_Sale] GO
The questions
Create a view that shows the ebook title, its author(s) and its price.
What was the total cost of producing the book Wordpess made easy?"
What, if any, royalties are due the author Robert Jacobs? Royalties are based on the author's percent of sales, but all advances must be paid back before they receive any additional money). (
What is the average time in months for a project from start date to completion?
Are there any customers who have never purchased a book? If there are, delete that customer
Increase the price of the ebook "Snacks for Gamers" by 3%.
How many copies of the ebook "SQL Server 2008 R2" have been sold?
What is the average charge for services by the editorial staff?
what is the total charged by each editor?
Which employees have charged more than 1000 dollars total?
Add a new customer and a purchase to the database.
Create a function that can be used to determine the total amount for a sale detail.
Create a stored procedure that takes the sale key for a parameter and returns a reciept with the sale information, the details and totals.
It is a business rule of the database that any sale item with a quantity of 15 or above gets a 5% discount. Create a trigger to enforce this.
add a table that is a child to project. Its purpose is to record meetings among the staff concerning project status. The table should have an identity for a key, a date and a topic line, and an xml column with this structure. Below is an example of the xml document and a schema for it. Before creating the table create a new schema collection to validate the xml field.
The xml document
<?xml version="1.0" encoding="utf-8"?> <meeting xmlns="http://www.BitbyBitEbooks.com/meeting"> <head> <present>Brown</present> <present>Garner</present> <present>Peterson</present> </head> <body> <discussion> The book has a lot of grammatical and writing errors. It is going to take work and time to clear them up. </discussion> <issues> <issue>Grammatical Errors</issue> <issue>timeLine</issue> </issues> <tasks> <task>contact author</task> <task>Revise timeline</task> </tasks> </body> </meeting>
The schema
<?xml version="1.0" encoding="utf-8"?> <xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://www.BitbyBitEbooks.com/meeting" xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="meeting"> <xs:complexType> <xs:sequence> <xs:element name="head"> <xs:complexType> <xs:sequence> <xs:element maxOccurs="unbounded" name="present" type="xs:string" /> </xs:sequence> </xs:complexType> </xs:element> <xs:element name="body"> <xs:complexType> <xs:sequence> <xs:element name="discussion" type="xs:string" /> <xs:element name="issues"> <xs:complexType> <xs:sequence> <xs:element maxOccurs="unbounded" name="issue" type="xs:string" /> </xs:sequence> </xs:complexType> </xs:element> <xs:element name="tasks"> <xs:complexType> <xs:sequence> <xs:element maxOccurs="unbounded" name="task" type="xs:string" /> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:schema>
Make a login and role for authors.
Create an Author role. authors should be able see projects, but not project details. They should be able to see the list of ebooks, and sale and sale detail information. But they shouldn't be able to add or change anything. Add the author user to the role.
What I am looking for
The project is worth 50 points. I want to see the SQL that would answer the question or complete the request(as in stored procedures and functions.) There are about 20 separate tasks, so each task is worth about 2 points with 10 points assigned to the more difficult tasks such as the stored procedure and trigger.
the questions do no have to be done in the order listed. Just let me know which one the code is for.
p>If you worked on this as a group, you only need to turn in one copy for the whole group. Just be sure to include all the names on the document.To turn in
as always paste all the code and questions into a Google doc and share it with spconger@gmail.com
Saturday, February 11, 2012
Assignment 12
Creating Logins, Users and roles
This is fairly simple
1. Create a new login called CustLogin which has a password of "P@ssw0rd1" and the default database "Automart."
2. Create a new User that uses the login CustLogin called CustUser with a default schema of Customer
3. Create a role called CustomerRole.
4. Grant select on the Customer schema to the role Customer Role
5. Use the stored procedure to add the user CustUser to the Role Customer
You should try to log on as the new user and see if everything is working as it should. You should only be able to see those tables and objects that belong to the customer schema. You should only be able to select, not insert update or delete
What I am looking for
Each step is worth 2 points for a total of 10
What to turn in
copy the code into a Google doc and share it with spconger@gmail.com
Assignment 11
Xml
Outputting data as XML
1. Out put person as xml raw as elements with a root of 'People'
2. Do an inner join bwtween Person and employee and output the results as xml auto with a root element of "Employees"
Querying XML
3. Select all the fields from the Customer.Autoservice table. Use the xml query() method on the Service description to only return the contents of the description element. Don't forget to account for the namespace.
XML Schema and XML data type
4. Alter the table InspectionDetail that we created in an earlier assignment and drop the xml column Description.
5. Create a new xml schema collection called "DescriptionSchema" using the following schema:
<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified"
elementFormDefault="qualified"
targetNamespace="http://www.automart.com/inspectionDescription"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="inspectiondescription">
<xs:complexType>
<xs:sequence>
<xs:element name="issue" type="xs:string" />
<xs:element name="details">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded"
name="detail"
type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
6. Now alter the table again, to add the xml column back in, but with schema added:
Description xml (DescriptionSchema),
7. Insert an new record into the inspection and then the inspectionDetail. You can make up the values, but for the column description enter the following xml:
<?xml version="1.0" encoding="utf-8"?> <inspectiondescription xmlns="http://www.automart.com/inspectionDescription" > <issue> cleanliness of the work area </issue> <details> <detail>discarded oil rags on counter</detail> <detail>Open pan of used oil left beside pit where it could be spilled</detail> <detail>Tools not cleaned immediately after use</detail> </details> </inspectiondescription>
What I am looking for
1-5 are worth 1 point each, 6 is worth 3 and 7 is worth 2 for a total of 10
To Turn in
copy all the code to a google doc and share with spconger@gmail.com
Assignment 10
More Stored Procedures and Triggers
Stored Procedure
Create a stored procedure to add a new customer. To do this add person, vehice and registerd customer info. First check to maks sure the customer does not already exist. If they do just print out a message saying they already exist; If they don't exist insert into all the tables necessary. Put the inserts in a transaction and a try catch. You want all the inserts to happen or none of them.
Remember you will need to find the newly generated person key. You can use @@identity or IDENT_Current to find it.
The procedure will create a temporary password that uses the first letter of the first name, the whole last name, and the first 3 characters of their vehicle License number. If you feel clever you can seperate this into a function and then call the function in the stored procedure
Use the procedure to enter a customer
Try Entering the same customer. You should get the message that the customer exists
Trigger
Create a trigger for insert on the table CustomerSurvey. If a customer rates the service at 2 or less, copy the record to a table called "Problem". This table should have the same structure as the CustomerSurvey table. The trigger should check to make sure the customer survey table exists. If it doesn't it should create it.
Enter a survey record with a rating of 1 and see if the trigger works. Also enter one with a rating of 3 to make sure that it isn't written to the problem table.
What I am Looking for
The stored procedure code will be worth 5 points
1 point for checking to see if the customer exists
1 point for creating a transactions
1 point for inserting the records
2 points for creating the temporary password
The trigger will also be worth 5 points
1 point for checking that the table exists
1 point for creating the table
1 point for checking the value of the rating
1 point for copying the results to the new table
1 point for a working trigger
To Turn in
Copy the code for the trigger and the stored procedure into a google doc and share with spconger@gmail.com
Wednesday, February 8, 2012
Assignment 9
Functions
We are going to create just one function. The purpose of the function is to total the cost of each service provided for a car. This has to take into account the price of the service, the discount amount and the tax amount. Here are a couple of queries that use the function. You can use these to test the function
Select ServicePrice, vs.VehicleServiceID, VehicleID, DiscountPercent, TaxPercent, dbo.fx_TotalDue(ServicePrice, DiscountPercent, TaxPercent) as Total From Employee.VehicleServiceDetail vsd inner Join Employee.VehicleService vs on vsd.VehicleServiceID=vs.VehicleServiceID inner join Customer.AutoService a on a.AutoServiceID=vsd.AutoServiceID Select SUM( dbo.fx_TotalDue(ServicePrice, DiscountPercent, TaxPercent)) as Total From Employee.VehicleServiceDetail vsd inner Join Employee.VehicleService vs on vsd.VehicleServiceID=vs.VehicleServiceID inner join Customer.AutoService a on a.AutoServiceID=vsd.AutoServiceID Where vsd.VehicleServiceID=4
Simple Stored procedures
In a sense this is just redoing the views to add a parameter.
Make a stored procedure that returns the employee information. It will take the parameter @Locationname
Make one that returns all the names, vehicle information and email for registered customers. It will take a parameter of @VehicleLicense
Assignment 8
Views
1. Create a view that shows Employee information. The view should include the employee's first name, last name, hire date and location name. You should alias each column.
2. Create a view that shows all the information about a registered customer. It should include their name, their vehicle licenses and makes and their email.
Indexes
3. Create a non clustered index on LastName in Person
4. Create a non clustered index on the License number in Vehicle
5. Create a non clustered index on AutoServiceID in VehicleServiceDetail
6. Create a non clustered index on ServiceDate in VehicleService
Tuesday, February 7, 2012
DVD Diagram
Shorthand for Normalization
These three rules will get you through most of database design
1. Every table should be only about one thing. Split out every separate topic into its own entity
2. any multivalued attributes should be removed to their own entity
3. Whenever you have a many to many relationship you must resolve the relationship into two one to many relationships by adding a linking table
Here is the diagram for the DVD we did in class. Click on it to get a larger image.
LINQ
I will add to this in the near future and provide the steps we took in SQL Server and the link designer
for now here is the LINQ code
protected void Button1_Click(object sender, EventArgs e)
{
AutomartDataClassesDataContext dc = new AutomartDataClassesDataContext();
var ourCustomers = from c in dc.RegisteredCustomers
orderby c.Person.LastName
where c.Person.LastName=="Anderson"
select new {c.Person.LastName,
c.Person.FirstName,c.Email};
GridView1.DataSource = ourCustomers.ToList();
GridView1.DataBind();
}
Monday, February 6, 2012
Creating and altering Tables
--Creating and altering tables
use master
go
Create database grading
use grading
Create Table Student
(
StudentID nchar(12) primary key,
StudentLastName nvarchar(255),
StudentFirstName nvarchar(255)
)
Create Table Course
(
CourseNumber nchar(7),
CourseName nvarchar(255) not null,
CourseCredits int not null,
Constraint pk_Course Primary Key (CourseNumber)
)
Create Table Section
(
SectionNumber nchar(4) not null,
CourseNumber nchar(7) Foreign Key
References Course(CourseNumber),
SectionQuarter nvarchar(7) not null,
SectionYear char(4) not null
)
Drop Table Section
alter Table Section
Add Constraint Pk_Section Primary Key(SectionNumber)
Create Table Roster
(
RosterID int identity(1,1),
StudentID nchar(12),
SectionNumber nchar(4),
Grade Decimal(3,2),
Notes xml,
Constraint pk_Roster primary key(RosterID),
Constraint FK_Student Foreign key (StudentID)
References Student(StudentID)
)
Alter Table Roster
Add constraint FK_Section Foreign Key(SectionNumber)
References Section(SectionNumber)
Alter Table Roster
Add Constraint Ck_Grade Check(Grade between 0 and 4)
Alter Table Section
Add Constraint ch_Quarter
Check (SectionQuarter in ('Fall', 'Winter', 'Spring', 'Summer'))
Insert into Student(StudentID, StudentLastName, StudentFirstName)
Values('919-00-9876','Smith','John')
Insert into Course(CourseNumber, CourseName, CourseCredits)
Values('ITC222','SQL',5)
Insert into Section(SectionNumber, CourseNumber,
SectionQuarter, SectionYear)
Values('3208','ITC222','Winter','2012')
--these will cause errors because of the check constraints
Insert into Section(SectionNumber, CourseNumber,
SectionQuarter, SectionYear)
Values('3209','ITC222','Jan','2012')
Insert into Roster(StudentID, SectionNumber, Grade, Notes)
Values ('919-00-9876','3208',5,null)
Create Table Student
(
StudentKey int identity(1,1) primary key,
StudentID nchar(12) unique
)
Wednesday, February 1, 2012
Here are a couple of examples of use case written documentation. These are from a program and database I wrote to track short term certificates
Here is the use Case diagram. The documentation is below:
Enter Certificate Information Use Case
Trigger:
First use of database-- adding new Certificate
Steps:
1. Enter Certificates into database (through Form)
2. Enter Courses required for certificate
a. Select course from course table
b. add foreign key to Certificate Course table
3. Review and confirm
Terminal Condition:
List of certificates with required courses in database
List Students Getting Certificates Use Case
Precondition:
Students and grades imported into database
Trigger:
Quarterly Report on Certificate completions
Constraints:
Must have a 2.0 or greater grade point average (or cert specific average)
Steps:
1. Run query for each which students have taken all the courses for one certificate (Each certificate will have to be queries separately)
2. Average the grades of all the students who have completed the certificate
3. Select only those who have an average greater than 2.0
4. Write them into a Certificates received (or temp?) table
5. Run the queries again for each possible certificate
6. Summarize the results of all queries in a report (from the Certificates Received table)
Terminal Condition:
Quarterly report of Certificates completed for given quarter
Assignment 4 Notes
It is not possible to assign the results of the web Service method directly to the DataSet, (though I swear I did this when I previewed the assignment some months ago.) It must be read as an XML file and then loaded into the DataSet. Here is a bit of code for that. Then you can bind the DataSet to the GridView
XmlNode node = zip.GetInfoByCity(txtCity.Text);
DataSet ds = new DataSet();
XmlNodeReader reader = new XmlNodeReader(node);
ds.ReadXml(reader);
Insert Update Delete
--inserts, updates and deletes
Use CommunityAssist
Insert into Person (LastName, FirstName)
Values('Smith', 'John'),
('Doe', 'Jane')
Select * from Person
Use Master
Drop table Employee.VehicleService
Create table Person2
(
FirstName nvarchar(255),
LastName Nvarchar(255)
)
--bulk insert of sorts
Insert into Person2(FirstName, LastName)
Select FirstName, LastName From Person
Select * From Person2
--creating a temporary table
Select LastName, Firstname
into #SpecialPeople
From Person
Select * From #SpecialPeople
--update with multiple fields an
--update should always
--have a where clause
update Person2
Set LastName='Anders',
Firstname='bob'
Where LastName='Anderson'
And FirstName='Jay'
Select * From Person2
Drop table Person2
--explicitly using a transaction
--can give you an undo
Begin transaction
--This turns everyone's name the same
Update Person2
Set Firstname='Bob'
Where LastName='zimmerman'
Select * From Person2
Rollback tran-- will undo all transactions\
--since the begin
Commit Tran -- will commit the transaction
--to the database
Use MagazineSubscription
Select * From magazineDetail
--increase all the prices by 20%
Update MagazineDetail
Set SubscriptionPrice=SubscriptionPrice * 1.2
-- Deletions
--can't delete records that
--have child records
Use CommunityAssist
Begin Tran
Delete From Person
Where PersonKey > 51
Select * From Person
Rollback tran
Commit tran
--removes all rows because no
--related records in a child table
Delete from Person2
--does the same as delete
--but more efficient
--doesn't do it row by row
Truncate table Person2
Select * From Person2
--get table information
exec sp_help PersonAddress
--system views
Select * from sys.Databases
Select name from sys.Tables
Select name from sys.procedures
Select * From Sys.columns
Subscribe to:
Posts (Atom)