Create tables

/* ***************************
Add to Community_Assist
EventType, Location, 
Event, EventAttendence, EventComments
************************* */
Use Community_Assist

Create table EventType
(
    EventTypeKey int identity(1,1) primary key,
 EventTypeName nvarchar(255) not null,
 EventTypeDescription nvarchar(255) null
);

Create Table EventLocation
(
   EventLocationKey int identity(1,1),
   EventLocationName nvarchar(255) not null,
   EventLocationAddress nvarchar(255) not null,
   EventLocationCity nvarchar(255) default 'Seattle',
   EventLocationState nchar(2) default 'WA',
   EventLocationZip nchar(10) not null,
   constraint pk_EventLocation Primary Key(EventLocationKey)

);

Create Table CAEvent
(
   CAEventKey int identity(1,1) primary key,
   CAEventName nvarchar(255) not null,
   CAEventDate Date not null,
   CAEventStartTime time null,
   CAEventEndTime time null,
   EventLocationKey int Foreign key 
  references EventLocation(EventLocationKey),
   EventTypeKey int Foreign key 
     references EventType(EventTypeKey),
   EmployeeKey int null,
   Constraint FK_EmployeeCAEvent Foreign Key(EmployeeKey)
  references Employee(EmployeeKey)

)

Create table EventAttendence
(
    CAEventKey int not null,
 PersonKey int not null,
 Constraint PK_EventAttendence 
    primary key(CaEventKey, PersonKey),
 Constraint FK_CAEventAttend foreign Key(CAEventKey)
    references CAEvent(CAEventKey),
    Constraint FK_PersonAttend foreign Key(PersonKey)
    references Person(PersonKey)
)

Create table EventComment
(
    EventCommentKey int identity(1,1) not null,
 CAEventKey int not null,
 PersonKey int not null,
 EventCommentDate Date not null,
 EventRating int not null,
 CommentText nvarchar(max)

)

Alter Table EventComment
Add Constraint PK_EventComment Primary key (EventCommentKey)

Alter Table EventComment
Add 
Constraint FK_CAEventComment Foreign Key(CAEventKey)
   References CAEvent(CAEventKey),
Constraint FK_PersonComment Foreign Key(PersonKey)
   References Person(PersonKey)

Alter Table EventComment
Add 
Constraint df_CommentDate  default GetDate() for EventCommentDate

Alter Table EventComment
Drop Constraint ck_Rating

Alter Table EventComment
Drop column eventRating

Alter Table EventComment
Add EventRating int


Alter Table EventComment
Add constraint ck_Rating Check (EventRating between 1 and 5)