Testing

Here is a picture of the code testing worksheet

Here is a link to the Fraction code on github

Stored Procedures

--stored procedures--
--Add a new person  to person table
--add an address to personAddress
--add phone numbers to contact
--all tied together with personkey

--First version raw
Use Community_Assist
Go
Create proc usp_NewPerson
@PersonLastName nvarchar(255),
@PersonFirstName nvarchar(255), 
@PersonEmail nvarchar(255), 
@PassWord nvarchar(255), 
@PersonAddressApt nvarchar(255)=null, 
@PersonAddressStreet nvarchar(255), 
@PersonAddressCity nvarchar(255)='Seattle', 
@PersonAddressState nchar(2)='WA', 
@PersonAddressZip nchar(12), 
@HomePhone nvarchar(255)=null,
@WorkPhone nvarchar(255)=null
AS
--get seed and password
Declare @Seed int
Declare @hash varbinary(500)
Set @Seed=dbo.fx_GetSeed()
Set @hash=dbo.fx_HashPassword(@Seed, @PassWord)
--insert into Person
Insert into Person(
PersonLastName, 
PersonFirstName, 
PersonEmail, 
PersonPassWord, 
PersonEntryDate, 
PersonPassWordSeed
)
Values(
@PersonLastName,
@PersonFirstName,
@PersonEmail,
@Hash,
GetDate(),
@Seed
)
--get the personkey for the person just inserted
declare @key int
Set @key=Ident_current('Person')
--insert into personAddress
Insert into PersonAddress( 
PersonAddressApt, 
PersonAddressStreet, 
PersonAddressCity, 
PersonAddressState, 
PersonAddressZip, 
PersonKey)
Values(
@PersonAddressApt,
@PersonAddressStreet, 
@PersonAddressCity, 
@PersonAddressState, 
@PersonAddressZip, 
@Key)
--get the contact type keys
Declare @homekey int
Declare @WorkKey int
Select @homekey=ContactTypekey from ContactType Type 
where contactTypeName='Home Phone'
Select @Workkey=ContactTypekey from ContactType Type 
where contactTypeName='Work Phone'
--if the value is not null insert it
if @HomePhone is not null
Begin
    Insert into contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@homePhone, @homeKey, @key)
End
if @WorkPhone is not null
Begin
    Insert into contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@WorkPhone, @WorkKey, @key)
End
Go
--test first version
Exec usp_NewPerson
@PersonLastName='Miller',
@PersonFirstName='Steve', 
@PersonEmail='steve.miller@gmail.com', 
@PassWord='MillerPass',  
@PersonAddressStreet='Space Cowboy Ave',  
@PersonAddressZip='98001', 
@HomePhone='2065552109'

Select * from contact where personkey=131

Go
--add try catch and transactions
Alter proc usp_NewPerson
@PersonLastName nvarchar(255),
@PersonFirstName nvarchar(255), 
@PersonEmail nvarchar(255), 
@PassWord nvarchar(255), 
@PersonAddressApt nvarchar(255)=null, 
@PersonAddressStreet nvarchar(255), 
@PersonAddressCity nvarchar(255)='Seattle', 
@PersonAddressState nchar(2)='WA', 
@PersonAddressZip nchar(12), 
@HomePhone nvarchar(255)=null,
@WorkPhone nvarchar(255)=null
AS
--get seed and password
Declare @Seed int
Declare @hash varbinary(500)
Set @Seed=dbo.fx_GetSeed()
Set @hash=dbo.fx_HashPassword(@Seed, @PassWord)
Begin Tran--begin a transaction
Begin Try
--insert into Person
Insert into Person(
PersonLastName, 
PersonFirstName, 
PersonEmail, 
PersonPassWord, 
PersonEntryDate, 
PersonPassWordSeed
)
Values(
@PersonLastName,
@PersonFirstName,
@PersonEmail,
@Hash,
GetDate(),
@Seed
)
--get the personkey for the person just inserted
declare @key int
Set @key=Ident_current('Person')
--insert into personAddress
Insert into PersonAddress( 
PersonAddressApt, 
PersonAddressStreet, 
PersonAddressCity, 
PersonAddressState, 
PersonAddressZip, 
PersonKey)
Values(
@PersonAddressApt,
@PersonAddressStreet, 
@PersonAddressCity, 
@PersonAddressState, 
@PersonAddressZip, 
@Key)
--get the contact type keys
Declare @homekey int
Declare @WorkKey int
Select @homekey=ContactTypekey from ContactType Type 
where contactTypeName='Home Phone'
Select @Workkey=ContactTypekey from ContactType Type 
where contactTypeName='Work Phone'
--if the value is not null insert it
if @HomePhone is not null
Begin
    Insert into contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@homePhone, @homeKey, @key)
