Tuesday, October 13, 2015

Selection examples

The if examples

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

namespace ifStatementExamples
{
    class Program
    {
        static void Main(string[] args)
        {
            int number;
            Console.WriteLine("Enter a number");
            //the tryParse returns a boolean
            //if the number is good it returns true
            //if the number is not good it returns false
            //if the number is good it also assigns it to the variable specified in the
            //out parameter, if it is not good it assigns 0
            bool goodNumber = int.TryParse(Console.ReadLine(), out number);

            // ! not, != not equal, == equals
            if (goodNumber == false)
            {
                Console.WriteLine("Please enter a good number");
                Console.ReadKey();
                return;
            }


            if (number > 20)
            {
                Console.WriteLine("your number is greater than 20");
            }
            else
            {
                Console.WriteLine("Your number is less than 20");
            }
            //&& = and
            //|| = or

            if (number > 0 && number <= 20)
            {
                Console.WriteLine("Your number is between 1 and 20");
            }
            else if (number > 20 && number <=  50)
            {
                Console.WriteLine("Your number is between 21 and 50");
            }
            else if (number > 50 && number <= 100)
            {
                Console.WriteLine("Your number is between 51 and 100");
            }
            else
            {
                Console.WriteLine("Your number is more than 100");
            }

            int number2;
            Console.WriteLine("Enter a number between 1 and 5");
            bool goodNumber2 = int.TryParse(Console.ReadLine(), out number2);

            if (!goodNumber2)
            {
                Console.WriteLine("Please enter a good number");
                Console.ReadKey();
                return;
            }

            //a switch is good for some things but is less
            //flexible than a if elseif. It can't test a range
            //of values but only specific values
            switch (number2)
            {
                case 1:
                    Console.WriteLine("One");
                    break;
                case 2:
                    Console.WriteLine("Two");
                    break;
                case 3: //you can fall through to the next case
                case 4:
                    Console.WriteLine("Three or Four");
                    break;
                case 5:
                    Console.WriteLine("Five");
                    break;
                default:
                    Console.WriteLine("Not between 1 and 5. Follow directions!");
                    break;
            }





            Console.ReadKey();

        }
    }
}

Here is the code for the extra credit

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

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            //determine how many busses you need
            //each bus has a capacity of 45
            // const is used to declare a constant. that means a value that
            //cannot be changed by the program
            const int CAPACITY= 45;
            int students;
            int busses;

            Console.WriteLine("How many people need a ride");
            bool goodNumber = int.TryParse(Console.ReadLine(), out students);

            //! means not a good number
            if (!goodNumber)
            {
                Console.WriteLine("Please enter a valid number");
                Console.ReadKey();
                return;
            }


            busses = students / CAPACITY;

            if (students % CAPACITY > 0)
            {
                //equivelent to busses = busses + 1
                //also could do busses++ which increments by 1
                busses += 1;
            }

            Console.WriteLine("You will need {0} busses", busses);

            Console.ReadKey();

            //the peer excercise
            //if(number % 2 ==0)
            // even
            //else
            //odd

        }
    }
}

Monday, October 12, 2015

Diagraming a database 1

Modeling a Database

Field list for DVDs

Groups: Entity should be about one thing: No multi valued groups

Candidate Key-- surrogate auto or random number for keys- Natural key is a natural field in the table

DVD

Title, length, Release Date, origin year, purchase year, number of disks, Rating, Description, Price, copyright

Actors

Name, gender, awards, alias

Features

FeatureType, description

Studio

Name, Location

Languages

name

Writer

Name

Genre

Name, description

Original list of fields

Name or title
director
Lead actors
Time length
Release year
Original year
Genre
Number of disks
Studio
Description
Writer
Language choices
Rating
Extras
Plot summary
Country of Origin
Date purchased
Price
copyright

Relationship types

Initial Diagram DVDs Actors

Data Examples

Wednesday, October 7, 2015

Requirements and Business Rules

Requirements and Business Rules

Here are the notes that we did in class, such as they are


Things the database must do
*Data requirements
*Reporting Requirements
*Security Requirements who will be using the database what permissions will they need


Some data requirements

Show Dates and times
Venues address city state zip phone webpage name email
Capacity restrictions description handicap access
Act capacity reserved vs open
Restrictions
Contact for venues acts
Customer list subscription to acts or venues

Reporting Requirements

Calendar
View a particular venue and see upcoming shows
View an act and see where they are playing
Query a venue based on restrictions

Security requirements:

Access management--
support
Application—general
Select (read) (CRUD) (except the customer or fan lists)
Insert (put in new records)
Update (change or edit existing records)
Delete (delete records)
Venues—update, insert, select --constraint
only their own data
Customer fan—select insert update only own information


Business rules

How things
Venues have to enter their own schedules
Rule that venues must update weekly
Confirmation email for new customers

Tuesday, October 6, 2015

Ints doubles and operators

Here is the code from today's class.

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

