ASP Example Link to code in GitHub

Here is the link to the code in github: https://github.com/spconger/ITC110ASPExample

Link to the simple notepad files

Here is the link to the simple notepad files on Github https://github.com/spconger/SimpleNotepad

MySQL SQL Morning

Use Sakila;
show tables;
Select * from Customer;

Select * from Actor;
Select * from Film;
Select * from film_actor;

Create view artist_films
As
Select first_name, Last_name, title, release_year
From actor
inner join film_Actor
on actor.Actor_Id = film_actor.actor_id
inner join film
on film.Film_id = film_actor.Film_id;

Select * from artist_films
where title = 'Academy Dinosaur';

Insert into Actor(first_name, last_name)
values('Joe','Smith'),
('Mark', 'Jones'),
('Thomas', 'Tankengine');

Insert into film_actor(actor_id, film_id)
values(201,1);

Update actor
Set Last_name = 'Smithers',
first_name='Joseph'
Where actor_id = 201;

Start transaction;
commit;

Select * from Actor;

Start Transaction;
Update actor
Set Last_name = 'Smithers',
first_name='Joseph';


use sakila;

select * from author;

Class peer Excercise and Link to code for WPF form

The code for the Windows form is posted on Github at https://github.com/spconger/FirstWPFForm

Here is the code for the peer excercise:

Word

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

namespace ClassPeerExcercise
{
    public class Word
    {
        public string Term { get; set; }
        public string Definition { get; set; }
    }
}

---

TermDictionary

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

namespace ClassPeerExcercise
{
    public class TermDictionary
    {
        private List words;

        public TermDictionary()
        {
            words = new List();
        }

        public void AddWord(Word w)
        {
            words.Add(w);
        }

        public string GetDefinition(string wordTerm)
        {
            string def = null;
            foreach(Word w in words)
            {
                if(w.Term.Equals(wordTerm))
                {
                    def = w.Definition;
                    break;
                }
            }

            return def;
        }

    }
}

---

Program

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

namespace ClassPeerExcercise
{
    class Program
    {
        TermDictionary td = new TermDictionary();
        static void Main(string[] args)
        {
            Program p = new ClassPeerExcercise.Program();
            Word w1 = new Word();
            w1.Term = "shirt";
            w1.Definition = "garmet with sleeves";
            p.td.AddWord(w1);

            Word w2 = new Word();
            w2.Term = "Book";
            w2.Definition = "covers containing pages";
            p.td.AddWord(w2);

            Word w3 = new Word();
            w3.Term = "pencil";
            w3.Definition = "writing utensil";
            p.td.AddWord(w3);

            Console.WriteLine("Enter a word to get Definition");
            string searchWord = Console.ReadLine();

            string def = p.td.GetDefinition(searchWord);

            if(def != null)
            {
                Console.WriteLine(def);
            }
            else
            {
                Console.WriteLine("Not in dictionary");
            }

            Console.WriteLine("Press any key to exit");
            Console.ReadKey();

        }
    }
}

Classes one

I have put the files on Github

The first example is at a href="https://github.com/spconger/ITC110ClassExample1"

the second example is at https://github.com/spconger/ITC110ClassExample2

Requirements Discussion Python Club

Morning requirements

Data Requirements:

Meetings Schedule: place, time, Date,speakers, subject, duration, 
Meeting minutes:  participants (all the above), minutes, Topics to Follow up, results,
Meetup: meetup place, time, Date, participants, speakers, subject, duration, contact, post date, member who posted
Members: MemberId, name, email, member since, leadership role
Posting: event
Resources and Tutorials: member who posted, Post date, subject, URL, description, rating
General Announcements: who posted, topic, Date Posted, Text
Discussion Board: Topic, who posted, discussion, comments
Login information--
Report inappropriate data--member reporting, post id, what is inappropriate, reviewed by, date, discussion

Reporting Requirements
Users: Officers, Members, public, Database Admin
Views do you need--security
Members, email list, 
List events, Upcoming events, history of events
List of meetings with minutes
List of Resources
List of Analytics
Advertizing

Security
Officers : what can they do
Post meetings, Add members, Delete members, remove posts, post minutes, Post events, post resources, discussions
Post meet ups, (Insert, update and delete in any table)
Manage member interactions to keep them legal and civil
View anything
Roles
members: Post events, discussion, announcements, add resources
Edit their own posts, but no one elses
view anything (question on member names and emails) member has option to make email public or not
Members can report inappropriate content, only view own reports and discussion
Non members
View most things, not member emails, not reports on inappropriate material.

Afternoon list

Data Requirements:
Meetings: time, location, subject, speaker, Date, RSVP, Description, email,  
Minutes: who attended, questions, Recap, comments,start and end time of meeting, Date time for post,
Who posted it

