Hecky's Project ASP.NET Solution

Top

 USE CASE DIAGRAM

  • company drivers are checking their duty schedule from garage depot notice board
  • company drivers are liaising through Senior Garage Administrator (SGA) who can only deal with one person at a time to address their work related issues (communication)
  • The administration has one operational manager who deal with day to day operations and manage all departments
  • The administration has an assistant operational manager who deal with disciplinary, public complains, referrals from SGA and occasionally covers operational managers’ job
  • There is also accident prevention manager, who deals health and safety and general accident issues and the public in relation to any accident issues on behalf of the company
  • And not the least a pay clerk who deals with staff pay
  • Driver reports absence
  • Driver view holiday history
  • Driver and Admin can register online
  • Driver can change duty with another driver but needs approval from admin
  • Staff view jobs relating to them e.g. meeting minutes
  • Driver can send message to operational manager and assistant operational manager
  • Driver can view pay slip

Figure 1: Use-case diagram

 

 

ERD (Entity Relationship Diagram)

Figure 2: ERD

 

 

 

IMPLEMENTATION

This section discuss the steps taken to develop the application and the challenges and logics put in place to ensure the application meets the requirements discussed in the Analysis stage of this report.

As discussed in the Design stage of this report, the web application was developed using ASP.NET technology with C# the programming language used to manage the back end code. The database used was SQL Server 2012 and T-SQL (Transact Structured Query Language) is the language used to communicate/query the database.

 

Securing the Website

The site’s security was implemented using the asp.net technology provided for User roles and membership. It allows setting of permissions based on user roles and restricting access to files in the containing folders where permissions were restricted. Doing this makes it easy to keep track of the permissions and as well assign more layers of security to all users without breaking the security system structure put in place.

The access rules for security were declared in the “~/web.config” and it relates to the tables created in the database. One of the major codes that determine the permission is shown below:

Figure 3: Setting the site security in the web.config file

 

Security Trimming

ASP.NET provide a useful feature called security trimming that allows menu links to be hidden to users who do not have access to view the link. This feature is set up by adding the below code and ensuring that a sitemap is used for menu control.

Figure 4: Sitemap usage

The security trimming feature reduces the link shown to users. For example, the screenshot shown below is for anonymous users who are yet to login:

Figure 5: Menu links shown to anonymous user

Whereas an authenticated user who is a driver can see more links and as well access it. The screenshot below shows the link for a driver:

Figure 6: Menu links shown to a driver

 

Communicating with the database

A separate class “DBHandler.cs” was created in the app_code directory to handle all database connection, query, inserts etc. This is done to make the site more maintainable and enable reuse of code while querying the database.

This class also includes exception handling code to prevent the web app from breaking when errors occur while querying the database.

 

Assigning Jobs to Drivers

This task is executed by users with admin privileges and can be found in the page located at "~/Admin/ManageJob". This section requires the use of client and server side validation to prevent the Administrator making mistakes and as well guide them during the creation of a job.

Some of the rules set in place to avoid problems include:

  • Driver is not assigned multiple jobs at the same date and time
  • One Bus is not assigned to multiple jobs

To avoid assigning multiple jobs to a driver, SQL query was written to check that the driver does not have a duty assigned to him/her at the particular date queried as shown below:

Figure 7: Preventing driver taking multiple jobs on the same date

 

The result of the above query ensures that an error message is displayed when an administrator attempts to assign a new job for a driver who has a scheduled job within the time frame chosen. This is shown in the screenshot below:

Figure 8: Driver prevented from having multiple jobs assigned at the same time

 

To avoid assigning multiple jobs to a bus, SQL query was written to check that the bus does not have a duty assigned at the particular date queried as shown below:

Figure 9: Preventing bus having multiple jobs assigned at the same time

 

The screenshot below shows the result of the above query code used to prevent a bus from been assigned to a job at the same time.

Figure 10: Bus creation with multiple jobs prevented

 

The view for managing a job is shown below and a validation for assigning a job included in the back end code as shown below:

Figure 11: Manage a Job page

Figure 12: Checks before creating a new job (Back end code)

 

Database Implementation

The database was created in SQL Server 2012 and the script used can be found in the Appendix section of this report. The database structure is exactly the same as that described in the ERD (Entity Relational Diagram) shown in the design of this report.

The Database name created is “HectorDB” and a total of 26 tables were created with majority of the tables handling the security part of the web application (User roles and membership).

