Basic numbers and operators

Here is what we did in class

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

namespace NumberExample
{
    class Program
    {
        /* ************************
         * this program is just an example of using
         * numeric types, specifically int and double
         * and the basic math operators
         * steve conger 9/29/2014 Evening class
         * **********************/
        static void Main(string[] args)
        {
            //declaring an int. You can declare several
            //variables at once as long as they are the same
            //type.
            int number, numberb, numberc;
            double number2; //a double has decimal places
            const double PI =3.14; //a constant can't be changed
            //constants are all caps by convention.

            Console.WriteLine("Enter an Integer");
            //everything entered on the console is a string
            //(true also of textboxes) and must be Parsed
            //or converted to the numeric type--in this case int
            number = int.Parse(Console.ReadLine());
            //another way to convert string to int
            //number = Convert.ToInt32(Console.ReadLine());
            Console.WriteLine("Enter another integer");
            numberb = int.Parse(Console.ReadLine());
            
            /**************************
             * operators
             * ***********************/

            //addition
            numberc = number + numberb;
            Console.WriteLine("{0} + {1} = {2}", number, numberb, numberc);

            //subtraction
            numberc = number - numberb;
            Console.WriteLine("{0} - {1} = {2}", number, numberb, numberc);

            //multiplication
            numberc = number * numberb;
            Console.WriteLine("{0} * {1} = {2}", number, numberb, numberc);

            //Integer division. In integer division only a whole number is returned 
            //5/2=2 Any remainder or decimal part is dropped
            numberc = number / numberb;
            Console.WriteLine("{0} / {1} = {2}", number, numberb, numberc);

            //Modulus
            //for integers the modulus % returns the remainder in an integer
            //division 5%2=1 because 5/2 = 2 with a remainder of 1
            numberc = number % numberb;
            Console.WriteLine("{0} % {1} = {2}", number, numberb, numberc);

            //doubles need to be parsed as a double
            Console.WriteLine("Enter a double");
            number2 = double.Parse(Console.ReadLine());


            //this will still result in 5/2=2 becuase the division is still between two
            //integers
            number2 = number / numberb;
            Console.WriteLine("{0} % {1} = {2}", number, numberb, number2);

            //if you want the result to be a double you have to "Cast" one side of
            //the division to a double. (double)number does that.
            number2 = (double)number / numberb;
            Console.WriteLine("{0} % {1} = {2}", number, numberb, number2);

            //The Math library is a static library that is always available.
            //just type Math and a dot to see the available
            Console.WriteLine(Math.Sqrt(number2));

            //Pause for Visual Studio
            Console.ReadKey();
        }
    }
}

Statement of Work: Book Reviews

History

Several people have been reviewing books over the years and want to consolidate their reviews into a database. They have posted in various web sites but want to bring it all together. They want to make it easier to post reviews, compare them and search for items of interest. They also want to avoid some of the “trolling” and abusive reviews that mar most sites. They are hoping that by only allowing registered members to review and comment on reviews they can minimize some of that.

The database will be a back end to a web site. They intend to hire separate developers to create the front end. They would love to see their database and site become a major draw for book lovers of all kinds.

Scope:

The database will store data about books, the reviewers, reviews and commentaries on reviews. Only registered reviewers can post reviews and commentary, but anyone can search and read reviews. There should be many ways to search for reviews and books. Each review will contain a numerical rating for the book as well as text.

Constraints:

The database will not be expanded to include other media such as music and film.

Objectives

1. Create a database to store book reviews
2. Make the database easily searchable
3. Minimize abusive and irrelevant reviews

Time line and deliverables

1. Gather information, interviews. (One week)

Deliverables: interview questions, questionnaire.

2. Establish requirements and business rules (One Week)

Deliverables: List of Requirements, business rules.

3. Design the database. ERD diagrams (One Week)

Deliverables: Entity Relation Diagram with all Entities, Attributes and relationships.

4. Review the design for normalization. (One Week)

Deliverables: Normalized ERD. List of changes with reasons

5. Build the physical database (0ne Week)

Deliverables: Database with tables and relationships