Members: names, emails, phone, Whether or not they is an officer, Category, date added
Discussion or blog: date, time, topic, Text of blog, member who posted, comments, title,
Public comment --registration
Events, Presentations: time, Date, location, (city state zip), speaker, contact about, 
what member posted, topic, Description, fee, age restriction, sponsor. signup sheet
Announcements: date time topic member who posted, content, 
appropriate: flag announcements, events, member who objects and reason for objecting
which announcement or event or other.
Resources name, url, date and time posted, Who posted it, 
comments, who posted it, rating
List of persons who express interest in the club (

Stake holders:
Members
Officers
Web site developers--
Public
Database Admin

Reporting Requirements
Track members
Calendar of events
See who joined recently
See meetings and minutes
List of resources
sorted by rating
Contacts report

Security
Officers
What permissions should they have (roles)
Add remove members, Add remove most anything, change updating most everything, (probably through stored procedures)
Select anything and veiw anything.
members: add resources, add blog entries, discussions, events, 
Update what they post, but nothing else.
Remove, but store
Public
view most things, view member (no, flag it yes or no)

Corrected code for troubleshooting

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

namespace TroubleShootingExample
{
    class Program
    {
        /*******************************
        This program creates an array
        Populates it with random numbers
        outputs the contents of the array
        and then returns the average,
        the minimum and the maximum
        values in the array.
        Several errors have been built in.
        Steve Conger 11/5/2016
        ********************************/

        int size = 0;
        static void Main(string[] args)
        {
            Program p = new Program();
            p.Display();
            p.PauseIt();
        }



        private int[] CreateArray()
        {
            Console.WriteLine("Enter the size of the Array");
            size = int.Parse(Console.ReadLine());
            int[] myRandomArray = new int[size];
            return myRandomArray;
        }

        private int[] PopulateArray()
        {
            int[] myArray = CreateArray();
            Random rand = new Random();
            for (int i = 0; i < myArray.Length; i++)
            {
                int number = rand.Next(1, 1001);
                myArray[i] = number;
            }

            return myArray;
        }

        private void DisplayArrayValues(int[] rArray)
        {
            foreach (int i in rArray)
            {
                Console.WriteLine(i);
            }
        }


        private int CalculateAverage(int[] rArray)
        {
            int total = 0;
            for (int i = 0; i < rArray.Length; i++)
            {
                total += rArray[i];
            }

            int average = total / size;
            return average;
            

        }

        private int CalculateMaximum(int[] rArray)
        {
            int max = 0;
            foreach (int i in rArray)
            {
                if (max < i)
                    max = i;
            }
            return max;
        }

        private int CalculateMinimum(int[] rArray)
        {
            int min = rArray[0];
            foreach (int i in rArray)
            {

                if (min > i)
                    min = i;
            }
            return min;
        }

        private void Display()
        {
            int[] newArray = PopulateArray();
            DisplayArrayValues(newArray);
            Console.WriteLine();
            Console.WriteLine("The Average array value is {0}", CalculateAverage(newArray));
            Console.WriteLine("The maximum value is {0}", CalculateMaximum(newArray));
            Console.WriteLine("The minimum value is {0}", CalculateMinimum(newArray));

        }

        private void PauseIt()
        {
            Console.WriteLine("Press any key to exit");
            Console.ReadKey();
        }


    }

}

SQL Afternoon

Select * from Person;
Select PersonLastName, PersonFirstName, PersonEmail
From Person;
Select PersonLastName, PersonFirstName, PersonEmail
From Person
order by PersonLastName;

Select PersonLastName, PersonFirstName, PersonEmail
From Person
order by PersonLastName Desc, PersonFirstName;

Select PersonLastName, PersonFirstName, PersonEmail
From Person
Where PersonLastName='Lewis'

Select * from Product
Where ProductPrice > 3

Select * from Product
Where ProductPrice between 3 and 5

Select * from Product
Where ProductPrice >=3 and ProductPrice <=5
Order by ProductPrice desc

Select * From Sale
Where SaleDate>'11/2/2016'and saleDate < '11/3/2016'

Select Month(SaleDate) as [Month], 
Year(SaleDate) as [Year] from Sale

Select Sale.SaleKey, SaleDate, PersonLastName, EmployeeKey,
ProductName,
SaleDetailPriceCharged,SaleDetailQuantity,
SaleDetailDiscount,SaleDetailSaleTaxPercent,
SaleDetailEatInTax
From Sale
inner join Person
on Sale.CustomerKey=Person.Personkey
inner Join SaleDetail
on Sale.SaleKey=SaleDetail.SaleKey
inner join Product
on Product.ProductKey=SaleDetail.ProductKey
Where Sale.SaleKey = 1

Insert into Sale(SaleDate, CustomerKey, EmployeeKey)
Values(GetDate(),5,2)
Insert into SaleDetail(SaleKey, ProductKey, 
SaleDetailPriceCharged, SaleDetailQuantity, 
SaleDetailDiscount, SaleDetailSaleTaxPercent, 
SaleDetailEatInTax)
Values(Ident_Current('Sale'), 3, 3.25,1,0,.09,.02),
(Ident_Current('Sale'), 5, 2.25,1,0,.09,.02)

Select * from SaleDetail

Select * from Person
Begin tran
Update Person Set PersonLastName='Smith'
Where PersonKey=3

Rollback tran
Commit tran

Methods

The first examples

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

namespace MethodsExample
{
    class Program
    {
        //int number; this would make available everwhere 
        //in the class
        static void Main(string[] args)
        {
            Program p = new Program();
            p.GetNumber();
            p.Pause();
        }

        private void GetNumber()
        {
            Console.WriteLine("Enter a number");
            int number = int.Parse(Console.ReadLine());
            Display(number);
        }

        private void Display(int numb)
        {
            int result = Cube(numb);
            Console.WriteLine("The cube of {0} is {1}", numb, result);
        }
           

        private int Cube(int num)
        {
            int cube = num * num * num;
            return cube;
        }

        private void Pause()
        {
            Console.WriteLine("Press any key to exit");
            Console.ReadKey();
        }
    }
}

the tip program

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

namespace TipMethodExample
{
    class Program
    {
        //Determine the amount of the meal
        //Determine the percent of tip
        //Calculate tax amount
        //Calcualte tip amount
        //Display results (amount, tax, tip, total);
        //assign constant values
        private const double TAXPERCENT = .092;
        private const double PERCENTDIVISOR = 100;

        static void Main(string[] args)
        {
            //instantiate the class Program
            Program p = new Program();

            //call the calculate method
            //which calls GetMealAmount and GetTipPercent
            p.Calculate();
            //call the pause method
            p.Pause();
        }

        private double GetMealAmount()
        {
            //this gets the meal amount and returns it to 
            //the calling method
            Console.WriteLine("Enter the Meal Amount");
            double amount = double.Parse(Console.ReadLine());
            return amount;//the return must return the type 
            //given in the method signature (double)
            //and must be the last statement in the method
        }

        private double GetTipPercent()
        {
            //this gets the tip percent and returns
            //it to the calling method
            Console.WriteLine("Enter the Tip Percent as a Whole number");
            double tipPercent = double.Parse(Console.ReadLine());
            return tipPercent;
        }

        private void Calculate()
        {
            //call get meal amount and assign returned value
            //to mealAmount
            //same for tipPerce
            double mealAmount = GetMealAmount();
            double tipPerc = GetTipPercent();
            double taxAmount = mealAmount * TAXPERCENT;
            double tipAmount = mealAmount * (tipPerc/ PERCENTDIVISOR);
            double total = mealAmount + taxAmount + tipAmount;
            //pass calculate values to Display method
            Display(mealAmount, taxAmount, tipAmount, total);

        }

        private void Display(double meal, double tax, double tip, double total)
        {
            Console.WriteLine("Meal:\t\t{0:C}", meal);
            Console.WriteLine("Tax:\t\t{0:C}", tax);
            Console.WriteLine("Tip:\t\t{0:C}", tip);
            Console.WriteLine("Total:\t\t{0:C}", total);
        }

        private void Pause()
        {
            Console.WriteLine("Press any key to exit");
            Console.ReadKey();
        }
    }
}

here is the peer excercise--though without comments for now

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

namespace PeerExcerciseMethods1
{
    class Program
    {
        //get a number
        //test the number
        //do the calculation
        //display the results
        static void Main(string[] args)
        {
            Program p = new Program();
            int number=p.TestNumber();
            p.Display(number);
            p.Pause();
        }

        private int GetNumber()
        {
            Console.WriteLine("Enter an integer between 1 and 40");
            int number=0;
            bool good = false;
            //this checks to make sure it is a valid
            while (!good)
            {
                good = int.TryParse(Console.ReadLine(), out number);
                if (!good)
                    Console.WriteLine("Enter a valid integer");
            }

            return number;
        }

        private int TestNumber()
        {
            int num = GetNumber();
            //the while instead of an if
            //loops until they get it right
            while (num < 0 || num > 40)
            {
                num = GetNumber();
            }
            return num;
        }

        private int GetPrime(int number)
        {
            return number * number - number + 41;
        }

        private void Display(int number)
        {
            Console.WriteLine("Your prime number is {0}", GetPrime(number));
        }

        private void Pause()
        {
            Console.WriteLine("Press any key to exit");
            Console.ReadKey();

        }


    }
}

SQl 1

Use Bakery Select * from Person; Select PersonLastname, PersonFirstname, PersonEmail From Person; Select PersonLastname, PersonFirstname, PersonEmail From Person Where PersonEmail is not null Order by PersonLastName;

Here is the SQL we did in class

Select PersonLastName, PersonFirstname, PersonEmail
From Person
Where PersonFirstName = 'Tabitha';

Select PersonLastName, PersonFirstname, PersonEmail
From Person
Where PersonFirstName Like 'T%';

Select * from Product

Select * from Product where productprice > 3
Select * from Product where productprice = 3
Select * from Product where productprice >= 3
Select * from Product where productprice between 2.5 and 3.5
Select * from Product where productprice >= 2.5 
and ProductPrice <= 3.5

Select * from Sale where SaleDate >= '10/31/2016'
Select * from saleDetail

Select Sale.SaleKey,SaleDate,PersonLastName, Employeekey, ProductName, 
SaleDetailPriceCharged, SaleDetailQuantity,SaleDetailDiscount,
SaleDetailSaleTaxPercent,SaleDetailEatInTax
From Person
inner join Sale
on Person.PersonKey=Sale.CustomerKey
inner Join SaleDetail
on Sale.SaleKey=SaleDetail.SaleKey
inner Join Product
on Product.ProductKey=SaleDetail.ProductKey
Where Sale.SaleKey=1

Select * from Product

Insert into Sale (SaleDate, CustomerKey, EmployeeKey)
Values(GetDate(),5,2)
Insert into SaleDetail(SaleKey, 
ProductKey, SaleDetailPriceCharged, 
SaleDetailQuantity, SaleDetailDiscount, 
SaleDetailSaleTaxPercent, 
SaleDetailEatInTax)
Values(ident_current('Sale'),3,2.25,1,0.0,0.09,0),
(ident_current('Sale'),6,1.25,1,0.0,0.9,0)

Select * from SaleDetail 

Select * from Person

Begin tran

Update Person Set PersonLastName='Smith'
Where PersonKey = 2

rollback tran

Commit tran

Arrays

Here are the first examples

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

namespace ArrayExamples
{
    class Program
    {
        /* overview of arrays*/

        static void Main(string[] args)
        {
            //manually laying out an array
            //Each element has an index number
            //indexes always start with 0
            int[] myArray = new int[5];
            myArray[0] = 1;
            myArray[1] = 3;
            myArray[2] = 14;
            myArray[3] = 12;
            myArray[4] = 8;

            //you can access an array element by its index
            Console.WriteLine("The third element is {0}", myArray[2]);

            int sum = 0;
            //calculating the sum
            for(int i =0; i<myArray.Length;i++)
            {
                Console.WriteLine(myArray[i]);
                sum += myArray[i]; //sum=sum + myArray[i]
            }

            Console.WriteLine("the sum is {0}", sum);
            Console.WriteLine("The average is {0}", (double)sum / myArray.Length);

            //getting the max
            int max = 0;
            for (int i = 0; i < myArray.Length; i++)
            {
                if (myArray[i] > max)
                {
                    max = myArray[i];
                }
            }

            Console.WriteLine("The maximum value is {0}", max);

            //alternate, easier way. C# contains methods
            //for sum, average, minimum and maximum
            Console.WriteLine(myArray.Sum());
            Console.WriteLine(myArray.Average());
            Console.WriteLine(myArray.Max());
            Console.WriteLine(myArray.Min());

            Console.WriteLine("Press any key to exit");
            Console.ReadKey();
        }
    }
}

the second set of examples

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

namespace arrayExamples2
{
    class Program
    {
        /*more array examples */
        static void Main(string[] args)
        {
            //one way to initialize an array
            //string[] teams = new string[5];
            //another way to initialize an array
            string[] teams = { "Seahawks", "Cardinals", "Rams", "Fourty Niners" };

            //loop through the array and output its content
            for(int i = 0; i < teams.Length; i++)
            {
                Console.WriteLine(teams[i]);
            }

            Console.WriteLine();

            //sort the array elements
            Array.Sort(teams);

            for (int i = 0; i  < teams.Length; i++)
            {
                Console.WriteLine(teams[i]);
            }

            //declare but not iniialize an array
            string[] foods;

            
            Console.WriteLine("how many foods do you want to list?");
            int number = int.Parse(Console.ReadLine());

            //initialize an array in
            foods = new string[number];

            for (int i=0;i <foods.Length;i++)
            {
                //add elements to the array dynamically
                Console.WriteLine("Add a food");
                foods[i] = Console.ReadLine();
            }

            // a different kind of loop: it loops
            //through all the objects in a collection
            //of objects in this case strings
            foreach(string s in foods)
            {
                Console.WriteLine(s);
            }

            //a two dimensional array
            string[,] Books = new string[3, 2];
            Books[0, 0] = "Lord of the rings";
            Books[0, 1] = "J.R.R Tolkein";
            Books[1, 0] = "Ulysses";
            Books[1, 1] = "James Joyce";
            Books[2, 0] = "Gravity's Rainbow";
            Books[2, 1] = "Thomas Pinchon";

            Console.WriteLine("Enter a title");
            string title = Console.ReadLine();

            for (int i = 0; i  < 3; i++)
            {
                if (title.Equals(Books[i, 0]))
                {
                    Console.WriteLine(Books[i, 1]);
                }
               
            }


            Console.WriteLine("Press any key to exit");
            Console.ReadKey();
        }
    }
}

peer excercise

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

namespace peerArray
{
    class Program
    {
        static void Main(string[] args)
        {
            int[] myArray = new int[20];
            Random rand = new Random();

            for(int i = 0;i<20;i++)
            {
                myArray[i] = rand.Next(1, 101);
                Console.WriteLine(myArray[i]);
            }

            Console.WriteLine();
            Console.WriteLine("the Max is {0}", myArray.Max());
            Console.WriteLine("the Min is {0}", myArray.Min());

            Console.WriteLine("Press any key to exit");
            Console.ReadKey();
        }
    }
}

SQL For creating databases and tables

Create Database Books2

use Books2

Create table book
(
  BookKey int identity(1,1) primary key,
  BookTitle nvarchar(255) not null,
  BookYear int null,
  BookPrice money not null
);

Go

Create table Author
(
   AuthorKey int identity(1,1) primary key,
   AuthorName nvarchar(255) not null
);

Go
Create table AuthorBook
(
  AuthorKey int not null,
  BookKey int not null,
  Constraint pk_AuthorBook primary key (AuthorKey, Bookkey),
  Constraint fk_Author foreign key (AuthorKey) references Author (authorKey),
  Constraint fk_Book foreign key (BookKey) references Book (BookKey)
 );

 Insert into Book(BookTitle, BookYear, BookPrice)
 Values('Charlotte''s Web',1952,7.5),
 ('American Gods',1999,8.50),
 ('The once and Future King',1950, 3.5)

 Insert into Author(AuthorName)
 values('E.B.White'),
 ('Neil Gaiman')

 Insert into AuthorBook(AuthorKey, bookKey)
 Values(1,1),
 (2,2),
 (2,3)

 Select * from Book
 Select * from Author
 Select * from AuthorBook

Loops

For loops

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

namespace ForLoopExcercise
{

    class Program
    {
        /*This program will explore
         * repetition with for loops
         * for loops are best for counted loops
         * where you know how many loops 
         * you want to do.
         *Steve Conger 10/18/2016
         */


        static void Main(string[] args)
        {
            int number = 0;
            long number2 = 0;
            Console.WriteLine("How many times do you want to loop?");
            number = int.Parse(Console.ReadLine());
            Console.WriteLine("Enter the number to get exponents");
            number2 = long.Parse(Console.ReadLine());
            Console.WriteLine();

            for (int i = 1; i <= number; i++)
            {
                number2 *= number2; // number=number * number
                Console.WriteLine(number2);
            }

            Console.WriteLine();
            for(int i=0;i<=10;i++)
            {
                Console.WriteLine(i);
                  
            }

            for (int i = 0; i <= 10; i+=2)
            {
                Console.WriteLine(i);
            }

            //infint loop
            //for (int i = 1; i > 0; i++)
            //{
            //    Console.WriteLine(i);
            //}

            Console.WriteLine("Press any key to exit");
            Console.ReadKey();
        }
    }
}

While Loops

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

namespace WhileLoopExample
{
    class Program
    {
        /*
         * This program shows while loops
         * the first provides a menu of 
         * opens and loops until the user 
         * chooses 4 for exit.
         * Within the loop is another loop
         * that loops until the user enters
         * a valid integer menu choice
         */
        static void Main(string[] args)
        {
            int choice = 1;
            //loop until they choose 4 to exit
            while (choice != 4)
            {
                Console.WriteLine("Hello:\t\t1");
                Console.WriteLine("Hello:\t\t2");
                Console.WriteLine("Hello3:\t\t3");
                Console.WriteLine("Goodbye:\t4");


                bool goodChoice = false;

                //loop until they enter a valid integer
                while (!goodChoice)
                {
                    Console.WriteLine("Enter your choice");
                    goodChoice = int.TryParse(Console.ReadLine(), out choice);

                    if(!goodChoice)
                    {
                        Console.WriteLine("Enter an integer 1 to 4");
                    }
                    
                }

                switch(choice)
                {
                    case 1:
                        Console.WriteLine("Hello One");
                        break;
                    case 2:
                        Console.WriteLine("Hello two");
                        break;
                    case 3:
                        Console.WriteLine("Hello three");
                        break;
                    case 4:
                        Console.WriteLine("Goodbye");       
                        break;
                    default:
                        Console.WriteLine("Invalid choice");
                        break;
                }//end switch
            
            }//end loop

            /*
             * do loops do the same thing as
             * while loops with one difference
             * they test the condition at the 
             * end of the loop. That means that
             * they always run at least once.
             * whereas a while loop may never 
             * run if the initial condition is false
             */
            string continues= "yes";
            do
            {
                Console.WriteLine("continue yes/no");
                continues = Console.ReadLine();
                continues = continues.ToLower();
            }while(continues.Equals("yes"));

            Console.WriteLine("Press any key to exit");
            Console.ReadKey();
        }//end main
    }//end class
}//end namespace

Afternoon version of Student Normalization

Normalization first Take

First normal form:

There should be no repeated fields, and no arrays. All the entries in an attribute should be of the same kind. Break out any repetitions, give everything a key

2nd Normal form:

You should remove all functional dependencies: (Sub Themes, groups of attributes that relate to each other but not to the topic (key) of the entity

3rd Normal form

Is about removing transient dependencies--attributes that modify or describe another attribute and not the key

Here is the student entity not normalized

Here is the student ERD after normalization

Entity Relational Design

An entity is depicted as a box. The top is the title or name of the entity. Next is a primary key, a separator, and then the attributes that describe the entity. Bold font indicates that an attribute is required.

There are three types of relationships between entities

One-to-Many, which is the normal relationship among entities and will account for 95% of all relationships. Here is one Department has many employees.

The one side always points to the primary key, the crow's foot (the three point prong) always points to the many side

One-to-One. This is quite rare but is legal. It says for every record in one table there is exactly one matching record in the second table. One example of this is when a table is split up for security reasons. For instance, the public information about an employee could be in one table and the private in another.

The third relationship is Many-to-Many. This is legal in design, but no database can process it. Whenever you have a many to many relationship, you must resolve it into two one-to-many relationships by creating a linking table. For instance, assume an employee can be in more than one department. That means that each employee can be in many departments and each department contains many employees. A many to many relationship. To resolve this we create a linking table, here called EmployeeDepartment.

Here is the coffee shop ERD we did in class

Selection Statements

Here is the code for our selection examples

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

namespace SelectionExamples
{
    class Program
    {
        /*This program provides examples of
         * if statements and switches
         * Steve Conger 10/11/2016*/

        static void Main(string[] args)
        {
            int number;
            Console.WriteLine("Enter number");
            //try parse returns a boolean (true/false)
            //if the number is good it assigns it to the variable
            //outside the method (out)

            bool goodNumber = int.TryParse(Console.ReadLine(),out number);

            if(goodNumber == false)
            {
                Console.WriteLine("restart and enter a valid integer");
                Console.WriteLine("Press any key to exit");
                Console.ReadKey();
                return; //ends the program
            }

            // >, <, >=, <=, ==, != (not equal)
            //with strings it is often best to use .Equals
            if (number > 10)
            {
                Console.WriteLine("the number is greater than 10");
            }
            else if(number==10)
            {
                Console.WriteLine("the number is 10");
            }
            else
            {
                Console.WriteLine("The number is less than 10");
            }

            Console.WriteLine("Enter the name of the current month");
            string month = Console.ReadLine();
            // || or , && and

            if(month.Equals("October") || month.Equals("october"))
            {
                Console.WriteLine("Happy Halloween");

            }
            else
            {
                Console.WriteLine("You are a bit confused");
            }


            //switch statement
            int choice;
            Console.WriteLine("Enter an integer between 1 and 4");
            bool good = int.TryParse(Console.ReadLine(), out choice);
            //!good same as good !== true or good == false
            if(!good)
            {
                return;
            }

            //switch statement
            switch(choice)
            {
                case 1:
                    Console.WriteLine("You entered 1");
                    break;
                case 2: //fall through to three
                case 3:
                    Console.WriteLine("You chose 2 or 3");
                    break;
                case 4:
                    Console.WriteLine("You entered 4");
                    break;
                default://captures anything else
                    Console.WriteLine("Not a valid choice");
                    break;
            }

            Console.WriteLine("Press any key to exit");
            Console.ReadKey();


            
        }
    }
}

here is the code for the birthday program

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

namespace Birthdate
{
    class Program
    {
        static void Main(string[] args)
        {
            DateTime birthday;
            DateTime today = DateTime.Now;
            int age;
            
            
            
            //you can try parse for DateTime as well
            Console.WriteLine("enter your birth date");
            bool goodDate = DateTime.TryParse(Console.ReadLine(), out birthday);

            if (!goodDate)
            {
                Console.WriteLine("enter a valid birth date");
                bool good = DateTime.TryParse(Console.ReadLine(), out birthday);
                //nested if statement
                if(!good) //give a second chance
                {
                    Console.WriteLine("Sorry still not valid");
                    Console.WriteLine("Press any key to exit");
                    Console.ReadKey();
                    return;
                }//end inner if

            }//end outer if
            //just subtract years. We could also do months and days
            age = today.Year - birthday.Year;
            Console.WriteLine("You are {0} years old", age);

            Console.WriteLine("Press any key to exit");
            Console.ReadKey();
        }//end main
    }//end program class
}//end namespace

Here is a solution for the peer excercise

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

namespace PeerIf
{
    class Program
    {
        static void Main(string[] args)
        {
            string password = "P@ssw0rd1";
            string username = "customer";
            string user;
            string pass;

            Console.WriteLine("Enter your user name");
            user = Console.ReadLine();

            Console.WriteLine("Enter your password");
            pass = Console.ReadLine();

            if(password.Equals(pass) && username.Equals(user))
            {
                Console.WriteLine("Welcome user");
            }
            else
            {
                Console.WriteLine("invalid password or user name");
            }

            /*an alternative way
             * if(username.Equals(user)
             * {
             *     if(password.Equals(pass)
             *     {
             *         Console.WriteLine("Welcome");
             *     }
             *     else
             *     {
             *          Console.WriteLIne("Inavalid");
             *     }

             * }
             * */

            Console.WriteLine("Press any key to exit");
            Console.ReadKey();





        }
    }
}

Using Integers and doubles

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

namespace NumberExamples
{
    class Program
    {
        /// 

        /// This program shows examples of integers
        /// and doubles with math operators
        /// Steve Conger 10/6/2016
        /// 
        /// 
        static void Main(string[] args)
        {
            //integers and doubles
            //operators + - * / %
            //decaring number variables
            string name;
            int number;
            int number2;
            int answer;
            double answer2;
            double number3;

            //getting values from the user
            //anything entered on the console is a string
            //it must be converted into the appropriate
            //type of number
            Console.WriteLine("Enter the first Integer");
            number = int.Parse(Console.ReadLine());

            Console.WriteLine("Enter the second Integer");
            number2 = int.Parse(Console.ReadLine());

            //this takes a console entry and converts into a double 
            //(with decimal places)
            Console.WriteLine("Enter a meal amount");
            number3 = double.Parse(Console.ReadLine());

            

            answer = number + number2;

            Console.WriteLine("The sum of {0} + {1} = {2}", number, number2, answer);
            answer = number2 / number;

            Console.WriteLine("The quotient of {0} / {1} = {2}", number2, number, answer);

            answer = number2 % number;
            Console.WriteLine("The remainder of {0} / {1} = {2}", number2, number, answer);

            //this casts the integer number2 into a double. it allows the division
            //to return decimal places
             answer2= (double)number2 / number;

            //Math is a built in library of math functions. 
            //Unlike the format codes this actually changes
            //the underlying number
            answer2 = Math.Round(answer2, 2);
           
            //{0:F@} would format the number to only show 2 decimal places
            //but it does not change the underlying numbers
            //c formats the number to look like currency
            Console.WriteLine("The double quotient of {0} / {1} = {2}", number2, number, answer2);
            Console.WriteLine("The double quotient of {0} / {1} = {2:C}", number2, number, answer2);

            Console.WriteLine("Press any key to exit");
            Console.ReadKey();



        }//end main
    }//end program
}//end namespace

First assignment Code

Here is the first part of assignment 1.

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

namespace Assignment1_1
{
    /*This program will take in a user's
    name and email and display it 
    last name, first name -- email
    Steve Conger, 10/4/2016
    */

    class Program
    {
        //starting point of the program
        static void Main(string[] args)
        {
            string firstName;
            string lastName;
            string email;

            Console.WriteLine("Enter your first name");
            firstName = Console.ReadLine();

            Console.WriteLine("Enter your last name");
            lastName = Console.ReadLine();

            Console.WriteLine("Enter your email");
            email = Console.ReadLine();

            Console.WriteLine("{0}, {1}--{2}", lastName, firstName, email);
            Console.WriteLine(lastName + ", " + firstName + "--" + email);

            Console.WriteLine("Press any key to exit");
            Console.ReadKey();


        }//end of Main
    }//end of class program
}//end of namespace

Here is the second part of assignment one

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

namespace Assignment1_2
{
    class Program
    {
        /*This program produces a
         * mailing label
         * steve conger 10/4/2016
         * */
        static void Main(string[] args)
        {
            Console.WriteLine("Enter your full name");
            string fullName = Console.ReadLine();

            Console.WriteLine("Enter your street address");
            string address = Console.ReadLine();

            Console.WriteLine("Enter your City");
            string city = Console.ReadLine();

            Console.WriteLine("Enter your State");
            string state = Console.ReadLine();

            Console.WriteLine("Enter your zip code");
            string zipcode = Console.ReadLine();

            Console.WriteLine();

            Console.WriteLine(fullName);
            Console.WriteLine(address);
            Console.WriteLine("{0}, {1} {2}",city, state, zipcode);

            Console.WriteLine();

            //an alternate way with line break excape characters (\n)
            Console.WriteLine(fullName+"\n" + address + "\n" + city + ", " + state + " " + zipcode);

            Console.WriteLine();

             Console.WriteLine("Press any key to exit");
            Console.ReadKey();

        }
    }
}

Requirements (Afternoon Class)

Requirements

Client information
Name, address, age, phone, email, homeless
Request amount,  paid amount distributed grant, max and min amounts, Employment
Gender, family, language (demographics), education level, number of kids
Nature request, categories
Dates of request—date of review, date paid
Income
How many times, who approved,
Donors (name, email, address (apartment, street, city, state)) main contact 
Donation amount, matching gifts, category of donor, Donation date
Employees—not human resources 

Reporting Requirements

Who donated
How much was donated per year, per month, per quarter
How many loans granted denied
How many per category

Security Requirements

Who should have access to what data?
Enter and edit own
Public access reports Categories

Requirements: Morning Class

Here are the fields (Data Requirements) that we came up with, discussing Community Assist.

Name of clients
Request amounts
Types of services
One time limit, life time limits
Donors—name 
How much they are donate
Address of donors (street, city, state, zip)
Addresses of clients and emails
Email addresses (not required)
Grants applied for—by charity
Grant approved or not and reason
Date of request, Date of Review, Date of donations
Date of dispersal
Direct or payee
Reviewer
Employees
Request status

Reporting requirements

What follow up with clients or record of meetings
Report on percentages for charity vs admin
Totals spent on each type of service
Totals by month and year

Security requirements

Personal information—who should be able to see it, how to protect
Employee access
Public roles 

Business Rules

Business rule—Do we allow anonymous donors?
Rules about who to they will accept money from.
Every grant must reviewed by at least 2 Employees
Every grant must be reviewed within 7 days after being posted
Constraint—will not be a human resources data

MetroAltDW script

use Master
if exists
  (Select name from sys.Databases
      where name = 'MetroAltDW')
Begin
   Drop Database MetroAltDW
End
Go
Create Database MetroAltDW
go
Use MetroAltDW
Go
Create table DimEmployee
(
  DimEmployeeKey int identity(1,1),
  EmployeeKey int,
  PositionKey int,
  PositionName nvarchar(255),
  EmployeeHireDate date,
  EmployeeHourlyPayRate decimal(5,2)
)
Go
Create table DimBus
( 
   DimBuskey int identity(1,1),
   BusKey int,
   BustTypeKey int,
   BusTypeDescription nvarchar(255),
   BusTypePurchasePrice decimal(12,2)
)
Go
Create table DimRoute
(
    DimRouteKey int identity(1,1),
 BusRouteKey int,
 BusRouteZone nvarchar(255)
)
Go
Create table DimSchedule
(
    DimscheduleKey int identity(1,1),
 BusDriverShiftKey int,
 busSheduleAssignmentKey int
 
)
Go
Create table DimDate
(
    dimDateKey int identity(1,1),
 BusScheduleAssignmentDate date,
 BusScheduleYear int,
 BusScheduleMonth int
)

go
Create table FactSchedule
(
   DimEmployeeKey int not null,
   DimBusKey int not null,
   DimRouteKey int not null,
   DimScheduleKey int not null,
   DimdateKey int not null,
   riders int,
   FaresPerRoute money
)
go

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_FactScheduleKey 
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_DimSchedule foreign key(DimScheduleKey)
References DimSchedule(DimScheduleKey)

Alter table FactSchedule
Add Constraint FK_DimDate foreign key(DimDateKey)
References DimDate(DimDateKey)

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, BustTypeKey, BusTypeDescription, BusTypePurchasePrice)
Select BusKey, bt.BusTypeKey, BusTypeDescription, BusTypePurchasePrice
From MetroAlt.dbo.Bus b
inner Join MetroAlt.dbo.Bustype bt
on b.BusTypekey=bt.BusTypeKey

Insert into DimRoute ( BusRouteKey, BusRouteZone)
Select  BusRouteKey, BusRouteZone from MetroAlt.dbo.BusRoute

insert into DimSchedule(BusDriverShiftKey, busSheduleAssignmentKey)
select BusDriverShiftKey, busScheduleAssignmentKey
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, FareAmount * riders 
From MetroAlt.dbo.BusScheduleAssignment bsa
inner join DimEmployee de
on de.EmployeeKey=bsa.EmployeeKey
inner join dimBus db
on bsa.BusKey=db.BusKey
inner join dimRoute dr
on dr.BusRouteKey=bsa.BusRouteKey
inner join DimSchedule ds
on ds.busSheduleAssignmentKey=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

Select * from FactSchedule

Full text Catalog

Alter Database Community_Assist
Add FileGroup FullTextGroup

Use Community_Assist

Create FullText Catalog ClientNeedDescriptions
on Filegroup FullTextGroup

--Drop FullText Catalog ClientNeedDescriptions

Create FullText index on GrantRequest(GrantRequestExplanation)
Key Index [PK__GrantReq__75A91ED011DB90BA]
on ClientNeedDescriptions
With Change_tracking auto
go

Select * from GrantRequest
Update GrantRequest
Set GrantRequestExplanation= 'I just got a new job and needed a bus pass'
where GrantRequestKey = 9

Select GrantRequestExplanation from GrantRequest 
where Freetext(GrantRequestExplanation, 'child')

Select GrantRequestExplanation from GrantRequest 
where Contains(GrantRequestExplanation, 'formsof(Inflectional, needing)')

Select GrantRequestExplanation from GrantRequest 
where Contains(GrantRequestExplanation, 'formsof(Inflectional, break)')

Select GrantRequestExplanation from GrantRequest 
where Contains(GrantRequestExplanation, 'child*')

Select GrantRequestExplanation from GrantRequest 
where Contains(GrantRequestExplanation, 'near((food, groceries),10)')

Select GrantRequestExplanation from GrantRequest 
where Contains(GrantRequestExplanation, 'afford AND month')

Select GrantRequestExplanation from GrantRequest 
where Contains(GrantRequestExplanation, 'Rent OR school')

Select GrantRequestExplanation from GrantRequest 
where Contains(GrantRequestExplanation, 'Food AND NOT stamps')

Data Warehouse First Draft

Use Master
go
if exists
  (Select name from sys.databases
     where name = 'Community_AssistDW')
Begin
Drop database Community_AssistDW
End
Go
Create database Community_AssistDW
Go
Use Community_AssistDW
Go
--create the dimension tables
Create table DimEmployee
(
 DimEmployeeKey int identity (1,1),
 EmployeeKey int,
 EmployeeHireDate date,
 EmployeeAnnualSalary money,
 PositionName nvarchar(255)
)
Go
Create table DimClient
(
    DimClientKey int identity(1,1),
 PersonKey int,
 PersonAddressZip nvarchar(255),
 PersonDateEntered DateTime
)
Go
Create table DimGrantType
(
   DimGrantTypeKey int identity(1,1),
   GrantTypeKey int,
   GrantTypeName nvarchar(255)

)
go
Create table DimGrant
(
   DimGrantKey int identity(1,1),
   GrantKey int
   
)
go
Create table DimDate
(
   DimDateKey int identity (1,1),
   GrantReviewDate datetime,
   GrantYear int,
   GrantMonth int
)

Go
--fact table
Create table FactGrant
(
    DimEmployeeKey int not null,
 DimClientKey int not null,
 DimGrantTypeKey int not null,
 DimGrantKey int not null,
 DimDateKey int not null,
 GrantRequestAmount money,
 GrantAllocationAmount money
)

--add primary key
Alter table DimEmployee
Add Constraint pk_DimEmployee primary key (DimEmployeeKey)

Alter table DimClient
Add Constraint pk_Dimclient primary key (DimClientKey)

Alter table DimGrantType
Add Constraint pk_GrantType primary key (DimGrantTypeKey)

Alter table DimGrant
Add Constraint pk_DimGrant primary key (DimGrantKey)

Alter table DimDate
Add Constraint pk_DimDate primary key (DimDateKey)

--add foreign keys
Alter table FactGrant
Add Constraint pk_FactGrant primary key (DimEmployeeKey, DimClientKey, DimGrantTypekey,
  DimGrantKey, DimDateKey)

Alter table FactGrant
Add Constraint fk_Employee foreign key (DimEmployeeKey) 
references DimEmployee(DimEmployeeKey)

Alter table FactGrant
Add Constraint fk_Client foreign key (DimClientKey) 
references DimClient(DimClientKey)

Alter table FactGrant
Add Constraint fk_GrantType foreign key (DimGrantTypeKey) 
references DimGrantType(DimGrantTypeKey)

Alter table FactGrant
Add Constraint fk_Grant foreign key (DimGrantKey) 
references DimGrant(DimGrantKey)

Alter table FactGrant
Add Constraint fk_Date foreign key (DimDateKey) 
references DimDate(DimDateKey)


Insert into DimEmployee(EmployeeKey, EmployeeHireDate, 
EmployeeAnnualSalary, PositionName)
Select e.EmployeeKey, EmployeeHireDate, EmployeeAnnualSalary, PositionName
From Community_Assist.dbo.Employee e
inner join Community_Assist.dbo.EmployeePosition ep
on e.EmployeeKey=ep.EmployeeKey
inner join Community_Assist.dbo.Position p
on p.PositionKey=ep.PositionKey

Insert into DimClient(PersonKey, PersonAddressZip, PersonDateEntered)
Select Distinct p.PersonKey, PersonAddressZip, PersonEntryDate
From Community_Assist.dbo.Person p
inner join Community_Assist.dbo.PersonAddress pa
on p.PersonKey=pa.PersonKey
inner join Community_Assist.dbo.GrantRequest gr
on p.PersonKey=gr.PersonKey

--Truncate table DimClient

Insert into DimGrantType (GrantTypeKey, GrantTypeName)
Select GrantTypeKey, GrantTypeName from Community_Assist.dbo.GrantType

Insert into DimDate(GrantReviewDate, GrantYear, GrantMonth)
Select GrantReviewDate, Year(GrantReviewDate), Month(GrantReviewDate)
From Community_Assist.dbo.GrantReview

Insert into DimGrant ( GrantKey)
Select GrantRequestKey from Community_Assist.dbo.GrantRequest

--this isn't working
Insert into FactGrant(DimEmployeeKey, DimClientKey, DimGrantTypeKey, DimGrantKey, DimDateKey, GrantRequestAmount, GrantAllocationAmount)
Select Distinct DimEmployeeKey, DimClientKey, DimGrantTypeKey, DimGrantKey, DimDateKey, GrantRequestAmount, GrantAllocationAmount 
From DimClient dc
inner join Community_Assist.Dbo.GrantRequest gr
on dc.PersonKey=gr.PersonKey
inner join dimGrant dg
on dg.GrantKey=gr.GrantRequestKey
inner join DimGrantType dgt
on dgt.GrantTypeKey=gr.GrantTypeKey
inner join Community_Assist.dbo.GrantReview rev
on rev.GrantRequestKey =gr.GrantRequestKey
inner join dimEmployee de
on de.EmployeeKey=rev.EmployeeKey
inner join DimDate dd
on dd.GrantReviewDate=rev.GrantReviewDate


delete from FactGrant

Select * from FactGrant

Select * from Community_Assist.dbo.GrantReview

---

Here is Turner's script which works better.

USE MASTER
GO
IF EXISTS (SELECT NAME FROM sys.databases WHERE name = 'Community_AssistDW')
BEGIN
	DROP DATABASE Community_AssistDW
END
GO

CREATE DATABASE Community_AssistDW
GO
USE Community_AssistDW
GO
-----------
CREATE TABLE DimEmployee
(
	DimEmployeeKey int identity(1,1),
	EmployeeKey int,
	EmployeeHireDate date,
	EmployeeAnnualSalary money,
	PositionName nvarchar(255)
)
GO

CREATE TABLE DimClient
(
	DimClientKey int identity(1,1),
	PersonKey int,
	PersonAddressZip nvarchar(255),
	PersonDateEntered DateTime
)
GO

CREATE TABLE DimGrantType
(
	DimGrantTypeKey int identity(1,1),
	GrantTypeKey int,
	GrantTypeName nvarchar(255)
)
GO

CREATE TABLE DimGrant
(
	DimGrantKey int identity(1,1),
	GrantKey int
)
GO

CREATE TABLE DimDate
(
	DimDateKey int identity(1,1),
	GrantReviewDate DateTime,
	GrantYear int,
	GrantMonth int
)
GO

CREATE TABLE FactGrant
(
	DimEmployeeKey int NOT NULL,
	DimClientKey int NOT NULL,
	DimGrantTypeKey int NOT NULL,
	DimGrantKey int NOT NULL,
	DimDateKey int NOT NULL,
	GrantRequestAmount money,
	GrantAllocationAmount money
)
GO

ALTER TABLE DimEmployee ADD CONSTRAINT pk_DimEmployee PRIMARY KEY (DimEmployeeKey)
ALTER TABLE DimClient ADD CONSTRAINT pk_Dimclient PRIMARY KEY (DimClientKey)
ALTER TABLE DimGrantType ADD CONSTRAINT pk_GrantType PRIMARY KEY (DimGrantTypeKey)
ALTER TABLE DimGrant ADD CONSTRAINT pk_DimGrant PRIMARY KEY (DimGrantKey)
ALTER TABLE DimDate ADD CONSTRAINT pk_DimDate PRIMARY KEY (DimDateKey)
ALTER TABLE FactGrant ADD CONSTRAINT pk_FactGrant PRIMARY KEY (DimEmployeeKey,DimClientKey,DimGrantTypeKey,DimGrantKey,DimDateKey)

ALTER TABLE FactGrant ADD CONSTRAINT fk_Employee FOREIGN KEY (DimEmployeeKey) REFERENCES DimEmployee(DimEmployeeKey)
ALTER TABLE FactGrant ADD CONSTRAINT fk_Client FOREIGN KEY (DimClientKey) REFERENCES DimClient(DimClientKey)
ALTER TABLE FactGrant ADD CONSTRAINT fk_GrantType FOREIGN KEY (DimGrantTypeKey) REFERENCES DimGrantType(DimGrantTypeKey)
ALTER TABLE FactGrant ADD CONSTRAINT fk_Grant FOREIGN KEY (DimGrantKey) REFERENCES DimGrant(DimGrantKey)
ALTER TABLE FactGrant ADD CONSTRAINT fk_Date FOREIGN KEY (DimDateKey) REFERENCES DimDate(DimDateKey)
GO

INSERT INTO DimEmployee(EmployeeKey, EmployeeHireDate, EmployeeAnnualSalary, PositionName)
	SELECT e.EmployeeKey, EmployeeHireDate, EmployeeAnnualSalary, PositionName FROM Community_Assist.dbo.Employee e
	INNER JOIN Community_Assist.dbo.EmployeePosition ep ON e.EmployeeKey = ep.EmployeeKey
	INNER JOIN Community_Assist.dbo.Position p ON p.PositionKey = ep.PositionKey
GO

INSERT INTO DimClient(PersonKey,PersonAddressZip,PersonDateEntered)
	SELECT p.PersonKey, PersonAddressZip, PersonEntryDate FROM Community_Assist.dbo.Person p
	INNER JOIN (SELECT MIN(PersonAddressKey) PersonAddressKey, PersonKey FROM Community_Assist.dbo.PersonAddress GROUP BY PersonKey) mpk ON p.PersonKey = mpk.PersonKey
	INNER JOIN Community_Assist.dbo.PersonAddress pa ON mpk.PersonAddressKey = pa.PersonAddressKey
	WHERE p.PersonKey IN (SELECT PersonKey FROM Community_Assist.dbo.GrantRequest)
GO

--TRUNCATE TABLE DimClient
INSERT INTO DimGrantType(GrantTypeKey, GrantTypeName)
	SELECT GrantTypeKey, GrantTypeName FROM Community_Assist.dbo.GrantType
GO

INSERT INTO DimDate(GrantReviewDate, GrantYear, GrantMonth)
	SELECT DISTINCT GrantReviewDate, YEAR(GrantReviewDate), MONTH(GrantReviewDate) FROM Community_Assist.dbo.GrantReview
GO

INSERT INTO DimGrant(GrantKey)
	SELECT GrantRequestKey FROM Community_Assist.dbo.GrantRequest
GO

INSERT INTO FactGrant(DimEmployeeKey, DimClientKey, DimGrantTypeKey, DimGrantKey, DimDateKey, GrantRequestAmount, GrantAllocationAmount)
	SELECT DimEmployeeKey, DimClientKey, DimGrantTypeKey, DimGrantKey, DimDateKey, GrantRequestAmount, GrantAllocationAmount
	FROM DimClient dc

	INNER JOIN Community_Assist.dbo.GrantRequest greq ON dc.PersonKey = greq.PersonKey
	INNER JOIN Community_Assist.dbo.Person p ON dc.PersonKey = p.PersonKey
	INNER JOIN Community_Assist.dbo.GrantReview  grev ON greq.GrantRequestKey = grev.GrantRequestKey
	INNER JOIN DimGrantType dgt ON greq.GrantTypeKey = dgt.GrantTypeKey
	INNER JOIN DimGrant dg ON dg.GrantKey = greq.GrantRequestKey
	INNER JOIN Community_Assist.dbo.Employee e ON grev.EmployeeKey = e.EmployeeKey	
	INNER JOIN DimEmployee de ON e.EmployeeKey = de.EmployeeKey
	INNER JOIN DimDate dd ON grev.GrantReviewDate = dd.GrantReviewDate
GO

SELECT * FROM FactGrant ORDER BY DimGrantTypeKey ASC
SELECT GrantTypeKey, COUNT(*) FROM Community_Assist.dbo.GrantRequest GROUP BY GrantTypeKey

SELECT * FROM Community_Assist.dbo.GrantReview
SELECT * FROM Community_Assist.dbo.GrantRequest WHERE GrantRequestKey = 18

-- shitty debug query
SELECT DimEmployeeKey, DimClientKey, DimGrantTypeKey, DimGrantKey, DimDateKey, grev.GrantReviewDate FROM DimClient dc
INNER JOIN Community_Assist.dbo.GrantRequest greq ON dc.PersonKey = greq.PersonKey
INNER JOIN Community_Assist.dbo.GrantReview grev ON greq.GrantRequestKey = grev.GrantRequestKey
INNER JOIN DimEmployee de ON grev.EmployeeKey = de.EmployeeKey
INNER JOIN DimGrantType dgt ON greq.GrantTypeKey = dgt.GrantTypeKey
INNER JOIN DimGrant dg ON greq.GrantRequestKey = dg.GrantKey
INNER JOIN DimDate dd ON grev.GrantReviewDate = dd.GrantReviewDate
ORDER BY DimClientKey ASC

SELECT * FROM DimClient ORDER BY PersonKey


SELECT MIN(PersonKey) PersonKey, MIN(PersonAddressKey) PersonAddressKey, PersonAddressZip, PersonAddressStreet FROM Community_Assist.dbo.PersonAddress GROUP BY PersonAddressZip, PersonAddressStreet ORDER BY PersonKey
SELECT * FROM Community_Assist.dbo.PersonAddress WHERE PersonAddressZip = ''

SELECT MIN(PersonAddressKey) PersonAddressKey, PersonKey FROM Community_Assist.dbo.PersonAddress GROUP BY PersonKey

Security Procedures

Create Schema ManagerSchema
Go
Create proc ManagerSchema.usp_UpdateEmployee
@EmployeeKey int, 
@EmployeeLastName nvarchar(255), 
@EmployeeFirstName nvarchar(255), 
@EmployeeAddress nvarchar(255), 
@EmployeeCity nvarchar(255), 
@EmployeeZipCode nchar(5), 
@EmployeePhone nchar(10), 
@EmployeeEmail nvarchar(255), 
@PositionName nvarchar(255),
@EmployeeHourlyPayRate decimal(5,2),
@OrignalDatePositionAssigned Date,
@EmployeePositionDateAssigned date
As
Declare @PositionKey int
Select @Positionkey = PositionKey from Position
Where PositionName = @PositionName
if @positionKey is null
Begin
print 'Invalid position'
return -1
End
Begin tran
Begin try
Update Employee
Set [EmployeeLastName] = @EmployeeLastName,
[EmployeeFirstName] = @EmployeeFirstName,
[EmployeeAddress]=@EmployeeAddress,
[EmployeeCity]=@EmployeeCity,
[EmployeeZipCode]=@EmployeeZipCode,
[EmployeePhone]=@EmployeePhone,
[EmployeeEmail]=@EmployeeEmail
Where EmployeeKey = @EmployeeKey

Update EmployeePosition
Set [PositionKey]=@PositionKey,
[EmployeeHourlyPayRate]=@EmployeeHourlyPayRate,
[EmployeePositionDateAssigned]=@EmployeePositionDateAssigned
Where EmployeeKey = @EmployeeKey
and EmployeePositionDateAssigned = @OrignalDatePositionAssigned

Commit Tran
End try
Begin Catch
Rollback tran
Print error_Message()
return -2
End Catch


Select * from Employee

exec ManagerSchema.usp_UpdateEmployee
@EmployeeKey = 1, 
@EmployeeLastName ='Summers', 
@EmployeeFirstName='Susanne', 
@EmployeeAddress='28 Elm Ave.', 
@EmployeeCity='Seattle', 
@EmployeeZipCode='98100', 
@EmployeePhone='2065554312', 
@EmployeeEmail='Elizabeth.Adams16@metroalt.com', 
@PositionName='Lawyer', 
@EmployeeHourlyPayRate=100.00, 
@OrignalDatePositionAssigned='4/14/1998', 
@EmployeePositionDateAssigned='7/28/2016'

Select * from EmployeePosition

create Unique index ix_UniqueEmail on Employee (EmployeeEmail)

--add a driver to a route for a day
go
Create proc managerSchema.usp_AssignShift
 @BusDriverShiftKey int, 
 @EmployeeKey int, 
 @BusRouteKey int, 
 @BusScheduleAssignmentDate date, 
 @BusKey int
 As
 Begin tran
 Begin try
 Insert into BusScheduleAssignment (
BusDriverShiftKey, 
EmployeeKey, 
BusRouteKey, 
BusScheduleAssignmentDate, 
BusKey)
Values(
@BusDriverShiftKey, 
@EmployeeKey, 
@BusRouteKey, 
@BusScheduleAssignmentDate, 
@BusKey)

Commit Tran
End Try
Begin Catch
Rollback tran
print Error_Message()
return -1
End Catch



Exec [ManagerSchema].[usp_AssignShift]
@BusDriverShiftKey=2, 
@EmployeeKey=6, 
@BusRouteKey=127, 
@BusScheduleAssignmentDate='7/29/2016', 
@BusKey=43

Select * from [dbo].[BusScheduleAssignment]
Select * from Busroute

Select * from EmployeePosition where EmployeeKey=6
go
Create Proc managerSchema.usp_TotalHoursDateRange
@EmployeeKey int,
@startDate Date,
@EndDate Date
AS

Select EmployeeKey, sum(dateDiff(hour,[BusDriverShiftStartTime],[BusDriverShiftStopTime])) [Total Hours]
From BusDriverShift bs
Inner join BusScheduleAssignment bsa
on bs.BusDriverShiftKey=bsa.BusDriverShiftKey
Where EmployeeKey=@EmployeeKey
And [BusScheduleAssignmentDate] between @startDate and @EndDate
Group by EmployeeKey
go
exec [ManagerSchema].[usp_TotalHoursDateRange]
@EmployeeKey= 6, 
@startDate ='6/1/2014', 
@EndDate='6/8/2014'

Security code

--logins are stored in master
Use master

--create sql server login
Create login GrantApplicantLogin with password='P@ssw0rd1', default_database=Community_Assist

--use community_assist
Use Community_Assist
go
--crate schema 
Create Schema ApplicantSchema
Go
--create user 
Create user GrantApplicant for login GrantApplicantLogin 
with default_schema= ApplicantSchema
Go
--create role
Create role ApplicantRole
Go

--assign permissions on objects to role
Grant Select, exec on schema::ApplicantSchema to ApplicantRole

--add the user to the role
alter Role Applicantrole add member GrantApplicant
go
--Create procedure as part of procedure
create proc ApplicantSchema.usp_GrantHistory
@personKey int
As
Select GrantRequestDate, GrantTypeName, GrantRequestExplanation,
GrantRequestAmount
From GrantRequest
inner join  GrantType
on GrantRequest.GrantTypeKey=GrantType.GrantTypeKey
where personkey = @personkey
go
--this is a view for the human resources schema
--we build this using the graphical tools
Create view HumanResourcesSchema.vw_Employee
as
Select PersonLastName, PersonFirstName,
PersonEmail, EmployeeHireDate, EmployeeannualSalary
From Person p
inner join Employee e
on p.personkey=e.personKey
go

Views for Reports

--List of Donors
--Amount allocated to each grant type
--total donations by year and month
--Total grants by year and month (count, total alloctated)
--Average request per grant type
--total requests vs total allocated (by year month--also by type)

use Community_Assist

Go
Create view vw_DonorContact
As
Select PersonLastName LastName,
PersonFirstName FirstName,
PersonEmail Email
From person
Where Personkey in (Select PersonKey from Donation)

Go
Alter view vw_DonorContactb
As
Select Distinct PersonLastName LastName,
PersonFirstName FirstName,
PersonEmail Email
From person
inner join Donation
on person.PersonKey = Donation.PersonKey
go

Select * from vw_DonorContactb order by LastName

Select * from Donation
go
Create view vw_TotalDonationsByYearMonth
as
Select Year(DonationDate) [Year], 
DateName(month, DonationDate) [MonthName],
format(sum(DonationAmount),'$ #,###.00') Total From Donation
Group by Year(donationdate), DateName(month, DonationDate)
go
Select * from vw_TotalDonationsByYearMonth order by Year

go
Create view vw_TotalGrantsByYearMonth
As
Select Year(GrantReviewDate) [Year],
DateName(month, GrantReviewDate) [Month],
Sum(GrantAllocationamount) Total
From GrantReview
Group by Year(GrantReviewDate), Datename(Month,GrantReviewDate)

go
Create view vw_TotalAllocatedByGrantType
As
Select GrantTypeName, sum(GrantAllocationAmount) Total
From GrantType gt
inner join GrantRequest gr
on gt.GrantTypeKey=gr.GrantTypeKey
inner join GrantReview grw
on gr.GrantRequestKey=grw.GrantRequestKey
Group by GrantTypeName

go
Create view vw_CountByGrantType
As
Select GrantTypeName, Count(*) [Count]
From GrantType gt
inner join GrantRequest gr
on gt.GrantTypeKey=gr.GrantTypeKey
inner join GrantReview grw
on gr.GrantRequestKey=grw.GrantRequestKey
Group by GrantTypeName

go

Create view vw_RequestvsAllocationByGrantType
AS
Select GrantTypeName, Sum(GrantRequestAmount) RequestAmount,
Sum(GrantAllocationAmount) AllocatedAmount,
Sum(GrantRequestAmount)-Sum(GrantAllocationAmount) [Difference]
From GrantType gt
inner join GrantRequest gr
on gt.GrantTypeKey=gr.GrantTypeKey
inner join GrantReview grw
on gr.GrantRequestKey=grw.GrantRequestKey
Group by GrantTypeName
go
Create proc usp_RequestvsAllocationByGrantType
@GrantTypeName nvarchar(255)
As
Select GrantTypeName, Sum(GrantRequestAmount) RequestAmount,
Sum(GrantAllocationAmount) AllocatedAmount,
Sum(GrantRequestAmount)-Sum(GrantAllocationAmount) [Difference]
From GrantType gt
inner join GrantRequest gr
on gt.GrantTypeKey=gr.GrantTypeKey
inner join GrantReview grw
on gr.GrantRequestKey=grw.GrantRequestKey
where GrantTypeName=@GrantTypeName
Group by GrantTypeName


exec usp_RequestvsAllocationByGrantType 'Child Care'

Snapshot

Create database Community_AssistSnapshot
on
(name='Community_Assist', 
filename=
'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Community_Assist.ds')
as
Snapshot of Community_Assist

Use Community_AssistSnapshot
Select * from Person

Use Community_Assist
Update Person
Set PersonFirstName = 'jason'
Where Personkey=1

Select * From Person

Backup Restore

--full backup of the database
Backup database Community_Assist
To disk= 'C:\Backups\Community_Assist.Bak'
with init

--create table after full backup and insert a record
Create table Test2
(
 TestKey int identity(1,1) primary key,
 TestDate DateTime default GetDate(),
 TestDescription nvarchar(255)
)

Insert into Test2 (TestDescription)
values('Table added after full backup.')

--then we do the differential backup
Backup Database Community_Assist
To Disk='C:\Backups\Community_Assist.Bak'
with differential



Insert into Test2 (TestDescription)
values('This record added after differential')

--now backup the log
use Master
Backup log Community_Assist
to disk ='C:\Backups\Community_Assistlog.Bak'
with norecovery, no_truncate
-- restore full backup (file  1)
Restore Database Community_Assist
From disk='C:\Backups\Community_Assist.Bak'
with norecovery, file=1

--Resore Differential backup (file 2)
Restore Database Community_Assist
From disk='C:\Backups\Community_Assist.Bak'
with norecovery, file=2

--restore the log 
Restore Log Community_Assist
From disk='C:\Backups\Community_Assistlog.Bak'
with recovery

use Community_Assist
Select * from Test2
/*
Syntax for restoring to a moment in time
RESTORE LOG AdventureWorks  
   FROM AdventureWorksBackups  
   WITH FILE=5, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';  
RESTORE DATABASE AdventureWorks WITH RECOVERY;   
*/

Table Partitioning

/*PARTITIONING EXAMPLE
I am going to use the Employee table in metroalt for this partition
though I am not going to make the partitions in MetroAlt itself,
Rather I will make a new database and a new table and copy the data
from metroAlt into that database.
For the partitions we will use 1995-1999, 2000-2004, 2004-2009, 2010 forward
Our database will need to have five file groups.
Here's the pattern:
Create database PartitionTest
go
Alter database PartitionTest
Add FileGroup Sales2005;
Go
Alter Database PartitionTest
Add file 
(
 name ='Sales2005',
 FileName='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SALES2005File.ndf',
 Size=5MB,
 MaxSize=200MB,
 FileGrowth=5mb
 )
 To filegroup Sales 2005
*/


if exists
   (Select name from sys.Databases 
   where name = 'EmployeePartition')
Begin
Drop Database EmployeePartition
end
Go
-- create the database.
Create database EmployeePartition

go
--add the first file group and file
alter database EmployeePartition
Add Filegroup Employees1995Group
Go
Alter database EmployeePartition
Add File
(name='Employees1995',
Filename='C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Employees1995File.mdf',
size=5mb,
MaxSize=200mb,
FileGrowth=5mb
)
to Filegroup Employees1995Group

go

alter database EmployeePartition
Add Filegroup Employees2000Group
Go
Alter database EmployeePartition
Add File
(name='Employees2000',
Filename='C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Employees2000File.mdf',
size=5mb,
MaxSize=200mb,
FileGrowth=5mb
)
to filegroup Employees2000Group
go

alter database EmployeePartition
Add Filegroup Employees2005Group
Go
Alter database EmployeePartition
Add File
(name='Employees2005',
Filename='C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Employees2005File.mdf',
size=5mb,
MaxSize=200mb,
FileGrowth=5mb
)
To Filegroup Employees2005group
go

alter database EmployeePartition
Add Filegroup Employees2010Group
Go
Alter database EmployeePartition
Add File
(name='Employees2010',
Filename='C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Employees2010File.mdf',
size=5mb,
MaxSize=200mb,
FileGrowth=5mb
)
to filegroup Employees2010Group

go

alter database EmployeePartition
Add Filegroup Employees2015Group
Go
Alter database EmployeePartition
Add File
(name='Employees2015',
Filename='C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Employees2015File.mdf',
size=5mb,
MaxSize=200mb,
FileGrowth=5mb
)
To filegroup Employees2015Group

/*Next we want to create the partion function and
the partition scheme.
*/

Use EmployeePartition

Create Partition Function Fx_Hiredate (date)
As range left
For values('19941231', '19991231','20041231','20091231')

Go

--create the partition schema
Create Partition scheme sch_HireDate
As Partition fx_HireDate
to (Employees1995Group, Employees2000Group, Employees2005Group, Employees2010Group, Employees2015Group)

--now create the table that uses the partition schema
CREATE TABLE [dbo].[Employee](
 [EmployeeKey] [int],
 [EmployeeLastName] [nvarchar](255),
 [EmployeeFirstName] [nvarchar](255),
 [EmployeeAddress] [nvarchar](255),
 [EmployeeCity] [nvarchar](255),
 [EmployeeZipCode] [nchar](5),
 [EmployeePhone] [nchar](10),
 [EmployeeEmail] [nvarchar](255),
 [EmployeeHireDate] [date] 
) on sch_HireDate(EmployeeHireDate)

--insert into the table
Insert into Employee(EmployeeKey, 
EmployeeLastName, 
EmployeeFirstName, 
EmployeeAddress, 
EmployeeCity, 
EmployeeZipCode, 
EmployeePhone, 
EmployeeEmail, 
EmployeeHireDate)
Select EmployeeKey, 
EmployeeLastName, 
EmployeeFirstName, 
EmployeeAddress, 
EmployeeCity, 
EmployeeZipCode, 
EmployeePhone, 
EmployeeEmail, 
EmployeeHireDate
From MetroAlt.Dbo.Employee

Select * from Employee

--1995-1999
Select * from Employee
where $partition.FX_HireDate(EmployeeHireDate)=2

--2000-2004
Select * from Employee
where $partition.FX_HireDate(EmployeeHireDate)=3

--2005-2009
Select * from Employee
where $partition.FX_HireDate(EmployeeHireDate)=4

--2010-2014

Select * from Employee
where $partition.FX_HireDate(EmployeeHireDate)=5

SQL from manage Databases

Create database TestDB2 --gives you all the defaults

--create database with specific file info
Create database TestDB3
On primary 
(Name=N'TestDB3', 
filename='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDb3.mdf')
Log on
(Name=N'TestDb3_log',
filename='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDb3_Log.ldf')

--Drop a database
Drop Database TestDb2

--detatch a database

USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'TestDB3'
GO

attach a database
USE [master]
GO
CREATE DATABASE [TestDB3] ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDb3.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDb3_Log.ldf' )
 FOR ATTACH
GO

Admin and XML

--the Amin Assignment

use metroalt

--what is a schema ownership of object
--default dbo.
go
Create schema ManagementSchema


Go
Create view ManagementSchema.vw_AnnualRidership
As
Select Year(BusScheduleAssignmentDate) as [Year],
Sum(Riders) as Total
From BusScheduleAssignment bsa
join Ridership r
on bsa.BusScheduleAssignmentKey=r.BusScheduleAssigmentKey
Group by Year(BusScheduleAssignmentDate)
go

Create role ManagementRole

Grant Select, insert, update on Employee to ManagementRole

Grant select on Schema::managementSchema to managementRole

Create login Albert with password='P@ssw0rd1'

Create user Albert for login Albert



exec sp_addrolemember 'ManagementRole','Albert'

Backup Database MetroAlt To Disk='C:\Backup\MetroAlt.bak'

--XML Assignment
Create Xml Schema Collection ManagementNotesXMLSchema
As
'

  
    
      
        
        
      
    
  
'

Create Table maintenanceNotes
(
   maintenanceNotesKey int identity(1,1),
   maintenanceNotesDate Date,
   maintenanceNote xml (managementNotesXmlSchema)

)

Insert into maintenanceNotes (maintenanceNotesDate, maintenanceNote)
Values (GetDate(), '

  
  
  
    The brakes are shot
  
  
    Needs new tires
  
')


Select * from maintenanceNotes

Use Community_Assist

Select * From GrantReviewComment

Select GrantReviewKey, EmployeeKey, 
GrantReviewCommentDate, 
GrantReviewNote.query
('declare namespace rn="http://www.community_assist.org/reviewnotes";//rn:reviewnote/rn:recommendation')
From GrantReviewComment

Fraction and unit testing

here is the test document

here is the Unit Test Code

using System;
using Fractions;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace FractionTestProject
{
    [TestClass]
    public class UnitTest1
    {

        [TestMethod]
        public void AdditionTestMethod()
        {
            Fraction f1 = new Fraction("1/2");
            Fraction f2 = new Fraction(1, 3);
            Fraction f3 = f1 + f2;

            Assert.AreEqual("5/6", f3.ToString());
        }

        [TestMethod]

        public void MultiplicationTestMethod()
        {
            Fraction f1 = new Fraction("1/2");
            Fraction f2 = new Fraction(1, 3);
            Fraction f3 = f1 * f2;

            Assert.AreEqual("1/6", f3.ToString());
        }

        [TestMethod]
        public void GreaterThanTestMethod()
        {
            Fraction f1 = new Fraction("1/2");
            Fraction f2 = new Fraction(1, 3);


            Assert.IsTrue(f1 > f2);
        }

    }
}

Triggers

--stored procedures
--parameterized view

use Community_Assist
go
Create proc usp_HRViewProc
@EmployeeKey int
As
Select PersonLastName,
PersonfirstName,
PersonEmail,
EmployeeHireDate,
EmployeeAnnualSalary
From Person p
inner Join Employee e
on p.PersonKey=e.PersonKey
Where EmployeeKey = @EmployeeKey

exec usp_HRViewProc @EmployeeKey=2
Go
--Create hash password function
Alter function fx_hashPassword2
(@seed int, @password nvarchar(50))
returns varbinary(500)
As
Begin
Declare @newPassword nvarchar(70)
set @newPassword = 
cast(@seed as nvarchar(20)) + @Password
Declare @hashed varbinary(500)
set @hashed = hashbytes('sha2_512', @newPassword)
return @hashed
End


Select * from businessrule
go
--new person stored procedure
Alter proc usp_NewPerson
@lastName nvarchar(255),
@FirstName nvarchar(255)=null,
@Email nvarchar(255),
@password nvarchar(50),
@AptNumber nvarchar(255)=null,
@Street nvarchar(255),
@City nvarchar(255)='Seattle',
@State nvarchar(255)='WA',
@Zip nvarchar(255),
@HomePhone nvarchar(255)
As
--test to see if person exists
if exists
 (Select PersonLastName, PersonEmail From Person
 Where PersonLastName=@LastName
 And PersonEmail=@Email)
 Begin--begin if
 Print 'Already registered'
 return
 End--end if

--create the password hash
Declare @seed int
Set @seed = dbo.fx_GetSeed()
declare @hashedPass varbinary(500)
set @hashedPass = dbo.fx_HashPassword(@seed,@password)

Begin tran --start a transaction
Begin try -- begin try
--insert into person
Insert into Person(  
PersonLastName, PersonFirstName,
 PersonEmail, PersonPassWord, 
 PersonEntryDate, PersonPassWordSeed)
 Values(@LastName, @FirstName,
 @Email, @hashedPass,GetDate(),@seed)

 --get the key of the person just inserted
 Declare @key int = ident_current('Person')
 --insert into PersonAddress
 Insert into PersonAddress(
 PersonAddressApt,
 PersonAddressStreet, 
 PersonAddressCity, PersonAddressState, 
 PersonAddressZip, PersonKey)
 Values( @AptNumber,@street,@city,@state,@Zip,@key)

 Insert into Contact([ContactNumber],
 [ContactTypeKey], [PersonKey])
 Values (@HomePhone, 1, @Key)

 Commit Tran --commit the transaction if no error
 End Try
 Begin Catch
 Rollback Tran
 Print Error_Message()
 End Catch


 exec usp_NewPerson
 @lastName = 'Conger', 
 @FirstName='Steve', 
 @Email='spconger@gmail.com', 
 @password='congerPass', 
 @Street='101 nowhere bld', 
 @Zip='98122', 
 @HomePhone='2065551201'

 Select * from PersonAddress
 Select * from Contact

 Select * from Person
 go
 --stored procedure update Address
 Create proc usp_UpdateAddress
 @PersonAddressApt nvarchar(255) = null, 
 @PersonAddressStreet nvarchar(255), 
 @PersonAddressCity nvarchar(255)= 'Seattle', 
 @PersonAddressState nvarchar(255)='Wa', 
 @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 = '304', 
@PersonAddressStreet='100 South Mann Street', 
@PersonAddressZip='98001',
@PersonKey=1;


--trigger
--like a stored proc but triggered by an event 
--insert update delete
--triggers have no parameters
-- for an event, instead of an event

Create Trigger Tr_PersonDelete on Person
instead of Delete
As
if not exists
  (Select name from sys.Tables 
    where name='PersonDelete')
Begin
CREATE TABLE [dbo].[PersonDelete](
 [PersonKey] int NOT NULL,
 [PersonLastName] nvarchar(255) NOT NULL,
 [PersonFirstName] nvarchar(255) NULL,
 [PersonEmail] nvarchar(255) NULL,
 [PersonPassWord] varbinary(500) NULL,
 [PersonEntryDate] datetime NOT NULL,
 [PersonPassWordSeed] int NULL
 )

End
Insert into PersonDelete (PersonKey,PersonLastName,
PersonFirstName,PersonEmail,PersonPassWord,
PersonEntryDate, PersonPassWordSeed)
Select PersonKey,PersonLastName,
PersonFirstName,PersonEmail,PersonPassWord,
PersonEntryDate, PersonPassWordSeed
From Deleted --temp table in temp database that stores 
--deletions and updates

Select * from Person

Insert into Person (PersonLastName, PersonFirstName, 
PersonEmail,  PersonEntryDate)
Values('NotHereLong','Deleted','Deleted@gmail.com', GetDate())

Delete from Person where Personkey=132

Select * from PersonDelete
--check to see if request is large than max request
Create trigger tr_CheckMax on GrantRequest
for Insert
as
if not exists
  (Select name from sys.Tables
    Where name ='OverMax')
Begin
Create Table OverMax
(
     RequestDate date,
  PersonKey int,
  GrantypeKey int,
  Explanation nvarchar(255) null,
  RequestAmount money

)

End

Declare @GrantMax money
Declare @GrantType int
Declare @RequestAmount money

Select @GrantType=GrantTypeKey from inserted
Select @GrantMax = GrantTypeMaximum from GrantType
   Where GranttypeKey=@GrantType
Select @RequestAmount = GrantRequestAmount from inserted

if @RequestAmount > @GrantMax
Begin
Insert into OverMax(RequestDate, PersonKey, GranTypeKey,Explanation, RequestAmount)
Select GrantRequestDate, PersonKey, GrantTypeKey, GrantRequestExplanation, GrantRequestAmount
From inserted
End

Select * from GrantType


insert into GrantRequest( 
GrantRequestDate, PersonKey, GrantTypeKey, 
GrantRequestExplanation, GrantRequestAmount)
Values (GetDate(),1,1,'Always hungry',300.00)

Select * from GrantRequest

Select * from OverMax

Sequence Diagram

here is a picture of the diagram we did in class. I also posted it on Github as a viso diagram

Stored procedures

--stored procedures
--parameterized view

use Community_Assist
go
Create proc usp_HRViewProc
@EmployeeKey int
As
Select PersonLastName,
PersonfirstName,
PersonEmail,
EmployeeHireDate,
EmployeeAnnualSalary
From Person p
inner Join Employee e
on p.PersonKey=e.PersonKey
Where EmployeeKey = @EmployeeKey

exec usp_HRViewProc @EmployeeKey=2
Go
--Create hash password function
Alter function fx_hashPassword2
(@seed int, @password nvarchar(50))
returns varbinary(500)
As
Begin
Declare @newPassword nvarchar(70)
set @newPassword = 
cast(@seed as nvarchar(20)) + @Password
Declare @hashed varbinary(500)
set @hashed = hashbytes('sha2_512', @newPassword)
return @hashed
End


Select * from businessrule
go
--new person stored procedure
Alter proc usp_NewPerson
@lastName nvarchar(255),
@FirstName nvarchar(255)=null,
@Email nvarchar(255),
@password nvarchar(50),
@AptNumber nvarchar(255)=null,
@Street nvarchar(255),
@City nvarchar(255)='Seattle',
@State nvarchar(255)='WA',
@Zip nvarchar(255),
@HomePhone nvarchar(255)
As
--test to see if person exists
if exists
 (Select PersonLastName, PersonEmail From Person
 Where PersonLastName=@LastName
 And PersonEmail=@Email)
 Begin--begin if
 Print 'Already registered'
 return
 End--end if

--create the password hash
Declare @seed int
Set @seed = dbo.fx_GetSeed()
declare @hashedPass varbinary(500)
set @hashedPass = dbo.fx_HashPassword(@seed,@password)

Begin tran --start a transaction
Begin try -- begin try
--insert into person
Insert into Person(  
PersonLastName, PersonFirstName,
 PersonEmail, PersonPassWord, 
 PersonEntryDate, PersonPassWordSeed)
 Values(@LastName, @FirstName,
 @Email, @hashedPass,GetDate(),@seed)

 --get the key of the person just inserted
 Declare @key int = ident_current('Person')
 --insert into PersonAddress
 Insert into PersonAddress(
 PersonAddressApt,
 PersonAddressStreet, 
 PersonAddressCity, PersonAddressState, 
 PersonAddressZip, PersonKey)
 Values( @AptNumber,@street,@city,@state,@Zip,@key)

 Insert into Contact([ContactNumber],
 [ContactTypeKey], [PersonKey])
 Values (@HomePhone, 1, @Key)

 Commit Tran --commit the transaction if no error
 End Try
 Begin Catch
 Rollback Tran
 Print Error_Message()
 End Catch


 exec usp_NewPerson
 @lastName = 'Conger', 
 @FirstName='Steve', 
 @Email='spconger@gmail.com', 
 @password='congerPass', 
 @Street='101 nowhere bld', 
 @Zip='98122', 
 @HomePhone='2065551201'

 Select * from PersonAddress
 Select * from Contact

Functions temp tables

use Community_Assist

--temporary tables 
--local temporary table. Local to this one session
Create Table #TempGrants
(
 GrantRequestKey int,
 GrantRequestDate datetime,
 PersonKey int,
 GrantTypeKey int,
 GrantRequestAmount money
)

insert into #tempGrants(GrantRequestKey,
GrantRequestDate,PersonKey, GrantTypeKey,
GrantRequestAmount)
Select GrantRequestKey,
GrantRequestDate,PersonKey, GrantTypeKey,
GrantRequestAmount from GrantRequest
Where Month(GrantRequestDate)=9

Select * from #TempGrants

--Global temporary table
Create Table ##TempGrantsGlobal
(
 GrantRequestKey int,
 GrantRequestDate datetime,
 PersonKey int,
 GrantTypeKey int,
 GrantRequestAmount money
)

insert into ##tempGrantsGlobal(GrantRequestKey,
GrantRequestDate,PersonKey, GrantTypeKey,
GrantRequestAmount)
Select GrantRequestKey,
GrantRequestDate,PersonKey, GrantTypeKey,
GrantRequestAmount from GrantRequest
Where Month(GrantRequestDate)=9

