Bnc9fkSC

1#
2# TABLE STRUCTURE FOR: Floor
3#
4
5DROP TABLE IF EXISTS `Floor`;
6
7CREATE TABLE `Floor` (
8  `ID` int(9)  NOT NULL AUTO_INCREMENT,
9  PRIMARY KEY (`ID`)
10);
11
12
13#
14# TABLE STRUCTURE FOR: shopOwner
15#
16
17DROP TABLE IF EXISTS `shopOwner`;
18
19CREATE TABLE `shopOwner` (
20  `ID` int(9)  NOT NULL AUTO_INCREMENT,
21  `SSN` varchar(255) NOT NULL,
22  `Email` varchar(100) NOT NULL,
23  `Password` varchar(255) NOT NULL,
24  `Phone` varchar(255) NOT NULL,
25  `First_Name` varchar(100) NOT NULL,
26  `Second_Name` varchar(100) NOT NULL,
27  PRIMARY KEY (`ID`)
28);
29
30#
31# TABLE STRUCTURE FOR: Shop
32#
33
34DROP TABLE IF EXISTS `Shop`;
35
36CREATE TABLE `Shop` (
37  `ID` int(9)  NOT NULL AUTO_INCREMENT,
38  `Floor_ID` int(9) NOT NULL,
39  `Area` float(10,3) NOT NULL,
40  `Phone` varchar(100) NOT NULL,
41  `Location_X` float(10,3) NOT NULL,
42  `Location_Y` float(10,3) NOT NULL,
43  FOREIGN KEY (Floor_ID) REFERENCES Floor(ID)
44  PRIMARY KEY (`ID`,`Floor_ID`)
45    
46);
47
48
49#
50# TABLE STRUCTURE FOR: Contract
51#
52
53DROP TABLE IF EXISTS `Contract`;
54
55CREATE TABLE `Contract` (
56  `Owner_ID` int(9)  NOT NULL,
57  `Shop_ID` int(9)  NOT NULL,
58  `Floor_ID` int(9)  NOT NULL,
59  `Start_Date` datetime DEFAULT current_timestamp(),
60  `End_Date` datetime,
61  `Termination_Date` datetime,
62  FOREIGN KEY (Owner_ID) REFERENCES shopOwner(ID),
63  FOREIGN KEY (Shop_ID) REFERENCES Shop(ID),
64  FOREIGN KEY (Floor_ID) REFERENCES Floor(ID),
65  PRIMARY KEY (`Owner_ID`,`Shop_ID`,`Floor_ID`,`Start_Date`) 
66);
67
68#
69# TABLE STRUCTURE FOR: businessActivity
70#
71
72DROP TABLE IF EXISTS `businessActivity`;
73
74CREATE TABLE `businessActivity` (
75  `Owner_ID` int(9) NOT NULL,
76  `Shop_ID` int(9) NOT NULL,
77  `Floor_ID` int(9) NOT NULL,
78  `Contract_Start_Date` datetime NOT NULL,
79  `Start_Date` datetime NOT NULL,
80  `End_Date` datetime NOT NULL,
81  `Category` varchar(255) NOT NULL,
82  `Email` varchar(100) NOT NULL,
83  `Image_Path` varchar(255) NOT NULL,
84  `Name` varchar(100) NOT NULL,
85  `Mobile_Number` int(9) NOT NULL,
86  `Website` varchar(255) NOT NULL,
87  FOREIGN KEY (Owner_ID) REFERENCES Contract(Owner_ID),
88  FOREIGN KEY (Shop_ID) REFERENCES Contract(Shop_ID),
89  FOREIGN KEY (Floor_ID) REFERENCES Contract(Floor_ID),
90  FOREIGN KEY (Contract_Start_Date) REFERENCES Contract(Start_Date),
91  PRIMARY KEY (`Owner_ID`,`Shop_ID`,`Floor_ID`,`Contract_Start_Date`,`Start_Date`)
92);
93
94
95#
96# TABLE STRUCTURE FOR: activityClosures
97#
98
99DROP TABLE IF EXISTS `activityClosures`;
100
101CREATE TABLE `activityClosures` (
102  `Owner_ID` int(9) NOT NULL,
103  `Shop_ID` int(9) NOT NULL,
104  `Floor_ID` int(9) NOT NULL,
105  `Activity_Start_Date` datetime NOT NULL,
106  `Start_Date` datetime NOT NULL,
107  `Closure_From` datetime NOT NULL,
108  `Closure_To` datetime NOT NULL,
109  `Closure_Reason` varchar(255) NOT NULL,
110  FOREIGN KEY (Owner_ID) REFERENCES businessActivity(Owner_ID),
111  FOREIGN KEY (Shop_ID) REFERENCES businessActivity(Shop_ID),
112  FOREIGN KEY (Floor_ID) REFERENCES businessActivity(Floor_ID),
113  FOREIGN KEY (Activity_Start_Date) REFERENCES businessActivity(Contract_Start_Date),
114  FOREIGN KEY (Start_Date) REFERENCES businessActivity(Start_Date),
115  PRIMARY KEY (`Owner_ID`,`Shop_ID`,`Floor_ID`,`Activity_Start_Date`,`Start_Date`)
116);