Sequence Diagram

here is a picture of the diagram we did in class. I also posted it on Github as a viso diagram

Stored procedures

--stored procedures
--parameterized view

use Community_Assist
go
Create proc usp_HRViewProc
@EmployeeKey int
As
Select PersonLastName,
PersonfirstName,
PersonEmail,
EmployeeHireDate,
EmployeeAnnualSalary
From Person p
inner Join Employee e
on p.PersonKey=e.PersonKey
Where EmployeeKey = @EmployeeKey

exec usp_HRViewProc @EmployeeKey=2
Go
--Create hash password function
Alter function fx_hashPassword2
(@seed int, @password nvarchar(50))
returns varbinary(500)
As
Begin
Declare @newPassword nvarchar(70)
set @newPassword = 
cast(@seed as nvarchar(20)) + @Password
Declare @hashed varbinary(500)
set @hashed = hashbytes('sha2_512', @newPassword)
return @hashed
End


Select * from businessrule
go
--new person stored procedure
Alter proc usp_NewPerson
@lastName nvarchar(255),
@FirstName nvarchar(255)=null,
@Email nvarchar(255),
@password nvarchar(50),
@AptNumber nvarchar(255)=null,
@Street nvarchar(255),
@City nvarchar(255)='Seattle',
@State nvarchar(255)='WA',
@Zip nvarchar(255),
@HomePhone nvarchar(255)
As
--test to see if person exists
if exists
 (Select PersonLastName, PersonEmail From Person
 Where PersonLastName=@LastName
 And PersonEmail=@Email)
 Begin--begin if
 Print 'Already registered'
 return
 End--end if

--create the password hash
Declare @seed int
Set @seed = dbo.fx_GetSeed()
declare @hashedPass varbinary(500)
set @hashedPass = dbo.fx_HashPassword(@seed,@password)

Begin tran --start a transaction
Begin try -- begin try
--insert into person
Insert into Person(  
PersonLastName, PersonFirstName,
 PersonEmail, PersonPassWord, 
 PersonEntryDate, PersonPassWordSeed)
 Values(@LastName, @FirstName,
 @Email, @hashedPass,GetDate(),@seed)

 --get the key of the person just inserted
 Declare @key int = ident_current('Person')
 --insert into PersonAddress
 Insert into PersonAddress(
 PersonAddressApt,
 PersonAddressStreet, 
 PersonAddressCity, PersonAddressState, 
 PersonAddressZip, PersonKey)
 Values( @AptNumber,@street,@city,@state,@Zip,@key)

 Insert into Contact([ContactNumber],
 [ContactTypeKey], [PersonKey])
 Values (@HomePhone, 1, @Key)

 Commit Tran --commit the transaction if no error
 End Try
 Begin Catch
 Rollback Tran
 Print Error_Message()
 End Catch


 exec usp_NewPerson
 @lastName = 'Conger', 
 @FirstName='Steve', 
 @Email='spconger@gmail.com', 
 @password='congerPass', 
 @Street='101 nowhere bld', 
 @Zip='98122', 
 @HomePhone='2065551201'

 Select * from PersonAddress
 Select * from Contact

Functions temp tables

use Community_Assist

--temporary tables 
--local temporary table. Local to this one session
Create Table #TempGrants
(
 GrantRequestKey int,
 GrantRequestDate datetime,
 PersonKey int,
 GrantTypeKey int,
 GrantRequestAmount money
)

insert into #tempGrants(GrantRequestKey,
GrantRequestDate,PersonKey, GrantTypeKey,
GrantRequestAmount)
Select GrantRequestKey,
GrantRequestDate,PersonKey, GrantTypeKey,
GrantRequestAmount from GrantRequest
Where Month(GrantRequestDate)=9

Select * from #TempGrants

--Global temporary table
Create Table ##TempGrantsGlobal
(
 GrantRequestKey int,
 GrantRequestDate datetime,
 PersonKey int,
 GrantTypeKey int,
 GrantRequestAmount money
)

insert into ##tempGrantsGlobal(GrantRequestKey,
GrantRequestDate,PersonKey, GrantTypeKey,
GrantRequestAmount)
Select GrantRequestKey,
GrantRequestDate,PersonKey, GrantTypeKey,
GrantRequestAmount from GrantRequest
Where Month(GrantRequestDate)=9

