Security SQL

--Employees
--Clients
--Donors
--public
--client Needs
--See what kinds of grants
--See the summary views
--They need to be able to register
 -- insert into person
 --insert into personAddress
 --insert into contact
--edit own contact information
--Apply for a grant
  --insert into grant Request
  --See own grant and check status
      --view grants (their own) grant Review
--schema collection of object
--Role collection of permission

Create schema GrantClientSchema;
Go
Create view GrantClientSchema.vw_ViewGrantTypes;
As
Select GrantTypeName Name, 
GrantTypeMaximum [One Time Maximum],
GrantTypeLifetimeMaximum [Life Time Maximum], 
GrantTypeDescription [Description]
From GrantType;
go
Select * from GrantClientSchema.vw_ViewGrantTypes;
go
Create proc usp_NewRegister
@PersonLastName nvarchar(255), 
@PersonFirstName nvarchar(255), 
@PersonEmail nvarchar(255), 
@PersonPlainPassWord nvarchar(50), 
@PersonAddressApt nvarchar(255) =null, 
@PersonAddressStreet nvarchar(255), 
@PersonAddressCity nvarchar(255)='Seattle', 
@PersonAddressState nchar(2)='WA', 
@PersonAddressZip nvarchar(11),
@homephone nvarchar(255) = null,
@Workphone nvarchar(255)=null
As
--check to make sure they don't exist
if not exists
  (Select PersonKey From Person
     Where PersonEmail=@PersonEmail
  And PersonLastName=@PersonLastName)
Begin --begin if they don't exist
--declare and set variables
Declare @seed int = dbo.fx_GetSeed()
Declare @hash varbinary(500) = 
dbo.fx_hashPassword(@seed
, @PersonPlainPassword)
Declare @PersonKey int
Declare @CurrentDate Datetime = GetDate()
Declare @HomePhoneType int =1
Declare @WorkPhoneType int =2

Begin tran--begin transaction
Begin try--begin try/catch
--insert into person
Insert into Person(PersonLastName, 
PersonFirstName, PersonEmail, 
PersonPassWord, PersonEntryDate, PersonPassWordSeed)
Values(@PersonLastName, @PersonFirstName,
@PersonEmail,@Hash, @CurrentDate,@Seed)
--get PersonKey
Set @PersonKey = IDENT_CURRENT('Person')
--insert into personAddress
Insert into PersonAddress(PersonAddressApt, 
PersonAddressStreet, PersonAddressCity, 
PersonAddressState, PersonAddressZip, PersonKey)
Values(@PersonAddressApt, 
@PersonAddressStreet, @PersonAddressCity, 
@PersonAddressState, @PersonAddressZip, @PersonKey)
--check on phones
if @homePhone is not null
Begin
  Insert into contact(ContactNumber, 
  ContactTypeKey, PersonKey)
  Values(@homephone,@HomePhoneType,@PersonKey)
End
if @WorkPhone is not null
Begin
  Insert into contact(ContactNumber, 
  ContactTypeKey, PersonKey)
  Values(@workphone,@WorkPhoneType,@PersonKey)
end
Commit tran
End Try
Begin Catch
Rollback Tran
print 'The insert was unsuccessful'
print error_message()
return error_number() 
End Catch

End --end if they don't exist
Else
Begin
print 'Person already exists'
End
  
exec usp_NewRegister
@PersonLastName = 'Nelson', 
@PersonFirstName='Miriam', 
@PersonEmail='MNelson@gmail.com', 
@PersonPlainPassWord='NelsonPass', 
@PersonAddressStreet='4412 South Sound Street', 
@PersonAddressZip='98100', 
@homephone='2065550975' 


Select * from Contact where PersonKey=150

Go
Create proc GrantClientSchema.usp_ViewGrantStatus
@PersonKey int
As 
Select [GrantRequestDate][Date], 
GrantTypeName [GrantType],
[GrantRequestExplanation] Explanation,
[GrantRequestAmount] Request,
[GrantReviewDate] [Review Date],
[GrantRequestStatus] [Status],
[GrantAllocationAmount] Allocation
From GrantRequest req
inner join Granttype gt
on req.GrantTypeKey=gt.GrantTypeKey
inner join GrantReview rev
on rev.GrantRequestKey=req.GrantRequestKey
Where PersonKey =@PersonKey

