-
Notifications
You must be signed in to change notification settings - Fork 0
/
Creating_tables.sql
143 lines (124 loc) · 3.31 KB
/
Creating_tables.sql
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
136
137
138
139
140
141
142
143
create Table Customer_Ad(
c_adr varchar(25) PRIMARY KEY,
c_apc numeric(6) NOT NULL
);
create table Customer(
cust_id char(5) PRIMARY KEY,
cust_pw varchar(10) NOT NULL,
name_f varchar(15) NOT NULL,
name_l varchar(15) NOT NULL,
c_adr varchar(25) REFERENCES Customer_Ad(c_adr),
dob date NOT NULL,
sex char(1) NOT NULL
);
create table Customer_ph(
cust_id char(5) REFERENCES Customer(cust_id),
c_ph numeric(10) NOT NULL,
CONSTRAINT cp_pk PRIMARY KEY(cust_id,c_ph)
);
create table Admin(
ad_id char(5) PRIMARY KEY,
ad_pw varchar(10) NOT NULL
);
create table Branch_Ad(
br_adr varchar(25) PRIMARY KEY,
br_apc numeric(6) NOT NULL
);
create table Branch(
ifsc char(10) PRIMARY KEY,
br_bal numeric(9) NOT NULL,
br_adr varchar(25) REFERENCES Branch_Ad(br_adr)
);
create table Branch_ph(
ifsc char(10) REFERENCES Branch(ifsc),
br_ph numeric(10) NOT NULL,
CONSTRAINT bp_pk PRIMARY KEY(ifsc,br_ph)
);
create table Account(
ac_no numeric(16) PRIMARY KEY,
ac_typ numeric(1) NOT NULL,
ac_bal numeric(9) NOT NULL,
cust_id char(5) REFERENCES Customer(cust_id),
ifsc char(10) REFERENCES Branch(ifsc)
);
create table Card(
cd_no numeric(16) PRIMARY KEY,
cd_typ numeric(1) NOT NULL,
pin numeric(4) NOT NULL,
cd_exp date NOT NULL,
ac_no numeric(16) REFERENCES Account(ac_no)
);
create table ATM_Ad(
atm_adr varchar(25) PRIMARY KEY,
atm_apc numeric(6) NOT NULL
);
create table ATM(
atm_id char(10) PRIMARY KEY,
atm_tp varchar(15) NOT NULL,
atm_bl numeric(9) NOT NULL,
atm_adr varchar(25) REFERENCES ATM_Ad(atm_adr),
avg_w numeric(4) NOT NULL,
avg_d numeric(4) NOT NULL,
avg_t numeric(4) NOT NULL,
ifsc char(10) REFERENCES Branch(ifsc)
);
create table Deposit(
tr_id char(10) PRIMARY KEY,
tr_tp numeric(1) NOT NULL,
tr_dt datetime NOT NULL,
td_amt numeric(6) NOT NULL,
atm_id char(10) REFERENCES ATM(atm_id),
ac_no numeric(16) REFERENCES Account(ac_no)
);
create table Withdraw(
tr_id char(10) PRIMARY KEY,
tr_tp numeric(1) NOT NULL,
tr_dt datetime NOT NULL,
tw_amt numeric(6) NOT NULL,
atm_id char(10) REFERENCES ATM(atm_id),
ac_no numeric(16) REFERENCES Account(ac_no)
);
create table Security(
atm_id char(10) REFERENCES ATM(atm_id),
s_dt datetime NOT NULL,
frd varchar(50) NOT NULL,
res varchar(200) NULL,
CONSTRAINT se_pk PRIMARY KEY(atm_id,s_dt)
);
create table Complaint(
cl_id char(10) PRIMARY KEY,
descr varchar(200) NOT NULL,
cl_stat varchar(15) NULL,
cust_id char(5) REFERENCES Customer(cust_id),
atm_id char(10) REFERENCES ATM(atm_id)
);
create table Vendor(
comp varchar(20) PRIMARY KEY,
rvw numeric(2) NOT NULL,
no_ct numeric(5) NOT NULL
);
create table Contract(
ct_id char(10) PRIMARY KEY,
ct_yr year NOT NULL,
amc numeric(7) NOT NULL,
warr numeric(2) NOT NULL,
atm_id char(10) REFERENCES ATM(atm_id),
comp varchar(20) REFERENCES Vendor(comp)
);
create table refill(
ifsc char(10) REFERENCES Branch(ifsc),
atm_id char(10) REFERENCES ATM(atm_id),
r_dt datetime NOT NULL,
r_amt numeric(9) NOT NULL,
CONSTRAINT re_pk PRIMARY KEY(ifsc,atm_id,r_dt)
);
create table monitor(
ad_id char(5) REFERENCES Admin(ad_id),
atm_id char(10) REFERENCES ATM(atm_id),
CONSTRAINT mo_pk PRIMARY KEY(ad_id,atm_id)
);
create table sign(
ad_id char(5) REFERENCES Admin(ad_id),
comp varchar(20) REFERENCES Vendor(comp),
CONSTRAINT si_pk PRIMARY KEY(ad_id,comp)
);