Select * from ##TempGrantsGlobal
--Functions
Go
Create Function fx_Cube
(@number int)
returns int
As
Begin
Declare @Cube int
Set @cube = @number * @number * @number
return @Cube
End
Go
Select dbo.fx_Cube(3) as [Cube]

--stored procedures
--triggers
 Select PersonKey,dbo.fx_cube(PersonKey) as cubed
 from Person
 order by cubed

 go
 Alter function fx_PercentAmount
 (@amount money, @percentage decimal(10,2)) -- parameters
 returns money --return type
 As
 Begin
 if(not @amount=0) --check to make sure not 0
 Begin --start outer if
 
     if(@Percentage > 1) --check to see how % entered
     Begin --begin inner if
   Set @Percentage = @percentage / 100
     End --end inner if
   Declare @result money --declare variable for result
   Set @result = @amount * @percentage --calculate amount
 End --end outer if
 Else --begin else
 Begin 
 Set @Result=0;
 End --end else
  return @result --return results
 End

 Select DonationAmount, dbo.fx_PercentAmount(DonationAmount, 20) as Org, 
 dbo.fx_PercentAmount(donationAmount, .8) as charity
 From Donation

  Select Sum(DonationAmount) Total, dbo.fx_PercentAmount(sum(DonationAmount), 20) as Org, 
 dbo.fx_PercentAmount(Sum(donationAmount), .8) as charity
 From Donation