Select * from ##TempGrantsGlobal
--Functions
Go
Create Function fx_Cube
(@number int)
returns int
As
Begin
Declare @Cube int
Set @cube = @number * @number * @number
return @Cube
End
Go
Select dbo.fx_Cube(3) as [Cube]

--stored procedures
--triggers
 Select PersonKey,dbo.fx_cube(PersonKey) as cubed
 from Person
 order by cubed

 go
 Alter function fx_PercentAmount
 (@amount money, @percentage decimal(10,2)) -- parameters
 returns money --return type
 As
 Begin
 if(not @amount=0) --check to make sure not 0
 Begin --start outer if
 
     if(@Percentage > 1) --check to see how % entered
     Begin --begin inner if
   Set @Percentage = @percentage / 100
     End --end inner if
   Declare @result money --declare variable for result
   Set @result = @amount * @percentage --calculate amount
 End --end outer if
 Else --begin else
 Begin 
 Set @Result=0;
 End --end else
  return @result --return results
 End

 Select DonationAmount, dbo.fx_PercentAmount(DonationAmount, 20) as Org, 
 dbo.fx_PercentAmount(donationAmount, .8) as charity
 From Donation

  Select Sum(DonationAmount) Total, dbo.fx_PercentAmount(sum(DonationAmount), 20) as Org, 
 dbo.fx_PercentAmount(Sum(donationAmount), .8) as charity
 From Donation

Transaction try catch

use Community_Assist

--this begins a manual transaction
Begin Tran

--try tries all the code before 
--if there are no errors
--it will execute all the code 
--commit the transaction and exit
--if there is an error it will drop to the catch
--rollback the transaction and print the error message
Begin Try
Insert into Person(
[PersonLastName],
[PersonFirstName],
[PersonEmail],
[PersonEntryDate])
Values('SquarePants',
'SpongeBuddy',
'sponge@bikinibottom.com',
GetDate())

Insert into PersonAddress(
[PersonAddressStreet],
[PersonAddressCity],
[PersonAddressState],
[PersonAddressZip],
[PersonKey])
Values('100 Pinapple lane',
'Bikinibottom',
'se',
'00001',
ident_current('Person'))

Insert into contact(
[ContactNumber],
[ContactTypeKey],
[PersonKey])
Values ('2005551245',
1,
ident_Current('Person'))

Commit tran
End Try
Begin Catch
Rollback tran
print error_message()
End Catch

Select * from Contact

class Relationships

Association means simply that two classes talk to each other. One calls methods in the other

Inheritance allows an inheriting class to get all the public fields and methods of the parent. It always proceeds from the more general, more abstract, to the more specific or concrete. The child class must be of the same kind as the parent.

An interface is a collection of method signatures. A class that implements an interface must provide a body for all the method signatures in the interface.

Composition is a relation where one class in totally contained in another class. If the container class is destroyed (goes out of scope) so does the contained class.

Aggregation is a relationship where one class is contained in the other, but the contained class persists even when the containing class is destroyed

Set Operators and modifying data

USE [Community_Assist]
GO

/****** Object:  View [dbo].[vw_HumanResources]    Script Date: 5/11/2016 10:11:00 AM ******/


ALTER view [dbo].[vw_HumanResources] with Schemabinding
AS
Select PersonLastName [LastName],
PersonFirstName [FirstName],
PersonEmail Email,
PersonAddressStreet [Address],
PersonAddressCity City,
PersonAddressZip ZipCode,
EmployeeHireDate HireDate
From dbo.Person p
Inner Join dbo.PersonAddress pa
on p.PersonKey=pa.PersonKey
inner join dbo.Employee e
on e.PersonKey = p.PersonKey

GO

--cross apply
Select Distinct a.GrantTypeKey, c.GrantRequestAmount
From dbo.GrantRequest a
cross Apply
(Select grantTypeKey, grantRequestAmount
From GrantRequest as b
Where b.GrantTypeKey = a.GrantTypeKey
Order By b.GrantRequestAmount desc, GrantTypeKey desc
Offset 0 rows fetch first 3 rows only) as c



Begin tran

Alter table person
Drop column PersonEmail

