Intelligent-Metro-System/Create_Tables.sql at main · Nikhil20012/Intelligent-Metro-System · 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
131
132
133
134
135
CREATE TABLE members
(
  email_id VARCHAR(30) NOT NULL,
  name VARCHAR(20) NOT NULL,
  gender VARCHAR(10) NOT NULL,
  password VARCHAR(30) NOT NULL,
  PRIMARY KEY (email_id)
);

CREATE TABLE metro_card
(
  card_id INT NOT NULL,
  balance INT NOT NULL,
  start VARCHAR(30) NOT NULL,
  destination VARCHAR(30) NOT NULL,
  s_time VARCHAR(30) NOT NULL,
  d_time VARCHAR(30) NOT NULL,
  email_id VARCHAR(30) NOT NULL,
  PRIMARY KEY (card_id),
  FOREIGN KEY (email_id) REFERENCES members(email_id)
);

CREATE TABLE admins
(
  admin_id INT NOT NULL,
  admin_name VARCHAR(20) NOT NULL,
  email_id VARCHAR(30) NOT NULL,
  PRIMARY KEY (admin_id),
  FOREIGN KEY (email_id) REFERENCES members(email_id)
);

CREATE TABLE lines
(
  line_id VARCHAR(10) NOT NULL,
  color VARCHAR(3) NOT NULL,
  start_station VARCHAR(30) NOT NULL,
  end_station VARCHAR(30) NOT NULL,
  PRIMARY KEY (line_id)
);

CREATE TABLE metro
(
  metro_id INT NOT NULL,
  line_id VARCHAR(10) NOT NULL,
  PRIMARY KEY (metro_id),
  FOREIGN KEY (line_id) REFERENCES lines(line_id)
);

CREATE TABLE phoneno
(
  p_no VARCHAR(15) NOT NULL,
  p_no2 VARCHAR(15) NOT NULL,
  email_id VARCHAR(30) NOT NULL,
  FOREIGN KEY (email_id) REFERENCES members(email_id)
);

CREATE TABLE payment
(
  payment_id INT NOT NULL,
  status VARCHAR(20) NOT NULL,
  card_id INT NOT NULL,
  PRIMARY KEY (payment_id),
  FOREIGN KEY (card_id) REFERENCES metro_card(card_id)
);

CREATE TABLE station
(
  station_id INT NOT NULL,
  station_name VARCHAR(30) NOT NULL,
  line_id VARCHAR(10) NOT NULL,
  PRIMARY KEY (station_id),
  FOREIGN KEY (line_id) REFERENCES lines(line_id)
);

CREATE TABLE crossing
(
  cross_id INT NOT NULL,
  line_1 VARCHAR(10) NOT NULL,
  line_2 VARCHAR(10) NOT NULL,
  station_id INT NOT NULL,
  PRIMARY KEY (cross_id),
  FOREIGN KEY (station_id) REFERENCES station(station_id)
);

CREATE TABLE users
(
  user_id INT NOT NULL,
  gender VARCHAR(10) NOT NULL,
  station_id INT NOT NULL,
  PRIMARY KEY (user_id),
  FOREIGN KEY (station_id) REFERENCES station(station_id)
);

CREATE TABLE ticket
(
  ticket_id INT NOT NULL,
  date_cur VARCHAR(15) NOT NULL,
  start_time VARCHAR(10) NOT NULL,
  end_time VARCHAR(10) NOT NULL,
  fare INT NOT NULL,
  start VARCHAR(30) NOT NULL,
  destination VARCHAR(30) NOT NULL,
  user_id INT NOT NULL,
  PRIMARY KEY (ticket_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE platform
(
  platform_no INT NOT NULL,
  arrival_time VARCHAR(10) NOT NULL,
  departure_time VARCHAR(10) NOT NULL,
  waiting_time VARCHAR(10) NOT NULL,
  station_id INT NOT NULL,
  user_id INT NOT NULL,
  FOREIGN KEY (station_id) REFERENCES station(station_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE boards
(
  metro_id INT NOT NULL,
  user_id INT NOT NULL,
  station_id INT NOT NULL,
  FOREIGN KEY (metro_id) REFERENCES metro(metro_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id),
  FOREIGN KEY (station_id) REFERENCES station(station_id)
);
CREATE TABLE report
(
  report_subject VARCHAR(50) NOT NULL,
  report VARCHAR(300) NOT NULL,
  email_id VARCHAR(30) NOT NULL,
  FOREIGN KEY (email_id) REFERENCES members(email_id)
);