Show Tracker Class diagrams

Here is a picture of what I think the initial domain level classes would be for the scenario Show Tracker.

I have not worried about relationships among the classes.

Class Diagrams 1

Here is the diagram we made

Here is the Java code for the User Class

package com.spconger;

public class User {
 
 private int userID;
 private String userName;
 
 public int getUserID() {
  return userID;
 }
 public void setUserID(int userID) {
  this.userID = userID;
 }
 public String getUserName() {
  return userName;
 }
 public void setUserName(String userName) {
  this.userName = userName;
 }
 

}

Here is the java code for the Customer class which inherits from User class

package com.spconger;

public class Customer extends User {
 private String email;
 private String phoneNumber;
 private int rewardPoints;
 
 public int getRewardPoints() {
  return rewardPoints;
 }
 public String getEmail() {
  return email;
 }
 public void setEmail(String email) {
  this.email = email;
 }
 public String getPhoneNumber() {
  return phoneNumber;
 }
 public void setPhoneNumber(String phoneNumber) {
  this.phoneNumber = phoneNumber;
 }
 
 public void addPoints(int points){
  rewardPoints += points;
 }
 public void usingPoints(int points){
  rewardPoints -= points;
 }

}

Here is the C# code for the user class

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

namespace ConsoleApp1
{
    abstract public class User
    {
        private int userID;
        private string userName;

        public int UserID { get => userID; set => userID = value; }
        public string UserName { get => userName; set => userName = value; }
    }
}

Here is the C# code for the Customer class which inherits from User

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

namespace ConsoleApp1
{
    public class Customer:User
    {
        public string Email { set; get; }
        public string PhoneNumber { set; get; }
        private int rewardPoints;

        public void AddPoints(int points)
        {
            rewardPoints += points;
        }

        public void UsingPoints(int points)
        {
            rewardPoints -= points;
        }

        public int GetRewardPoints()
        {
            return rewardPoints;
        }
    }
}

SubQueries

--Sub queries
--where, select clause, from clause
use Community_Assist

--gives you the max per person key which is not what you
--really want to see
Select Personkey, max(donationAmount) from Donation
Group by PersonKey

--subqueries in the where clause
--allows you to see which donations are bigger than the average
--donation. The subquery must return only a single value,
--though more than one row might result in query results
Select DonationDate, Personkey, DonationAmount 
from Donation
where DonationAmount>(Select avg(donationAmount) from Donation)

--the "in" keyword lets you match against a set of results
--this returns all the people whose keys are in the Employee table
--ie employees. It is important to match like with like--personkey
--with personkey
Select personkey, PersonLastName, PersonFirstname, PersonEmail
From Person
Where personKey in (Select Personkey from Employee)

--with literal set
Select personkey, PersonLastName, PersonFirstname, PersonEmail
From Person
Where personKey in (2,4,5,129)

--subqueries with in can also be used like an outer join
--to show which services were NOT requested
Select GrantTypeKey, GrantTypeName from GrantType 
where GrantTypeKey not in (Select distinct GrantTypeKey from GrantRequest)

--In can be used to chain together subqueries. This one shows
--which employees did grant reivews. You can, of course,
--get the same information with a join
Select PersonKey, PersonLastName, PersonFirstName, PersonEmail
from person
Where PersonKey in
      (Select Personkey from Employee 
     where EmployeeKey in
  (Select EmployeeKey from GrantReview))

--which employees did NOT do reviews
Select PersonKey, PersonLastName, PersonFirstName, PersonEmail
from person
Where PersonKey in
      (Select Personkey from Employee 
     where EmployeeKey not in
  (Select EmployeeKey from GrantReview))

--subqueries in select
Select * from GrantRequest

--this queries uses subqueries in the SELECT clause. 
--Year, month, sum(GrantRequestAmount) and GrantTypeName are part
--of the normal query. The sum is grouped by
--year and month and grantTypeName
--The grand total requires a subquery, because you want the total
--of the whole table, not the sub group totals.
--the same is true of the percent. You have to divide
--the grouped by total by the Grand Total, which again
--must be done with a subquery.
Select Year(GrantRequestDate) [Year],
month(GrantRequestDate)[month],
GrantTypeName, 
sum(GrantRequestAmount) as Total,
(Select Sum(GrantRequestAmount) from GrantRequest) as GrandTotal,
sum(grantRequestAmount) / 
(Select Sum(GrantRequestAmount) from GrantRequest) * 100 as [Percent]
From GrantRequest
inner join GrantType
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey
Group by Year(GrantRequestDate), month(GrantRequestDate), GrantTypeName