namespace ExamplesForAssignment2
{
    class Program
    {
        /*************************
        These are examples for assignment 2
        involving operators 
        +   addition
        -    subtraction
        *    multiplication
        /    division
        %   modulus--remainder in integer division
        and the numeric data types
        int, whole number
        and double, float or decimal 
        ****************************/

        static void Main(string[] args)
        {
            //number variables int double
            int number, number2;
            int sum, difference, product, quotient, remainder;
            double  exponent;
            double decimalQuotient;

            //inputs
            Console.WriteLine("enter an integer");
            //C# treats all input from the console as a string
            //Parse removes the quotes and sees if the content
            //is of the correct type--in this case int
            number = int.Parse(Console.ReadLine());

            Console.WriteLine("enter another integer");
            number2 = int.Parse(Console.ReadLine());

            Console.WriteLine("Enter a double");
            //this is an example of parsing a double from the console
            double newNumber = double.Parse(Console.ReadLine());

            //algorithm
            //Here are all the operators
            sum = number + number2;
            difference = number - number2;
            product = number * number2;
            quotient = number / number2;
            remainder = number % number2;
            //in this one we cast one of the sides to a double
            //the equation always defaults to the type with the higher precision
            //doubles always have a higher precision because they contain doubles
            //this makes it so the result returns the decimal part
            decimalQuotient = (double)number / number2;
            //the Math library is static and always available
            exponent = Math.Pow(number, number2);

            //outputs
            Console.WriteLine("The sum of {0}, and {1} is {2}", number, number2, sum);
            Console.WriteLine("The difference of {0}, and {1} is {2}", number, number2, difference);
            Console.WriteLine("The product of {0}, and {1} is {2}", number, number2, product);
            Console.WriteLine("The quotient of {0}, and {1} is {2}", number, number2, quotient);
            Console.WriteLine("The remainder of {0}, and {1} is {2}", number, number2, remainder);
            Console.WriteLine("The exponent of {0}, and {1} is {2}",number, number2, exponent);
            Console.WriteLine("The decimal quotient of {0}, and {1} is {2}", number, number2, decimalQuotient);

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



        }
    }
}

Monday, October 5, 2015

Information Gathering

Information Gathering

Here are our class notes on information gathering, such as they are.

Ask client (interviews) -- prepare
One retreat profession facilitator record everything
stakeholders (access) security
current problems
why they want a new database
What the database requires
What would they like beyond requirements
Growth scalability

 

Look at the existing systems Budget Security
Phone/dept (should never have two kinds of values in a field)
Forms (entering data) fields
Reports (displaying data) summary

 

Job Shadowing
Exceptions
flow

Thursday, October 1, 2015

First assignment example

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

namespace AssignmentExamples
{
    class Program
    {
        //comments with your name 
        static void Main(string[] args)
        {
            //prompt
            Console.WriteLine("Enter your favorite color ");
            //getting input
            string colorChoice= Console.ReadLine();
            Console.WriteLine("Enter your favorite animal");
            string animal = Console.ReadLine();
            //output results with placeholders
            Console.WriteLine
                ("Your favorite color is {0}, and your favorite animal is a{1}"
                ,colorChoice, animal);
            
            //concatination
            Console.WriteLine("your favorite color is " + colorChoice +
                ", your favorite animal is a " + animal);

            //pause long enough to read it
            Console.ReadKey();


        }
    }
}

Tuesday, August 18, 2015

Login Stored procedures and test database

Use Master
go
Create database LoginTest
Go
Use LoginTest
go
Create table SecurityQuestion
(
  QuestionKey int identity(1,1) primary key,
  Question nvarchar(255) not null
)

go

Create table UserLogin
(
   UserKey int identity(1,1) primary key,
   UserName nvarchar(50) not null,
   UserEmail nvarchar(255) not null,
   UserRandomInt int not null,
   UserPassword varBinary(500) not null,
   UserDateEntered Date not null,
   UserDateLastModified Date not null,

)
alter table UserLogin
add constraint unique_UserName unique(userName)

alter table UserLogin
add constraint unique_Email unique(useremail)

go

Create Table UserSecurityQuestion
(
 UserKey int Foreign Key references UserLogin(UserKey) not null,
 QuestionKey int Foreign Key references SecurityQuestion(QuestionKey) not null,
 UserAnswer NVarchar(255) not null,
 Constraint PK_UserSecurityQuestion primary key(UserKey, QuestionKey),
 
)

Go
Create table LoginHistory
(
 LoginHistoryKey int identity(1,1) primary key,
 UserKey int foreign key references UserLogin(userKey),
 LoginHistoryDateTime datetime default GetDate()
)

Go

Insert into SecurityQuestion(Question)
values('Where were you when you got your first traffic ticket?'),
('What is your least favorite book?'),
('What acloholic drink made you the sickest?'),
('What food do you truely hate?')