End
if @WorkPhone is not null
Begin
    Insert into contact(ContactNumber, ContactTypeKey, PersonKey)
 Values(@WorkPhone, @WorkKey, @key)
End
Commit tran
End Try
Begin Catch
Rollback tran
print error_message()
End catch
go
--second test this should throw error
Exec usp_NewPerson
@PersonLastName='Miller',
@PersonFirstName='Steve', 
@PersonEmail='steve.miller@gmail.com', 
@PassWord='MillerPass',  
@PersonAddressStreet='Space Cowboy Ave',  
@PersonAddressZip='98001', 
@HomePhone='2065552109'

Exec usp_NewPerson
@PersonLastName='Stewart',
@PersonFirstName='James', 
@PersonEmail='James.Stewart@gmail.com', 
@PassWord='StewartPass',  
@PersonAddressStreet='201 Borah Ave',  
@PersonAddressZip='98001', 
@HomePhone='2065552166'

Select * From Person

Go
Create procedure usp_UpdatePerson
@PersonLastName nvarchar(255),
@PersonFirstName nvarchar(255), 
@PersonEmail nvarchar(255),
@PersonKey int
As
Update Person
Set PersonLastName=@personLastname,
PersonFirstName=@personfirstname,
PersonEmail=@personEmail
Where personkey = @personkey
go

exec usp_UpdatePerson
@personlastname='Manning',
@personFirstname='Louis',
@personEmail='LManning@gmail.com',
@Personkey=3






Select * from Person

UML Diagrams for Venue

Use Case

Activity

Class Diagrams

Sequence

Temp Tables and functions

/*Temporary tables and functions*/
Use Community_Assist
Create table #TempPerson
(
   PersonKey int,
   PersonLastName nvarchar(255),
   PersonFirstName nvarchar(255),
   PersonEmail nvarchar(255)
)

Insert into #tempPerson(PersonKey,
   PersonLastName ,
   PersonFirstName ,
   PersonEmail)
Select  PersonKey,
   PersonLastName,
   PersonFirstName,
   PersonEmail 
   From Person

   Select * from #TempPerson

   Create Table ##tempEmployee
   (
   EmployeeKey int, 
   PersonKey int, 
   EmployeeHireDate Date, 
   EmployeeAnnualSalary money
   )

   Insert into ##TempEmployee
   Select * from Employee

   Select * from ##TempEmployee

   --functions
   go
   Create function fx_cubed(@number int)
   returns int
   As
   Begin
   Declare @Cube int
   Set @Cube=@number * @number * @number
   return @Cube
   End
   Go
   Select dbo.fx_cubed(3)
   Go
   --75% to charity, 25% to maintenance
   Create function fx_DonationPercents
   (@amount money, @percent decimal(5,2))
   returns money
   As
   Begin--begin function
   Declare @Percentage money
   If @Percent <= 1
   Begin --begin if
     Set @Percentage=@Amount * @percent
   End --end if
   Else
   Begin --begin else
     Declare @percentDivisor decimal(5,2) = 100.00
     Set @percentage=@Amount * (@percent / @PercentDivisor)
   End--end else
   return @percentage
   End --end function
   go

   Select donationAmount, 
   dbo.fx_DonationPercents(DonationAmount, .25) Maintenance,
    dbo.fx_DonationPercents(DonationAmount, 75) Charity
 From Donation

  Select 
   sum(dbo.fx_DonationPercents(DonationAmount, .25)) Maintenance,
   sum (dbo.fx_DonationPercents(DonationAmount, 75)) Charity
 From Donation
go
Create function fx_Address
(@street nvarchar(255),
@City nvarchar(255),
@State nvarchar(255),
@Zip nvarchar(255))
returns nvarchar(255)
As
Begin
Declare @address nvarchar(255)
set @Address = @street + ', ' + @city + ', ' + @state + ' ' + @zip
return @address
End
go
Select PersonLastName, dbo.fx_Address(PersonAddressStreet, 
PersonAddressCity, PersonAddressState, PersonAddressZip) [address]
From Person
inner join PersonAddress
on person.personkey = personaddress.personkey
Select * from GrantRequest
Select * from GrantReview

go
Create function fx_GetAllocationAmount
(@grantKey int)
Returns money
As
Begin
Declare @allocation money
Select @allocation=GrantAllocationAmount from GrantReview 
Where GrantRequestKey = @grantKey
return @allocation
End
Go