--this is the same query but with formatting added
Select Year(GrantRequestDate) [Year],
month(GrantRequestDate)[month],
GrantTypeName, 
format(sum(GrantRequestAmount),'$#,##0.00') as Total,
format((Select Sum(GrantRequestAmount) from GrantRequest),'$#,##0.00') as GrandTotal,
format(sum(grantRequestAmount) / 
(Select Sum(GrantRequestAmount) from GrantRequest),'0.00 %') as [Percent]
From GrantRequest
inner join GrantType
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey
Group by Year(GrantRequestDate), month(GrantRequestDate), GrantTypeName

--Coordinated subqueries are queries in which the subquery 
--references the outer query. The subquery matches
--the granttypekey from gr1, the outer query with the granttypeKey
--from gr2 inside the subquery. The effect is to make sure
--that like is matched with like. GrantTypeKey 1 (Food) is matched 
--only against other granttypeKey 1 values, GrantTypeKey 2 (Rent)
--is matched only against other grantTypeKey 2 values, etc.
--a coordinated subquery is the SQL equivalent of a recursive
--function in other programming languages, and like them  
-- requires a lot of processor time 

Select GrantTypeKey, GrantRequestAmount
From GrantRequest gr1
Where GrantRequestAmount > 
(Select avg(GrantRequestAmount)
from GrantRequest gr2
Where gr1.GrantTypeKey=gr2.GrantTypeKey)

Select GrantTypeKey, GrantRequestAmount
From GrantRequest 
Where GrantRequestAmount > 
(Select avg(GrantRequestAmount)
from GrantRequest )

Select avg(GrantRequestAmount) From GrantRequest where GrantTypekey=3



Select * from Person
Select * from Employee

Activity Diagrams for ShowTracker

Fan Registration

---

Notify fan if new show is of interest

---

Venue Add Information

---

Add new Show

---

Joins

--Joins--Queries across tables
--Cross Joins
Use Community_Assist

Select Person.PersonKey, PersonLastName, ContactNumber from Person 
Cross Join Contact

Select Person.PersonKey, PersonLastName, ContactNumber 
from Person, Contact 

--Inner Joins--they return matching rows from two or more tables
--old syntax
Select PersonFirstName, PersonLastName, PersonEmail,
PersonAddressStreet, PersonAddressCity, PersonAddressZip
From Person, PersonAddress
where Person.PersonKey = PersonAddress.PersonKey

--new syntax
Select PersonFirstName, PersonLastName, PersonEmail,
PersonAddressStreet, PersonAddressCity, PersonAddressZip
From Person
inner join PersonAddress
on Person.PersonKey=PersonAddress.PersonKey

--alias
Select p.Personkey,PersonFirstName, PersonLastName, PersonEmail,
PersonAddressStreet, PersonAddressCity, PersonAddressZip
From Person p
inner join PersonAddress pa
on p.PersonKey=pa.PersonKey

--multi more than two
Select * from GrantRequest

Select GrantRequestDate, PersonLastName, PersonEmail,
GrantTypeName, GrantRequestExplanation, GrantRequestAmount
From GrantRequest
inner join GrantType
on GrantRequest.GrantTypeKey=GrantType.GrantTypeKey
inner join Person
on Person.PersonKey=GrantRequest.PersonKey

--Outer joins--showing mismatchings
--returns all the records from one table 
--whether it has a match or not
--only matching records from the other table

Select Distinct GrantType.GrantTypeKey,GrantTypeName, GrantRequest.GrantTypeKey
From GrantType
Left outer Join GrantRequest
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey
Where GrantRequest.GrantTypeKey is null

Select Distinct GrantType.GrantTypeKey,GrantTypeName, GrantRequest.GrantTypeKey
From GrantRequest
Right outer Join GrantType
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey
Where GrantRequest.GrantTypeKey is null

Select Distinct GrantType.GrantTypeKey,GrantTypeName, GrantRequest.GrantTypeKey
From GrantRequest
Right Join GrantType
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey
Where GrantRequest.GrantTypeKey is null

Select Distinct GrantType.GrantTypeKey,GrantTypeName, GrantRequest.GrantTypeKey
From GrantType
full outer Join GrantRequest
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey
Where GrantRequest.GrantTypeKey is null

Here are the activity diagrams we did in class for the Self Checkout

Basic

With Split and Join

With Swim lanes

Select 2 Functions

--selects two--functions
--scalar
--Date Time functions
--formating
--Aggregate functions Sum, Avg, count, Max, min

