CompanyDatabaseDesign/DatabaseScript.sql at main · bahadirverir/CompanyDatabaseDesign · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
CREATE SCHEMA `CompanyDB` ;

CREATE TABLE `CompanyDB`.`Employees` (
  `EmployeeID` INT NOT NULL AUTO_INCREMENT,
  `Name` VARCHAR(45) NOT NULL,
  `Surname` VARCHAR(45) NOT NULL,
  `Age` INT NOT NULL,
  `Gender` VARCHAR(45) NOT NULL,
  `Salary` INT NOT NULL,
  `Contact` VARCHAR(45) NOT NULL,
  `Residence Address` VARCHAR(45) NOT NULL,
  `Education Degree` VARCHAR(45) NOT NULL,
  `Experience` INT NOT NULL,
  PRIMARY KEY (`EmployeeID`));

CREATE TABLE `CompanyDB`.`Projects` (
  `ProjectID` INT NOT NULL,
  `Project Name` VARCHAR(45) NOT NULL,
  `Start Date` VARCHAR(45) NOT NULL,
  `Time` INT NOT NULL,
  `Status` VARCHAR(45) NOT NULL,
  `Budget` INT NOT NULL,
  PRIMARY KEY (`ProjectID`));

  CREATE TABLE `CompanyDB`.`Offices` (
  `OfficeID` INT NOT NULL,
  `Location` VARCHAR(45) NOT NULL,
  `Office Status` VARCHAR(45) NOT NULL,
  `Activity Time` VARCHAR(45) NOT NULL,
  `Opening Time` VARCHAR(45) NOT NULL,
  `Closing Time` VARCHAR(45) NOT NULL,
  `Contact` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`OfficeID`));

CREATE TABLE `CompanyDB`.`Departments` (
  `DepartmentID` INT NOT NULL,
  `Department Name` VARCHAR(45) NOT NULL,
  `Employee Count` INT NOT NULL,
  PRIMARY KEY (`DepartmentID`));

  CREATE TABLE `CompanyDB`.`Customers` (
  `CustomerID` INT NOT NULL,
  `Customer Name` VARCHAR(45) NOT NULL,
  `Customer Surname` VARCHAR(45) NOT NULL,
  `Customer Address` VARCHAR(45) NOT NULL,
  `Card Number` INT NOT NULL,
  `Card Type` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`CustomerID`));

  CREATE TABLE `CompanyDB`.`Orders` (
  `OrderID` INT NOT NULL,
  `ProductID` INT NOT NULL,
  `CustomerID` INT NOT NULL,
  `Order Date` VARCHAR(45) NOT NULL,
  `Total Amount` INT NOT NULL,
  `Order Status` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`OrderID`));

CREATE TABLE `CompanyDB`.`Products` (
  `ProductID` INT NOT NULL,
  `Product Name` VARCHAR(45) NOT NULL,
  `Price` INT NOT NULL,
  `Product Type` VARCHAR(45) NOT NULL,
  `Warranty Period` INT NOT NULL,
  `Warranty Status` VARCHAR(45) NOT NULL,
  `Sales Status` VARCHAR(45) NOT NULL,
  `Sold Quantity` INT NOT NULL,
  PRIMARY KEY (`ProductID`));

ALTER TABLE `CompanyDB`.`Employees`
ADD COLUMN `DepartmentID` INT NOT NULL AFTER `Experience`,
ADD COLUMN `OfficeID` INT NOT NULL AFTER `DepartmentID`,
ADD COLUMN `ProjectID` INT NOT NULL AFTER `OfficeID`;

ALTER TABLE `CompanyDB`.`Employees`
MODIFY COLUMN `ProjectID` INT NULL;

ALTER TABLE `CompanyDB`.`Employees`
ADD CONSTRAINT `FK_Employees_Department`
  FOREIGN KEY (`DepartmentID`)
  REFERENCES `CompanyDB`.`Departments` (`DepartmentID`)
  ON DELETE CASCADE
  ON UPDATE CASCADE,
ADD CONSTRAINT `FK_Employees_Office`
  FOREIGN KEY (`OfficeID`)
  REFERENCES `CompanyDB`.`Offices` (`OfficeID`)
  ON DELETE CASCADE
  ON UPDATE CASCADE,
ADD CONSTRAINT `FK_Employees_Project`
  FOREIGN KEY (`ProjectID`)
  REFERENCES `CompanyDB`.`Projects` (`ProjectID`)
  ON DELETE SET NULL
  ON UPDATE CASCADE;

  ALTER TABLE `CompanyDB`.`Orders`
  ADD CONSTRAINT `FK_Orders_Customers`
  FOREIGN KEY (`CustomerID`)
  REFERENCES `CompanyDB`.`Customers` (`CustomerID`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

  CREATE TABLE `CompanyDB`.`Customer-Product` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `CustomerID` INT NOT NULL,
  `ProductID` INT NOT NULL,
  PRIMARY KEY (`ID`),
  CONSTRAINT `FK_Customer` FOREIGN KEY (`CustomerID`)
  REFERENCES `CompanyDB`.`Customers`(`CustomerID`)
  ON DELETE CASCADE
  ON UPDATE CASCADE,
  CONSTRAINT `FK_Product` FOREIGN KEY (`ProductID`)
  REFERENCES `CompanyDB`.`Products`(`ProductID`)
  ON DELETE CASCADE
  ON UPDATE CASCADE);

CREATE TABLE `CompanyDB`.`Department-Customer` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `DepartmentID` INT NOT NULL,
  `CustomerID` INT NOT NULL,
  PRIMARY KEY (`ID`),
  CONSTRAINT `FK_Department-Customer_DepartmentID`
    FOREIGN KEY (`DepartmentID`)
    REFERENCES `CompanyDB`.`Departments` (`DepartmentID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `FK_Department-Customer_CustomerID`
    FOREIGN KEY (`CustomerID`)
    REFERENCES `CompanyDB`.`Customers` (`CustomerID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE);