ISM 218 Project Assignment - Part 2 Oak Creek Stadium Section 3 and 4 Solution

3 a) a. Create the Tables. Each Table must have a Primary Key constraint, Foreign Key

constraints where applicable, and other constraints as written in the table

instance charts.

Queries:

create table  SeasonPass (SeasonPassID Integer(7) not null,ExpirationDate Date Not null,primary key(SeasonPassID));

create table Customer(CustomerID INTEGER(7) not null, C_FName varchar(20) not null,C_Lname  varchar(30) not null,C_Height INTEGER(7) not null,SeasonPass_ID INTEGER not null,primary key(CustomerID) ,foreign key(SeasonPass_ID) references SeasonPass(SeasonPassID) );

create table Team(TeamID INTEGER(7) not null,Team_Name VARCHAR(30) not null,Coach_FName VARCHAR(20) not null,Coach_LName VARCHAR(30) not null,City VARCHAR(20) not null, State VARCHAR(20) not null,primary key(TeamID));

create table Event(EventID INTEGER(7) not null ,Event_DateTime DATETIME not null,SportType varchar(20) not null,SeatsAvailable INTEGER(4) not null,HomeTeamID INTEGER(7) not null,VisitorTeamID INTEGER(7) not null,

primary key(EventID), foreign key(HomeTeamID) references Team(TeamID), foreign key(VisitorTeamID) references Team(TeamID), CHECK (SportType IN( 'basketball', 'hockey', 'football', 'soccer' )));