Select GrantRequestKey, GrantRequestAmount, 
dbo.fx_GetAllocationAmount(grantRequestKey)Allocation
From GrantRequest 

Sequence Diagrams

Login

Add Show

Creating and altering tables

--tables
Use Community_Assist

Create table EventType
(
   EventTypeKey int identity(1,1) primary key,
   EventTypeName nvarchar(255) not null,
   EventTypeDescription nvarchar(255) null
)

--how to drop key constraint
Alter table Eventype
Drop Constraint [PK__EventTyp__F376BCE4BD778CDF]

Create table EventLocation
(
    EventLocationkey int identity(1,1) not null,
 EventLocationName nvarchar(255) not null,
 EventLocationAddress nvarchar(255) not null,
 EventLocationCity nvarchar(255) default 'Seattle',
 EventLocationState nchar(2) default 'WA',
 EventLocationZip nchar(12) not null,
 EventLocationEmail nvarchar(255) null,
 EventLocationUrl nvarchar(255),
 Constraint PK_EventLocation primary key(EventLocationkey)
)

Create table CAEvent
(
    EventKey int identity(1,1) not null,
 EventDate Date not null,
 EventStartTime Time not null,
 EventTypeKey int references EventType(EventTypeKey),
 EventLocationkey int,
 EmployeeKey int,
 constraint PK_CAEvent primary key (EventKey),
 constraint FK_Location Foreign Key(EventLocationKey)
       references EventLocation(EventLocationKey),
 constraint FK_EmployeeEvent Foreign Key(EmployeeKey)
       references Employee(EmployeeKey)

)

Create table Attendence
(
    EventKey int not null,
 PersonKey int not null
)

Alter table Attendence
Add Constraint PK_Attendence primary key(EventKey, PersonKey)

Alter table Attendence
add Constraint FK_EventAttendence Foreign Key(EventKey)
     References CAEvent(EventKey),
constraint FK_PersonAttendence Foreign Key (PersonKey)
     References Person(PersonKey)

Create table EventComment
( 
   CommentKey int identity(1,1) not null primary key,
   EventKey int references CaEvent(EventKey) ,
   PersonKey int references Person(PersonKey),
   CommentText nvarchar(max),
   CommentDate Datetime default GetDate()
)

Alter table EventLocation
Add constraint unq_name unique(EventLocationName)

--bit is used for booleans 1 true 0 false
Alter table CAEvent
Add Catered bit

Alter table EventType
Drop column EventTypeDescription

Alter Table EventComment
Add EventRating int

Alter table EventComment
Add Constraint ck_rating Check(Eventrating between 1 and 5)

Set Operators and modifying Data

--set operators and modifying data

--union

use Community_Assist
Select PersonLastName, PersonFirstName, PersonEmail, PersonAddressCity, 'person' [table]
From Person
Inner join PersonAddress
on Person.PersonKey=PersonAddress.PersonKey
Union
Select EmployeeLastName, EmployeeFirstName, EmployeeEmail, EmployeeCity, 'Employee'
From MetroAlt.dbo.Employee

--intersection
Select PersonAddressCity [City] From PersonAddress
Intersect
Select EmployeeCity from MetroAlt.dbo.Employee


Select EmployeeCity [City]from MetroAlt.dbo.Employee
Intersect
Select PersonAddressCity  From PersonAddress

--except
Select EmployeeCity [City]from MetroAlt.dbo.Employee
Except
Select PersonAddressCity  From PersonAddress

Select PersonAddressCity [city] from PersonAddress
Except
Select EmployeeCity from MetroAlt.dbo.Employee

--Modifying data
Insert into Donation(PersonKey, DonationDate, DonationAmount)
Values(4,getDate(),1000.00),
(7,getDate(),500.00),
(34,getDate(),100.00)

Select * from Donation

--insert a new person, new Address, new contact and donation
Declare @Seed int
Set @seed=dbo.fx_GetSeed()
Insert into Person(PersonLastName, PersonFirstName, PersonEmail,
PersonPassWord, PersonEntryDate, PersonPassWordSeed)
Values('Curry','Steph','steph.curry@msn.com',
dbo.fx_HashPassword(@seed,'CurryPass'),getDate(),@seed)

Insert into PersonAddress(PersonAddressApt, PersonAddressStreet,
 PersonAddressZip, PersonKey)
Values(null,'101010 Broadway','98100', ident_current('Person'))

