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