Use Community_Assist

Select * from Donation
--GetDate returns current date and time
Select GetDate() today
--math follows algebraic order of operations
Select 5 + 30/3 * 4

Select DonationAmount, DonationAmount * .3 As Overhead from Donation

--DateTime
--functions for selecting date parts
Select Year(DonationDate)as [Year] from Donation

Select * from Donation where Year(DonationDate)=2016
Select Month(DonationDate) [Month] from Donation

Select * from Donation 
where Month(DonationDate) between 2 and 4
And Year(DonationDate) = 2016

Select Day(DonationDate) [Day] from Donation

--date part will work the same as Year, Month , Day
--but also allows you to do time
Select DatePart(hour,DonationDate) [Hour],
DatePart(Minute, DonationDate) [Minute],
DatePart(Second, DonationDate) [Second]
from Donation

--Subtract one date from another
--you have to choose the unit you want to subtract
Select DateDiff(Day,GetDate(),'6/16/2017') DaysLeft
--add two dates
Select DateAdd(Second,1000000000,GetDate())

--the date functions return an integer
--to concatinate them with charater types
--they have to be converted to character types
--the cast function does that
Select Datename(Month, DonationDate) + ' ' + 
Cast(Day(DonationDate) as nvarchar(4)) + 
', ' + cast(Year(donationDate) as nvarchar(4)) as [Date]
from Donation

--aggregate functions operate on sets of rows at a time
Select Sum(DonationAmount) Total from Donation
Select Avg(DonationAmount) Average from Donation
Select Count(DonationAmount) NumberofDonations from Donation
Select Max(DonationAmount) Highest from Donation
Select Min(DonationAmount) Least from Donation

Select Count(DonationAmount) NumberofDonations from Donation
Where DonationAmount > 500

Select Count(DonationAmount)[Count],
Avg(DonationAmount) Average,
Sum(donationAmount) Total
From Donation

Select * From GrantRequest

--any column that is not a part of an aggregate function
--(when a select has aggregate functions)
--must be made part of a Group by clause
Select GrantTypeKey, Count(GrantTypeKey)[Count], 
Avg(GrantRequestAmount) Average, 
Sum(GrantRequestAmount) Total
From GrantRequest
Group by GrantTypeKey

Select Year(GrantRequestDate) [Year],
GrantTypeKey, Count(GrantTypeKey)[Count], 
Avg(GrantRequestAmount) Average, 
Sum(GrantRequestAmount) Total
From GrantRequest
Where Year(GrantRequestDate)=2015
Group by Year(GrantRequestDate),GrantTypeKey

--Having is like where but used when
--the criteria includes an aggregate value
--in this case Avg
--It always follows the Group by 
--(the where always goes before group by)
Select Year(GrantRequestDate) [Year],
GrantTypeKey, Count(GrantTypeKey)[Count], 
Avg(GrantRequestAmount) Average, 
Sum(GrantRequestAmount) Total
From GrantRequest
Where Year(GrantRequestDate)=2015
Group by Year(GrantRequestDate),GrantTypeKey
Having Avg(GrantRequestAmount)> 400

--Case is a lot like a Switch in C# or Python
--it checks for a value, if it matches
--it will swap it for a different value
Select DonationKey, DonationAmount, 
Case Month(DonationDate)
When 1 then 'January'
when 2 then 'February'
when 3 then 'March'
when 4 then 'April'
when 8 then 'August'
when 9 then 'September'
end as [Month]
From donation

--Format lets you format numeric types as characters
--This one shows the Donation amount as currency
--# is a optional digit, 0 required
Select Format(DonationAmount, '$#,##0.00') from Donation

Select * from Contact
--substring takes 3 arguments, the character field or string
--you are selecting from, the starting character and the 
--number of characters you want to return
Select '(' + substring(ContactNumber,1,3) + ')' 
+ substring(ContactNumber,4,3) + '-' 
+ substring(ContactNumber,7,4) Phone
from Contact

Select format(Cast(ContactNumber as bigint),'(000)000-0000') Phone
From Contact


--system views are useful for returning system information
Select name from sys.Databases
Select * from sys.Databases
Select name from sys.Tables
Select * from sys.Tables

Select * from sys.all_columns where object_id=373576369

Use Cases For Show Tracker

Here are the main actors.

---

Here are the use cases for the General user.

---

Here are the Use cases for Venues. Venues, and all other users, inherit from General User, meaning they also have those use cases.

---

Here are two written use cases for Venues

---

Use cases for Artists

---

Written Use case for Artist

---

