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