One problem encountered during this implementation is the time taken to perfect my Transact-Sql query skills which was vital in many areas of the implementation including the code written in the ASP.NET pages.

 

Creating the Message

The implementation of the message required a lot of careful planning and testing to ensure the right user receives the correct message sent to them. A combination of SQL code and C# code was used to implement this task. The screenshot below shows the messages shown to a driver.

Figure 13: Messages displayed to a driver

 

 

ANALYSIS A – SQL CODE

USE [master]

GO

/****** Object:  Database [HectorDb]    ******/

CREATE DATABASE [HectorDb]

 

USE [HectorDb]

GO

CREATE TABLE [dbo].[Applications](

       [ApplicationName] [nvarchar](235) NOT NULL,

       [ApplicationId] [uniqueidentifier] NOT NULL,

       [Description] [nvarchar](256) NULL,

PRIMARY KEY CLUSTERED

(

       [ApplicationId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

/****** Object:  Table [dbo].[aspnet_Applications]    ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[aspnet_Applications](

       [ApplicationName] [nvarchar](256) NOT NULL,

       [LoweredApplicationName] [nvarchar](256) NOT NULL,

       [ApplicationId] [uniqueidentifier] NOT NULL,

       [Description] [nvarchar](256) NULL,

PRIMARY KEY NONCLUSTERED

(

       [ApplicationId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],

UNIQUE NONCLUSTERED

(

       [LoweredApplicationName] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],

UNIQUE NONCLUSTERED

(

       [ApplicationName] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

/****** Object:  Table [dbo].[aspnet_Membership]    ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[aspnet_Membership](

       [ApplicationId] [uniqueidentifier] NOT NULL,

       [UserId] [uniqueidentifier] NOT NULL,

       [Password] [nvarchar](128) NOT NULL,

       [PasswordFormat] [int] NOT NULL,

       [PasswordSalt] [nvarchar](128) NOT NULL,

       [MobilePIN] [nvarchar](16) NULL,

       [Email] [nvarchar](256) NULL,

       [LoweredEmail] [nvarchar](256) NULL,

       [PasswordQuestion] [nvarchar](256) NULL,

       [PasswordAnswer] [nvarchar](128) NULL,

       [IsApproved] [bit] NOT NULL,

       [IsLockedOut] [bit] NOT NULL,

       [CreateDate] [datetime] NOT NULL,

       [LastLoginDate] [datetime] NOT NULL,

       [LastPasswordChangedDate] [datetime] NOT NULL,

       [LastLockoutDate] [datetime] NOT NULL,

       [FailedPasswordAttemptCount] [int] NOT NULL,

       [FailedPasswordAttemptWindowStart] [datetime] NOT NULL,

       [FailedPasswordAnswerAttemptCount] [int] NOT NULL,

       [FailedPasswordAnswerAttemptWindowStart] [datetime] NOT NULL,

       [Comment] [ntext] NULL,

PRIMARY KEY NONCLUSTERED

(

       [UserId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

GO

/****** Object:  Table [dbo].[aspnet_Paths]  ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[aspnet_Paths](

       [ApplicationId] [uniqueidentifier] NOT NULL,

       [PathId] [uniqueidentifier] NOT NULL,

       [Path] [nvarchar](256) NOT NULL,

       [LoweredPath] [nvarchar](256) NOT NULL,

PRIMARY KEY NONCLUSTERED

(

       [PathId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

/****** Object:  Table [dbo].[aspnet_PersonalizationAllUsers] ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[aspnet_PersonalizationAllUsers](

       [PathId] [uniqueidentifier] NOT NULL,

       [PageSettings] [image] NOT NULL,

       [LastUpdatedDate] [datetime] NOT NULL,

PRIMARY KEY CLUSTERED

(

       [PathId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

GO

/****** Object:  Table [dbo].[aspnet_PersonalizationPerUser]  ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[aspnet_PersonalizationPerUser](

       [Id] [uniqueidentifier] NOT NULL,

       [PathId] [uniqueidentifier] NULL,

       [UserId] [uniqueidentifier] NULL,

       [PageSettings] [image] NOT NULL,

       [LastUpdatedDate] [datetime] NOT NULL,

PRIMARY KEY NONCLUSTERED

(

       [Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

GO

/****** Object:  Table [dbo].[aspnet_Profile]    ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[aspnet_Profile](

       [UserId] [uniqueidentifier] NOT NULL,

       [PropertyNames] [ntext] NOT NULL,

       [PropertyValuesString] [ntext] NOT NULL,

       [PropertyValuesBinary] [image] NOT NULL,

       [LastUpdatedDate] [datetime] NOT NULL,

PRIMARY KEY CLUSTERED

(

       [UserId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

GO

/****** Object:  Table [dbo].[aspnet_Roles]    ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[aspnet_Roles](

       [ApplicationId] [uniqueidentifier] NOT NULL,

       [RoleId] [uniqueidentifier] NOT NULL,

       [RoleName] [nvarchar](256) NOT NULL,

       [LoweredRoleName] [nvarchar](256) NOT NULL,

       [Description] [nvarchar](256) NULL,

PRIMARY KEY NONCLUSTERED

(

       [RoleId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

/****** Object:  Table [dbo].[aspnet_SchemaVersions]  ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[aspnet_SchemaVersions](

       [Feature] [nvarchar](128) NOT NULL,

       [CompatibleSchemaVersion] [nvarchar](128) NOT NULL,

       [IsCurrentVersion] [bit] NOT NULL,

PRIMARY KEY CLUSTERED

(

       [Feature] ASC,

       [CompatibleSchemaVersion] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

/****** Object:  Table [dbo].[aspnet_Users]    ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[aspnet_Users](

       [ApplicationId] [uniqueidentifier] NOT NULL,

       [UserId] [uniqueidentifier] NOT NULL,

       [UserName] [nvarchar](256) NOT NULL,

       [LoweredUserName] [nvarchar](256) NOT NULL,

       [MobileAlias] [nvarchar](16) NULL,

       [IsAnonymous] [bit] NOT NULL,

       [LastActivityDate] [datetime] NOT NULL,

PRIMARY KEY NONCLUSTERED

(

       [UserId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

/****** Object:  Table [dbo].[aspnet_UsersInRoles]    ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[aspnet_UsersInRoles](

       [UserId] [uniqueidentifier] NOT NULL,

       [RoleId] [uniqueidentifier] NOT NULL,

PRIMARY KEY CLUSTERED

(

       [UserId] ASC,

       [RoleId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

/****** Object:  Table [dbo].[aspnet_WebEvent_Events] ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[aspnet_WebEvent_Events](

       [EventId] [char](32) NOT NULL,

       [EventTimeUtc] [datetime] NOT NULL,

       [EventTime] [datetime] NOT NULL,

       [EventType] [nvarchar](256) NOT NULL,

       [EventSequence] [decimal](19, 0) NOT NULL,

       [EventOccurrence] [decimal](19, 0) NOT NULL,

       [EventCode] [int] NOT NULL,

       [EventDetailCode] [int] NOT NULL,

       [Message] [nvarchar](1024) NULL,

       [ApplicationPath] [nvarchar](256) NULL,

       [ApplicationVirtualPath] [nvarchar](256) NULL,

       [MachineName] [nvarchar](256) NOT NULL,

       [RequestUrl] [nvarchar](1024) NULL,

       [ExceptionType] [nvarchar](256) NULL,

       [Details] [ntext] NULL,

PRIMARY KEY CLUSTERED

(

       [EventId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

GO

SET ANSI_PADDING OFF

GO

/****** Object:  Table [dbo].[Bus]    ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Bus](

       [busid] [varchar](10) NOT NULL,

       [enginenumber] [varchar](50) NULL,

       [chasisnumber] [varchar](50) NULL,

       [platenumber] [varchar](50) NULL,

       [condition] [varchar](50) NULL,

 CONSTRAINT [PK_Bus] PRIMARY KEY CLUSTERED

(

       [busid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

SET ANSI_PADDING OFF

GO

/****** Object:  Table [dbo].[DriverDuty]    ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[DriverDuty](

       [dutyid] [int] IDENTITY(1,1) NOT NULL,

       [day_starttime] [datetime] NOT NULL,

       [day_endtime] [datetime] NOT NULL,

       [username] [varchar](50) NOT NULL,

       [startlocid] [int] NOT NULL,

       [endlocid] [int] NULL,

       [busid] [varchar](10) NULL,

       [shiftcompleted] [int] NULL,

 CONSTRAINT [PK_DriverDuty] PRIMARY KEY CLUSTERED

(

       [dutyid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

SET ANSI_PADDING OFF

GO

/****** Object:  Table [dbo].[EmployeeContract]    ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[EmployeeContract](

       [contractid] [int] IDENTITY(1,1) NOT NULL,

       [salary] [decimal](18, 2) NOT NULL,

       [dateemployed] [date] NOT NULL,

       [dateunemployed] [date] NULL,

       [holidaydaysallowed] [int] NULL,

       [commission] [decimal](4, 2) NULL,

       [username] [varchar](50) NULL,

 CONSTRAINT [PK_EmployeeContract] PRIMARY KEY CLUSTERED

(

       [contractid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

SET ANSI_PADDING OFF

GO

/****** Object:  Table [dbo].[HolidayTaken]   ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[HolidayTaken](

       [holtakenid] [int] IDENTITY(1,1) NOT NULL,

       [startdate] [datetime] NOT NULL,

       [enddate] [datetime] NOT NULL,

       [approved] [int] NULL,

       [username] [varchar](50) NOT NULL,

       [holtypeid] [int] NOT NULL,

 CONSTRAINT [PK_HolidayTaken] PRIMARY KEY CLUSTERED

(

       [holtakenid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

SET ANSI_PADDING OFF

GO

/****** Object:  Table [dbo].[HolidayType]    ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[HolidayType](

       [holtypeid] [int] IDENTITY(1,1) NOT NULL,

       [name] [varchar](150) NOT NULL,

       [description] [varchar](200) NULL

) ON [PRIMARY]

 

GO

SET ANSI_PADDING OFF

GO

/****** Object:  Table [dbo].[Location]  ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Location](

       [locid] [int] IDENTITY(1,1) NOT NULL,

       [locname] [varchar](100) NOT NULL,

       [locdescription] [varchar](200) NULL,

 CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED

(

       [locid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

SET ANSI_PADDING OFF

GO

/****** Object:  Table [dbo].[Memberships]    ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Memberships](

       [ApplicationId] [uniqueidentifier] NOT NULL,

       [UserId] [uniqueidentifier] NOT NULL,

       [Password] [nvarchar](128) NOT NULL,

       [PasswordFormat] [int] NOT NULL,

       [PasswordSalt] [nvarchar](128) NOT NULL,

       [Email] [nvarchar](256) NULL,

       [PasswordQuestion] [nvarchar](256) NULL,

       [PasswordAnswer] [nvarchar](128) NULL,

       [IsApproved] [bit] NOT NULL,

       [IsLockedOut] [bit] NOT NULL,

       [CreateDate] [datetime] NOT NULL,

       [LastLoginDate] [datetime] NOT NULL,

       [LastPasswordChangedDate] [datetime] NOT NULL,

       [LastLockoutDate] [datetime] NOT NULL,

       [FailedPasswordAttemptCount] [int] NOT NULL,

       [FailedPasswordAttemptWindowStart] [datetime] NOT NULL,

       [FailedPasswordAnswerAttemptCount] [int] NOT NULL,

       [FailedPasswordAnswerAttemptWindowsStart] [datetime] NOT NULL,

       [Comment] [nvarchar](256) NULL,

PRIMARY KEY CLUSTERED

(

       [UserId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

/****** Object:  Table [dbo].[Message]    ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Message](

       [messageid] [int] IDENTITY(1,1) NOT NULL,

       [subject] [varchar](100) NOT NULL,

       [messagebody] [text] NOT NULL,

       [username_from] [varchar](50) NOT NULL,

       [username_to] [varchar](50) NOT NULL,

       [created_date] [datetime] NULL,

 CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED

(

       [messageid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

GO

SET ANSI_PADDING OFF

GO

/****** Object:  Table [dbo].[Profiles]    ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Profiles](

       [UserId] [uniqueidentifier] NOT NULL,

       [PropertyNames] [nvarchar](4000) NOT NULL,

       [PropertyValueStrings] [nvarchar](4000) NOT NULL,

       [PropertyValueBinary] [image] NOT NULL,

       [LastUpdatedDate] [datetime] NOT NULL,

PRIMARY KEY CLUSTERED

(

       [UserId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

GO

/****** Object:  Table [dbo].[Roles]    ******/

SET ANSI_NULLS ON                         

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Roles](

       [ApplicationId] [uniqueidentifier] NOT NULL,

       [RoleId] [uniqueidentifier] NOT NULL,

       [RoleName] [nvarchar](256) NOT NULL,

       [Description] [nvarchar](256) NULL,

PRIMARY KEY CLUSTERED

(

       [RoleId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

/****** Object:  Table [dbo].[Users]    ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Users](

       [ApplicationId] [uniqueidentifier] NOT NULL,

       [UserId] [uniqueidentifier] NOT NULL,

       [UserName] [nvarchar](50) NOT NULL,

       [IsAnonymous] [bit] NOT NULL,

       [LastActivityDate] [datetime] NOT NULL,

PRIMARY KEY CLUSTERED

(

       [UserId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

/****** Object:  Table [dbo].[UsersInRoles]    ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[UsersInRoles](

       [UserId] [uniqueidentifier] NOT NULL,

       [RoleId] [uniqueidentifier] NOT NULL,

PRIMARY KEY CLUSTERED

(

       [UserId] ASC,

       [RoleId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

/****** Object:  Table [dbo].[UsersOpenAuthAccounts]    ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[UsersOpenAuthAccounts](

       [ApplicationName] [nvarchar](128) NOT NULL,

       [ProviderName] [nvarchar](128) NOT NULL,

       [ProviderUserId] [nvarchar](128) NOT NULL,

       [ProviderUserName] [nvarchar](max) NOT NULL,

       [MembershipUserName] [nvarchar](128) NOT NULL,

       [LastUsedUtc] [datetime] NULL,

PRIMARY KEY CLUSTERED

(

       [ApplicationName] ASC,

       [ProviderName] ASC,

       [ProviderUserId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

GO

/****** Object:  Table [dbo].[UsersOpenAuthData]    ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[UsersOpenAuthData](

       [ApplicationName] [nvarchar](128) NOT NULL,

       [MembershipUserName] [nvarchar](128) NOT NULL,

       [HasLocalPassword] [bit] NOT NULL,

PRIMARY KEY CLUSTERED

(

       [ApplicationName] ASC,

       [MembershipUserName] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

/****** Object:  View [dbo].[vw_aspnet_Applications]   ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

 

  CREATE VIEW [dbo].[vw_aspnet_Applications]

  AS SELECT [dbo].[aspnet_Applications].[ApplicationName], [dbo].[aspnet_Applications].[LoweredApplicationName], [dbo].[aspnet_Applications].[ApplicationId], [dbo].[aspnet_Applications].[Description]

  FROM [dbo].[aspnet_Applications]

 

GO

/****** Object:  View [dbo].[vw_aspnet_MembershipUsers]   ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

 

  CREATE VIEW [dbo].[vw_aspnet_MembershipUsers]

  AS SELECT [dbo].[aspnet_Membership].[UserId],

            [dbo].[aspnet_Membership].[PasswordFormat],

            [dbo].[aspnet_Membership].[MobilePIN],

            [dbo].[aspnet_Membership].[Email],

            [dbo].[aspnet_Membership].[LoweredEmail],

            [dbo].[aspnet_Membership].[PasswordQuestion],

            [dbo].[aspnet_Membership].[PasswordAnswer],

            [dbo].[aspnet_Membership].[IsApproved],

            [dbo].[aspnet_Membership].[IsLockedOut],

            [dbo].[aspnet_Membership].[CreateDate],

            [dbo].[aspnet_Membership].[LastLoginDate],

            [dbo].[aspnet_Membership].[LastPasswordChangedDate],

            [dbo].[aspnet_Membership].[LastLockoutDate],

            [dbo].[aspnet_Membership].[FailedPasswordAttemptCount],

            [dbo].[aspnet_Membership].[FailedPasswordAttemptWindowStart],

            [dbo].[aspnet_Membership].[FailedPasswordAnswerAttemptCount],

            [dbo].[aspnet_Membership].[FailedPasswordAnswerAttemptWindowStart],

            [dbo].[aspnet_Membership].[Comment],

            [dbo].[aspnet_Users].[ApplicationId],

            [dbo].[aspnet_Users].[UserName],

            [dbo].[aspnet_Users].[MobileAlias],

            [dbo].[aspnet_Users].[IsAnonymous],

            [dbo].[aspnet_Users].[LastActivityDate]

  FROM [dbo].[aspnet_Membership] INNER JOIN [dbo].[aspnet_Users]

      ON [dbo].[aspnet_Membership].[UserId] = [dbo].[aspnet_Users].[UserId]

 

GO

/****** Object:  View [dbo].[vw_aspnet_Profiles]    ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

 

  CREATE VIEW [dbo].[vw_aspnet_Profiles]

  AS SELECT [dbo].[aspnet_Profile].[UserId], [dbo].[aspnet_Profile].[LastUpdatedDate],

      [DataSize]=  DATALENGTH([dbo].[aspnet_Profile].[PropertyNames])

                 + DATALENGTH([dbo].[aspnet_Profile].[PropertyValuesString])

                 + DATALENGTH([dbo].[aspnet_Profile].[PropertyValuesBinary])

  FROM [dbo].[aspnet_Profile]

 

GO

/****** Object:  View [dbo].[vw_aspnet_Roles]    ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

 

  CREATE VIEW [dbo].[vw_aspnet_Roles]

  AS SELECT [dbo].[aspnet_Roles].[ApplicationId], [dbo].[aspnet_Roles].[RoleId], [dbo].[aspnet_Roles].[RoleName], [dbo].[aspnet_Roles].[LoweredRoleName], [dbo].[aspnet_Roles].[Description]

  FROM [dbo].[aspnet_Roles]

 

GO

/****** Object:  View [dbo].[vw_aspnet_Users]    ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

 

  CREATE VIEW [dbo].[vw_aspnet_Users]

  AS SELECT [dbo].[aspnet_Users].[ApplicationId], [dbo].[aspnet_Users].[UserId], [dbo].[aspnet_Users].[UserName], [dbo].[aspnet_Users].[LoweredUserName], [dbo].[aspnet_Users].[MobileAlias], [dbo].[aspnet_Users].[IsAnonymous], [dbo].[aspnet_Users].[LastActivityDate]

  FROM [dbo].[aspnet_Users]

 

GO

/****** Object:  View [dbo].[vw_aspnet_UsersInRoles]   ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

 

  CREATE VIEW [dbo].[vw_aspnet_UsersInRoles]

  AS SELECT [dbo].[aspnet_UsersInRoles].[UserId], [dbo].[aspnet_UsersInRoles].[RoleId]

  FROM [dbo].[aspnet_UsersInRoles]

 

GO

/****** Object:  View [dbo].[vw_aspnet_WebPartState_Paths]  ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

 

  CREATE VIEW [dbo].[vw_aspnet_WebPartState_Paths]

  AS SELECT [dbo].[aspnet_Paths].[ApplicationId], [dbo].[aspnet_Paths].[PathId], [dbo].[aspnet_Paths].[Path], [dbo].[aspnet_Paths].[LoweredPath]

  FROM [dbo].[aspnet_Paths]

 

GO

/****** Object:  View [dbo].[vw_aspnet_WebPartState_Shared]    ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

 

  CREATE VIEW [dbo].[vw_aspnet_WebPartState_Shared]

  AS SELECT [dbo].[aspnet_PersonalizationAllUsers].[PathId], [DataSize]=DATALENGTH([dbo].[aspnet_PersonalizationAllUsers].[PageSettings]), [dbo].[aspnet_PersonalizationAllUsers].[LastUpdatedDate]

  FROM [dbo].[aspnet_PersonalizationAllUsers]

 

GO

/****** Object:  View [dbo].[vw_aspnet_WebPartState_User]    ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

 

  CREATE VIEW [dbo].[vw_aspnet_WebPartState_User]

  AS SELECT [dbo].[aspnet_PersonalizationPerUser].[PathId], [dbo].[aspnet_PersonalizationPerUser].[UserId], [DataSize]=DATALENGTH([dbo].[aspnet_PersonalizationPerUser].[PageSettings]), [dbo].[aspnet_PersonalizationPerUser].[LastUpdatedDate]

  FROM [dbo].[aspnet_PersonalizationPerUser]

 

GO

SET ANSI_PADDING ON

 

GO

/****** Object:  Index [aspnet_Applications_Index]    ******/

CREATE CLUSTERED INDEX [aspnet_Applications_Index] ON [dbo].[aspnet_Applications]

(

       [LoweredApplicationName] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

SET ANSI_PADDING ON

 

GO

/****** Object:  Index [aspnet_Membership_index]    ******/

CREATE CLUSTERED INDEX [aspnet_Membership_index] ON [dbo].[aspnet_Membership]

(

       [ApplicationId] ASC,

       [LoweredEmail] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

SET ANSI_PADDING ON

 

GO

/****** Object:  Index [aspnet_Paths_index]    ******/

CREATE UNIQUE CLUSTERED INDEX [aspnet_Paths_index] ON [dbo].[aspnet_Paths]

(

       [ApplicationId] ASC,

       [LoweredPath] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

/****** Object:  Index [aspnet_PersonalizationPerUser_index1]    ******/

CREATE UNIQUE CLUSTERED INDEX [aspnet_PersonalizationPerUser_index1] ON [dbo].[aspnet_PersonalizationPerUser]

(

       [PathId] ASC,

       [UserId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

SET ANSI_PADDING ON

 

GO

/****** Object:  Index [aspnet_Roles_index1]    ******/

CREATE UNIQUE CLUSTERED INDEX [aspnet_Roles_index1] ON [dbo].[aspnet_Roles]

(

       [ApplicationId] ASC,

       [LoweredRoleName] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

SET ANSI_PADDING ON

 

GO

/****** Object:  Index [aspnet_Users_Index]    ******/

CREATE UNIQUE CLUSTERED INDEX [aspnet_Users_Index] ON [dbo].[aspnet_Users]

(

       [ApplicationId] ASC,

       [LoweredUserName] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

/****** Object:  Index [aspnet_PersonalizationPerUser_ncindex2]    ******/

CREATE UNIQUE NONCLUSTERED INDEX [aspnet_PersonalizationPerUser_ncindex2] ON [dbo].[aspnet_PersonalizationPerUser]

(

       [UserId] ASC,

       [PathId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

/****** Object:  Index [aspnet_Users_Index2]   ******/

CREATE NONCLUSTERED INDEX [aspnet_Users_Index2] ON [dbo].[aspnet_Users]

(

       [ApplicationId] ASC,

       [LastActivityDate] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

/****** Object:  Index [aspnet_UsersInRoles_index]    ******/

CREATE NONCLUSTERED INDEX [aspnet_UsersInRoles_index] ON [dbo].[aspnet_UsersInRoles]

(

       [RoleId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

ALTER TABLE [dbo].[aspnet_Applications] ADD  DEFAULT (newid()) FOR [ApplicationId]

GO

ALTER TABLE [dbo].[aspnet_Membership] ADD  DEFAULT ((0)) FOR [PasswordFormat]

GO

ALTER TABLE [dbo].[aspnet_Paths] ADD  DEFAULT (newid()) FOR [PathId]

GO

ALTER TABLE [dbo].[aspnet_PersonalizationPerUser] ADD  DEFAULT (newid()) FOR [Id]

GO

ALTER TABLE [dbo].[aspnet_Roles] ADD  DEFAULT (newid()) FOR [RoleId]

GO

ALTER TABLE [dbo].[aspnet_Users] ADD  DEFAULT (newid()) FOR [UserId]

GO

ALTER TABLE [dbo].[aspnet_Users] ADD  DEFAULT (NULL) FOR [MobileAlias]

GO

ALTER TABLE [dbo].[aspnet_Users] ADD  DEFAULT ((0)) FOR [IsAnonymous]

GO

ALTER TABLE [dbo].[Bus] ADD  CONSTRAINT [DF_Bus_condition]  DEFAULT ('Good') FOR [condition]

GO

ALTER TABLE [dbo].[DriverDuty] ADD  CONSTRAINT [DF_DriverDuty_shiftcompleted]  DEFAULT ((0)) FOR [shiftcompleted]

GO

ALTER TABLE [dbo].[EmployeeContract] ADD  CONSTRAINT [DF_EmployeeContract_dateemployed]  DEFAULT (getdate()) FOR [dateemployed]

GO

ALTER TABLE [dbo].[EmployeeContract] ADD  CONSTRAINT [DF_EmployeeContract_holidaydaysallowed]  DEFAULT ((0)) FOR [holidaydaysallowed]

GO

ALTER TABLE [dbo].[HolidayTaken] ADD  CONSTRAINT [DF_HolidayTaken_approved]  DEFAULT ((0)) FOR [approved]

GO

ALTER TABLE [dbo].[Message] ADD  CONSTRAINT [DF_Message_created_date]  DEFAULT (getdate()) FOR [created_date]

GO

ALTER TABLE [dbo].[aspnet_Membership]  WITH CHECK ADD FOREIGN KEY([ApplicationId])

REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])

GO

ALTER TABLE [dbo].[aspnet_Membership]  WITH CHECK ADD FOREIGN KEY([UserId])

REFERENCES [dbo].[aspnet_Users] ([UserId])

GO

ALTER TABLE [dbo].[aspnet_Paths]  WITH CHECK ADD FOREIGN KEY([ApplicationId])

REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])

GO

ALTER TABLE [dbo].[aspnet_PersonalizationAllUsers]  WITH CHECK ADD FOREIGN KEY([PathId])

REFERENCES [dbo].[aspnet_Paths] ([PathId])

GO

ALTER TABLE [dbo].[aspnet_PersonalizationPerUser]  WITH CHECK ADD FOREIGN KEY([PathId])

REFERENCES [dbo].[aspnet_Paths] ([PathId])

GO

ALTER TABLE [dbo].[aspnet_PersonalizationPerUser]  WITH CHECK ADD FOREIGN KEY([UserId])

REFERENCES [dbo].[aspnet_Users] ([UserId])

GO

ALTER TABLE [dbo].[aspnet_Profile]  WITH CHECK ADD FOREIGN KEY([UserId])

REFERENCES [dbo].[aspnet_Users] ([UserId])

GO

ALTER TABLE [dbo].[aspnet_Roles]  WITH CHECK ADD FOREIGN KEY([ApplicationId])

REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])

GO

ALTER TABLE [dbo].[aspnet_Users]  WITH CHECK ADD FOREIGN KEY([ApplicationId])

REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])

GO

ALTER TABLE [dbo].[aspnet_UsersInRoles]  WITH CHECK ADD FOREIGN KEY([RoleId])

REFERENCES [dbo].[aspnet_Roles] ([RoleId])

GO

ALTER TABLE [dbo].[aspnet_UsersInRoles]  WITH CHECK ADD FOREIGN KEY([UserId])

REFERENCES [dbo].[aspnet_Users] ([UserId])

GO

ALTER TABLE [dbo].[Memberships]  WITH CHECK ADD  CONSTRAINT [MembershipApplication] FOREIGN KEY([ApplicationId])

REFERENCES [dbo].[Applications] ([ApplicationId])

GO

ALTER TABLE [dbo].[Memberships] CHECK CONSTRAINT [MembershipApplication]

GO

ALTER TABLE [dbo].[Memberships]  WITH CHECK ADD  CONSTRAINT [MembershipUser] FOREIGN KEY([UserId])

REFERENCES [dbo].[Users] ([UserId])

GO

ALTER TABLE [dbo].[Memberships] CHECK CONSTRAINT [MembershipUser]

GO

ALTER TABLE [dbo].[Profiles]  WITH CHECK ADD  CONSTRAINT [UserProfile] FOREIGN KEY([UserId])

REFERENCES [dbo].[Users] ([UserId])

GO

ALTER TABLE [dbo].[Profiles] CHECK CONSTRAINT [UserProfile]

GO

ALTER TABLE [dbo].[Roles]  WITH CHECK ADD  CONSTRAINT [RoleApplication] FOREIGN KEY([ApplicationId])

REFERENCES [dbo].[Applications] ([ApplicationId])

GO

ALTER TABLE [dbo].[Roles] CHECK CONSTRAINT [RoleApplication]

GO

ALTER TABLE [dbo].[Users]  WITH CHECK ADD  CONSTRAINT [UserApplication] FOREIGN KEY([ApplicationId])

REFERENCES [dbo].[Applications] ([ApplicationId])

GO

ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [UserApplication]

GO

ALTER TABLE [dbo].[UsersInRoles]  WITH CHECK ADD  CONSTRAINT [UsersInRoleRole] FOREIGN KEY([RoleId])

REFERENCES [dbo].[Roles] ([RoleId])

GO

ALTER TABLE [dbo].[UsersInRoles] CHECK CONSTRAINT [UsersInRoleRole]

GO

ALTER TABLE [dbo].[UsersInRoles]  WITH CHECK ADD  CONSTRAINT [UsersInRoleUser] FOREIGN KEY([UserId])

REFERENCES [dbo].[Users] ([UserId])

GO

ALTER TABLE [dbo].[UsersInRoles] CHECK CONSTRAINT [UsersInRoleUser]

GO

ALTER TABLE [dbo].[UsersOpenAuthAccounts]  WITH CHECK ADD  CONSTRAINT [OpenAuthAccount_UserData] FOREIGN KEY([ApplicationName], [MembershipUserName])

REFERENCES [dbo].[UsersOpenAuthData] ([ApplicationName], [MembershipUserName])

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[UsersOpenAuthAccounts] CHECK CONSTRAINT [OpenAuthAccount_UserData]

GO

USE [master]

GO

ALTER DATABASE [HectorDb] SET  READ_WRITE

GO

 

 

Share this page...

Twitter icon
Facebook icon
Google icon
StumbleUpon icon
Del.icio.us icon
Digg icon
LinkedIn icon
MySpace icon
Newsvine icon
Pinterest icon
Reddit icon
Technorati icon
Yahoo! icon
e-mail icon