Logins, Users, Permissions

Use communityAssist
go
--this is a server level login
--you always log in to the server first
--this gives no permissions but connection
Create Login EmployeeLogin with password='P@ssword1', 
default_database=communityAssist
go
--a schema is a collection of objects
Create schema EmployeeSchema
go

--logins are server level, users are database level. Database
--permissions are granted at the user level
--we are also addeding the user to the EmployeeSchema
Create User EmployeeUser for login EmployeeLogin with default_schema =EmployeeSchema

--here we create a couple of views that belong
--to the employeeSchema
Go
Create view EmployeeSchema.vw_DonationTotals
As
Select Month(DonationDate) as [Month],
Year(donationDate)as [Year],
sum(donationAmount) as total
From Donation
group by Year(donationDate), Month(donationDate)
Go
Create view EmployeeSchema.vw_Donors
As
Select LastName, FirstName, donationDate, donationAmount
From person p
inner join donation d
on p.personkey=d.personkey
go
--now we create a role. roles are collections of permissions
Create Role EmployeeRole
go
--here we assign some permissions to the Employeerole
Grant Select on Schema::EmployeeSchema to EmployeeRole
Grant select on Person to EmployeeRole
Grant insert on ServiceGrant to EmployeeRole

--Now we add the user to the role

exec sp_addrolemember 
@membername='EmployeeUser',
@RoleName='EmployeeRole'

/*************************************
to login as the new user you must first make sure
that the server has SQL Server and Windows Authentication
enabled. to do that
1. Right click on the server (top level)
2. Select Properties
3. Select Security
4. Click the radio button beside SQL Server and Window's Authentication
5. Click OK. You will recieve a warning that changes won't take effect
until the server is resarted.
6. Click OK
7. right click on server again
8.Choose restart, you will have to say yes to the restart twice
9. Once the server has restarted click on connect at the top of the
object explorer
10. in the login dialog box change the windows authentication 
to sql server authentication
11. Enter the login name and the password
***********************************************/

Here is the sequence diagram we did in class

XML in SQL Server

--xml

use CommunityAssist

Select * from person for xml raw

Select * From person for xml raw, elements, root('People')

Select * From person for xml raw('person'), elements, root('people')

Select lastName, firstname, DonationDate, donationAmount
From Person 
inner Join Donation
on person.PersonKey=donation.PersonKey
for xml auto

Select lastName, firstname, DonationDate, donationAmount
From Person 
inner Join Donation
on person.PersonKey=donation.PersonKey
for xml auto, elements, root('root');

use Automart

Select * From Customer.AutoService
Select AutoServiceID, serviceName, serviceprice, serviceDescription.query('declare namespace sd="http://www.automart.com/servicedescription";//sd:servicedescription/sd:parts')
From Customer.AutoService
Where AutoServiceID=7

use CommunityAssist

--an xml schema "validates" an xml document
--by comparing its structure to the one described
--in the schema
--a schema collection stores the schema in sql server
--for comparing xml documents
Create xml Schema Collection xsc_Memo
As
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="memo">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="head">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="to" type="xs:string" />
              <xs:element name="from" type="xs:string" />
              <xs:element name="about" type="xs:string" />
              <xs:element name="date" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="body">
          <xs:complexType>
            <xs:sequence>
              <xs:element maxOccurs="unbounded" name="para" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
      <xs:attribute name="namespace" type="xs:string" use="required" />
    </xs:complexType>
  </xs:element>
</xs:schema>'

Create table Meeting
(
 MeetingID int identity(1,1) primary key,
 MeetingNotes xml(xsc_Memo) --this binds the xml to the schema
)