Exec GrantClientSchema.usp_ViewGrantStatus 1

Create role GrantClientRole

Grant Select, Execute on 
 Schema::GrantClientSchema to GrantClientRole

 Grant Select on vw_Donations to GrantClientRole
 Grant Select on vw_GrantTypeTotals to GrantClientRole
Go
Create proc usp_UpdateAddress
@PersonAddressKey int,
@PersonAddressApt nvarchar(255) = null, 
@PersonAddressStreet nvarchar(255), 
@PersonAddressCity nvarchar(255), 
@PersonAddressState nchar(2), 
@PersonAddressZip nchar(11), 
@PersonKey int
As
UpDate PersonAddress
Set PersonAddressApt=@PersonAddressApt,
PersonAddressStreet=@PersonAddressStreet,
PersonAddressCity=@personAddressCity,
PersonAddressState=@PersonAddressState,
PersonAddressZip=@PersonAddressZip
Where PersonKey =@PersonKey
And PersonAddressKey=@PersonAddressKey

Select * from PersonAddress

Exec [dbo].[usp_UpdateAddress]
@PersonAddressKey =1, 
@PersonAddressApt=null,   
@PersonAddressStreet='1002 North Mann Street', 
@PersonAddressCity='Seattle', 
@PersonAddressState='Wa', 
@PersonAddressZip='98001', 
@PersonKey=1

More SQL

use Cafe

Select * from Diets

Insert into Recipe(RecipeName, RecipeInstructions, 
RecipeTimeMinutes, Component)
Values('Cheese Sandwhich',
'Add Cheese to bread and grill in butter',
5,0)

Select * from Recipe
Select * from Inventory
Select * from Ingredient
Select * from RecipeIngredient

Delete From Ingredient where IngredientID > 10

Insert into Inventory(InventoryName, Cost, VenderID, Expiration)
Values('Bread',4.32,1,'7/30/2017'),
('cheese',12.50,1,'9/30/2017')

Insert into Ingredient(Units, Name, InventoryID)
Values('slice', 'bread',5),
('slice','Cheddar',6)

Insert into RecipeIngredient(RecipeID, IngredientID, Amount)
Values(2,5,2),
(2,6,1)
Update RecipeIngredient 
Set IngredientID=9
where RecipeID=2 and amount=2

Update RecipeIngredient
Set IngredientID=10
where RecipeID=2 and amount=1



Select RecipeName, RecipeInstructions, RecipeTimeMinutes,
 name as Ingredient, Amount
 From Recipe
 inner join RecipeIngredient
 on Recipe.RecipeID=RecipeIngredient.RecipeID
 inner join Ingredient
 on Ingredient.IngredientID=RecipeIngredient.IngredientID
 Where Recipe.recipeName='cheese sandwhich'

First SQL

Use Community_Assist

Select * from Person

Select Personfirstname, PersonlastName, PersonEmail
From Person
Order by PersonLastName;

Select Personfirstname, PersonlastName, PersonEmail
From Person
Order by PersonLastName desc;

Select * From Employee;
Select * from EmployeePosition;

--two table join
Select PersonLastName, PersonFirstName, 
EmployeeHireDate, EmployeeAnnualSalary
From Person
Inner Join Employee
On Person.PersonKey=Employee.PersonKey


Select * from Position
--four table join
Select PersonLastName, PersonFirstName, 
EmployeeHireDate, EmployeeAnnualSalary,
PositionName
From Person
Inner Join Employee
On Person.PersonKey=Employee.PersonKey
inner join EmployeePosition
on EmployeePosition.EmployeeKey=Employee.EmployeeKey
inner join Position
on Position.PositionKey=EmployeePosition.PositionKey

--Insert 
Insert into person(PersonLastName, PersonFirstName, 
PersonEmail, PersonEntryDate)
Values('Rose','Axel','arose@gmail.com', GetDate())

Insert into Contact( ContactNumber, ContactTypeKey, 
PersonKey)
Values('2065551209',1, 130),
('2065551210',2, 130)

Select * from Contact where personkey=130

