Sequence Diagrams

Here is the login Sequence

Here is the game sequence diagram

Stored Procedures

use Community_Assist

--stored procedures
--script
--parameterized view
go
Create proc usp_CityProc
@City nvarchar(255)
As
Select PersonLastname [Last],
personFirstName [first],
PersonEmail Email,
PersonAddressCity City
From Person p
inner join PersonAddress pa
on p.PersonKey=pa.PersonKey
Where PersonAddressCity=@City

exec usp_CityProc 'Kent'

--more complicated procedure
--3 different stages (just the inserts, try catch error trapping
--check to see if already exits
--Register a new user
--insert into person
--when insert into person you need to hash the password
--insert into personAddress
--insert into contacts
go
Create proc usp_RegisterMark2
@lastName nvarchar(255),
@firstName nvarchar(255),
@Email nvarchar(255),
@Password nvarchar(255),
@Apartment nvarchar(255) =null,
@Street nvarchar(255),
@City nvarchar(255)= 'Seattle',
@State nchar(2) ='WA',
@Zip nchar(10),
@home nvarchar(255)= null,
@work nvarchar(255) =null
As
--get the random number seed
Declare @seed int = dbo.fx_GetSeed()
--Declare variable to store the hash
Declare @hashed varbinary(500)
--hash the plain text password
set @hashed = dbo.fx_HashPassword(@seed, @password)
--insert Person
Insert into Person(PersonLastName,
PersonFirstName, PersonEmail, PersonPassWord,
PersonEntryDate, PersonPassWordSeed)
Values(@lastName,@firstName,@email,
@hashed,GetDate(),@seed)
--get the most recent identity from Person
Declare @PersonKey int = Ident_Current('Person')
--insert into PersonAddress
Insert into PersonAddress (PersonAddressApt,
 PersonAddressStreet, PersonAddressCity, 
 PersonAddressState, PersonAddressZip, PersonKey)
 Values(@Apartment,@Street, @City,@state,@zip, @PersonKey)
 --insert into contact
 if @home is not null
 begin
    Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@home, 1, @PersonKey)
 end
  if @work is not null
 begin
    Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@Work, 2, @PersonKey)
 end
 go

 exec usp_RegisterMark2
@lastName='Branson', 
@firstName='Martin', 
@Email='bmartin@gmail.com', 
@Password='BransonPass', 
@Street='1001 North Elsewhere', 
@Zip='98100', 
@home='2065552314'

Select * from Contact

--second version with try catch
--transactions
go
Alter proc usp_RegisterMark2
@lastName nvarchar(255),
@firstName nvarchar(255),
@Email nvarchar(255),
@Password nvarchar(255),
@Apartment nvarchar(255) =null,
@Street nvarchar(255),
@City nvarchar(255)= 'Seattle',
@State nchar(2) ='WA',
@Zip nchar(10),
@home nvarchar(255)= null,
@work nvarchar(255) =null
As
--get the random number seed
Declare @seed int = dbo.fx_GetSeed()
--Declare variable to store the hash
Declare @hashed varbinary(500)
--hash the plain text password
set @hashed = dbo.fx_HashPassword(@seed, @password)
--begin transaction
begin tran
--begin try
Begin try
--insert Person
Insert into Person(PersonLastName,
PersonFirstName, PersonEmail, PersonPassWord,
PersonEntryDate, PersonPassWordSeed)
Values(@lastName,@firstName,@email,
@hashed,GetDate(),@seed)
--get the most recent identity from Person
Declare @PersonKey int = Ident_Current('Person')
--insert into PersonAddress
Insert into PersonAddress (PersonAddressApt,
 PersonAddressStreet, PersonAddressCity, 
 PersonAddressState, PersonAddressZip, PersonKey)
 Values(@Apartment,@Street, @City,@state,@zip, @PersonKey)
 --insert into contact
 if @home is not null
 begin
    Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@home, 1, @PersonKey)
 end
  if @work is not null
 begin
    Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@Work, 2, @PersonKey)
 end
 Commit tran --write the transaction
 End try --end the try
 Begin Catch --catch an error
 Rollback tran --undo anything that has been done
 print Error_Message()
 End catch
 go

exec usp_RegisterMark2
@lastName='Branson', 
@firstName='Martin', 
@Email='bmartin@gmail.com', 
@Password='BransonPass', 
@Street='1001 North Elsewhere', 
@Zip='98100', 
@home='2065552314'