Insert into Contact(ContactNumber, ContactTypeKey, PersonKey)
Values('2065551029',1,ident_current('Person'))

Insert into Donation(PersonKey, DonationDate, DonationAmount)
Values(ident_current('Person'), GetDate(),1200.00)

Select * From Person
Select * from PersonAddress
Select * from Contact
Select * from Donation

--Updates

Create table Person2
(
    personLastName nvarchar(255),
 personFirstName nvarchar(255),
 personEmail nvarchar(255)
)

Insert into person2
Select Personlastname, PErsonfirstName, PersonEmail
From PErson

Select * from Person2

Update Person2
Set personFirstName='Jason',
personlastName='Andrews'
where PersonEmail='JAnderson@gmail.com'

Begin tran
Update Person2
Set personLastName='Smith'
Rollback tran
Commit tran

Update Person
Set personFirstName='Jason',
personlastName='Andrews',
personEmail='Jandrews@gmail.com'
Where personkey=1

Select * from Person
order by PersonLastName

Select * from GrantType

Update GrantType
Set GrantTypeMaximum=GrantTypeMaximum * 1.05,
GrantTypeLifetimeMaximum=GrantTypeLifetimeMaximum * 1.05

Begin tran
Delete from PersonAddress
Select * from PersonAddress
Rollback tran

Select * from Donation

Begin tran
Delete from donation 
where donationKey =52
Commit tran

Truncate table Person2
Select * from Person2

--Get rid of object
Drop table Person2

More Class relations

Here is our interface

public interface I_items {
   public void addItem(Item i);
   public void removeItem(Item i);
}

Here is the code for a class that implements that interface

import java.util.ArrayList;

public class Inventory implements I_items{
  private ArrayList<Item> items;

@Override
public void addItem(Item i) {
 // TODO Auto-generated method stub
 
}

@Override
public void removeItem(Item i) {
 // TODO Auto-generated method stub
 
}
  
}

Here is the diagram for the card game program

Here is the link to the card game code on GitHub

Object Oriented principles

4 principles of OOPs
Inheritance--general-->more specific--code reuse
Encapsulation--seperation of concerns--every class should be self 
contained, as few dependencies as possible
Polymorphism--adapt and behave differently depending on environment
--overloading methods, overriding methods--generics
Abstraction--generalize, group everthing that is common

4 Types of Relationships
Inheritance
Association
Composition
Aggregation

Here is the diagram that shows the inheritance hierarchy

Here are the code classes that show that inheritance

Person Class

package com.spconger;

public abstract class Person {
 private String number;
 private String name;
 private String email;
 private String phone;
 
 public String getNumber() {
  return number;
 }
 public void setNumber(String number) {
  this.number = number;
 }
 public String getName() {
  return name;
 }
 public void setName(String name) {
  this.name = name;
 }
 public String getEmail() {
  return email;
 }
 public void setEmail(String email) {
  this.email = email;
 }
 public String getPhone() {
  return phone;
 }
 public void setPhone(String phone) {
  this.phone = phone;
 }

}

The Customer Class

package com.spconger;

import java.util.ArrayList;

public class Customer extends Person{
 ArrayList<String> coupons = new ArrayList<String>();
    public void AddCoupons(String coupon){
     coupons.add(coupon);
    }
    public String toString(){
     return getName();
    }
}

The EmployeeClass

package com.spconger;

public abstract class Employee extends Person {
  private String position;
  private String hireDate;
public String getPosition() {
 return position;
}
public void setPosition(String position) {
 this.position = position;
}
public String getHireDate() {
 return hireDate;
}
public void setHireDate(String hireDate) {
 this.hireDate = hireDate;
}
  public double calculatePay(){
   return 0.0;
  }
}

The SalaryEmployee Class

package com.spconger;

public class SalaryEmployee extends Employee{
 private double salary;

 public double getSalary() {
  return salary;
 }

 public void setSalary(double salary) {
  this.salary = salary;
 }
 
 public double calculatePay(){
  return getSalary()/12;
 }
 
}

Program Class

package com.spconger;

import java.util.Scanner;

public class Program {

 public static void main(String[] args) {
  // TODO Auto-generated method stub
  Customer c = new Customer();
  c.setName("George");
  
  SalaryEmployee se = new SalaryEmployee();
  se.setName("Jenny");
  se.setSalary(30000.00);
  
  System.out.println(se.getName() + "," + se.getSalary() 
  + ", " + se.calculatePay());
  
  System.out.println(c.toString());
 }

}

Composition and Aggregation Diagrams