Begin tran
Update Person 
Set PersonLastName='Smith'

Select * from Person
Rollback tran

Begin tran

Update Person
Set PersonFirstName='Jason'
Where PersonKey=1

Commit tran

--Delete
Delete from Person
where PersonKey=130

Delete from Contact
Where personkey=130

Security

/****************************
login  Authentication and Authorization
Login --server user mapped to the login and is for a database
Windows Authentication--Active directory
Sql Server Authentication--password username

Roles --Collections of Permissions
Schema -- ownership of a collection of objects

Community_Assist
Admin
Reviewers SELECT UPDATE DELETE INSERT DROP CREATE ALTER EXEC

Volunteers
Clients
General--public
Donors

What kinds of views would people have
Stored Procedures, How interact

Role
Schema
*/

--schema
use Community_Assist
Go
Create schema ClientSchema
go
Create view ClientSchema.vw_GrantType
As
Select * from GrantType
go
Select * from ClientSchema.vw_GrantType
Go
Create proc ClientSchema.usp_grantStatus
@PersonKey int
As
Select GrantTypeName [GrantType],
GrantRequestDate [Date],
GrantRequestExplanation [Explanation],
GrantRequestAmount Amount,
GrantRequestStatus [Status],
GrantAllocationAmount Allocation
From GrantType gt
inner join GrantRequest req
on gt.GrantTypeKey=req.GrantTypeKey
inner join GrantReview rev
on req.GrantRequestKey=rev.GrantRequestKey
Where personkey = @PersonKey

exec ClientSchema.usp_grantStatus 1

Create role ClientRole

Grant Select, execute on Schema::ClientSchema to ClientRole

Create Role GeneralUserRole
Grant Select on GrantType to GeneralUserRole
Grant Select on vw_Donations to GeneralUserRole
Grant insert on Person to GeneralUserRole

Create login Jody with password='P@ssword1', 
default_database=Community_Assist

Create user Jody for login jody with default_schema=ClientSchema
exec sp_AddRoleMember 'ClientRole','jody'

Cafe ERD SCreenshot

Normalization

Cd
Track numbers
Artist, Artist Country Artists
CD Title
title of Track
Length of Track
Studio--label
Genre, sub genre
Year of release
warning

Normalization: First Normal Form
There should be no repeating groups, no arrays
Every column should be of the same type

13
Track
Bad vibrations, entrance song, the sniper

Phone/email

Second Normal form: Getting rid of sub sets

Third normal form: where one field describes another field that is not the key

Database Snapshots

use master

Create database Community_Assist_Snapshot
on 
(name='Community_Assist', 
Filename='C:\Program Files\Microsoft SQL Server\MSSQL12.ITC224_6\MSSQL\DATA\Community_Assist_snapshot.ds')
As
snapshot of Community_Assist

Use Community_Assist_Snapshot

Select * from Person

Use Community_Assist
update person
Set PersonFirstName ='jason'
where personkey=1

use Master
Restore database Community_Assist 
from Database_snapshot = 'Community_Assist_Snapshot'

Views and procedures for reports

--report donations 
--Grant requests
--amounts requested vs amount granted
--Employee hr
--Donors
--Grants per type

Use Community_Assist
go
Alter view vw_Donations
As
Select Year(DonationDate) [Year],
Month(DonationDate) [Month],
Sum (DonationAmount) Total
From Donation
group by Year(DonationDate), Month(donationDate)

Select * from vw_Donations
go
Alter view vw_GrantRequests
As
SELECT 
Year(GrantRequestDate) [Year]
, Month(GrantRequestDate) [Month]
, Sum(GrantRequestAmount) Request
, Sum(GrantAllocationAmount) Allocation
From GrantRequest gr
inner join GrantType gt
on gr.GrantTypeKey=gt.GrantTypeKey
inner join GrantReview grev
on gr.GrantRequestKey=grev.GrantRequestKey
group by Year(GrantRequestDate)
, Month(GrantRequestDate)


Select * from vw_GrantRequests