6. Enter sample Data and Test the database (One Week)

Deliverables: Sample Data. SQL queries on requirements with results

7. Refine security and other elements (One Week)

Deliverables: Security Plan. Disaster Management Plan.

First Exercise Assignment1

Here is the first exercise of assignment 1

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

namespace Assignment1
{
    class Program
    {
        /**********************
         * this is for assignment1
         * Part 1 entering name and email
         * Steve Conger 9/24/2014
         ***********************/
        static void Main(string[] args)
        {
            //declare variables
            string firstName;
            string lastName;
            string email;

            //get input
            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();

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

            Console.ReadKey();
        }
    }
}

CommunityAssist Data Warehouse

Create database CommunityAssistDw

use CommunityAssistDW

Create table DimClient
(
    DimClientKey int identity(1,1) primary key,
 PersonKey int,
 ClientLastName nvarchar(255),
 ClientFirstName nvarchar(255),
 ClientEmail nvarchar(25),
 ClientZipcode nchar(10)
)

Create table DimEmployee
(
 DimEmployeeKey int identity(1,1) primary key,
 PersonKey int,
 EmployeeKey int,
 EmployeeHireDate dateTime,
 EmployeeStatus nchar(2)
)

Create table DimService
(
 DimServiceKey int identity(1,1) primary key,
 ServiceName nvarchar(255),
 ServiceKey int
)

Create table DimGrant
(
 DimGrantKey int identity(1,1) primary key,
 Grantkey int,
 GrantApprovalStatus nvarchar(10)
)

Create Table DimDate
(
 DimDateKey int identity (1,1) primary key,
 GrantDate dateTime,
 GrantReviewDate dateTime,
 GrantYear int,
 GrantMonth int,
 ReviewYear int,
 ReviewMonth int
)


Create table FactGrant
(
 DimClientKey int not null,
 DimServiceKey int not Null,
 DimGrantKey int not null,
 DimDateKey int not null,
 GrantAmount money,
 GrantAllocation money
)

Alter table FactGrant
Add Constraint PK_factGrant primary key 
(DimclientKey, dimServiceKey,  DimDateKey, DimGrantKey)

Alter Table FactGrant
Add Constraint FK_DimClient foreign key(DimClientKey)
references DimClient(DimClientKey)

Alter Table FactGrant
Add Constraint FK_DimSevice foreign key(DimServiceKey)
references DimService(DimServiceKey)

Alter Table FactGrant
Add Constraint FK_DimDate foreign key(DimDateKey)
references DimDate(DimDateKey)

Alter Table FactGrant
Add Constraint FK_DimGrant foreign key(DimGrantKey)
references DimGrant(DimGrantKey)

Insert into DimClient(PersonKey, ClientLastName, ClientFirstName, ClientEmail, ClientZipcode)
Select Distinct sg.PersonKey, PersonLastName, PersonFirstName,
  PersonUserName, Zip
 From CommunityAssist.dbo.Person p
 inner Join CommunityAssist.dbo.PersonAddress pa
 on p.PersonKey=pa.PersonKey
 inner Join CommunityAssist.dbo.ServiceGrant sg
 on p.PersonKey=sg.PersonKey


insert into DimService(ServiceName, ServiceKey)
Select ServiceName, ServiceKey from CommunityAssist.dbo.CommunityService

Insert into DimGrant(Grantkey, GrantApprovalStatus)
Select GrantKey, GrantApprovalStatus from CommunityAssist.dbo.ServiceGrant

Insert into dimDate(GrantDate, GrantReviewDate, GrantYear, GrantMonth, ReviewYear, ReviewMonth)
Select GrantDate, GrantReviewDate, Year(GrantDate), Month(GrantDate), 
      Year(GrantReviewDate), Month(GrantReviewDate)
   From CommunityAssist.dbo.ServiceGrant

