Wednesday, July 18, 2018

Code for MetroAltDW SSIS

--Drop Keys (foreign then primary)
--Remove Data (truncate)
--Insert data
--reapply keys (primary then foreign)

--drop constraints

   Alter table Factschedule
   drop constraint fk_Dimdate

      Alter table Factschedule
   drop constraint fk_schedule

      Alter table Factschedule
   drop constraint fk_DimRoute

      Alter table Factschedule
   drop constraint fk_Dimbus

      Alter table Factschedule
   drop constraint fk_DimEmployee

      Alter table DimEmployee
   drop constraint Pk_DimEmployee

      Alter table DimBus
   drop constraint Pk_DimBus

      Alter table Dimschedule
   drop constraint Pk_Dimschedule

      Alter table DimRoute
   drop constraint PK_DimRoute

      Alter table DimDate
   drop constraint Pk_Dimdate

   Alter table Factschedule
   Drop constraint Pk_FactSchedule
 
 --truncate tables
 truncate table Dimbus
 truncate table dimdate
 truncate table dimEmployee
 Truncate table dimroute
 truncate table dimschedule
 truncate table factschedule



 --reinsert the live data
  Insert into DimEmployee(EmployeeKey, PositionKey, 
   PositionName, EmployeeHireDate, EmployeeHourlyPayRate)
   Select e.EmployeeKey, p.PositionKey, 
   PositionName, EmployeeHireDate, EmployeeHourlyPayRate
   From MetroAlt.dbo.Employee e
   inner join MetroAlt.dbo.EmployeePosition ep
   on e.EmployeeKey=ep.EmployeeKey
   inner join MetroAlt.dbo.Position p
   on p.PositionKey=ep.PositionKey

  Insert into DimBus(BusKey, BusTypeKey, 
  BustypeDescription, BusTypePurchasePrice)
  Select b.BusKey, bt.BusTypeKey, 
  BustypeDescription, BusTypePurchasePrice
  From MetroAlt.dbo.Bus b
  inner join MetroAlt.dbo.Bustype bt
  on b.BusTypekey=bt.BusTypeKey

 Insert into Dimroute(RouteKey, BusRouteZone)
 Select BusRouteKey, BusRouteZone
 From MetroAlt.dbo.BusRoute

 Insert into dimSchedule(BusDriverShiftKey, 
 BusScheduleAssignmentKey, 
 Employeekey)
 Select BusDriverShiftKey, 
 BusScheduleAssignmentKey, 
 Employeekey
 From MetroAlt.dbo.BusScheduleAssignment

 Insert into DimDate(BusScheduleAssignmentDate, 
 BusScheduleYear, BusScheduleMonth)
 Select distinct BusScheduleAssignmentDate, 
 Year(BusScheduleAssignmentDate),
 Month(BusScheduleAssignmentDate)
 From Metroalt.dbo.BusScheduleAssignment

 Insert into FactSchedule(DimEmployeeKey, 
 DimBusKey, DimrouteKey, DimScheduleKey, 
 DimDateKey, Riders, FaresPerRoute)
 Select Distinct DimEmployeeKey, 
 DimBusKey, DimrouteKey, DimScheduleKey, 
 DimDateKey, Riders, Riders * FareAmount
 From MetroAlt.Dbo.BusScheduleAssignment bsa
 inner join DimEmployee de
 on de.EmployeeKey=bsa.EmployeeKey
 inner join dimBus db
 on db.BusKey=bsa.BusKey
 inner join Dimroute dr
 on dr.RouteKey=bsa.BusRouteKey
 inner join DimSchedule ds
 on ds.BusScheduleAssignmentKey=bsa.BusScheduleAssignmentKey
 inner join DimDate dd
 on dd.BusScheduleAssignmentDate=bsa.BusScheduleAssignmentDate
 inner join MetroAlt.dbo.Ridership r
 on r.BusScheduleAssigmentKey=bsa.BusScheduleAssignmentKey
 inner join MetroAlt.dbo.Fare f
 on f.FareKey=r.FareKey 

 --reapply the constraints
Alter table DimEmployee
Add constraint pk_DimEmployee primary key (DimEmployeeKey)

Alter table DimBus
Add constraint pk_DimBus primary key (DimBusKey)

Alter table Dimroute
Add constraint pk_DimRoute primary key (DimRouteKey)

Alter table DimSchedule
Add constraint pk_DimSchedule primary key (DimScheduleKey)