--third and final version
--we will check to see if person exists
go
Alter proc usp_RegisterMark2
@lastName nvarchar(255),
@firstName nvarchar(255),
@Email nvarchar(255),
@Password nvarchar(255),
@Apartment nvarchar(255) =null,
@Street nvarchar(255),
@City nvarchar(255)= 'Seattle',
@State nchar(2) ='WA',
@Zip nchar(10),
@home nvarchar(255)= null,
@work nvarchar(255) =null
As
if Not exists
  (Select * from Person
    Where PersonEmail=@Email
 And PersonLastName = @LastName
 And PersonFirstName=@FirstName)
Begin --begin if
--get the random number seed
Declare @seed int = dbo.fx_GetSeed()
--Declare variable to store the hash
Declare @hashed varbinary(500)
--hash the plain text password
set @hashed = dbo.fx_HashPassword(@seed, @password)
--begin transaction
begin tran
--begin try
Begin try
--insert Person
Insert into Person(PersonLastName,
PersonFirstName, PersonEmail, PersonPassWord,
PersonEntryDate, PersonPassWordSeed)
Values(@lastName,@firstName,@email,
@hashed,GetDate(),@seed)
--get the most recent identity from Person
Declare @PersonKey int = Ident_Current('Person')
--insert into PersonAddress
Insert into PersonAddress (PersonAddressApt,
 PersonAddressStreet, PersonAddressCity, 
 PersonAddressState, PersonAddressZip, PersonKey)
 Values(@Apartment,@Street, @City,@state,@zip, @PersonKey)
 --insert into contact
 if @home is not null
 begin
    Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@home, 1, @PersonKey)
 end
  if @work is not null
 begin
    Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@Work, 2, @PersonKey)
 end
 Commit tran --write the transaction
 End try --end the try
 Begin Catch --catch an error
 Rollback tran --undo anything that has been done
 print Error_Message()
 End catch
 End--end if
 Else --if person does exist
 Begin
 print 'Already in database'
 End
 go

 exec usp_RegisterMark2
@lastName='Branson', 
@firstName='Martin', 
@Email='bmartin@gmail.com', 
@Password='BransonPass', 
@Street='1001 North Elsewhere', 
@Zip='98100', 
@home='2065552314'

--create a stored procedure to
--update address information
go
Create proc usp_UpdateAddress
@PersonAddressApt nvarchar(255), 
@PersonAddressStreet nvarchar(255), 
@PersonAddressCity nvarchar(255), 
@PersonAddressState nvarchar(255), 
@PersonAddressZip nvarchar(255), 
@PersonKey int
As
Begin tran
Begin try
Update PersonAddress
Set PersonAddressApt=@personAddressApt,
PersonAddressStreet=@personAddressStreet,
PersonAddressCity=@PersonAddressCity,
PersonAddressState = @PersonAddressState,
PersonAddressZip=@PersonAddressZip
Where PersonKey = @PersonKey
Commit tran
End Try
Begin Catch
Rollback tran
print Error_message()
End Catch

Select * from PersonAddress

Exec usp_UpdateAddress
@PersonAddressApt='10A',
@PersonAddressStreet='1001 North Mann Street', 
@PersonAddressCity='Seattle', 
@PersonAddressState='Wa', 
@PersonAddressZip='98001', 
@PersonKey=1

Functions and Temporary Tables

use Community_Assist
--temporary tables
Create table #TempTable
(
   PersonKey int,
   personLastName nvarchar(255),
   PersonFirstName nvarchar(255),
   PersonEmail nvarchar(255)
)

Insert into #tempTable (PersonKey, personLastName,PersonFirstName, PersonEmail)
Select Personkey, PersonLastName, PersonFirstName, PersonEmail
From Person 

Select * from #TempTable

Create table ##TempTable2
(
   PersonKey int,
   personLastName nvarchar(255),
   PersonFirstName nvarchar(255),
   PersonEmail nvarchar(255)
)
Insert into ##tempTable2 (PersonKey, personLastName,PersonFirstName, PersonEmail)
Select Personkey, PersonLastName, PersonFirstName, PersonEmail
From Person 

--functions--scalar
go
Create function fx_Cube
(@number int)
returns int
As
Begin
Declare @cube int
Set @Cube = @number * @number * @number
return @Cube
End
Go

Select EmployeeKey, dbo.fx_Cube(EmployeeKey) as cubed from Employee