Transaction try catch

use Community_Assist

--this begins a manual transaction
Begin Tran

--try tries all the code before 
--if there are no errors
--it will execute all the code 
--commit the transaction and exit
--if there is an error it will drop to the catch
--rollback the transaction and print the error message
Begin Try
Insert into Person(
[PersonLastName],
[PersonFirstName],
[PersonEmail],
[PersonEntryDate])
Values('SquarePants',
'SpongeBuddy',
'sponge@bikinibottom.com',
GetDate())

Insert into PersonAddress(
[PersonAddressStreet],
[PersonAddressCity],
[PersonAddressState],
[PersonAddressZip],
[PersonKey])
Values('100 Pinapple lane',
'Bikinibottom',
'se',
'00001',
ident_current('Person'))

Insert into contact(
[ContactNumber],
[ContactTypeKey],
[PersonKey])
Values ('2005551245',
1,
ident_Current('Person'))

Commit tran
End Try
Begin Catch
Rollback tran
print error_message()
End Catch

Select * from Contact

class Relationships

Association means simply that two classes talk to each other. One calls methods in the other

Inheritance allows an inheriting class to get all the public fields and methods of the parent. It always proceeds from the more general, more abstract, to the more specific or concrete. The child class must be of the same kind as the parent.

An interface is a collection of method signatures. A class that implements an interface must provide a body for all the method signatures in the interface.

