XML

--xml

--not part of assignment

use metroAlt

Select * from Employee
For xml raw ('employee'), elements, root('root')

Select BusBarnCity, BusBarnAddress, BusKey, BusPurchaseDate
from BusBarn 
inner join Bus 
on BusBarn.BusBarnKey=bus.BusBarnKey
For xml auto, elements, root('Barns')

--Assignment

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>'

Select * from MaintenanceDetail

Alter table MaintenanceDetail 
Drop column MaintenanceDetailNote

Alter table MaintenanceDetail 
Add MaintenanceDetailNote xml(MaintenanceNoteSchemaCollection)

Insert into MaintenanceDetail(
MaintenanceKey, BusServiceKey, EmployeeKey, MaintenanceDetailNote)
Values(1,1,9,
'<?xml version="1.0" encoding="utf-8"?>
<maintenancenote xmlns="http://www.metroalt.com/maintenancenote">
  <title>B service</title>
<note>
  <p>tires are wearing fast</p>
  <p>I recommend new tires within  5000 miles</p>
</note>
  <followup>Schedule replacement for August 2016</followup>
</maintenancenote>')

Select * from MaintenanceDetail

Select MaintenanceDetailKey, 
MaintenanceDetailNote.query('declare namespace mn="http://www.metroalt.com/maintenancenote"; 
//mn:maintenancenote/mn:title') as Titles
From MaintenanceDetail

Trigger and Admin Assignments

--triggers
use MetroAlt
Go
Create trigger tr_CheckDelete 
on MaintenanceDetail 
instead of delete
As
if not exists
   (Select name from sys.Tables 
   where name ='MaintenanceDetailDeletes')
Begin
    Create table MaintenanceDetailDeletes
 (
  MaintenanceDetailkey int, 
  MaintenanceKey int, 
  BusServiceKey int, 
  EmployeeKey int, 
  MaintenanceDetailNote nvarchar(255)
 )
End
Insert into MaintenanceDetailDeletes
(MaintenanceDetailkey, 
MaintenanceKey, 
BusServiceKey, 
EmployeeKey, 
MaintenanceDetailNote)
Select MaintenanceDetailkey, 
MaintenanceKey, 
BusServiceKey, 
EmployeeKey, 
MaintenanceDetailNote
from Deleted

Select * from MaintenanceDetail

Insert into BusService(BusServiceName)
values('oil change'),
('brakes')

Insert into Maintenance(MaintenceDate, BusKey)
values(getDate(),45)

Insert into MaintenanceDetail(MaintenanceKey, BusServiceKey, EmployeeKey, MaintenanceDetailNote)
values(IDENT_CURRENT('Maintenance'),1,8, 'dry as a bone')

Delete from MaintenanceDetail where MaintenanceDetailkey=1

Select * from MaintenanceDetail
Select * from MaintenanceDetailDeletes

Select * from BusScheduleAssignment

go
Create trigger tr_overtime on [dbo].[BusScheduleAssignment]
for insert
As
Declare @EmployeeKey int
Declare @Date Date
Declare @Count int

Select @employeeKey=EmployeeKey, @Date = BusScheduleAssignmentDate
from Inserted
Select @count=Count(EmployeeKey) from BusScheduleAssignment
where [BusScheduleAssignmentDate]=@Date and EmployeeKey = @EmployeeKey
if @count > 1
Begin
   if not exists
     (Select Name from sys.tables 
  where Name = 'Overtime')
  Begin
     Create table Overtime
  (
    BusScheduleAssignmentKey int, 
    BusDriverShiftKey int, 
    EmployeeKey int, 
    BusRouteKey int, 
    BusScheduleAssignmentDate Date, 
    BusKey int

  )
  End
  Insert into Overtime (BusScheduleAssignmentKey, 
  BusDriverShiftKey, EmployeeKey, BusRouteKey, 
  BusScheduleAssignmentDate, BusKey)
  Select BusScheduleAssignmentKey, BusDriverShiftKey, EmployeeKey, 
  BusRouteKey, BusScheduleAssignmentDate, BusKey
  From Inserted
End

Insert into BusScheduleAssignment( 
BusDriverShiftKey, EmployeeKey, 
BusRouteKey, BusScheduleAssignmentDate, BusKey)
Values(1,4,23,GetDate(),4)

Insert into BusScheduleAssignment( 
BusDriverShiftKey, EmployeeKey, 
BusRouteKey, BusScheduleAssignmentDate, BusKey)
Values(2,4,23,GetDate(),4)

Select * from Overtime

--Admin 
--schema
go
Create schema ManagementSchema
go
Create view managementSchema.EmployeeView
As
Select EmployeeKey, EmployeeLastName, 
EmployeeFirstName, EmployeeAddress,
EmployeeCity, 
EmployeeZipCode, EmployeePhone, 
EmployeeEmail, EmployeeHireDate
From Employee
go
Create view ManagementSchema.BusSchedule
As
Select * from BusScheduleAssignment
Go
--roles collections of permissions
Create role ManagementRole
go
Grant Select, Insert, Update on schema::managementSchema to managementRole

Create login sconger with password='p@ssword', 
default_database = metroAlt

Create user sconger for login sconger

exec sp_addrolemember managementrole, sconger

Backup database metroAlt to disk='C:\backups\MetroAlt.bak'

Restore Database metroAlt from disk='C:\backups\metroAlt'

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

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