fhyi3E9K

1USE [master]
2GO
3
4DROP DATABASE IF EXISTS Transport
5
6
7CREATE DATABASE Transport
8
9USE [Transport]
10GO
11
12DROP TABLE if exists Course
13DROP TABLE if exists Clients
14DROP TABLE if exists Locations
15DROP TABLE if exists Materials
16DROP TABLE if exists Course_Type
17DROP TABLE if exists Semi_Trailers
18DROP TABLE if exists Drivers
19DROP TABLE if exists Trucks
20DROP TABLE if exists Package_Type
21DROP TABLE if exists Semi_Trailer_Type
22
23
24CREATE TABLE Clients
25(
26	Client_Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
27	Company_Name NVARCHAR(255) NOT NULL,
28	Contact_Number VARCHAR(255) NOT NULL,
29	Fax VARCHAR(255) NULL
30)
31GO
32
33CREATE TABLE Package_Type
34(
35	[Type] NVARCHAR(6) PRIMARY KEY NOT NULL
36)
37GO
38
39CREATE TABLE Semi_Trailer_Type
40(
41	[Type] NVARCHAR(12) PRIMARY KEY NOT NULL
42)
43GO
44
45CREATE TABLE Course_Type
46(
47	[Type] NVARCHAR(9) PRIMARY KEY NOT NULL
48)
49GO
50
51CREATE TABLE Semi_Trailers
52(
53	Semi_Trailer_Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
54	Semi_Trailer_Type NVARCHAR(12) FOREIGN KEY REFERENCES Semi_Trailer_Type([Type]) NOT NULL,
55	Max_Amount INT NOT NULL,
56	CHECK (Max_Amount>0)
57)
58GO
59CREATE TABLE Materials
60(
61	Material_Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
62	[Name] NVARCHAR(255) NOT NULL,
63	Package_Type NVARCHAR(6) FOREIGN KEY REFERENCES Package_Type([Type]) NOT NULL
64)
65GO
66
67CREATE TABLE Drivers
68(
69	Driver_Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
70	[Name] NVARCHAR(255) NOT NULL,
71	[LastName] NVARCHAR(255) NOT NULL,
72	Birth_Date DATETIME NOT NULL,
73	Employment_Date DATETIME NOT NULL,
74	Country NVARCHAR(255) NOT NULL,
75	City NVARCHAR(255) NOT NULL,
76	Street NVARCHAR(255) NOT NULL,
77	Number_Of_The_Building NVARCHAR(255) NOT NULL
78)
79GO
80
81CREATE TABLE Trucks
82(
83	Truck_Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
84	Brand NVARCHAR(255),
85	Max_Weight VARCHAR(255),
86	Semi_Trailer INT FOREIGN KEY REFERENCES Semi_Trailers(Semi_Trailer_Id) NOT NULL,
87	Driver INT FOREIGN KEY REFERENCES Drivers(Driver_Id) NOT NULL
88)
89GO
90
91CREATE TABLE Locations
92(
93	Location_Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
94	Country NVARCHAR(255) NOT NULL,
95	City NVARCHAR(255) NOT NULL,
96	Street NVARCHAR(255) NOT NULL,
97	Number_Of_The_Building VARCHAR(255) NOT NULL,
98	Number_Of_The_Flat VARCHAR(255) NOT NULL
99)
100GO
101
102CREATE TABLE Course
103(
104	Course_Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
105	Course_Type NVARCHAR(9) FOREIGN KEY REFERENCES Course_Type([Type]) NOT NULL,
106	Client INT FOREIGN KEY REFERENCES Clients([Client_Id]) NOT NULL,
107	Material INT FOREIGN KEY REFERENCES Materials(Material_Id) NOT NULL,
108	Trucks INT FOREIGN KEY REFERENCES Trucks(Truck_Id) NOT NULL,
109	[Location] INT FOREIGN KEY REFERENCES Locations(Location_Id) NOT NULL
110)
111GO