Alter table DimDate
Add constraint pk_DimDate primary key (DimDateKey)

Alter table FactSchedule
Add constraint pk_FactSchedule 
primary key (DimEmployeeKey, DimBusKey, 
DimRouteKey, DimScheduleKey, DimDateKey)

Alter table factSchedule
add constraint fk_DimEmployee foreign key (DimEmployeeKey)
   References DimEmployee(DimEmployeeKey)

Alter table factSchedule
add constraint fk_DimBus foreign key (DimbusKey)
   References DimBus(DimBusKey)

Alter table factSchedule
add constraint fk_DimRoute foreign key (DimRouteKey)
   References DimRoute(DimRouteKey)

Alter table factSchedule
add constraint fk_Schedule foreign key (DimScheduleKey)
   References DimSchedule(DimScheduleKey)

Alter table factSchedule
add constraint fk_DimDate foreign key (DimDateKey)
   References DimDate(DimDateKey)


Select * from FactSchedule

Wednesday, June 6, 2018

Admin XML and JSON assignments

--admin commands
Use MetroAlt
go
--schema management of objects
Create schema Management
Go
Create view Management.vw_AnnualRidership
As
Select year(BusscheduleAssignmentDate) [Year],
Count(riders) Total
From BusScheduleAssignment bs
inner join Ridership r
on r.BusScheduleAssigmentKey=bs.BusScheduleAssignmentKey
Group by year(BusscheduleAssignmentDate)

go
Create view Management.vw_EmployeeInfo
As
Select EmployeeFirstName, EmployeeLastName, EmployeeEmail,
PositionName, EmployeeHourlyPayRate
From Employee e
inner join EmployeePosition ep
on e.EmployeeKey=ep.EmployeeKey
inner join Position p
on p.PositionKey=ep.PositionKey
Go
Create role managementRole

Grant Select on Schema::Management to ManagementRole

Grant Select, Insert, update on Employee to ManagementRole
Grant Select, Insert, Update on EmployeePosition to Managementrole

Create login managementLogin with password='P@ssw0rd1', default_database=MetroAlt
Use MetroAlt
Create user managementUser for login managementLogin 
Alter role managementrole add member managementUser

Backup Database MetroAlt to disk='C:\backup\MetroAlt.bak'
Restore Database metroAlt from disk='C:\backups\metroAlt'

--end admin assignment

--Begin Xml

Create xml Schema Collection MaintenanceNoteSchemaCollection
As
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" 
           elementFormDefault="qualified" 
           targetNamespace="http://www.metroalt.com/maintenancenote" 
           xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="maintenancenote">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="title" />
        <xs:element name="note">
          <xs:complexType>
            <xs:sequence>
              <xs:element maxOccurs="unbounded" name="p" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="followup" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>'

Create table MaintenanceDetail
(
     maintenanceDetailKey int identity(1,1) primary key,
  maintenanceNotes nvarchar(255)

)

Alter table MaintenanceDetail
Drop column MaintenanceNotes

Alter table MaintenanceDetail
add MaintenanceNotes xml (MaintenanceNoteSchemaCollection)