Insert into Meeting (MeetingNotes)
Values('<?xml version="1.0" encoding="utf-8"?>
<memo namespace="http://www.communityassist.org/memo">
  <head>
    <to>all staff</to>
    <from>Management</from>
    <about>Work behavior</about>
    <date>5/20/2013</date>
  </head>
  <body>
    <para>It has come to our attention that employees are starting the after work beer drinking earlier each friday</para>
    <para>Stop it.</para>
  </body>
</memo>')

--this one won't work because the to and from are reversed in order
Insert into Meeting (MeetingNotes)
Values('<?xml version="1.0" encoding="utf-8"?>
<memo namespace="http://www.communityassist.org/memo">
  <head>
    <from>Management</from>
 <to>all staff</to>
    <about>Work behavior</about>
    <date>5/20/2013</date>
  </head>
  <body>
    <para>It has come to our attention that employees are starting the after work beer drinking earlier each friday</para>
    <para>Stop it.</para>
  </body>
</memo>')

Select * From Meeting

Triggers

-- triggers
--this is a very simple trigger
--it activates on inserts and updates to the 
--person table, but does nothing but
--print out a statemet
Create trigger tr_doNothingTrigger
on Person
after Insert, Update
As
Print 'there was an insert'

--test it
Insert into Person(lastName, Firstname)
Values('Brown', 'John')

Update Person
Set LastName='Browning'
Where Firstname='John'
and Lastname='Brown'
go

--this trigger prevents any deletion
--from the Donation table
--instead of triggers intercept the action
--and do what is in the body of the trigger
--instead of the command
Create trigger tr_DonationDelete
on Donation
instead of Delete
As
Print 'You are not allowed to Delete'



Delete from Donation where DonationKey=3



--to remove this trigger
Drop trigger tr_donationDelete


--just to see who has high total grant amounts
Select personkey, sum(GrantAmount)
From ServiceGrant
Group by Personkey

--business rule will be that the maximum lifetime grant is 2000
-- on an insert into the ServiceGrant
--if their total grants are greater than 2000, 
--then write their grant into a temp table, showing their total grants and
--how much is still available if any
--if their total is less than 2000 with the new grant then just insert it
go
--**************************************************
Create trigger tr_LifeTimeGrant
on ServiceGrant
instead of Insert --will intercept insertions
As
--delaring variables
Declare @PersonKey int
Declare @TotalGrant money
Declare @Grant money
Declare @maxGrant money
Declare @RemainingGrant money
--settin values
set @maxGrant=2000
--these values come from the temporary table
--inserted that exists for as long as the insert
--transaction is open (a millisecond or less)
--but you can access it in a trigger
Select @Personkey=personkey, @Grant=GrantAmount
from Inserted

--get the totals from the GrantService Table
Select @TotalGrant=Sum(GrantAmount) 
From ServiceGrant
Where personKey=@Personkey

--check to see if the total plus the current grant
--are greater than the allowed maximum grant
if (@TotalGrant + @Grant >= @maxGrant)
Begin
  --if it is make sure the tempGrant table
  --exists. If not make it
  if not exists
   (Select name 
   from sys.Tables 
   where name = 'TempGrant')
   Begin
    Create table TempGrant
    (
     PersonKey int,
     GrantAmount money,
     TotalGrant money,
     AvailableGrant money

    )
   End
   
  --table made set the remaining grant amount
   Set @RemainingGrant=@maxGrant-@TotalGrant
   --insert into the tempGrant table
   Insert into TempGrant 
   values(@PersonKey, @Grant, @TotalGrant, @RemainingGrant)
End --end the of
Else --begin the else
Begin
--if it is not larger than the maximum alloted value 
--just insert it into the service grant table
Insert into ServiceGrant(
GrantAmount, GrantDate, PersonKey, ServiceKey, EmployeeKey)
Select GrantAmount, GrantDate, PersonKey, ServiceKey, EmployeeKey
From Inserted
End
--****************************************
--test the trigger

Insert into ServiceGrant(GrantAmount, GrantDate, PersonKey, ServiceKey, EmployeeKey)
Values(950,getDate(),16,3,1)



Select * from TempGrant

Drop table TempGrant

Second Stored Procedure

--stored procedures

--Add new Donor

--insert into person
--insert into personAddress
--insert into personContact
--insert into donation

--we could check to make sure it is an new donor

use communityAssist
go
--version one
alter proc usp_NewDonor
--add all the parameters
@lastname nvarchar(255),
@firstName nvarchar(255),
@Apartment nvarchar(255) =null,
@Street nvarchar(255),
@city nvarchar(255) = 'Seattle',
@state nvarchar(2)='Wa',
@zip nvarchar(10),
@homePhone nvarchar(255),
@email nvarchar(255),
@Donation money,
@EmployeeKey int =null
AS
Insert into Person (LastName, FirstName)
values(@Lastname, @FirstName)

Insert into PersonAddress(Street, Apartment, [State], City, Zip, PersonKey)
Values(@Street, @Apartment, @State,@city,@Zip, ident_current('Person'))

Insert into PersonContact(ContactInfo, PersonKey, ContactTypeKey)
Values(@homePhone, ident_current('Person'), 
(select contacttypeKey from ContactType where contactTypeName='home phone'))

Insert into PersonContact(ContactInfo, PersonKey, ContactTypeKey)
Values(@email, ident_current('Person'), 
(select contacttypeKey from ContactType where contactTypeName='email'))

Insert into Donation(DonationDate, DonationAmount, PersonKey, EmployeeKey)
Values(GetDate(), @Donation, ident_Current('Person'), @EmployeeKey)
go
--inserting all the parameter values
exec usp_NewDonor
@lastname='Skywalker', 
@firstName='Luke', 
@Apartment='101', 
@Street='2001 Dagoba', 
@city='Seattle', 
@state='Wa', 
@zip='98122', 
@homePhone='2065551345', 
@email='skywalker@starwars.com', 
@Donation=2000, 
@EmployeeKey=2

Select * From Person
Select * from PersonAddress
Select * From PersonContact where personkey =52
Select * from Donation

--checking if the defaults work
exec usp_NewDonor
@lastname='Skywalker', 
@firstName='Leah', 
@Street='2011 Dagoba', 
@zip='98122', 
@homePhone='2065551645', 
@email='skywalker@starwars.com', 
@Donation=1400

Select * from Person where Personkey = (Select max(personKey) from Person)
go

--procedure version two
alter proc usp_NewDonor
--add all the parameters
@lastname nvarchar(255),
@firstName nvarchar(255),
@Apartment nvarchar(255) =null,
@Street nvarchar(255),
@city nvarchar(255) = 'Seattle',
@state nvarchar(2)='Wa',
@zip nvarchar(10),
@homePhone nvarchar(255),
@email nvarchar(255),
@Donation money,
@EmployeeKey int =null
AS
--internal variable
Declare @personKey int
--begin the transaction
--must have higher scope than the try
Begin transaction
--begin the try
--all the code in the try will be "tried"
--if there is an error it will stop executing
--and go to the catch
Begin try
--test to make sure it is, in fact,
--a new donor
if not exists
 (Select p.personKey 
    from person p
    inner join personcontact pc
  on p.Personkey=pc.PersonKey
    where lastname=@lastname
    and firstname = @Firstname
    and ContactInfo = @email
    and ContactTypeKey=6)
Begin --if is true they don't exist {

Insert into Person (LastName, FirstName)
values(@Lastname, @FirstName)
--assign the new personkey to the variable we declared
--at the start of the procedure
Set @Personkey=ident_current('Person');

Insert into PersonAddress(Street, Apartment, [State], City, Zip, PersonKey)
Values(@Street, @Apartment, @State,@city,@Zip, @Personkey)

Insert into PersonContact(ContactInfo, PersonKey, ContactTypeKey)
Values(@homePhone, @PersonKey, 
(select contacttypeKey from ContactType where contactTypeName='home phone'))

Insert into PersonContact(ContactInfo, PersonKey, ContactTypeKey)
Values(@email, @PersonKey, 
(select contacttypeKey from ContactType where contactTypeName='email'))

End--}
Else--if the person does exist
Begin
--get the existing person's person key
Select @personkey=p.personKey 
    from person p
    inner join personcontact pc
  on p.Personkey=pc.PersonKey
    where lastname=@lastname
    and firstname = @Firstname
    and ContactInfo = @email
    and ContactTypeKey=6
end
--Now insert the donation whether they are a new
--donor or not
Insert into Donation(DonationDate, DonationAmount, PersonKey, EmployeeKey)
Values(GetDate(), @Donation, @PersonKey, @EmployeeKey)
--if there are no errors commit the transaction
--and write the inserts
Commit tran
end try
Begin Catch
--if there is an error rollback the transaction
--and undo any inserts before the error
Rollback tran
--show an error message
print error_message()
print 'there was an error. The insertion was rolled back'
End Catch

--this will cause an error because the employeekey 16
--does not exist
exec usp_NewDonor
@lastname='Brown', 
@firstName='Luke', 
@Apartment='101', 
@Street='2001 North Streets', 
@city='Seattle', 
@state='Wa', 
@zip='98122', 
@homePhone='2065551245', 
@email='lb@gmail.com', 
@Donation=450, 
@EmployeeKey=16

Select * from Person
Select * From Donation

Relations Amoung Classes

Association

The first relationship is Association. This just means that two classes are associated, that one can call methods in the other.

Here is the class diagram for association. (I am at home and so am using Visio 2013. The diagrams will look a little different than the ones I did at school.)

Here is some very simple code to show what association can look like in practice

Class1

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication1
{
    class Class1
    {
      //this is more complex than need be
      //I made a string field to hold the 
      //value returned from class two
      //then I made a property for it.
      //I have a method that calls class2
      //and assigns the value returned to the
      //string. The method is called from
      //the constructor

        string classTwoString;

        public Class1()
        {
            CallClass2();
        }

        public string ClassTwoString
        {
            get { return classTwoString; }
            set { classTwoString = value; }
        }

        private void CallClass2()
        {
        Class2 c2 = new Class2();
         
        
            ClassTwoString=c2.HereIAm();

        }
    }
}

Class2

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication1
{
    class Class2
    {   
      //this class is beyond simple
      //it contains one method
      //that returns a string

        public string HereIAm()
        {
            return "Hello from Class 2";
        }
    }
}

---

Inheritance

Inheritance is a "Generalization/specialization" relationship. The parent is more generalized, the child more specialized or specific. So in our diagram we have Person as the most general class. Customer and Employee are more specialized versions of Person. HourlyEmployee, SalariedEmployee, and ContractEmployee are more specialized versions of Employee.

Inheritance allows you to abstract out common elements and reduce repetition. Child classes inherit all public fields and methods from the parent

Here are all the inheritance classes

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace inheritanceExample
{
    abstract class  Person
    {
        public string Name { set; get; }
        public string Address { set; get; }
        public string City { set; get; }
        public string State { set; get; }
        public string phone {set; get;}
    }
}


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace inheritanceExample
{
    class Customer:Person
    {
        public string CustomerID { get; set; }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace inheritanceExample
{
    abstract class Employee:Person
    {
        public string HireDate { get; set; }
        public string Title { set; get; }
        public string EmployeeID { set; get; }

       
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace inheritanceExample
{
    class Hourly:Employee
    {
        public double Rate { get; set; }

       
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace inheritanceExample
{
    class Program
    {
        static void Main(string[] args)
        {
            Customer c = new Customer();
            Hourly h = new Hourly();
           
            
        }
    }
}

---

Interface

An interface is an abstraction of methods. When a class implements an interface it "Contracts" to implement all the methods in the interface

Here is the code for an interface

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication2
{
    interface Interface1
    {
        public double CalculatePay();
    }
}

Functions and Simple Stored Procedures

--funtions

--a function that cubes a number

use communityAssist
go

create function fx_Cubed
(@number int)
Returns int
As
Begin
return @number * @number * @number
End

Select dbo.fx_Cubed(5) as [cubed]
Go
--convert the percentage to decimal
--multiply the amount by the converted percentage
--return the percent amount
Alter function fx_DonationPercent
(@Amount money, @Percentage decimal(5,2))
returns money
as
begin
 if (@percentage > 1)
 Begin
  set @percentage = @percentage / 100
 end
return @amount * @percentage
end

--using the function
Select donationAmount, 
dbo.fx_DonationPercent(donationAmount, 78) as [to charity],
dbo.fx_DonationPercent(donationamount, 22) as [to Organization]
from Donation

--using the function with other functions
Select '$' + cast(sum(DonationAmount) as nvarchar)as total,
'$' + cast(sum(dbo.fx_DonationPercent(donationAmount, 78) ) as nvarchar)as [to charity],'$' + cast(sum(dbo.fx_DonationPercent(donationamount, 22)) as nvarchar)as [to Organization]
from Donation

--using the function with a decimal instead of a whole number
Select donationAmount, 
dbo.fx_DonationPercent(donationAmount, .78) as [to charity],
dbo.fx_DonationPercent(donationamount, .22) as [to Organization]
from Donation

Go
---parameterized views
Alter procedure usp_Donations
--the user provides this paramater value
@PersonID as int
As --bebinig of the body of the stored procedure
Select LastName,
Firstname,
DonationDate,
DonationAmount
From Donation
inner join Person
on Person.PersonKey = donation.PersonKey
where donation.PersonKey = @PersonID 

--calling the stored procedure
execute usp_Donations 3

--alternate way to call the stored procedure
usp_donations
@PersonID =3

Select * from Donation
go
--this is a more complex version of the stored procedure
--it takes lastname, firstname and emails as parameters
--then it uses them to look up the PersonKey
--then it uses the personkey as a criteria for the query
Alter Procedure usp_DonationsByName
@lastname nvarchar(255),
@firstname nvarchar(255),
@email nvarchar(255)
As
--declare a variable to store the personkey
Declare @PersonKey int
--get the value of the personkey
--based on the last and first names
--and email
Select @Personkey=p.personkey 
from Person p
inner join PersonContact pc
on p.PersonKey=pc.Personkey
Where LastName=@LastName
And FirstName=@firstName
And ContactInfo = @Email
And ContactTypeKey=6
--a print statment can be used as a check
--when you are troubleshooting
--you should remove it from the final version
Print 'PersonKey = ' + cast(@PersonKey as Nvarchar)
--run the query with personkey
--as a parameter in the where clause
Select LastName,
Firstname,
DonationDate,
DonationAmount
From Donation
inner join Person
on Person.PersonKey = donation.PersonKey
where donation.PersonKey = @Personkey

--using the procedure
usp_donationsByName
@lastName='Mann',
@FirstName='Louis',
@Email='lmann@mannco.com'

GPA Classes

First we did a simple Use Case

Then we worked out these class diagrams. the idea is that we would create a Grade class to store the values and pass it to a list in the GPACalculator class to store and evaluate

Next we added the code. Here is the Grade class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace GPACalculatorProgram
{
    class Grade
    {
        //this is the shortcut way to declare
        //simple properties
        public double GradePoint { get; set; }
        public int Credits { get; set; }
        public string ClassName { get; set; }

        //we also add two constructors
        //a default constructor (no arguments)
        public Grade()
        {
            GradePoint = 0;
            Credits = 0;
            ClassName = null;
        }

        //an alternate constructor that takes three arguments
        public Grade(double grade, int numberOfCredits,string nameOfClass)
        {
            GradePoint = grade;
            Credits = numberOfCredits;
            ClassName = nameOfClass;
        }
    }
}

---

Here is the GPACalcualte class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace GPACalculatorProgram
{
    class GPACalculator
    {
        //a generic list object that stores Grades
        private List gradeList;

        //constructor
        public GPACalculator()
        {
            gradeList= new List();
        }
        
        //method to add grades to list
        public void AddGrade(Grade grade)
        {
            gradeList.Add(grade);
        }

        //calculate GPA
        public double CalculateGPA()
        {
            double gpa = 0;
            double weight=0;
            int totalCredits = 0;
            foreach (Grade g in gradeList)
            {
                weight += g.Credits * g.GradePoint;
                totalCredits += g.Credits;
            }

            gpa = weight / totalCredits;
            return gpa;
        }

        //return the gradelist with the grades
        public List GetGrades()
        {
            return gradeList;
        }
    }
}

---

Here is the Display class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace GPACalculatorProgram
{
    class Display
    {
        GPACalculator calc;
        //contructor
        public Display()
        {
            calc = new GPACalculator();
        }
        //do inputs
        //get outputs GPA--List the Contents

        //this loops for as long as a user wants to enter
        //grades. It could be made better with some validation
        public void AddGradeInfo()
        {
            string addMore="yes";
            while (addMore.Equals("yes") )
            {
                Console.WriteLine("Enter the Course Name");
                string course = Console.ReadLine();
                Console.WriteLine("Enter your Grade");
                double g = double.Parse(Console.ReadLine());
                Console.WriteLine("Enter the Credits");
                int c = int.Parse(Console.ReadLine());

                //package the information into a grade object
                Grade gr = new Grade(g,c,course);
                //pass the object to the GPACalculator method
                //that adds it to the list
                calc.AddGrade(gr);

                Console.WriteLine("Do you want to add another Grade? Yes/No");
                addMore = Console.ReadLine().ToLower();

            }
        }

            public void GetGPA()
            {
                //show the gpa
                Console.WriteLine(calc.CalculateGPA().ToString("F2"));
            }

            public void showGrades()
            {
                //list out all the grade
                List gr = calc.GetGrades();
                Console.WriteLine("Course\tGrade\tCredits");
                foreach (Grade g in gr)
                {
                    
                    Console.WriteLine(g.ClassName + "\t" + g.GradePoint + "\t" + g.Credits);
                }
            }
        }
    }

---

Finally the Program

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace GPACalculatorProgram
{
    class Program
    {
        static void Main(string[] args)
        {
            Display d = new Display();
            d.AddGradeInfo();
            d.GetGPA();
            d.showGrades();
            Console.ReadKey();
        }
    }
}

Here are the classes as they look in Visual Studio's Class Diagram

Here is the sequence diagram we generated in visual Studio