Rollback tran

--Set operators and modifying data
--Windows functions pivot

Select PersonKey, PersonFirstName, PersonLastName, PersonEmail
From Person
Union
Select EmployeeKey,EmployeefirstName, EmployeeLastName, EmployeeEmail
From MetroAlt.dbo.Employee


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

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

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

/******************************************
*********Not part of assignment*********/

--ranking functions

Select GrantRequestKey, GrantTypeKey, GrantRequestAmount,
Row_Number() over (order by GrantRequestAmount desc) as RowNumber,
Rank() over (order by GrantRequestAmount desc) as [Rank],
Dense_rank() over (order by GrantRequestAmount desc) as [DenseRank],
Ntile(10) over (order by GrantRequestAmount desc) as [NTile]
From GrantRequest
Order by GrantRequestAmount desc

--partition functions
Select GrantRequestKey, GrantTypeKey, GrantRequestAmount,
Sum(GrantRequestAmount) over() as TotalAllocation,
sum(GrantRequestAmount) over(partition by GrantTypeKey) as PerType,
(sum(GrantRequestAmount) over(partition by GrantTypeKey) /
Sum(GrantRequestAmount) over() * 100) as [TypePercentOfWhole],
GrantRequestAmount/
sum(GrantRequestAmount) over(partition by GrantTypeKey) * 100 as GrantPercentOfType
From GrantRequest
Order by GrantTypeKey 

--pivot
Select * from GrantRequest

Select  [Month], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10]
From (Select Month(GrantRequestDate) as [Month] ,GrantTypeKey, GrantRequestKey
From dbo.GrantRequest) as a
pivot (Count(GrantRequestKey) for GrantTypeKey in 
    ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])) AS b

/*************************************************/
--Inserts
Insert into person
(PersonLastName, PersonFirstName, PersonEmail,  PersonEntryDate)
Values('Simpson', 'Homer', 'Homer@springfield.com', GetDate()),
('Simpson','Marge','Marge@springfield.com',GetDate())

Select * from Person

Create Table Person2
(
    Lastname nvarchar(225),
 FirstName nvarchar(255),
 Email nvarchar(255)
)

Insert into Person2(LastName, firstname, email)
Select PersonLastName, PersonFirstName, PersonEmail
From Person

Select * from Person2

Begin tran

Update Person2
Set FirstName='Jason'
Where Email='JAnderson@gmail.com'

Commit Tran

Update Person2
Set LastName='Smith'

Rollback tran

Delete from Person2
Where PersonKey=130

Select * from Person
Drop Table Person2

Table Expressions

--Table Expressions
Use Community_Assist

--Table expressions are Subqueries in the from clause
Select PersonKey, [Last], [First], City 
From 
(Select p.PersonKey, 
PersonLastName [Last],
PersonFirstName [First],
PersonAddressCity City
From Person p
inner join PersonAddress pa
on p.PersonKey =pa.PersonKey
Where PersonAddressCity='Bellevue') as BellevueResidents
--the subquery must be given a table name
--as alias

Select RequestMonth, ServiceName, Count(ServiceName) as [Count] 
From
(Select Month(GrantRequestDate) RequestMonth, 
GrantTypename ServiceName 
From GrantRequest gr
inner Join GrantType gt
on gr.GrantTypeKey=gt.GrantTypeKey) as GrantCount
Group by RequestMonth, ServiceName

--Common table Expressions are the same as 
--Table Expressions but structured differently
--they define the table query first 

go
with BellevueResidents as
(
 Select p.PersonKey, 
PersonLastName [Last],
PersonFirstName [First],
PersonAddressCity City
From Person p
inner join PersonAddress pa
on p.PersonKey =pa.PersonKey
Where PersonAddressCity='Bellevue'
)
Select [Last], [first], city From BellevueResidents

Go
With ServiceCount as
(
   Select Month(GrantRequestDate) RequestMonth, 
GrantTypename ServiceName 
From GrantRequest gr
inner Join GrantType gt
on gr.GrantTypeKey=gt.GrantTypeKey
)
Select RequestMonth, ServiceName, Count(ServiceName) [Count] from ServiceCount
Group by RequestMonth, ServiceName