Insert into maintenanceDetail(maintenanceNotes)
values('<?xml version="1.0" encoding="utf-8"?>
<maintenancenote xmlns="http://www.metroalt.com/maintenancenote">
  <title>Wear and Tear on Hydralic units</title>
<note>
  <p>The hydralic units are showing signs of stress</p>
  <p>I recommend the replacement of the units</p>
</note>
  <followup>Schedule replacement for June 2016</followup>
</maintenancenote>')

--will fail because title is not Title
Insert into maintenanceDetail(maintenanceNotes)
values('<?xml version="1.0" encoding="utf-8"?>
<maintenancenote xmlns="http://www.metroalt.com/maintenancenote">
  <Title>Wear and Tear on Hydralic units</Title>
<note>
  <p>The hydralic units are showing signs of stress</p>
  <p>I recommend the replacement of the units</p>
</note>
  <followup>Schedule replacement for June 2016</followup>
</maintenancenote>')

Select * from MaintenanceDetail

--extras
Select * from Employee for xml raw('Employee'), elements, root('Employees')

--end xml
--begin JSON
Select * from Busbarn for JSON auto
Select BusBarnKey, Busbarnphone, busBarnAddress as 'address.street', 
Busbarncity as 'address.city', BusBarnZipcode as 'address.zipcode'
from Busbarn for JSON Path

Alter Database MetroAlt set compatibility_Level=130

Declare @JSON nvarchar(max)
Set @JSON='{"_id" : 1, "product": "IPad", "price" : 894.50, "quantityAvailable" : 13}'
Select * from OpenJSon(@JSON)

Declare @products nvarchar(max)
Set @products='[
    {"_id" : 1, "product": "IPad", "price" : 894.50, "quantityAvailable" : 13},
    {"_id" : 2, "product": "Chrome Book", "price" : 245.99, "quantityAvailable": 23},
    {"_id" : 3, "product": "Bose Lap Top Speakers", "price" : 89.50, "quantityAvailable" : 10},
    {"_id" : 4, "product": "Blue Tooth Game Controller", "price" : 149.99, "quantityAvailable" : 3},
    {"_id" : 5, "product": "Star Wars Mouse Pad", "price" : 1.50, "quantityAvailable" : 100},
    {"_id" : 6, "product": "Dell XPS Desk Top Computer", "price" : 945.00, "quantityAvailable" : 7},
    {"_id" : 7, "product": "Microsoft Surface Pro", "price" : 1250.75, "quantityAvailable" : 9},
    {"_id" : 8, "product": "Norton Anti Virus", "price" : 75.50, "quantityAvailable": 2},
    {"_id" : 9, "product": "Mechanical Keyboard", "price" : 125.50, "quantityAvailable" : 3},
    {"_id" : 10, "product": "Android Tablet", "price" : 345.23, "quantityAvailable" : 5}
  ]'
  Select * from OpenJson(@products)
  with (
    id int '$._id',
 product nvarchar(255) '$.product',
 price decimal(8,2) '$.price',
 quantity int '$.quantityAvailable'
 )
--end JSON

Monday, June 4, 2018

Triggers

--Triggers like stored proc
--except they are triggered by an event
--insert, update, delete
--enforcing business
--every grant request should be reviewed within 2 days
--after, instead of

use Community_Assist
go
Create Trigger tr_ReveiwList on GrantRequest
after insert 
As
--check to see if table exists
if not exists
   (Select name from sys.tables
      where name='ToReview')
Begin
  Create Table ToReview
  (
      GrantRequestKey int,
   GrantRequestDate datetime,
   DateToReviewBy Date
  )
end
Declare @GrantKey int
Declare @GrantRequestDate Datetime
Declare @DateToReview Date
Select @GrantKey=GrantRequestKey, 
@GrantRequestDate=GrantRequestDate
From Inserted
Set @DateToReview=dateAdd(Day,2,@GrantRequestDate)
Insert into ToReview(GrantRequestKey, GrantRequestDate, DateToReviewBy)
Values(@GrantKey, @GrantRequestDate, @DateToReview)
go

Insert into GrantRequest(GrantRequestDate, PersonKey, GrantTypeKey, 
GrantRequestExplanation, GrantRequestAmount)
Values(GetDate(),4,1,'Hungry this morning',60)

Select * from toReview


Select * from BusinessRule

Go
Create trigger tr_CheckMaximum on GrantRequest
instead of insert
As
Declare @requestAmount money
Declare @MaxRequestAmount money
Declare @GrantType int

Select @RequestAmount=GrantRequestAmount, 
       @GrantType=GrantTypeKey
    From inserted

Select @MaxRequestAmount=GrantTypeMaximum 
       From GrantType
    Where GrantTypekey = @GrantType

If @RequestAmount > @MaxRequestAmount
Begin
       print 'Request exceeds maximum allowed'
    return
End
Else
Begin
  Insert into GrantRequest(GrantRequestDate, PersonKey, 
  GrantTypeKey, GrantRequestExplanation, GrantRequestAmount)
  Select  GrantRequestDate, PersonKey, GrantTypeKey, 
  GrantRequestExplanation, GrantRequestAmount from inserted
End

Insert into GrantRequest(GrantRequestDate, PersonKey, 
  GrantTypeKey, GrantRequestExplanation, GrantRequestAmount)
  Values(GetDate(), 6, 1, 'Need Food', 100.75)


  Select * from GrantType
Select * from GrantRequest
Select * from ToReview

Tuesday, May 29, 2018

Testing

Here is a picture of the code testing worksheet

Here is a link to the Fraction code on github

Wednesday, May 23, 2018

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

Tuesday, May 22, 2018

UML Diagrams for Venue

Use Case

Activity

Class Diagrams

Sequence

Monday, May 21, 2018

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