Insert into FactGrant(DimClientKey, DimServiceKey, DimGrantKey, DimDateKey, GrantAmount, GrantAllocation)
Select DimClientKey, DimServiceKey, DimGrantKey, DimDateKey, GrantAmount, GrantAllocation
From DimClient dc
inner join CommunityAssist.dbo.serviceGrant sg
on dc.PersonKey=sg.PersonKey
inner join DimService ds
on ds.ServiceKey =sg.ServiceKey
inner join DimGrant dg
on dg.Grantkey=sg.GrantKey
inner join DimDate dd
on dd.GrantDate=sg.GrantDate

Data Warehouse script

I figured out what the problem was from class. The elements in the fact table were not in the same granularity. Particularly. employees and Customers were a problem. We could incorporate the customers if we went a step up to the general service table, rather than service details. We could add another fact table to handle this, but since most of the calculable values are at the detail level, I focused the fact table on that.

Here is the ERD

Here is the script

use master
Go
/************************************************
* Check to see if the database exists.
* If it does drop it and then recreate it.
************************************************/
if exists
 (Select name from sys.Databases 
   where name='AutomartDataWarehouse')
begin
 Drop database AutomartDataWarehouse
end
go
Create database AutomartDataWareHouse
Go
Use AutomartDataWareHouse
/*******************************************
* Create the Dimension tables.
********************************************/
Go


Go
Create table DimVehicle
(
   DimVehicleKey int identity(1,1) primary Key,
   VehicleID int,
   personKey int,
   LicenseNumber nvarchar(10),
   VehicleMake nvarchar(255),
   VehicleYEar nchar(4)
)
Go
Create table DimLocation
(
  DimLocationKey int identity(1,1) primary key,
  LocationID int,
  locationName nvarchar(255),
  LocationAddress nvarchar(255),
  LocationCity nvarchar(255),
  LocationState nchar(2),
  LocationZip nchar(10),
  LocationPhone nchar(13)
)

Go
Create Table DimService
(
 DimServiceKey int identity(1,1) primary Key,
 serviceId int,
 ServiceName nvarchar(255)
)
go
Create table DimDate
(
  DimDateKey int identity(1,1) primary key,
  ServiceDate Date,
  ServiceYear int,
  ServiceMonth int
)

/***********************************************
* Create the fact tables. The failure of the previous
* fact table was that it was of two different
* granularities that couldn't be resolved. To solve this
* I Left out the customer and employee dims. A different
* fact table might include them.
*************************************************/
Create table FactService
(
   FactServiceKey int identity(1,1),
      DimVehicleKey int Foreign Key references DimVehicle(DimVehicleKey),
   DimLocationKey int Foreign Key references DimLocation(DimLocationKey),
   DimDateKey int Foreign Key references DimDate(DimDateKey),
   DimServiceKey int Foreign Key references DimService(DimServiceKey),
  
   Constraint PK_FactService primary key(FactServiceKey,DimVehicleKey, DimLocationKey, DimDateKey, dimServiceKey),
   ServicePrice money,
   DiscountPercent decimal(3,2),
   TaxPercent decimal(3,2)
)

Go


/*****************************************************
* Populate the dim tables
******************************************************/


Go
Insert into DimVehicle(VehicleID,
   LicenseNumber,
   VehicleMake,
   VehicleYEar,
   personKey)
   Select VehicleID,
   LicenseNumber,
   VehicleMake,
   VehicleYEar, personKey
   From Automart.Customer.Vehicle
 
 Go
 Insert into DimLocation (
  LocationID,
  locationName,
  LocationAddress,
  LocationCity,
  LocationState,
  LocationZip,
  LocationPhone
 )
 Select 
 LocationID,
  locationName,
  LocationAddress,
  LocationCity,
  LocationState,
  LocationZip,
  LocationPhone
  From Automart.Customer.Location

  Go
  Insert into DimService(
  serviceId,
   ServiceName 
  )
  Select AutoServiceID, ServiceName
  from Automart.customer.AutoService

Go