Go
--this defines a variable
--and sets the value
--It is then used in the common table expression
Declare @GrantTypeKey int
Set @GrantTypeKey = 5;--one of the only place ; is required

With ServiceCount as
(
   Select Month(GrantRequestDate) RequestMonth, 
GrantTypename ServiceName 
From GrantRequest gr
inner Join GrantType gt
on gr.GrantTypeKey=gt.GrantTypeKey
Where gt.GrantTypeKey=@GrantTypeKey
)
Select RequestMonth, ServiceName, Count(ServiceName) [Count] from ServiceCount
Group by RequestMonth, ServiceName


--views are stored queries. 
--They can be used to provide
--a set of users views of the database
--that corrispond to how they use the database
Go
Alter view vw_HumanResources
AS
Select PersonLastName [LastName],
PersonFirstName [FirstName],
PersonEmail Email,
PersonAddressStreet [Address],
PersonAddressCity City,
PersonAddressZip ZipCode,
EmployeeHireDate HireDate
From Person p
Inner Join PersonAddress pa
on p.PersonKey=pa.PersonKey
inner join Employee e
on e.PersonKey = p.PersonKey
go
Select [lastName], [FirstName], City, HireDate From vw_HumanResources
--won't work because you can't see underlying fields
Select PersonLastName from vw_HumanResources

go
-- a schema, in this context,
--is a collection of related objects
Create schema HumanResources
Go
--this view belongs to the humanResources schema
Create view HumanResources.vw_HR
AS
Select PersonLastName [LastName],
PersonFirstName [FirstName],
PersonEmail Email,
PersonAddressStreet [Address],
PersonAddressCity City,
PersonAddressZip ZipCode,
EmployeeHireDate HireDate
From Person p
Inner Join PersonAddress pa
on p.PersonKey=pa.PersonKey
inner join Employee e
on e.PersonKey = p.PersonKey;


select * from HumanResources.vw_HR;

Select * From [SccITI\Kpowell].vw_hr
Go
--this is a table function
--it effectively
Create function fx_Employee
(@GrantTypeKey int)
returns Table
As
Return
Select GrantRequestKey, GrantRequestDate,PersonKey,
GrantTypeKey, GrantRequestExplanation, GrantRequestAmount
From GrantRequest
Where GrantTypeKey = @GrantTypeKey
Go

--table functions are called in the from
Select * from dbo.fx_Employee(3)

Activity Diagrams

Here is a simple Activity Diagram

Here is a diagram with splits and joins

An Activity diagram with swim lanes

GitHub Two

Copying, Downloading and Forking From Website

---

In this tutorial I will discuss how to get files from GitHub to use in your own coding.

Copying code

---

It is easy to copy code. Go to the repository with the code you want and then open the code file.Click on the Raw Button. This will give you the code as pure text, minus any formatting or web site information.

Once you have it in Raw view Select the code you wish to use and copy it.

Downloading

---

Another way to get the code is simply to download it as a zip file. Once you have extracted it you can simply run the program--as long as all the dependencies such as Databases and other libraries are in place.

Forking

Forking allows you to make a copy of someone's repository on your site. To do this I am going to go to another person's Repositories and Fork it to my own. I am going to use the site that Github uses in their tutorial, https://github.com/octocat/Spoon-Knife. I am not going to show Pull requests which allow you to notify the person you forked from and other interested uses of your changes to the original. I am also not going to go into how to keep the repositories in sync. For that look at the tutorial.

Click the Fork Button. There is a please wait screen and then the code is copied to your GitHub site.

Windows Client

---

For the Windows client, I am just going to focus on cloning or copying a site. I will clone one of my own repositories onto my computer.

First, I will click the plus sign--the same one you use to create a new repository, only this time I will choose clone. From a drop down list of repositories on my site I select Inheritance Example Java.

Click the check mark and choose where to save the cloned Repository. Now the repository is on your computer.

If you want to clone someone else's repository, first fork it to your GitHub account, then clone it.

Command Line

---

To copy or clone a repository from the command line is really simple. It also has the advantage that you can copy someone else's repository without first forking it. Here is the command to copy that sample Spoon-Knife repository:

git clone git://github.com/octocat/Spoon-Knife.git

Here is the screenshot of the command console

In the Third part we will look at version control