GOC335 Vytvoření tabulek

USE NemesisEvents

CREATE TABLE Users (
    -- Fields required by membership provider
    UserName                nvarchar(100)       not null,
    PasswordHash            binary(64)          not null,
    PasswordSalt            binary(128)         not null,
    Email                   nvarchar(100)       not null,
    Comment                 nvarchar(max)       null,
    IsApproved              bit                 not null,
    DateCreated             datetime            not null,
    DateLastLogin           datetime            null,
    DateLastActivity        datetime            null,
    DateLastPasswordChange  datetime            not null,
    -- Custom fields
    DisplayName             nvarchar(100)       null,
    IsAdministrator         bit                 not null,
    IsOrganizer             bit                 not null,
)

CREATE TABLE Events (
    EventId                 int identity        not null,
    Name                    nvarchar(100)       not null,
    Description             nvarchar(max)       not null,
    DateBegin               datetime            not null,
    VenueId                 int                 not null,
    OwnerUserName           nvarchar(100)       not null,
    UseRegistration         bit                 not null,
    AllowRegistration       bit                 not null,
)

CREATE TABLE Venues (
    VenueId                 int identity        not null,
    Name                    nvarchar(100)       not null,
    StreetAddress           nvarchar(100)       null,
    Description             nvarchar(max)       null,
    AreaId                  int                 not null,
)

CREATE TABLE Areas (
    AreaId                  int identity        not null,
    Name                    nvarchar(100)       not null,
)

-- Primary keys
ALTER TABLE Venues ADD CONSTRAINT PK_Venues PRIMARY KEY CLUSTERED (VenueId)
ALTER TABLE Users ADD CONSTRAINT PK_Users PRIMARY KEY CLUSTERED (UserName)
ALTER TABLE Events ADD CONSTRAINT PK_Events PRIMARY KEY CLUSTERED (EventId)
ALTER TABLE Areas ADD CONSTRAINT PK_Areas PRIMARY KEY CLUSTERED (AreaId)

-- Foreign keys
ALTER TABLE Venues ADD CONSTRAINT FK_Venues_Areas FOREIGN KEY (AreaId) REFERENCES Areas (AreaId)
ALTER TABLE Events ADD CONSTRAINT FK_Events_Venues FOREIGN KEY (VenueId) REFERENCES Venues (VenueId)
ALTER TABLE Events ADD CONSTRAINT FK_Events_Users FOREIGN KEY (OwnerUserName) REFERENCES Users (UserName)