Insert into DimDate(
 ServiceDate,
  ServiceYear,
  ServiceMonth)
 Select 
 ServiceDate,
  Year(ServiceDate),
  Month(ServiceDate)
  From Automart.Employee.VehicleService
  Go

  /***************************************
  * Insert into the fact table
  ****************************************/

  Insert into FactService(DimLocationKey, DimDateKey, DimServiceKey, DimVehicleKey, ServicePrice, DiscountPercent, TaxPercent)
  Select DimLocationKey, DimDateKey, DimServiceKey, DimVehicleKey, ServicePrice, DiscountPercent, TaxPercent
  from DimLocation dl
  inner Join Automart.Customer.Location  loc
  on dl.LocationID=loc.LocationID
  inner join Automart.Employee.VehicleService vs
  on loc.LocationID=dl.LocationID
  inner Join DimVehicle dv
  on vs.VehicleID=dv.VehicleID
  inner Join DimDate dd
  on dd.ServiceDate=vs.ServiceDate
  inner join Automart.Employee.VehicleServiceDetail vsd
  on vs.VehicleServiceID=vsd.VehicleServiceID
  inner Join DimService ds
  on ds.serviceId=vsd.VehicleServiceID
  inner join Automart.Customer.AutoService a
  on vsd.AutoServiceID=a.AutoServiceID

We will follow by creating a SSIS script to import data on a regular basis and creating a cube

Basic Security Script

Use Automart
go
--create schema for managers
Create Schema manager
--create an object that belongs to the schema Manager
Go
Create view manager.vw_LocationSummary
 As
 Select LocationName, count(distinct vs.VehicleServiceId) as [Count],
 sum(dbo.fx_GetTotalDue(ServicePrice, DiscountPercent)) as Total
 From Customer.AutoService a
 inner join Employee.VehicleServiceDetail vsd
 on a.AutoServiceID=vsd.AutoServiceID
 inner Join Employee.VehicleService vs
 on vsd.VehicleServiceID=vs.VehicleServiceID
 inner Join Customer.Location loc
 on loc.LocationID=vs.LocationID
 Group by LocationName
 go
 --Create a role for Managers
 create role MangagerRole
 Go
 --provide permission for manager role
 Grant select, update on Schema::manager to ManagerRole

--create a login for managers
Create Login ManagerLogin with password='P@ssw0rd1'

--create a user in automart that is mapped to that login
Create user ManagerUser for Login ManagerLogin

--add the user to the role
exec sys.sp_addrolemember 'managerRole', 'ManagerUser'

--now login you should only see the objects that belong to the schema Manager
--and only have the permissions assigned to the role

Populating the Dim tables

Insert into DimCustomer(RegisteredCustomerKey, PersonKey, LastName, FirstName, Email)
Select RegisteredCustomerID, p.PersonKey, LastName, FirstName, Email
From Automart.dbo.Person p
inner join Automart.Customer.RegisteredCustomer rc
on p.Personkey=rc.PersonKey

Insert into DimEmployee(EmployeeID, PersonKey, HireDate, LocationID, SupervisorID)
Select EmployeeID, PersonKey, HireDate, LocationID, SupervisorID
From Automart.dbo.Employee

Insert into DimLocation(LocationID, LocationName, LocationAddress, LocationCity, LocationState, LocationZip, LocationPhone)
Select LocationID, LocationName, LocationAddress, LocationCity, LocationState, LocationZip, LocationPhone
From Automart.Customer.Location

Insert into DimVehicle (VehicleID, VehicleMake, VehicleYear)
Select VehicleID, VehicleMake, VehicleYear
From Automart.Customer.Vehicle

Insert into DimVehicleService( VehicleServiceID, VehicleID, LocationName, ServiceName)
Select  vs.VehicleServiceID, VehicleID, LocationName, ServiceName
From Automart.Employee.VehicleService vs
inner Join Automart.Employee.VehicleServiceDetail vsd
on vs.VehicleServiceID=vsd.VehicleServiceID
inner Join Automart.Customer.Location loc
on loc.LocationID=vs.LocationID
inner Join Automart.Customer.AutoService a
on a.AutoServiceID=vsd.AutoServiceID

Insert into DimDate (ServiceDate, ServiceTime, sDay, sMonth, sYear)
Select  ServiceDate, ServiceTime, Day(ServiceDate),Month(ServiceDate), 
Year(ServiceDate)
From automart.Employee.VehicleService

Alter table FactSales Drop constraint [PK_FactSales]