GOC335 DDL T-SQL

-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 04/02/2011 16:00:07
-- Generated from EDMX file: c:\users\developer\documents\visual studio 2010\Projects\NemesisEvents\Altairis.Nemesis.Events.Data\NemesisEvents.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [NemesisEvents];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_Events_Users]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Events] DROP CONSTRAINT [FK_Events_Users];
GO
IF OBJECT_ID(N'[dbo].[FK_Events_Venues]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Events] DROP CONSTRAINT [FK_Events_Venues];
GO
IF OBJECT_ID(N'[dbo].[FK_Venues_Areas]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Venues] DROP CONSTRAINT [FK_Venues_Areas];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Areas]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Areas];
GO
IF OBJECT_ID(N'[dbo].[Events]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Events];
GO
IF OBJECT_ID(N'[dbo].[Users]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Users];
GO
IF OBJECT_ID(N'[dbo].[Venues]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Venues];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Areas'
CREATE TABLE [dbo].[Areas] (
    [AreaId] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(100)  NOT NULL
);
GO

-- Creating table 'Events'
CREATE TABLE [dbo].[Events] (
    [EventId] int IDENTITY(1,1) 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
);
GO

-- Creating table 'Users'
CREATE TABLE [dbo].[Users] (
    [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,
    [DisplayName] nvarchar(100)  NULL,
    [IsAdministrator] bit  NOT NULL,
    [IsOrganizer] bit  NOT NULL
);
GO

-- Creating table 'Venues'
CREATE TABLE [dbo].[Venues] (
    [VenueId] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(100)  NOT NULL,
    [StreetAddress] nvarchar(100)  NULL,
    [Description] nvarchar(max)  NULL,
    [AreaId] int  NOT NULL
);
GO

-- Creating table 'UsersRegisteredToEvent'
CREATE TABLE [dbo].[UsersRegisteredToEvent] (
    [UsersRegistered_UserName] nvarchar(100)  NOT NULL,
    [EventsRegistered_EventId] int  NOT NULL
);
GO

-- Creating table 'UserArea'
CREATE TABLE [dbo].[UserArea] (
    [WatchingUsers_UserName] nvarchar(100)  NOT NULL,
    [WatchedAreas_AreaId] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [AreaId] in table 'Areas'
ALTER TABLE [dbo].[Areas]
ADD CONSTRAINT [PK_Areas]
    PRIMARY KEY CLUSTERED ([AreaId] ASC);
GO

-- Creating primary key on [EventId] in table 'Events'
ALTER TABLE [dbo].[Events]
ADD CONSTRAINT [PK_Events]
    PRIMARY KEY CLUSTERED ([EventId] ASC);
GO

-- Creating primary key on [UserName] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [PK_Users]
    PRIMARY KEY CLUSTERED ([UserName] ASC);
GO

-- Creating primary key on [VenueId] in table 'Venues'
ALTER TABLE [dbo].[Venues]
ADD CONSTRAINT [PK_Venues]
    PRIMARY KEY CLUSTERED ([VenueId] ASC);
GO

-- Creating primary key on [UsersRegistered_UserName], [EventsRegistered_EventId] in table 'UsersRegisteredToEvent'
ALTER TABLE [dbo].[UsersRegisteredToEvent]
ADD CONSTRAINT [PK_UsersRegisteredToEvent]
    PRIMARY KEY NONCLUSTERED ([UsersRegistered_UserName], [EventsRegistered_EventId] ASC);
GO

-- Creating primary key on [WatchingUsers_UserName], [WatchedAreas_AreaId] in table 'UserArea'
ALTER TABLE [dbo].[UserArea]
ADD CONSTRAINT [PK_UserArea]
    PRIMARY KEY NONCLUSTERED ([WatchingUsers_UserName], [WatchedAreas_AreaId] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [AreaId] in table 'Venues'
ALTER TABLE [dbo].[Venues]
ADD CONSTRAINT [FK_Venues_Areas]
    FOREIGN KEY ([AreaId])
    REFERENCES [dbo].[Areas]
        ([AreaId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Venues_Areas'
CREATE INDEX [IX_FK_Venues_Areas]
ON [dbo].[Venues]
    ([AreaId]);
GO

-- Creating foreign key on [OwnerUserName] in table 'Events'
ALTER TABLE [dbo].[Events]
ADD CONSTRAINT [FK_Events_Users]
    FOREIGN KEY ([OwnerUserName])
    REFERENCES [dbo].[Users]
        ([UserName])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Events_Users'
CREATE INDEX [IX_FK_Events_Users]
ON [dbo].[Events]
    ([OwnerUserName]);
GO

-- Creating foreign key on [VenueId] in table 'Events'
ALTER TABLE [dbo].[Events]
ADD CONSTRAINT [FK_Events_Venues]
    FOREIGN KEY ([VenueId])
    REFERENCES [dbo].[Venues]
        ([VenueId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Events_Venues'
CREATE INDEX [IX_FK_Events_Venues]
ON [dbo].[Events]
    ([VenueId]);
GO

-- Creating foreign key on [UsersRegistered_UserName] in table 'UsersRegisteredToEvent'
ALTER TABLE [dbo].[UsersRegisteredToEvent]
ADD CONSTRAINT [FK_UsersRegisteredToEvent_User]
    FOREIGN KEY ([UsersRegistered_UserName])
    REFERENCES [dbo].[Users]
        ([UserName])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [EventsRegistered_EventId] in table 'UsersRegisteredToEvent'
ALTER TABLE [dbo].[UsersRegisteredToEvent]
ADD CONSTRAINT [FK_UsersRegisteredToEvent_Event]
    FOREIGN KEY ([EventsRegistered_EventId])
    REFERENCES [dbo].[Events]
        ([EventId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UsersRegisteredToEvent_Event'
CREATE INDEX [IX_FK_UsersRegisteredToEvent_Event]
ON [dbo].[UsersRegisteredToEvent]
    ([EventsRegistered_EventId]);
GO

-- Creating foreign key on [WatchingUsers_UserName] in table 'UserArea'
ALTER TABLE [dbo].[UserArea]
ADD CONSTRAINT [FK_UserArea_User]
    FOREIGN KEY ([WatchingUsers_UserName])
    REFERENCES [dbo].[Users]
        ([UserName])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [WatchedAreas_AreaId] in table 'UserArea'
ALTER TABLE [dbo].[UserArea]
ADD CONSTRAINT [FK_UserArea_Area]
    FOREIGN KEY ([WatchedAreas_AreaId])
    REFERENCES [dbo].[Areas]
        ([AreaId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserArea_Area'
CREATE INDEX [IX_FK_UserArea_Area]
ON [dbo].[UserArea]
    ([WatchedAreas_AreaId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------