Composition is a relation where one class in totally contained in another class. If the container class is destroyed (goes out of scope) so does the contained class.

Aggregation is a relationship where one class is contained in the other, but the contained class persists even when the containing class is destroyed

Set Operators and modifying data

USE [Community_Assist]
GO

/****** Object:  View [dbo].[vw_HumanResources]    Script Date: 5/11/2016 10:11:00 AM ******/


ALTER view [dbo].[vw_HumanResources] with Schemabinding
AS
Select PersonLastName [LastName],
PersonFirstName [FirstName],
PersonEmail Email,
PersonAddressStreet [Address],
PersonAddressCity City,
PersonAddressZip ZipCode,
EmployeeHireDate HireDate
From dbo.Person p
Inner Join dbo.PersonAddress pa
on p.PersonKey=pa.PersonKey
inner join dbo.Employee e
on e.PersonKey = p.PersonKey

GO

--cross apply
Select Distinct a.GrantTypeKey, c.GrantRequestAmount
From dbo.GrantRequest a
cross Apply
(Select grantTypeKey, grantRequestAmount
From GrantRequest as b
Where b.GrantTypeKey = a.GrantTypeKey
Order By b.GrantRequestAmount desc, GrantTypeKey desc
Offset 0 rows fetch first 3 rows only) as c



