ScriptDatabaseAndDiagram/Script.sql

create table "Roles"(
RoleID int not null generated always as identity primary key,
Title varchar(30) not null
);

create table "Trade networks"(
TradeID int not null generated always as identity primary key,
Title varchar(30) not null
);

create table "Acceptance"(
AcceptanceID int not null generated always as identity primary key,
Title varchar(30) not null
);

create table "Employees"(
EmployeesID int not null generated always as identity primary key,
Surname varchar(30) not null,
"name" varchar(30) not null,
Patronymic varchar(30) not null,
PhoneNumber varchar(30) not null,
Email varchar(30) not null,
"role" int not null,
"password" varchar(30) not null
);

create table "Outlets"(
OutletID int not null generated always as identity primary key,
Address varchar(100) not null,
"location" varchar(100) not null,
"Trade networks" int not null
);

create table "Projects"(
ProjectID int not null generated always as identity primary key,
Title varchar(30) not null,
NumOfVisitsPerWeek int not null
);

create table "Products"(
ProductID int not null generated always as identity primary key,
Title varchar(30) not null,
Project int not null
);

create table "Visits"(
VisitID int not null generated always as identity primary key,
Outlet int not null,
Project int not null,
VisitDate date not null,
VisitTime time not null,
Accepted int not null,
MerchComment varchar(200) not null
);

create table "Product reports"(
ProductReportID int not null generated always as identity primary key,
Product int not null,
Price int not null,
PriceToTheCard int not null,
ActualBalance int not null,
VirtualBalance int not null,
Visit int not null
);

create table "Projects and outlets"(
ProjectID int not null,
OutletID int not null
);

create table "Projects and employees"(
EmployeeID int not null,
OutletID int not null
);

alter table "Employees"  
add foreign key (Role) references "Roles"(roleiD)

alter table "Outlets"  
add foreign key ("Trade networks") references "Trade networks"(tradeiD)

alter table "Visits"  
add foreign key (accepted) references "Acceptance"(acceptanceid)

alter table "Products" 
add foreign key (project) references "Projects"(projectid)

alter table "Product reports" 
add foreign key (product) references "Products"(productid)

alter table "Product reports" 
add foreign key (visit) references "Visits"(visitid)

alter table "Projects and outlets" 
add foreign key (projectid) references "Projects"(projectid)

alter table "Projects and outlets" 
add foreign key (outletid) references "Outlets"(outletid)

alter table "Projects and employees"
rename column outletid to projectid

alter table "Projects and employees"  
add foreign key (projectid) references "Projects"(projectid)

alter table "Projects and employees"  
add foreign key (employeeid) references "Employees"(employeesid)

alter table "Visits" 
add foreign key (project) references "Projects"(projectid)

alter table "Visits"
add foreign key (outlet) references "Outlets"(outletid)