go
Create View vw_GrantTypeTotals
As
Select Year(GrantRequestDate) [Year]
, GrantTypeName
, sum(GrantRequestAmount) Request
, sum(GrantAllocationAmount) Allocation
From GrantRequest gr
inner join GrantType gt
on gr.GrantTypeKey=gt.GrantTypeKey
inner join GrantReview grev
on gr.GrantRequestKey=grev.GrantRequestKey
group by Year(GrantRequestDate)
, GrantTypeName

Select * from vw_GrantTypeTotals

Create View vw_Employees
As
Select PersonLastName [Last Name]
,PersonFirstName [First Name]
,PersonEmail Email
,EmployeeHireDate [Hire Date]
,EmployeeAnnualSalary 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 pos
on ep.PositionKey=pos.PositionKey

Select * from vw_Employees

Use MetroAlt

Select * from RiderShip
Select * from Fare

Create view vw_AnnualRevenues
as
Select Year(BusScheduleAssignmentDate) [Year],
format(Sum(Riders * FareAmount),'$ #,##0.00') TotalFares
From BusScheduleAssignment bsa
inner join Ridership r
on bsa.BusscheduleAssignmentKey=r.[BusScheduleAssigmentKey]
inner join Fare f
on f.FareKey=r.FareKey
Group by Year(BusScheduleAssignmentDate)

Select * from busRoute
go
Create view vw_RevenuesByCity
As
Select Year(BusScheduleAssignmentDate) [Year],
BusRouteZone [City],
format(Sum(Riders * FareAmount),'$ #,##0.00') TotalFares
From BusScheduleAssignment bsa
inner join Ridership r
on bsa.BusscheduleAssignmentKey=r.[BusScheduleAssigmentKey]
inner join Fare f
on f.FareKey=r.FareKey
inner join BusRoute br
on br.BusRouteKey=bsa.BusRouteKey
Group by Year(BusScheduleAssignmentDate),
BusRouteZone
Go
Alter proc usp_BusRoute
@BusKey int
As 
Select distinct bsa.BusKey,BusStopAddress, BusStopCity, BusStopZipcode
From BusStop bs
inner join BusRouteStops brs
on bs.BusStopKey= brs.BusStopKey
inner join BusScheduleAssignment bsa
on bsa.BusRouteKey=brs.BusRouteKey
Where Buskey=@Buskey

exec usp_BusRoute 72

Backup Restore

--Basic backup
--differential backup
--back up log
--restores
--restore to a point in time

Backup Database Community_Assist 
to Disk='C:\backups\Community_Assist.bak'
with expiredate ='7/12/2017'

use Community_Assist
Create table AfterBackup
(
   afterbackupkey int identity(1,1) primary key,
   AfterbackupTime datetime
)

Insert into AfterBackup(AfterbackupTime)
values(GetDate())

Select * from AfterBackup 
Disk ='C:\backups\Community_Assist.log' 

Backup Database Community_Assist 
to Disk='C:\backups\Community_Assist.bak'
with differential

Backup log Community_Assist 
to Disk ='C:\backups\Community_Assist.log'
Use Master

Backup log Community_Assist to 
Disk ='C:\backups\Community_Assist.log' 
with norecovery

Restore database Community_Assist 
From Disk ='C:\backups\Community_Assist.bak' 
with recovery, file =1

Restore database Community_Assist 
From Disk ='C:\backups\Community_Assist.bak' 
with norecovery, file =2

Restore log Community_Assist 
From Disk ='C:\backups\Community_Assist.log' 
with recovery 

Create Database Test
Go
Use Test
Go
Create Table People
(
   personkey int,
   PersonLastName nvarchar(255),
   PersonFirstname nvarchar(255),
   Email nvarchar(255)
)
Go
Insert into People(personKey, 
PersonLastName, PersonFirstname,
Email)
Select personKey, 
PersonLastName, PersonFirstname,
PersonEmail from Community_Assist.dbo.Person

Select * from People

Backup database test to disk='C:\Backups\test.bak'
Backup log test to disk='C:\Backups\test.log'

update People
set PersonLastName='Smith'
use Master



RESTORE LOG Test 
   FROM Disk='C:\Backups\Test.log'  
   WITH FILE=1, NORECOVERY, STOPAT = 'jul 11, 2017 2:10 PM';  
RESTORE DATABASE Test WITH RECOVERY;  
Use Master
Use test
Select * from People