Select * from Person
go
/* this one doesn't work for some reason
Alter Function fx_Address
(@Address nvarchar(255),
@apartment nvarchar(255),
@City nvarchar(255),
@State nvarchar(255),
@Zip nvarchar(255))
returns nvarchar(255)
As
Begin
Declare @complete nvarchar(255)

if @Apartment is not null
   Begin
   set @complete = @address + ' ' + @Apartment + ' '
   + @city + ', ' + @state + ' ' + @zip
   End
Else 
   Begin
   set @complete = @address + ' ' + @city + ', ' + @state + ' ' + @zip
   End
return @Complete
End */

go
go
Alter function fx_OneLineAddress
(@Apartment nvarchar(255),
@Street  nvarchar(255),
@City  nvarchar(255),
@State nchar(2),
@Zip nchar(9))
returns nvarchar(255)
as
Begin
Declare @address nvarchar(255)
if @Apartment is null
   Begin
     Set @Address=@Street + ', ' + @City + ', ' + @state + ' ' + @zip
   End
else
   Begin
      Set @Address= @Street + ', ' + @Apartment + ', ' + @City + ', ' + @state + ' ' + @zip
   End
   return @Address
End
go

Select PersonLastName, PersonFirstName, 
dbo.fx_oneLineAddress(
PersonAddressApt, 
PersonAddressStreet, 
PersonAddressCity, 
PersonAddressState, 
PersonAddressZip) as [Address]
From Person p
inner Join PersonAddress pa
on p.PersonKey = pa.PersonKey
go

Create function fx_RequestMax
(@GrantTypeKey int,
@RequestAmount money)
returns money
As
Begin
Declare @Max money
Select @Max=GrantTypeMaximum from GrantType
Where GrantTypeKey = @GrantTypeKey
Declare @Differance money
set @Differance = @max - @RequestAmount
Return @Differance
End
go
Select GrantRequestKey, GrantRequestDate, GrantRequestAmount,
dbo.fx_RequestMax(GrantTypeKey, GrantRequestAmount) as Diff
From GrantRequest

Code Assignment Example

Artist Class

package com.spconger;

public class Artist {

 private String artistName;
 private String artistURL;
 private String artistInfo;
 
 public String getArtistName() {
  return artistName;
 }
 public void setArtistName(String artistName) {
  this.artistName = artistName;
 }
 public String getArtistURL() {
  return artistURL;
 }
 public void setArtistURL(String artistURL) {
  this.artistURL = artistURL;
 }
 public String getArtistInfo() {
  return artistInfo;
 }
 public void setArtistInfo(String artistInfo) {
  this.artistInfo = artistInfo;
 }
}

Here is the fan Class

package com.spconger;

import java.util.ArrayList;

public class Fan {
 private String Name;
 private String Email;
 private ArrayList<Artist>followArtists;
 private ArrayList<String> genres;
 private ArrayList<String> alerts;
 
 public Fan()
 {
  followArtists = new ArrayList<Artist>();
 }
 public String getName() {
  return Name;
 }
 public void setName(String name) {
  Name = name;
 }
 public String getEmail() {
  return Email;
 }
 public void setEmail(String email) {
  Email = email;
 }
 public ArrayList<Artist> getFollowArtists() {
  return followArtists;
 }
 
 public void AddArtist(Artist a){
  followArtists.add(a);
 }
 
 public void RemoveArtist(Artist a){
  followArtists.remove(a);
 }
 
 
}

Here is the Program class where I call the classes and methods

package com.spconger;

import java.util.ArrayList;

public class Program {

 public static void main(String[] args) {
  Fan f = new Fan();
  f.setName("Joe Demaggio");
  f.setEmail("JD@gmail.com");
  Artist a1 = new Artist();
  a1.setArtistName("ACDC");
  f.AddArtist(a1);
  Artist a2 = new Artist();
  a2.setArtistName("Bob Dylan");
  f.AddArtist(a2);
  Artist a3 = new Artist();
  a3.setArtistName("Ozzy Osborne");
  f.AddArtist(a3);
  
  ArrayList<Artist>artists = f.getFollowArtists();
  
  for(Artist a : artists){
   System.out.println(a.getArtistName());
  }
  System.out.println();
  f.RemoveArtist(a1);
  ArrayList<Artist>artists1 = f.getFollowArtists();
  
  for(Artist a : artists1){
   System.out.println(a.getArtistName());
  }
  
  
  
 }

}

Set operators and Inserts, Updates and Deletes

--set operators
--union joins two different tables
--both sides of the union need to have a similar structure
use Community_Assist
Select PersonLastName, PersonFirstName, PersonEmail
From Person
Union
Select EmployeeLastName, EmployeeFirstName, EmployeeEmail
From MetroAlt.dbo.Employee