Begin tran

Alter table person
Drop column PersonEmail

Rollback tran

--Set operators and modifying data
--Windows functions pivot

Select PersonKey, PersonFirstName, PersonLastName, PersonEmail
From Person
Union
Select EmployeeKey,EmployeefirstName, EmployeeLastName, EmployeeEmail
From MetroAlt.dbo.Employee


Select PersonAddressCity From PersonAddress
intersect
Select EmployeeCity from MetroAlt.dbo.Employee

Select PersonAddressCity From PersonAddress
Except
Select EmployeeCity from MetroAlt.dbo.Employee

Select EmployeeCity from MetroAlt.dbo.Employee
except
Select PersonAddressCity From PersonAddress

/******************************************
*********Not part of assignment*********/

--ranking functions

Select GrantRequestKey, GrantTypeKey, GrantRequestAmount,
Row_Number() over (order by GrantRequestAmount desc) as RowNumber,
Rank() over (order by GrantRequestAmount desc) as [Rank],
Dense_rank() over (order by GrantRequestAmount desc) as [DenseRank],
Ntile(10) over (order by GrantRequestAmount desc) as [NTile]
From GrantRequest
Order by GrantRequestAmount desc

--partition functions
Select GrantRequestKey, GrantTypeKey, GrantRequestAmount,
Sum(GrantRequestAmount) over() as TotalAllocation,
sum(GrantRequestAmount) over(partition by GrantTypeKey) as PerType,
(sum(GrantRequestAmount) over(partition by GrantTypeKey) /
Sum(GrantRequestAmount) over() * 100) as [TypePercentOfWhole],
GrantRequestAmount/
sum(GrantRequestAmount) over(partition by GrantTypeKey) * 100 as GrantPercentOfType
From GrantRequest
Order by GrantTypeKey 

--pivot
Select * from GrantRequest

Select  [Month], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10]
From (Select Month(GrantRequestDate) as [Month] ,GrantTypeKey, GrantRequestKey
From dbo.GrantRequest) as a
pivot (Count(GrantRequestKey) for GrantTypeKey in 
    ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])) AS b

/*************************************************/
--Inserts
Insert into person
(PersonLastName, PersonFirstName, PersonEmail,  PersonEntryDate)
Values('Simpson', 'Homer', 'Homer@springfield.com', GetDate()),
('Simpson','Marge','Marge@springfield.com',GetDate())

Select * from Person

Create Table Person2
(
    Lastname nvarchar(225),
 FirstName nvarchar(255),
 Email nvarchar(255)
)

Insert into Person2(LastName, firstname, email)
Select PersonLastName, PersonFirstName, PersonEmail
From Person

Select * from Person2

Begin tran

Update Person2
Set FirstName='Jason'
Where Email='JAnderson@gmail.com'

Commit Tran

Update Person2
Set LastName='Smith'

Rollback tran

Delete from Person2
Where PersonKey=130

Select * from Person
Drop Table Person2