go
create function fx_hashPassword
(@password nvarchar(50), @RandomInt int)
returns varbinary(500)
As
begin
Declare @Combined nvarchar(60)
Declare @hashed varbinary(500)
Set @Combined = @password + cast(@randomInt as Nvarchar(10))
Set @hashed = HASHBYTES('sha2_512', @combined)
return @hashed
End
go

Select dbo.fx_hashPassword('mypass','1342567901')

Go

Create function fx_getRandomInt()
returns int
As
Begin
Declare @intNumber int
set @intNumber=DatePart(NanoSecond, GetDate())
return @intNumber
End

go

--password, all the info for login table
--userKey as output
--write to userlogin table
--write security question table
--write to login history table
--put in transaction

Alter proc usp_NewLogin
@userName nvarchar(50),
@Password nvarchar(50),
@userEmail nvarchar(255),
@securityQuestion int,
@answer nvarchar(255)
As
--declare internal variables
Declare @intRandom int
Declare @hash varbinary(500)
Declare @Date Date
--check to see if user exists
If Exists
 (Select userKey from userLogin
 where userName=@userName
 And UserEmail=@userEmail)
Begin
Print 'user already exists'
return -1
End

--get random seed 
select @intRandom=dbo.fx_getRandomInt()
--get hash of password
select @hash = dbo.fx_hashPassword(@password, @intRandom)

Set @date =GetDate()
--Begin transaction
Begin tran
Begin try
--insert int userLogin
Insert into UserLogin(UserName, UserEmail, UserRandomInt, UserPassword, UserDateEntered, UserDateLastModified)
Values(@username, @userEmail, @intRandom, @hash, @date, @date)

Declare @UserKey int
Set @UserKey= Ident_Current('UserLogin')
--insert into userSecurityQuestion
Insert into UserSecurityQuestion(UserKey, QuestionKey, UserAnswer)
Values(@UserKey, @SecurityQuestion, @answer)
--Insert into LoginHistory
Insert into LoginHistory(UserKey, LoginHistoryDateTime)
values(@UserKey, @Date)
commit tran
Return @userKey
End Try
Begin Catch
Rollback tran
return 0
End Catch

Exec usp_NewLogin
@userName='George', 
@Password='P@ssw0rd1', 
@userEmail='George@gmail.com', 
@securityQuestion=3, 
@answer='whiskey'

Select * from SecurityQuestion

Select * From userLogin
Select * From UserSecurityQuestion
Select * From LoginHistory



--existing login
--intake password username
--get the salt that goes with the username
--(-1) if no username
--rehash the text password with the salt
--compare the hashes
--if they match the login is successful return user key
--if they fail return 0
go
Alter proc usp_Login
@Password nvarchar(50),
@userName nvarchar(50)
As
Declare @intRandom int
Declare @Newhash varbinary(500)
Declare @DBHash varbinary(500)
Declare @UserKey int

Select @UserKey= userKey, @intRandom=UserRandomInt, @DBHash=userPassword from UserLogin
Where UserName=@userName

if @IntRandom is null
 Begin
  Print '-1'
  Return -1
 End
Select @newHash=dbo.fx_hashPassword(@password, @intRandom)

if @DBHash=@Newhash
 Begin

 insert into LoginHistory(UserKey, loginHistoryDateTime)
 Values(@UserKey,GetDate())

 print cast(@UserKey as nvarchar(10))
    Return @UserKey
 end
Else
   Begin
  print '0'
  Return 0
   End


Exec usp_login
@Password='P@ssw0rd1', 
@userName='spconger'

Select * From LoginHistory


/*
login and validate
Get new Password
Rehash the passord
Update the Login table
*/
go

Create proc usp_ChangePassword
@userName nvarchar(50),
@password nvarchar(50),
@newPassword nvarchar(50)
As
Declare @intRandom int
Declare @Newhash varbinary(500)
Declare @DBHash varbinary(500)
Declare @UserKey int

Select @UserKey= userKey, @intRandom=UserRandomInt, @DBHash=userPassword from UserLogin
Where UserName=@userName

if @IntRandom is null
 Begin
  Print '-1'
  Return -1
End
Select @newHash=dbo.fx_hashPassword(@password, @intRandom)

if @DBHash=@Newhash
 Begin
 Declare @newRandom int
 Set @newRandom=dbo.fx_getRandomInt()
 Declare @UpdateHash varbinary(500)
 Set @UpdateHash=dbo.fx_hashPassword(@newPassword, @NewRandom)

 update UserLogin 
 Set UserRandomInt=@newRandom,
 UserPassword=@updateHash
 Where userKey=@UserKey


 insert into LoginHistory(UserKey, loginHistoryDateTime)
 Values(@UserKey,GetDate())

 print cast(@UserKey as nvarchar(10))
    Return @UserKey
 end
Else
   Begin
  print '0'
  Return 0
   End

   Select * from userLogin

   exec usp_ChangePassword
   @userName = 'spconger', 
   @password='P@ssw0rd1', 
   @newPassword='P@ssw0rd2'

   Exec usp_login
@Password='P@ssw0rd2', 
@userName='spconger'