Select PersonLastName, PersonFirstName, PersonEmail, PersonAddressCity
From Person p
Inner Join PersonAddress pa
ON p.PersonKey=pa.PersonKey
Union
Select EmployeeLastName, EmployeeFirstName, EmployeeEmail, EmployeeCity
From MetroAlt.dbo.Employee

--intersect returns all the values that are in both
--selects
Select  PersonAddressCity
From PersonAddress pa
Intersect
Select EmployeeCity
From MetroAlt.dbo.Employee

Select EmployeeCity
From MetroAlt.dbo.Employee
intersect
Select  PersonAddressCity
From PersonAddress pa

--Except returns only those values that are in 
--the first query that are NOT in the second

Select  PersonAddressCity
From PersonAddress pa
Except
Select EmployeeCity
From MetroAlt.dbo.Employee

Select EmployeeCity
From MetroAlt.dbo.Employee
except
Select  PersonAddressCity
From PersonAddress pa

--modify data

--basic insert
Insert into Person(PersonLastName, PersonFirstName, PersonEmail,PersonEntryDate)
values ('Johnson','Rupert','rj@outlook.com',getDate())

--insert multiple rows
Insert into Person(PersonLastName, PersonFirstName, PersonEmail,PersonEntryDate)
values ('Lexington','Mark','marklex@gmail.com', GetDate()),
('Ford', 'Harrison', 'Hansolo@starwars.com', GetDate())

--create variable for password and seed
Declare @seed int = dbo.fx_getseed()
Declare @password varbinary(500) = dbo.fx_HashPassword(@seed, 'MoonPass')

Insert into Person(PersonLastName, PersonFirstName, 
PersonEmail, PersonPassWord, PersonEntryDate, 
PersonPassWordSeed)
Values('Moon', 'Shadow','shadow@gmail.com', @password, GetDate(),@seed)

--the ident_current function returns the last autonumber (identity) created
--in the database named
Insert into PersonAddress( 
PersonAddressStreet, 
PersonAddressCity, PersonAddressState, 
PersonAddressZip, PersonKey)
Values ('1010 South Street', 'Seattle', 'WA', '98000',IDENT_CURRENT('Person'))



Select * from PersonAddress
--update changes existing data.
--it is one of the most dangerous
--SQL Commands
Update Person
Set PersonFirstName='Jason'
where PersonKey =1 

--begin a manual transaction (allows undo)
Begin tran

Update Person
Set PersonLastName='Smith',
PersonEmail='rs@outlook.com'
Where personKey=130

Select * from Person

Select * from GrantType

Rollback tran --undo transaction
Commit Tran -- write the results to the database

--update everything on purpose
Update GrantType
Set GrantTypeMaximum=GrantTypeMaximum * 1.05,
GrantTypeLifetimeMaximum=GrantTypeLifetimeMaximum * 1.1

Delete From Person where personkey=1 
--won't work because of referential integrity constraints


begin tran
--but it will work on a child table
--this command will delete all the records
--in the personAddress table
Delete from Personaddress
Select * from personAddress
rollback tran

Create table People
(
   lastname nvarchar(255),
   firstname nvarchar(255),
   email nvarchar(255)
)

Insert into people(lastname, firstname, email)
select personlastname, personfirstName, personEmail
from person

Select * from people

Delete from people
Where email ='JAnderson@gmail.com'

--another way to delete all
--the records in a table
Truncate table people

--Drops the whole table
Drop table people

Class Relationships

Cert Tracker Classes

Here are the classes we did together in class

More on Joins.

Here are the whiteboard drawings we did in class. The first is for a CROSS JOIN. in a CROSS JOIN, each row in the first table is matched to every row in the second table. This gives you a result set that shows every possible combination of values. A CROSS JOIN ignores any actual relationships between the tables, that is why they don't have an ON clause to specify the relationship. A CROSS JOIN can be useful for some things, but it is very rare that you will want to use one. In fact, almost never.

An INNER JOIN returns only matching rows, that is only rows where the primary key of the first table is a foreign key in the second table. Any unmatched records are ignored. This is the kind of join you will use at least 90% of the time. It allows you to see all the sales belonging to a particular customer, for instance, or all the people who have donated money.

An OUTER JOIN returns all the records from one side of the relationship and only matching records from the other side. In a LEFT OUTER JOIN the query returns all the records in the first table listed and only matching records in the second. A RIGHT OUTER JOIN reverses that, returning all the records from the second table and only matching records from the first. A FULL OUTER JOIN returns all the records from both tables whether they match or not. OUTER joins are useful for finding records that DON'T match: Customers that don't have orders, Inventory items that no one has ever purchased, etc. Again, only use OUTER JOINS when you are looking for mismatches.

