SQLize | PHPize | SQLtest

-- DDL CREATE DATABASE Company; GO USE Company; GO CREATE TABLE dbo.Employee ( EmployeeID int NOT NULL IDENTITY, SSN char(11) NOT NULL, FullName nvarchar(100) NOT NULL CHECK (LEN(TRIM(FullName)) > 0), EMail nvarchar(150), HireDate date NOT NULL DEFAULT GETDATE(), RoomID smallint, DepartmentID tinyint NOT NULL, CONSTRAINT CK_Employee_SSN CHECK (SSN LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'), CONSTRAINT UQ_Employee_SSN UNIQUE (SSN), CONSTRAINT PK_Employee PRIMARY KEY (EmployeeID) ); CREATE TABLE dbo.EmployeeArchive -- for some experiments ! ( SSN char(11) NOT NULL, FullName nvarchar(100) NOT NULL, HireDate date, CONSTRAINT CK_EmployeeArchive_SSN CHECK (SSN LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]') ); CREATE TABLE dbo.Room ( RoomID smallint NOT NULL IDENTITY, RoomNumber smallint NOT NULL, BuildingAddress nvarchar(200) NOT NULL, [Floor] tinyint, NumberOfPlaces tinyint NOT NULL DEFAULT 10, CONSTRAINT CK_Room_All check (RoomNumber > 0 AND LEN(TRIM(BuildingAddress)) > 0 AND NumberOfPlaces >= 0), CONSTRAINT UQ_Room_RoomNumber_BuildingAddress UNIQUE (RoomNumber, BuildingAddress), CONSTRAINT PK_Room PRIMARY KEY (RoomID) ); CREATE TABLE dbo.Department ( DepartmentID tinyint NOT NULL IDENTITY, DepName nvarchar(60) NOT NULL CHECK (LEN(TRIM(DepName)) > 0), DepManager int NOT NULL UNIQUE, CONSTRAINT UQ_Department_DepName UNIQUE (DepName), CONSTRAINT PK_Department PRIMARY KEY (DepartmentID) ); CREATE TABLE dbo.Project ( ProjectID int NOT NULL IDENTITY, PrjName nvarchar(60) NOT NULL CHECK (LEN(TRIM(PrjName)) > 0), StartDate date NOT NULL, PrjManager int NOT NULL, Customer nvarchar(150), CONSTRAINT UQ_Project_PrjName UNIQUE (PrjName), CONSTRAINT PK_Project PRIMARY KEY (ProjectID) ); CREATE TABLE dbo.EmployeeProjectLink ( EmployeeID int NOT NULL, ProjectID int NOT NULL, HoursPerDay tinyint NOT NULL CHECK (HoursPerDay > 0 AND HoursPerDay < 12), CONSTRAINT PK_EmployeeProjectLink PRIMARY KEY (EmployeeID, ProjectID) ); CREATE TABLE dbo.EmployeeAddition ( EmployeeID int NOT NULL, Photo nvarchar(200), -- it's just a path to a file ! [Address] nvarchar(200), CONSTRAINT PK_EmployeeAddition PRIMARY KEY (EmployeeID) ); ALTER TABLE dbo.Employee ADD CONSTRAINT FK_Employee_Room FOREIGN KEY (RoomID) REFERENCES dbo.Room (RoomID); ALTER TABLE dbo.Employee ADD CONSTRAINT FK_Employee_Department FOREIGN KEY (DepartmentID) REFERENCES dbo.Department (DepartmentID); ALTER TABLE dbo.Department ADD CONSTRAINT FK_Department_Employee FOREIGN KEY (DepManager) REFERENCES dbo.Employee (EmployeeID); ALTER TABLE dbo.Project ADD CONSTRAINT FK_Project_Employee FOREIGN KEY (PrjManager) REFERENCES dbo.Employee (EmployeeID); ALTER TABLE dbo.EmployeeProjectLink ADD CONSTRAINT FK_EmployeeProjectLink_Employee FOREIGN KEY (EmployeeID) REFERENCES dbo.Employee (EmployeeID); ALTER TABLE dbo.EmployeeProjectLink ADD CONSTRAINT FK_EmployeeProjectLink_Project FOREIGN KEY (ProjectID) REFERENCES dbo.Project (ProjectID); ALTER TABLE dbo.EmployeeAddition ADD CONSTRAINT FK_EmployeeAddition_Employee FOREIGN KEY (EmployeeID) REFERENCES dbo.Employee (EmployeeID); GO -- DML USE Company; GO -- insertion order is important !!! INSERT INTO dbo.Room (RoomNumber, BuildingAddress) VALUES (200, N'1329 Carroll Avenue'); INSERT INTO dbo.Room (RoomNumber, BuildingAddress, [Floor]) VALUES (300, N'1329 Carroll Avenue', 1), (400, N'1329 Carroll Avenue', 2); INSERT INTO dbo.Room (RoomNumber, BuildingAddress, NumberOfPlaces) VALUES (200, N'1344 Carroll Avenue', 0), (300, N'1344 Carroll Avenue', 0), (400, N'1344 Carroll Avenue', 0); ALTER TABLE dbo.Employee NOCHECK CONSTRAINT FK_Employee_Department; -- !!! INSERT INTO dbo.Employee (SSN, FullName, EMail, HireDate, RoomID, DepartmentID) VALUES ('567-07-3072', N'Rob Kein', N'KeinR@Comp.com', '2020-10-25', NULL, 1), ('566-85-8867', N'Ted King', N'KingT@Comp.com', '2019-05-15', 2, 1), ('609-15-6922', N'Eric Dane', N'DaneE@Comp.com', '2020-05-10', 1, 2), ('434-18-4851', N'Kerr Smith', N'SmithK@Comp.com', '2020-10-18', 1, 2), ('541-36-7076', N'Holly Combs', NULL, '2020-10-28', 3, 3), ('488-24-9686', N'Rose McGowan', NULL, '2020-11-03', 3, 3), ('003-40-1966', N'Bred Kern', NULL, '2021-09-23', NULL, 3); INSERT INTO dbo.Department (DepName, DepManager) VALUES (N'JAVA', 1), (N'MOBILE', 4), (N'.NET', 7); ALTER TABLE dbo.Employee CHECK CONSTRAINT FK_Employee_Department; -- !!! INSERT INTO dbo.Project (PrjName, StartDate, PrjManager, Customer) VALUES (N'WMAX', '2019-06-01', 2, N'NordWay'), (N'GWEB', '2020-11-01', 1, N'Mercury'), (N'RTEST', '2020-11-01', 5, N'Company'), (N'QNEXT', '2020-06-01', 3, N'NordWay'), (N'UNITY', '2020-11-01', 4, N'Mercury'), (N'NEWPRJ', '2021-01-01', 4, NULL); INSERT INTO dbo.EmployeeAddition (EmployeeID, Photo, [Address]) VALUES (1, NULL, N'909-1/2 E 49th St LA, CA, 90011'), (5, N'D:\PHOTO\Employee_5.jpg', N'7609 Mckinley Ave LA, CA, 90001'), (7, N'D:\PHOTO\Employee_7.jpg', NULL); INSERT INTO dbo.EmployeeProjectLink (EmployeeID, ProjectID, HoursPerDay) VALUES (1, 1, 6), (1, 2, 2), (2, 1, 4), (2, 3, 4), (3, 4, 8), (4, 4, 2), (4, 5, 2), (5, 1, 4), (5, 3, 6); INSERT INTO dbo.EmployeeArchive VALUES ('567-07-3072', N'Rob Kein', '2020-10-25'); GO