HDhyZ7fR

1USE [master]
2GO
3
4DROP DATABASE IF EXISTS [Structures]
5GO
6
7CREATE DATABASE [Structures]
8GO
9
10USE [Structures]
11GO
12
13IF OBJECT_ID('Expertises', 'U') IS NOT NULL
14DROP TABLE [Expertises]
15GO
16
17IF OBJECT_ID('Employees', 'U') IS NOT NULL
18DROP TABLE [Employees]
19GO
20
21IF OBJECT_ID('Expertises', 'U') IS NULL
22CREATE TABLE [Expertises]
23(
24	[ID] INT IDENTITY(1,1),
25	[Name] VARCHAR(15) NOT NULL,
26	CONSTRAINT [ExpertiseID_PK] PRIMARY KEY ([ID]),
27	CONSTRAINT [ExpertiseName_Unique] UNIQUE ([Name])
28)
29GO
30
31IF OBJECT_ID('Employees', 'U') IS NULL
32CREATE TABLE [Employees]
33(
34	[ID] INT IDENTITY(1,1),
35	[First] VARCHAR(15) NOT NULL,
36	[Last] VARCHAR(15) NOT NULL,
37	[City] VARCHAR(15) NOT NULL,
38	[Birthday] DATE NOT NULL,
39	[Phone] VARCHAR(15) NOT NULL,
40	[ExpertiseID] INT NOT NULL,
41	[ManagerID] INT NOT NULL,
42	CONSTRAINT [EmployeesID_PK] PRIMARY KEY ([ID]),
43	CONSTRAINT [EmployeesPhone_Validation] CHECK ([Phone] LIKE '[0-9]{3}-[0-9]{7}'),
44	CONSTRAINT [EmployeesExpertise_FK] FOREIGN KEY ([ExpertiseID]) REFERENCES [Expertises] ([ID]),
45	CONSTRAINT [EmployeesManager_FK] FOREIGN KEY ([ManagerID]) REFERENCES [Employees] ([ID])
46)
47GO