Database

CREATE TABLE dbo.CustomerTable (
CustomerId INT NOT NULL,
Surname NVARCHAR (50) NOT NULL,
FirstName NVARCHAR (50) NOT NULL,
MiddleName NVARCHAR (50) NULL,
Street NVARCHAR (50) NULL,
City NVARCHAR (50) NULL,
State NVARCHAR (50) NULL,
ZipCode NCHAR (10) NULL,
Phone NCHAR (10) NOT NULL,
BirthDate DATE NOT NULL,
EmailAddress NVARCHAR (50) NULL,
EmergencyContact NVARCHAR (50) NOT NULL,
ECPhone NCHAR (10) NOT NULL,
PRIMARY KEY CLUSTERED (CustomerId ASC)
);

CREATE TABLE dbo.DepartmentIDTable (
DepartmentId INT NOT NULL,
DepartmentName VARCHAR (50) NULL,
DepartmentHeadID INT NOT NULL,
PRIMARY KEY CLUSTERED (DepartmentId ASC),
CONSTRAINT FK_DepartmentIDTable_ToTable FOREIGN KEY (DepartmentHeadID) REFERENCES dbo.EmployeeTable (EmployeeID)
);

CREATE TABLE dbo.EmployeeTable (
EmployeeID INT NOT NULL,
Surname NVARCHAR (50) NULL,
FirstName NVARCHAR (50) NULL,
MiddleName NVARCHAR (50) NULL,
DepartmentID INT NOT NULL,
Street NVARCHAR (50) NULL,
City NVARCHAR (50) NULL,
State NVARCHAR (50) NULL,
Country NVARCHAR (50) NULL,
ZipCode NCHAR (10) NULL,
CellPhone NCHAR (10) NULL,
HomePhone NCHAR (10) NULL,
Status NCHAR (10) NULL,
SocialSecurityNumber NCHAR (10) NULL,
Salary MONEY NULL,
StartDate DATE NULL,
TermedDate DATE NULL,
BirthDate DATE NULL,
Gender NCHAR (10) NULL,
PRIMARY KEY CLUSTERED (EmployeeID ASC),
CONSTRAINT FK_EmployeeTable_ToTable FOREIGN KEY (DepartmentID) REFERENCES dbo.DepartmentIDTable (DepartmentId)
);

CREATE TABLE dbo.FinancialCodesTable (
FinancialCodeId INT NOT NULL,
FinancialType NVARCHAR (50) NULL,
PRIMARY KEY CLUSTERED (FinancialCodeId ASC)
);

CREATE TABLE dbo.Lesson (
Date INT NOT NULL,
Time TIME (7) NOT NULL,
ID NCHAR (10) NOT NULL,
PRIMARY KEY CLUSTERED (Date ASC)
);

CREATE TABLE dbo.LockerTable (
LockerId INT NOT NULL,
LockerAvailable NCHAR (10) NULL,
Lockerprice MONEY NULL,
LockerType NCHAR (10) NULL,
PRIMARY KEY CLUSTERED (LockerId ASC)
);

CREATE TABLE dbo.Login (
UserName VARCHAR (10) NOT NULL,
Password VARCHAR (10) NOT NULL,
ID INT NOT NULL,
SecurityID INT NULL,
CustomerID INT NULL,
EmployeeID INT NULL,
PRIMARY KEY CLUSTERED (UserName ASC),
CONSTRAINT FK_Login_ToTable FOREIGN KEY (CustomerId) REFERENCES dbo.CustomerTable (CustomerId),
CONSTRAINT FK_Login_ToTable2 FOREIGN KEY (EmployeeID) REFERENCES dbo.EmployeeTable (EmployeeID),
);

GO
CREATE UNIQUE NONCLUSTERED INDEX IX_Login_Column
ON dbo.Login(UserName ASC);



CREATE TABLE dbo.MembershipCodesTable (
MembeshipId INT NOT NULL,
MembershipType NVARCHAR (50) NOT NULL,
UnitPrice MONEY NOT NULL,
PRIMARY KEY CLUSTERED (MembeshipId ASC)
);

CREATE TABLE dbo.SalesOrderTable (
SalesOrdersId INT NOT NULL,
CustomerID INT NOT NULL,
PurchaseDate DATE NOT NULL,
FinancialCode INT NOT NULL,
EmployeeID INT NOT NULL,
PRIMARY KEY CLUSTERED (SalesOrdersId ASC),
CONSTRAINT FK_SalesOrderTable_ToTable FOREIGN KEY (CustomerID) REFERENCES dbo.CustomerTable (CustomerId),
CONSTRAINT FK_SalesOrderTable_ToTable_2 FOREIGN KEY (EmployeeID) REFERENCES dbo.EmployeeTable (EmployeeID),
CONSTRAINT FK_SalesOrderTable_ToTable_1 FOREIGN KEY (FinancialCode) REFERENCES dbo.FinancialCodesTable (FinancialCodeId)
);

CREATE TABLE dbo.TransactionTable (
SalesOrdersId INT NOT NULL,
LineID INT NOT NULL,
ProductID INT NOT NULL,
Quantity INT NOT NULL,
PRIMARY KEY CLUSTERED (LineID ASC),
CONSTRAINT FK_TransactionTable_ToTable FOREIGN KEY (SalesOrdersId) REFERENCES dbo.SalesOrderTable (SalesOrdersId)
);

Last edited Apr 4, 2013 at 2:56 AM by dkelyg3215, version 5

Comments

No comments yet.