Use cases for Fan

---

Written use case for fan

---

Basic Select Statements

use Community_Assist
--select statements
--the * is a wild card meaning return all columns
--in the order they are in the underlying table
Select * from Person
Select * from PersonAddress

--you can alias column names
--the "as" is optional
--square brackets are necessary if there are illegal
--characters such as spaces
Select PersonLastName as [Last Name], 
PersonFirstName [First Name], 
PersonEmail Email
from Person

--the where clause limits the number of rows by specifying 
--what values to return
--character types are always quoted with single quotes
Select PersonAddressApt,PersonAddressStreet,
PersonAddressCity,PersonAddressZip
From PersonAddress
Where PersonAddressCity='Kent'

--with an or clause
Select PersonAddressApt,PersonAddressStreet,
PersonAddressCity,PersonAddressZip
From PersonAddress
Where PersonAddressCity='Kent'
Or PersonAddressCity='Bellevue'

--nulls cannot be = to or compared to any value
--so the "is" keyword is used to find nulls
Select PersonAddressApt,PersonAddressStreet,
PersonAddressCity,PersonAddressZip
From PersonAddress
Where PersonAddressApt is null

--find values that are NOT null
Select PersonAddressApt,PersonAddressStreet,
PersonAddressCity,PersonAddressZip
From PersonAddress
Where PersonAddressApt is not null

--not
Select PersonAddressApt,PersonAddressStreet,
PersonAddressCity,PersonAddressZip
From PersonAddress
Where not PersonAddressCity ='Seattle' --!=, <>

--numeric values are not quoted and can be
--used with comparitives = > < >= <=
Select DonationDate, DonationAmount from Donation
Where DonationAmount > 500

--dates are quoted
Select DonationDate, DonationAmount from Donation
Where DonationDate > '8/30/2015'

Select DonationDate, DonationAmount from Donation
Where DonationDate = '9/3/2015'

--be wary of Date time types the time is counted in the results
Select DonationDate, DonationAmount from Donation
Where DonationDate between '9/3/2015' and '9/11/2015'

--order by sorts ASC is the default
--DESC sorts descending
Select * from Person
order by PersonLastName asc, PersonFirstname Desc

Select * from Donation
order by DonationAmount desc

--Like searches for patterns. the "%" is a wild card
--it represents any number of characters
Select * from Person
Where PersonLastName like 'Z%n'

Select * from Person
Where PersonLastName like '%mm%'
-- the underscore _ is a wild card for single character

Select * from Person
Where PersonLastName like 'Tan_er'

--Distict returns only Distinct, unique rows. 
--(it doesn't apply to the columns only the rows)
Select Distinct PersonKey from Donation
order by PersonKey

--returns only the specified number of rows
--literally just takes the first rows for as many
--as are specified-- order by makes the command
--return a more meaningful result
Select Distinct top 10 DonationAmount from Donation
order by DonationAmount desc

--the offset starts at the specified number 
--of rows from the top and then returns
--the stated number of rows
--order by is required
Select Distinct DonationAmount from Donation
Order by DonationAmount desc
Offset 5 rows fetch next 10 rows only

Use Cases One

Here are the use case diagrams we made in class about a self checkout machine in a supermarket

Creating Tables

--creating tables
/*this is a multiple
line */

Create Database TestTables

Use TestTables

Create table Person
(
 PersonKey int identity(1,1) primary key,
 PersonLastName nvarchar(255) not null,
 PersonFirstName nvarchar(255) null,
 PersonEmail nvarchar(255) not null,
 PersonAddedDate Datetime default GetDate()
)

Create Table PersonAddress
(
   PersonAddressKey int identity(1,1),
   PersonAddressStreet nvarchar(255) not null,
   PersonAddressCity nvarchar(255) default 'Seattle',
   PersonKey int, --Foreign Key references Person(personKey)
   constraint PK_PersonAddress primary key(PersonAddressKey),
   constraint FK_PersonAddress Foreign key(personKey)
        references Person(PersonKey)
)

Create Table PersonContact
(
   PersonContactKey int identity(1,1),
   PersonContactHomePhone nchar(14),
   PersonKey int

)

Alter table PersonContact
Add Constraint Pk_PersonContact primary key (PersonContactKey),
Constraint Fk_personContact foreign key (PersonKey)
     references Person(PersonKey)

--dropping a column
Alter table Person
Drop column PersoneEmail

--adding a column
Alter Table Person
Add PersonEmail nvarchar(255)

--add unique constraint
Alter table Person
Add constraint unique_Email Unique(PersonEmail)