Table Expressions

--table expressions
--sub query in the from clause
--sub queries must be aliased

Select PersonLastName, PersonFirstName, PersonEmail, City
From (Select PersonLastname, PersonFirstName, PersonEmail, PersonAddressCity City
From Person p
inner join PersonAddress pa
on p.PersonKey = pa.PersonKey
where PersonAddressCity='Bellevue') as BellevueResidents

--same but with wildcard
Select *
From (Select PersonLastname, PersonFirstName, PersonEmail, PersonAddressCity City
From Person p
inner join PersonAddress pa
on p.PersonKey = pa.PersonKey
where PersonAddressCity='Bellevue') as BellevueResidents

--another table expression example
Select GrantType, total,  Average
From (Select GrantTypeName GrantType, Sum(GrantRequestAmount) as Total,
Avg(GrantRequestAmount) as Average
From GrantRequest gr
inner join GrantType gt
on gr.GrantTypeKey=gt.GrantTypeKey
Group by GrantTypeName) as TypeTotals

--Common table expressions are similar
--to reqular expressions except
--the subquery is defined first which
--is better logically

With BellevueResidents as
(
 Select PersonLastname, PersonFirstName, PersonEmail, PersonAddressCity City
    From Person p
    inner join PersonAddress pa
    on p.PersonKey = pa.PersonKey
    where PersonAddressCity='Bellevue'
) Select * from BellevueResidents
go
-- a second example
with typeTotals as
(
  Select GrantTypeName GrantType, Sum(GrantRequestAmount) as Total,
  Avg(GrantRequestAmount) as Average
  From GrantRequest gr
  inner join GrantType gt
  on gr.GrantTypeKey=gt.GrantTypeKey
  Group by GrantTypeName
)Select GrantType, Total, Average from TypeTotals

go
--Declare @City nvarchar(255) ='Kent'
Declare @city nvarchar(255)
set @city ='Kent';

With BellevueResidents as
(
 Select PersonLastname, PersonFirstName, PersonEmail, PersonAddressCity City
    From Person p
    inner join PersonAddress pa
    on p.PersonKey = pa.PersonKey
    where PersonAddressCity=@city
) Select * from BellevueResidents

go

--views are basically stored queries-- they usually
--represent "a view" some user has of the data
--they also can be used to obscure the underlying
--structure of the database, because the user
--of a view can only see the aliases for the columns
--and can't see the underlying tables
Create view vw_Employee
As
Select
PersonLastName [Last Name],
PersonFirstName [First Name],
PersonEmail Email,
EmployeeHireDate [Hire Date],
EmployeeAnnualSalary [Annual Salary],
PositionName [Position]
From Person p
inner join Employee e
on p.Personkey=e.PersonKey
inner join EmployeePosition ep
on e.EmployeeKey = ep.EmployeeKey
inner join Position ps
on ep.PositionKey=ps.PositionKey


Go
Select [last name], [first name], Email from vw_Employee
Where [Annual Salary] > 0
order by [Last Name]

--schemabinding makes it so that the
--tables underlying the view
--cannot be changed
--at least not without removing the view
go
Create view vw_Donations with schemabinding
As
Select PersonLastName,
PersonFirstName,
PersonEmail,
DonationDate,
DonationAmount
From dbo.person p
inner join dbo.donation d
on p.PersonKey=d.PersonKey
go
Select * from vw_Donations

go
--this won't work because table bound to view 
Alter table Donation
Drop column DonationDate
go
--a schema, in this context, is a set of objects 
--with common ownership
--dbo, database owner, is the default schema
Create schema managerSchema
go

--here is a view that is owned by the managerSchema
Create view managerSchema.RequestSummary
As
Select GrantTypeName GrantType, Sum(GrantRequestAmount) as Total,
  Avg(GrantRequestAmount) as Average
  From GrantRequest gr
  inner join GrantType gt
  on gr.GrantTypeKey=gt.GrantTypeKey
  Group by GrantTypeName


  go
  --table valued function is a function that
  --returns a table as a result
  --this one takes a parameter @City
  --that must be provided by the user
  Create function fx_GetCities(@city nvarchar(255))
  returns table
  As
  Return
  (
 Select PersonLastname, PersonFirstName, PersonEmail, PersonAddressCity City
    From Person p
    inner join PersonAddress pa
    on p.PersonKey = pa.PersonKey
    where PersonAddressCity=@city
 )

 --using the function
 go
 Select * from dbo.fx_GetCities('Kent')