Rxaz1dXr

· 7 years ago · Oct 27, 2018, 04:42 PM
1/* 
2 * this version of the employee table definitions (p 91 of Elmasri-Navathe 6th ed)
3 * uses the ALTER TABLE option so that foreign key declarations do not precede
4 * the relevant table definition
5 *
6 * Also, tabs were eliminated for easier copy/paste
7 *
8 * if things get wedged, foreign-key constraints can be DROPPED:
9 * alter table employee DROP foreign key dno;
10 *
11 */
12
13DROP SCHEMA IF EXISTS Bank;
14CREATE SCHEMA Bank COLLATE = utf8_general_ci;
15USE Bank;
16
17create table Loan (
18    Loan_num VARCHAR(255) NOT NULL,
19    amount DECIMAL(10,2) NULL,
20    payback_month DECIMAL(10,2) NULL,
21    interest_rate DECIMAL(10,2) NULL,
22    primary key (Loan_num)
23) ;
24
25create table customer  (
26    customer_id VARCHAR(255) NOT NULL,
27    fname VARCHAR(255) NULL,
28    lname VARCHAR(255) NULL,
29    dob DATE NULL,
30    sex VARCHAR(45) NULL,
31    h_addr VARCHAR(255) NULL,
32    w_addr VARCHAR(255) NULL,
33    Loan_id VARCHAR(255) NULL,
34    primary key (customer_id),
35    foreign key (Loan_id) references Loan(Loan_num)
36) ;
37
38create table cus_phonenum  (
39    customer_id VARCHAR(255) NOT NULL,
40    phone_number VARCHAR(255) NULL,
41    foreign key (customer_id) references customer(customer_id),
42    UNIQUE (phone_number)
43) ;
44
45create table acccont_category  (
46    Category_id VARCHAR(255) NOT NULL,
47    Category_name VARCHAR(255) NULL,
48    Category_type VARCHAR(255) NULL,
49    interest_month DECIMAL(2,2) NULL,
50    primary key (Category_id)
51) ;
52
53create table account  (
54    customer_id VARCHAR(255) NOT NULL,
55    account_num VARCHAR(255) NOT NULL,
56    balance DECIMAL(10,2) NOT NULL,
57    Category_id VARCHAR(255) NOT NULL,
58    primary key (account_num),
59    foreign key (customer_id) references customer(customer_id),
60    foreign key (Category_id) references acccont_category(Category_id)
61) ;
62
63create table credit_card  (
64    customer_id VARCHAR(255) NOT NULL,
65    account_num VARCHAR(255) NOT NULL,
66    creditcard_num VARCHAR(255) NOT NULL,
67    exp_date DATE NULL,
68    Pay_limit DECIMAL(10,2) NULL,
69    unique (account_num),
70    primary key (creditcard_num),
71    foreign key (customer_id) references customer(customer_id),
72    foreign key (account_num) references account(account_num)
73) ;
74
75
76create table Asset  (
77    Loan_num VARCHAR(255) NOT NULL,
78    Asset_name VARCHAR(255) NULL,
79    Asset_Description VARCHAR(1023) NULL,
80    foreign key (Loan_num) references Loan(Loan_num)
81) ;
82
83
84create table officer  (
85    officer_id VARCHAR(255) NOT NULL,
86    fname VARCHAR(255) NULL,
87    lname VARCHAR(255) NULL,
88    sex VARCHAR(45) NULL,
89    Date_of_birth DATE NULL,
90    Officer_type_id VARCHAR(45) NULL,
91    primary key (officer_id),
92    foreign key (Officer_type_id) references Officer_type(IDtype)
93) ;
94
95create table Officer_type  (
96    IDtype VARCHAR(45) NOT NULL,
97    Nametype VARCHAR(255) NOT NULL,
98    primary key (IDtype)
99) ;
100
101create table officer_contact  (
102    officer_officer_id VARCHAR(255) NOT NULL,
103    officer_email VARCHAR(320) NULL,
104    primary key (officer_officer_id)
105) ;
106
107create table Dept_tracker_calendar  (
108    officer_id VARCHAR(255) NOT NULL,
109    payback_date DATE NULL,
110    Loan_num VARCHAR(255) NULL,
111    creditcard_num VARCHAR(255) NULL,
112    foreign key (officer_id) references officer(officer_id),
113    foreign key (Loan_num) references Loan(Loan_num),
114    foreign key (creditcard_num) references credit_card(creditcard_num)
115) ;
116
117create table crm_officer_calendar  (
118   officer_id VARCHAR(255) NOT NULL,
119   date_of DATE NULL,
120   work TEXT(1023) NULL,
121   foreign key (officer_id) references officer(officer_id)
122) ;
123
124create table corresponding  (
125   officer_id VARCHAR(255) NOT NULL,
126   IDtype INT NOT NULL,
127   customer_id VARCHAR(255) NOT NULL,
128   foreign key (officer_id) references officer(officer_id),
129   foreign key (IDtype) references Officer_type(IDtype),
130   foreign key (customer_id) references customer(customer_id)
131) ;
132
133
134
135INSERT INTO Loan
136(Loan_num, amount, payback_month, interest_rate)
137values
138('00001', 100000.00, 5000.00, 5.2),
139('00002', 25000.00, 4000.00, 5.5),
140('00003', 150000.00, 1000.00, 2.7);
141 
142 
143INSERT INTO customer
144(customer_id, fname, lname, dob,sex,h_addr,w_addr,Loan_id)
145values
146('12345678', 'Halla', 'Shields',    '1988-05-22','M','6841 Convallis Rd.','Nocciano','00001'),
147('23456789', 'Nasim', 'Glover',    '1988-05-22', 'F','987-4102 Orci Rd.','Nocciano','00002'),
148('45678912', 'Rylee', 'Hamilton',  '1988-05-22','M','987-4102 Orci Rd.','Nocciano','00002'),
149('56456498', 'Oren', 'Mercado',    '1988-05-22','F','987-4102 Orci Rd.','Nocciano','00001'),
150('98789745', 'Adele', 'Nixon',     '1988-05-22','M', '987-4102 Orci Rd.','Nocciano','00003'),
151('98756415', 'Risa', 'Murphy',   '1988-05-22',   'F','987-4102 Orci Rd.','Nocciano','00002'),
152('35154548', 'Jocelyn', 'Andrews',  '1988-05-22','M','987-4102 Orci Rd.','Nocciano','00001'),
153('14848745', 'Edward', 'Barron',  '1988-05-22',  'F','987-4102 Orci Rd.','Nocciano','00003'),
154('55555555', 'Janna', 'Placilla',  '1988-05-22', 'M','P.O. Box 545, 721 Eu St.','Roccanova','00003');
155 
156 
157INSERT INTO cus_phonenum
158(customer_id, phone_number)
159values
160('12345678','0564050793'),
161('12345678','0897821545'),
162('23456789','0800017695'),
163('45678912','0554080992'),
164('56456498','0964839896'),
165('98789745','0867585306'),
166('98756415','0867515456'),
167('35154548','0894621418'),
168('14848745','0344984561'),
169('55555555','0979845621');
170 
171INSERT INTO acccont_category
172(Category_id, Category_name,Category_type,interest_month)
173values
174('1','à¸à¸à¸¡à¸§à¸±à¸™à¸™à¸µà¹‰à¸ˆà¸™à¹à¸™à¹ˆ','à¸à¸à¸¡à¸—à¸£à¸±à¸žà¸¢à¹Œ',0.25),
175('2','à¸ˆà¸™à¹à¸¥à¹‰à¸§','à¸à¸à¸¡à¸—à¸£à¸±à¸žà¸¢à¹Œ',0.01),
176('3','à¸‚à¸à¸„à¸°à¹à¸™à¸™à¹€à¸«à¸à¸°','à¹€à¸‡à¸´à¸™à¸à¸²à¸à¸à¸£à¸°à¹à¸ªà¸£à¸²à¸¢à¸§à¸±à¸™',4.00),
177('4','à¸™à¸°à¸™à¸°à¸™à¸°à¸™à¸°à¸™','à¹€à¸‡à¸´à¸™à¸à¸²à¸à¸à¸£à¸°à¹à¸ªà¸£à¸²à¸¢à¹€à¸”à¸·à¸à¸™',4.00);
178
179INSERT INTO account
180(customer_id, account_num,balance,Category_id)
181values
182('12345678','82248247499',100000000.69,'1'),
183('45678912','48221063599',20342940.00,'2'),
184('98789745','06385876199',12340.00,'3'),
185('23456789','82248247499',1234423.69,'3'),
186('56456498','06385876199',12340.00,'4'),
187('98756415','82248247499',1234.69,'2'),
188('35154548','48221063599',11234.00,'1'),
189('14848745','06385876199',123415.00,'4'),
190('55555555','37860255899',123425.00,'4');
191 
192 
193INSERT INTO credit_card
194(customer_id, account_num,creditcard_num,exp_date,Pay_limit)
195values
196('12345678','82248247499','975453350992','2019-06-1',1000.00),
197('45678912','48221063599','435950826992','2078-07-1',1200.00),
198('98789745','06385876199','789148945992','2077-08-1',14000.00),
199('55555555','37860255899','212370139992','2077-05-1',15000.00);
200 
201 
202INSERT INTO Asset
203(Loan_num, Asset_name,Asset_Description)
204values
205('00001','car_red',"à¸šà¸¥à¸²à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†"),
206('00002','à¸£à¸–à¸ªà¸²à¸¡à¸¥à¹‰à¸',"à¸šà¸¥à¸²à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†"),
207('00003','à¸à¸£à¸£à¹„à¸à¸£',"à¸šà¸¥à¸²à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†"),
208('00003','à¸ªà¸²à¸£à¸žà¸±à¸”à¸‚à¸à¸‡à¸à¸¸à¹Šà¸à¸à¸´à¹Šà¸',"à¸šà¸¥à¸²à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†à¹†");
209 
210INSERT INTO Officer_type
211(IDtype, Nametype)
212values
213('1','CRM'),
214('2','DEEP_TRACKER');
215 
216 
217 
218INSERT INTO officer
219(officer_id, fname,lname,sex,Date_of_birth,Officer_type_id)
220values
221('98484894', 'Halla', 'Jones',  'M',  '1988-05-22','1'),
222('98751218', 'Igor', 'Short', 'F',   '1988-05-22', '1'),
223('78548459', 'Randall', 'Blankenship','M',  '1988-05-22','1'),
224('14848745', 'Liberty', 'Wooten', 'F', '1988-05-22',  '2'),
225('16548748', 'Julian', 'Jensen',  'M', '1988-05-22','2');
226 
227 
228INSERT INTO officer_contact
229(officer_officer_id, officer_email)
230values
231('14848745', 'eu.augue@arcu.ca'),
232('16548748', 'gravida@NullaaliquetProin.com');
233 
234INSERT INTO crm_officer_calendar
235(officer_id, date_of,work)
236values
237('98484894','2018-10-30',"à¸›à¸£à¸°à¸Šà¸¸à¸¡"),
238('98751218','2018-10-30',"à¸ªà¹ˆà¸‡à¸‡à¸²à¸™DB"),
239('78548459','2018-11-5',"à¸ªà¹ˆà¸‡à¸‡à¸²à¸™DB"),
240('78548459','2018-11-5',"à¸ªà¹ˆà¸‡à¸‡à¸²à¸™DB");
241 
242INSERT INTO Dept_tracker_calendar
243(officer_id, payback_date,Loan_num,creditcard_num)
244values
245('16548748','2018-10-30','00001',null),
246('14848745','2018-10-30','00002',null),
247('14848745','2018-11-5','00001','975453350992'),
248('16548748','2018-11-5',null,'212370139992');
249 
250 
251INSERT INTO corresponding
252(officer_id, IDtype,customer_id)
253values
254('16548748','2','12345678'),
255('14848745','2','23456789'),
256('14848745','2','45678912'),
257('16548748','2','56456498'),
258('14848745','2','98789745'),
259('14848745','2','98756415'),
260('16548748','2','35154548'),
261('14848745','2','14848745'),
262('16548748','2','55555555');