API tools faq
paste
Guest User

Untitled

a guest
Jan 12th, 2018
292
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 35.99 KB | None | 0 0
raw download clone embed print report
  1. DROP TABLE PENDING_JOB_REQ;
  2. DROP TABLE PENDING_SIM_REQ;
  3. DROP TABLE AVAILABLE_PREPAID_SIM;
  4. DROP TABLE AVAILABLE_POSTPAID_SIM;
  5. DROP TABLE CALL_DATABASE;
  6. DROP TABLE SMS_DATABASE;
  7. DROP TABLE NET_USAGE_DATABASE;
  8. DROP TABLE POSTPAID_BILL ;
  9. DROP TABLE PREPAID_SIM ;
  10. DROP TABLE POSTPAID_SIM ;
  11. DROP TABLE SIM ;
  12. DROP TABLE SIM_TYPE ;
  13. DROP TABLE INTER_SLIP_BONUS;
  14. DROP TABLE ADDITIONAL_SALARY;
  15. DROP TABLE PAYSLIP;
  16. DROP TABLE EMPLOYEES;
  17. DROP TABLE JOBS;
  18. DROP TABLE USER_DATA;
  19. DROP TABLE PASSWORDS;
  20. DROP TABLE OFFER_PACKAGES;
  21.  
  22.  
  23. --------------------------------packages start---------------------------------------
  24.  
  25. CREATE TABLE OFFER_PACKAGES(
  26. PACK_ID NUMBER PRIMARY KEY ,
  27. PACK_NAME VARCHAR2(100) UNIQUE ,
  28. START_TIME NUMBER , -- 24 clock hour
  29. END_TIME NUMBER,
  30. CALL_COST NUMBER ,
  31. SMS_COST NUMBER ,
  32. NET_COST NUMBER
  33. );
  34.  
  35. INSERT INTO OFFER_PACKAGES(PACK_ID, PACK_NAME, START_TIME, END_TIME, CALL_COST, SMS_COST, NET_COST) VALUES(1,'PAY-PER-USE', 0,23, 1, 1, 1);
  36. INSERT INTO OFFER_PACKAGES(PACK_ID, PACK_NAME, START_TIME, END_TIME, CALL_COST, SMS_COST, NET_COST) VALUES(2,'DESH', 10,18, 0.5, 2, 1);
  37. INSERT INTO OFFER_PACKAGES(PACK_ID, PACK_NAME, START_TIME, END_TIME, CALL_COST, SMS_COST, NET_COST) VALUES(3,'BIJOY',06,22, 1,1,0.5);
  38. INSERT INTO OFFER_PACKAGES(PACK_ID, PACK_NAME, START_TIME, END_TIME, CALL_COST, SMS_COST, NET_COST) VALUES(4,'EKUSH',00,12, 0.35, 1.5, 1);
  39.  
  40.  
  41.  
  42. --------------------------------packages end---------------------------------------
  43.  
  44.  
  45.  
  46.  
  47. ---------------------------------AVAILaBLE PASSWORD START --------------------------------
  48.  
  49. CREATE TABLE PASSWORDS(
  50. USER_PASSWORD VARCHAR2(100) PRIMARY KEY
  51. );
  52.  
  53. ---------------------------------AVAILaBLE PASSWORD END --------------------------------
  54.  
  55. -------------------------USER Table start------------------------------------------
  56. CREATE TABLE USER_DATA (
  57. USER_NAME VARCHAR2(100) NOT NULL,
  58. NID_NO number CONSTRAINT USER_PK PRIMARY KEY,
  59. DATE_OF_BIRTH VARCHAR2(100) NOT NULL,
  60. NAME_OF_FATHER VARCHAR2(100) NOT NULL,
  61. NAME_OF_MOTHER VARCHAR2(100) NOT NULL,
  62. STREET_ADDRESS VARCHAR2(100) NOT NULL,
  63. CITY VARCHAR2(100) NOT NULL,
  64. IS_EMPLOYEE VARCHAR2(100) NOT NULL,
  65. USER_PASSWORD VARCHAR2(100) UNIQUE
  66. );
  67.  
  68. INSERT INTO USER_DATA (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,USER_PASSWORD) VALUES ('Jacob',1878,'Feb 12, 1971','Leonard','Jayme','Ap #925-8567 Nulla Rd.','Montpellier','NO','U9RK1');
  69. INSERT INTO USER_DATA (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,USER_PASSWORD) VALUES ('Octavius',1879,'Apr 19, 1939','Vance','Debra','7308 Ligula Av.','Aguacaliente (San Francisco)','NO','U1CK9');
  70.  
  71. INSERT INTO USER_DATA (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,USER_PASSWORD) VALUES ('Larissa',1880,'Jan 12, 1935','Leonard','Serina','863-3484 Urna. Avenue','Curacautín','NO','U5CU1');
  72. INSERT INTO USER_DATA (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,USER_PASSWORD) VALUES ('Winter',1881,'Aug 25, 1952','Holmes','Yael','Ap #534-3655 Nulla Rd.','Henderson','NO','H1MQ7');
  73.  
  74. INSERT INTO USER_DATA (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,USER_PASSWORD) VALUES ('Valentine',1882,'Jun 3, 1940','Guy','Octavia','P.O. Box 190, 361 Lectus St.','Tobermory','NO','H0LH9');
  75. INSERT INTO USER_DATA (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,USER_PASSWORD) VALUES ('Molly',1883,'Nov 22, 1965','Hashim','Alexa','6502 Massa. Street','San Nicolás','NO','Y3SR3');
  76. INSERT INTO USER_DATA (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,USER_PASSWORD) VALUES ('Abbot',1884,'Dec 8, 1986','Benedict','Rina','Ap #577-8748 Tortor, Street','Sauvenire','NO','S3QK9');
  77.  
  78. INSERT INTO USER_DATA (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,USER_PASSWORD) VALUES ('Tashya',1885,'Mar 30, 1954','Lev','Sasha','P.O. Box 331, 1269 Metus St.','Bolzano Vicentino','YES','J8FD7');
  79. INSERT INTO USER_DATA (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,USER_PASSWORD) VALUES ('Harriet',1886,'Jul 12, 1918','Rashad','Melyssa','2921 Sit St.','Millesimo','YES','P1PQ0');
  80. INSERT INTO USER_DATA (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,USER_PASSWORD) VALUES ('Salvador',1887,'Jul 26, 1975','Vance','Sara','P.O. Box 120, 2384 A St.','Mulhouse','YES','K5DG1');
  81. INSERT INTO USER_DATA (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,USER_PASSWORD) VALUES ('Tamara',1888,'Jan 21, 2001','Kaseem','Oprah','862 Nisl Road','Pelotas','YES','Z7UW2');
  82. INSERT INTO USER_DATA (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,USER_PASSWORD) VALUES ('Gannon',1889,'Jan 10, 1968','Price','Nola','6692 Consequat St.','Kanpur','YES','W0TD0');
  83. -------------------------USER Table end------------------------------------------
  84.  
  85. -------------------------SIM Type Start---------------------------------------
  86. CREATE TABLE SIM_TYPE(
  87. TYPE_ID number PRIMARY KEY,
  88. TYPE_NAME VARCHAR2(100) NOT NULL
  89. );
  90.  
  91. INSERT INTO SIM_TYPE(TYPE_ID,TYPE_NAME) VALUES (1,'PRE-PAID');
  92. INSERT INTO SIM_TYPE(TYPE_ID,TYPE_NAME) VALUES (2,'POST-PAID');
  93.  
  94. -----------------------SIM Type End---------------------------------------------
  95.  
  96. -----------------------SIM Table Start---------------------------------------------
  97.  
  98. CREATE TABLE SIM (
  99. PHONE_NO varchar2(100) CONSTRAINT PK PRIMARY KEY,
  100. NID_NO number NOT NULL,
  101. TYPE_ID number NOT NULL,
  102. ISSUE_DATE TIMESTAMP NOT NULL,
  103. ISSUED_BY varchar2(100) NOT NULL,
  104. CURRENT_PACKAGE_ID NUMBER DEFAULT 1,
  105. CONSTRAINT NID_PK FOREIGN KEY (NID_NO) REFERENCES USER_DATA (NID_NO) ON DELETE CASCADE,
  106. CONSTRAINT TYPE_ID_FK FOREIGN KEY (TYPE_ID) REFERENCES SIM_TYPE(TYPE_ID) ON DELETE CASCADE ,
  107. CONSTRAINT CURRENT_PACK_FK FOREIGN KEY (CURRENT_PACKAGE_ID) REFERENCES OFFER_PACKAGES(PACK_ID) ON DELETE CASCADE
  108. );
  109.  
  110. INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES ('01935849658',1878,1,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),104);
  111. INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES ('01992409859',1879,1,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),105);
  112. ----------------------------------------just prepaid----------------------------------------
  113. INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES ('01960850638',1880,2,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),104);
  114. INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES ('01965550938',1881,2,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),104);
  115. ---------------------------------------- just post paid-------------------------------------
  116. INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES ('01993969922',1882,1,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),105);
  117. INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES ('01980184234',1883,1,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),104);
  118. INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES ('01936994382',1884,1,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),105);
  119. ---------------------------------------- mix prepaid ------------------------------
  120. INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES ('01976862069',1882,2,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),104);
  121. INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES ('01990275361',1883,2,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),104);
  122. INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES ('01937883634',1884,2,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),105);
  123. -----------------------------------------mix postpaid-------------------------------------
  124. INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES ('01937303098',1885,2,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),104);
  125. INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES ('01911071571',1886,2,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),105);
  126. INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES ('01990122106',1887,2,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),105);
  127. INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES ('01950134157',1888,2,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),104);
  128. INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES ('01967328196',1889,2,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),104);
  129.  
  130. ------------------------------------------employee-------------------------------
  131. -----------------------------------SIM Table End-------------------------------------------
  132.  
  133.  
  134. -----------------------------------Pre-Paid SIM Table start-------------------------------------------
  135. CREATE TABLE PREPAID_SIM (
  136. SIM_NO varchar2(100) PRIMARY KEY,
  137. TOTAL_CALL_DURATION NUMBER NOT NULL,
  138. TOTAL_MESSAGE_SENT NUMBER NOT NULL,
  139. TOTAL_INTERNET_USAGE NUMBER NOT NULL,
  140. BALANCE NUMBER NOT NULL CONSTRAINT BALANCE_CK CHECK(BALANCE >= 0) ,
  141. CONSTRAINT FK3 FOREIGN KEY (SIM_NO) REFERENCES SIM(PHONE_NO) ON DELETE CASCADE
  142. );
  143.  
  144. INSERT INTO PREPAID_SIM (SIM_NO,TOTAL_CALL_DURATION ,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE,BALANCE) VALUES ('01935849658',12,58,1251,373);
  145. INSERT INTO PREPAID_SIM (SIM_NO,TOTAL_CALL_DURATION ,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE,BALANCE) VALUES ('01992409859',25,15,1329,98);
  146. ------------------just prepaid---------------------
  147. INSERT INTO PREPAID_SIM (SIM_NO,TOTAL_CALL_DURATION ,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE,BALANCE) VALUES ('01993969922',192,44,3279,407);
  148. INSERT INTO PREPAID_SIM (SIM_NO,TOTAL_CALL_DURATION ,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE,BALANCE) VALUES ('01980184234',227,60,2509,80);
  149. INSERT INTO PREPAID_SIM (SIM_NO,TOTAL_CALL_DURATION ,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE,BALANCE) VALUES ('01936994382',554,62,56,211);
  150. --------------------mix prepaid-----------------------
  151. -----------------------------------Pre-Paid SIM Table End-------------------------------------------
  152.  
  153.  
  154.  
  155. -----------------------------------Post-Paid SIM Table Start-------------------------------------------
  156. CREATE TABLE POSTPAID_SIM (
  157. SIM_NO varchar2(100) PRIMARY KEY,
  158. TOTAL_CALL_DURATION NUMBER NOT NULL,
  159. TOTAL_MESSAGE_SENT NUMBER NOT NULL,
  160. TOTAL_INTERNET_USAGE NUMBER NOT NULL,
  161. CONSTRAINT POST_FK FOREIGN KEY (SIM_NO) REFERENCES SIM(PHONE_NO) ON DELETE CASCADE
  162. );
  163.  
  164. INSERT INTO POSTPAID_SIM(SIM_NO,TOTAL_CALL_DURATION,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE) VALUES ('01960850638',12,59,1251);
  165. INSERT INTO POSTPAID_SIM(SIM_NO,TOTAL_CALL_DURATION,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE) VALUES ('01965550938',25,35,1329);
  166. --------------------------just post-----------------------
  167. INSERT INTO POSTPAID_SIM(SIM_NO,TOTAL_CALL_DURATION,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE) VALUES ('01976862069',19,44,3279);
  168. INSERT INTO POSTPAID_SIM(SIM_NO,TOTAL_CALL_DURATION,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE) VALUES ('01990275361',22,60,2509);
  169. INSERT INTO POSTPAID_SIM(SIM_NO,TOTAL_CALL_DURATION,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE) VALUES ('01937883634',55,62,56 );
  170. ---------------------------mix post-------------------------
  171. INSERT INTO POSTPAID_SIM(SIM_NO,TOTAL_CALL_DURATION,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE) VALUES ('01937303098',12,85,4088);
  172. INSERT INTO POSTPAID_SIM(SIM_NO,TOTAL_CALL_DURATION,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE) VALUES ('01911071571',25,77,463);
  173. INSERT INTO POSTPAID_SIM(SIM_NO,TOTAL_CALL_DURATION,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE) VALUES ('01990122106',62,1,10);
  174. INSERT INTO POSTPAID_SIM(SIM_NO,TOTAL_CALL_DURATION,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE) VALUES ('01950134157',19,1,0);
  175. INSERT INTO POSTPAID_SIM(SIM_NO,TOTAL_CALL_DURATION,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE) VALUES ('01967328196',22,1,0);
  176. --------------------------employee---------------------------
  177. -----------------------------------Post-Paid SIM Table End-------------------------------------------
  178.  
  179.  
  180.  
  181. -----------------------------------Call Database Start-------------------------------------------
  182. CREATE TABLE CALL_DATABASE (
  183. CALL_ID NUMBER PRIMARY KEY,
  184. CALLER_NO varchar2(100) NOT NULL,
  185. RECEIVER_NO varchar2(100) NOT NULL,
  186. STARTING_TIME TIMESTAMP,
  187. ENDING_TIME TIMESTAMP,
  188. CONSTRAINT FK1 FOREIGN KEY (CALLER_NO) REFERENCES SIM(PHONE_NO) ON DELETE CASCADE
  189. );
  190.  
  191. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (1,'01935849658','01893969922', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  192. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (2,'01960850638','01980184234', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  193. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (3,'01960850638','01936994382', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  194. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (4,'01965550938','01710162106', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  195. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (5,'01993969922','01943306287', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  196. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (6,'01980184234','01504115381', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  197. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (7,'01990275361','01600007434', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  198. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (8,'01976862069','01862642555', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  199. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (9, '01990275361','01976862069',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  200. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (10,'01937883634','01990275361',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  201. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (11,'01937883634','01937883634',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  202. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (12,'01937883634','01793969922',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  203. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (13,'01993969922','01980184234',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  204. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (14,'01980184234','01936994382',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  205. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (15,'01993969922','01837303098',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  206. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (16,'01980184234','01911071571',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  207. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (17,'01992409859','01990122106',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  208.  
  209. -----------------------------------Call Database End-------------------------------------------
  210.  
  211.  
  212. -----------------------------------SMS Database Start-------------------------------------------
  213. CREATE TABLE SMS_DATABASE (
  214. SMS_ID varchar2(100) CONSTRAINT sms_PK PRIMARY KEY,
  215. SENDER_NO varchar2(100) ,
  216. RECEIVER_NO varchar2(100) NOT NULL,
  217. SENDING_TIME TIMESTAMP,
  218. CONSTRAINT sms_FK2 FOREIGN KEY (SENDER_NO) REFERENCES SIM(PHONE_NO) ON DELETE CASCADE
  219. );
  220.  
  221. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (1,'01935849658','01893969922', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  222. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (2,'01960850638','01980184234', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  223. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (3,'01960850638','01936994382', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  224. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (4,'01965550938','01710162106', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  225. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (5,'01993969922','01943306287', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  226. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (6,'01980184234','01504115381', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  227. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (7,'01990275361','01600007434', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  228. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (8,'01976862069','01862642555', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  229. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (9, '01990275361','01976862069',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  230. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (10,'01937883634','01990275361',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  231. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (11,'01937883634','01937883634',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  232. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (12,'01937883634','01793969922',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  233. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (13,'01993969922','01980184234',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  234. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (14,'01980184234','01936994382',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  235. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (15,'01993969922','01837303098',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  236. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (16,'01980184234','01911071571',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  237. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (17,'01992409859','01990122106',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  238.  
  239.  
  240. -----------------------------------SMS Database End-------------------------------------------
  241.  
  242.  
  243. -----------------------------------Net Usage Database Start-------------------------------------------
  244. CREATE TABLE NET_USAGE_DATABASE (
  245. SESSION_ID NUMBER PRIMARY KEY ,
  246. SIM_NO varchar2(100),
  247. USING_DATE TIMESTAMP ,
  248. USED_KB NUMBER,
  249. CONSTRAINT NET_FK FOREIGN KEY (SIM_NO) REFERENCES SIM(PHONE_NO) ON DELETE CASCADE
  250. );
  251.  
  252. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (1,'01935849658', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),2167);
  253. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (2,'01960850638', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),870);
  254. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (3,'01960850638', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),4808);
  255. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (4,'01965550938', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),3627);
  256. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (5,'01993969922', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),1424);
  257. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (6,'01980184234', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),2175);
  258. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (7,'01990275361', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),4663);
  259. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (8,'01976862069', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),4218);
  260. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (9, '01990275361',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),3200);
  261. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (10,'01937883634',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),4219);
  262. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (11,'01937883634',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),3208);
  263. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (12,'01937883634',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),1017);
  264. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (13,'01993969922',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),3460);
  265. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (14,'01980184234',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),4001);
  266. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (15,'01993969922',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),4878);
  267. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (16,'01980184234',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),4204);
  268. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (17,'01992409859',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),4644);
  269.  
  270.  
  271. -----------------------------------Net Usage Database End-------------------------------------------
  272.  
  273.  
  274.  
  275. -----------------------------------Post-paid Bill Start-------------------------------------------
  276. -- IF NON-EMPLOYEE
  277. CREATE TABLE POSTPAID_BILL (
  278. BILL_ID NUMBER PRIMARY KEY,
  279. SIM_NO varchar2(100),
  280. CALL_BILL NUMBER,
  281. SMS_BILL NUMBER ,
  282. INTERNET_BILL NUMBER ,
  283. TOTAL_COST NUMBER ,
  284. CONSTRAINT POST_BILL_FK FOREIGN KEY(SIM_NO) REFERENCES POSTPAID_SIM(SIM_NO) ON DELETE CASCADE
  285. );
  286.  
  287. INSERT INTO POSTPAID_BILL(BILL_ID, SIM_NO, CALL_BILL, SMS_BILL, INTERNET_BILL, TOTAL_COST) VALUES (1,'01960850638' ,10,59,125, 194);
  288. INSERT INTO POSTPAID_BILL(BILL_ID, SIM_NO, CALL_BILL, SMS_BILL, INTERNET_BILL, TOTAL_COST) VALUES (2,'01965550938' ,124,35,132,291);
  289. --------------------------------------JUST POSTPAID------------------------------------------------,-------
  290. INSERT INTO POSTPAID_BILL(BILL_ID, SIM_NO, CALL_BILL, SMS_BILL, INTERNET_BILL, TOTAL_COST) VALUES (3,'01976862069' ,51,44,327,422);
  291. INSERT INTO POSTPAID_BILL(BILL_ID, SIM_NO, CALL_BILL, SMS_BILL, INTERNET_BILL, TOTAL_COST) VALUES (4,'01990275361' ,143,60,250,453);
  292. INSERT INTO POSTPAID_BILL(BILL_ID, SIM_NO, CALL_BILL, SMS_BILL, INTERNET_BILL, TOTAL_COST) VALUES (5,'01937883634' ,1421,62,56 ,1539 );
  293. ---------------------------------------MIXED POSTPAID----------------------------------------------,---------
  294. INSERT INTO POSTPAID_BILL(BILL_ID, SIM_NO, CALL_BILL, SMS_BILL, INTERNET_BILL, TOTAL_COST) VALUES (6,'01937303098' ,100,85,408,593);
  295. INSERT INTO POSTPAID_BILL(BILL_ID, SIM_NO, CALL_BILL, SMS_BILL, INTERNET_BILL, TOTAL_COST) VALUES (7,'01911071571' ,234,77,46,357);
  296. INSERT INTO POSTPAID_BILL(BILL_ID, SIM_NO, CALL_BILL, SMS_BILL, INTERNET_BILL, TOTAL_COST) VALUES (8,'01990122106' ,82,1,20,103);
  297. INSERT INTO POSTPAID_BILL(BILL_ID, SIM_NO, CALL_BILL, SMS_BILL, INTERNET_BILL, TOTAL_COST) VALUES (9,'01950134157' ,132,12,40, 184);
  298. INSERT INTO POSTPAID_BILL(BILL_ID, SIM_NO, CALL_BILL, SMS_BILL, INTERNET_BILL, TOTAL_COST) VALUES (10,'01967328196',15,42,30,87);
  299. --------------------------employee---------------------------
  300.  
  301.  
  302. -----------------------------------Post-paid Bill End-------------------------------------------
  303.  
  304.  
  305. -----------------------------------JOB Start-------------------------------------------
  306. CREATE TABLE JOBS(
  307. JOB_ID NUMBER PRIMARY KEY ,
  308. JOB_TITLE VARCHAR2(100) UNIQUE NOT NULL ,
  309. MIN_SALARY NUMBER NOT NULL ,
  310. MAX_SALARY NUMBER NOT NULL
  311. );
  312.  
  313. INSERT INTO JOBS(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES (101 , 'Administrator', 50000 , 80000) ;
  314. INSERT INTO JOBS(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES (102 , 'Engineer', 40000 , 60000) ;
  315. INSERT INTO JOBS(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES (103 , 'Human Resources Representative', 30000 , 40000) ;
  316. INSERT INTO JOBS(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES (104 , 'Accounting Manager', 20000 , 30000) ;
  317. INSERT INTO JOBS(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES (105 , 'Customer Care', 10000 , 20000) ;
  318.  
  319. -----------------------------------Jobs Table End-------------------------------------------
  320.  
  321.  
  322. -----------------------------------Bonus table start-------------------------------------------
  323. CREATE TABLE ADDITIONAL_SALARY(
  324. ADDSAL_ID NUMBER PRIMARY KEY CONSTRAINT ADDSAL_ID_CHK CHECK(ADDSAL_ID > 0) ,
  325. ADDSAL_NAME VARCHAR2(100) UNIQUE NOT NULL ,
  326. AMOUNT NUMBER CONSTRAINT ADD_SAL_AMNT CHECK(AMOUNT > 0)
  327. );
  328.  
  329. INSERT INTO ADDITIONAL_SALARY(ADDSAL_ID, ADDSAL_NAME, AMOUNT) VALUES (1 , 'HEALTH CARE', 5000);
  330. INSERT INTO ADDITIONAL_SALARY(ADDSAL_ID, ADDSAL_NAME, AMOUNT) VALUES (2 , 'PERFORMANCE BONUS', 5000);
  331. INSERT INTO ADDITIONAL_SALARY(ADDSAL_ID, ADDSAL_NAME, AMOUNT) VALUES (3 , 'FESTIVAL BONUS', 5000);
  332. INSERT INTO ADDITIONAL_SALARY(ADDSAL_ID, ADDSAL_NAME, AMOUNT) VALUES (4 , 'OVERTIME BONUS', 5000);
  333.  
  334. -----------------------------------Bonus table End-------------------------------------------
  335.  
  336.  
  337.  
  338. -----------------------------------Employee Start-------------------------------------------
  339. CREATE TABLE EMPLOYEES (
  340. EMPLOYEE_ID number primary key ,
  341. EMPLOYEE_NAME VARCHAR2(100) NOT NULL,
  342. NID_NO NUMBER UNIQUE ,
  343. EMAIL VARCHAR2(100) UNIQUE ,
  344. BANK_ACC_NO NUMBER UNIQUE ,
  345. HIRE_DATE TIMESTAMP NOT NULL,
  346. SSC_YEAR number NOT NULL,
  347. SSC_GPA NUMBER NOT NULL,
  348. HSC_YEAR NUMBER NOT NULL,
  349. HSC_GPA NUMBER NOT NULL ,
  350. UNIVERSITY VARCHAR2(100) NOT NULL,
  351. CGPA NUMBER NOT NULL,
  352. EXPERIENCE VARCHAR2(100) NOT NULL,
  353. JOB_ID NUMBER NOT NULL ,
  354. BASIC_SALARY NUMBER NOT NULL ,
  355. CONSTRAINT EMP_FK FOREIGN KEY(NID_NO) REFERENCES USER_DATA(NID_NO) ON DELETE CASCADE,
  356. CONSTRAINT EMPJOB_FK FOREIGN KEY(JOB_ID) REFERENCES JOBS(JOB_ID) ON DELETE CASCADE
  357. );
  358.  
  359. INSERT INTO EMPLOYEES (EMPLOYEE_ID,EMPLOYEE_NAME,NID_NO,EMAIL,BANK_ACC_NO,HIRE_DATE,SSC_YEAR,SSC_GPA ,HSC_YEAR,HSC_GPA ,UNIVERSITY ,CGPA ,EXPERIENCE ,JOB_ID,BASIC_SALARY) VALUES (101,'Tashya' ,1885,'Nam.ac.nulla@malesuadafames.org',3001,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),2007, 5.00, 2009, 5.00, 'BUET', 3.67, '2 years as Network Engineer at BanglaPhone' ,101,79103);
  360. INSERT INTO EMPLOYEES (EMPLOYEE_ID,EMPLOYEE_NAME,NID_NO,EMAIL,BANK_ACC_NO,HIRE_DATE,SSC_YEAR,SSC_GPA ,HSC_YEAR,HSC_GPA ,UNIVERSITY ,CGPA ,EXPERIENCE ,JOB_ID,BASIC_SALARY) VALUES (102,'Harriet',1886,'In@ametdapibus.org',3002,TO_DATE('2015-12-07 09:26:50','yyyy/mm/dd hh24:mi:ss'),2008, 5.00, 2010, 5.00, 'Dhaka University', 3.57, '1 years as HR OFFICER at GRAMEENPHONE' ,102,51525);
  361. INSERT INTO EMPLOYEES (EMPLOYEE_ID,EMPLOYEE_NAME,NID_NO,EMAIL,BANK_ACC_NO,HIRE_DATE,SSC_YEAR,SSC_GPA ,HSC_YEAR,HSC_GPA ,UNIVERSITY ,CGPA ,EXPERIENCE ,JOB_ID,BASIC_SALARY) VALUES (103,'Salvado',1887,'Mauris.eu@massaQuisqueporttitor.net',3003,TO_DATE('2010-10-29 09:26:50','yyyy/mm/dd hh24:mi:ss'),2004, 5.00, 2006, 5.00, 'IUT', 3.67, '1 years as Customer Manager at BanglaPhone' ,103,44371);
  362. INSERT INTO EMPLOYEES (EMPLOYEE_ID,EMPLOYEE_NAME,NID_NO,EMAIL,BANK_ACC_NO,HIRE_DATE,SSC_YEAR,SSC_GPA ,HSC_YEAR,HSC_GPA ,UNIVERSITY ,CGPA ,EXPERIENCE ,JOB_ID,BASIC_SALARY) VALUES (104,'Tamara' ,1888,'vestibulum.lorem.sit@velit.net',3004,TO_DATE('2018-03-08 09:26:50','yyyy/mm/dd hh24:mi:ss'),2011, 5.00, 2013, 5.00, 'RUET', 3.64, '3 years as Accounting Manager at VaioPhone' ,104,42073);
  363. INSERT INTO EMPLOYEES (EMPLOYEE_ID,EMPLOYEE_NAME,NID_NO,EMAIL,BANK_ACC_NO,HIRE_DATE,SSC_YEAR,SSC_GPA ,HSC_YEAR,HSC_GPA ,UNIVERSITY ,CGPA ,EXPERIENCE ,JOB_ID,BASIC_SALARY) VALUES (105,'Gannon' ,1889,'justo.Praesent.luctus@Quisque.net',3005,TO_DATE('2016-04-11 19:26:50','yyyy/mm/dd hh24:mi:ss'),2009, 5.00, 2011, 5.00, 'BUET', 3.87, '2 years as Network Engineer at WePhone' ,105,25171);
  364.  
  365. -----------------------------------Employee End-------------------------------------------
  366.  
  367.  
  368. -----------------------------------Payslip Start-------------------------------------------
  369. CREATE TABLE PAYSLIP(
  370. PAYSLIP_ID NUMBER PRIMARY KEY ,
  371. EMPLOYEE_ID NUMBER ,
  372. TOTAL_SALARY NUMBER NOT NULL ,
  373. ISSUE_DATE TIMESTAMP NOT NULL,
  374. CONSTRAINT SLIP_EMP_ID_FK FOREIGN KEY(EMPLOYEE_ID) REFERENCES EMPLOYEES(EMPLOYEE_ID) ON DELETE CASCADE
  375. );
  376.  
  377. INSERT INTO PAYSLIP(EMPLOYEE_ID, PAYSLIP_ID, TOTAL_SALARY, ISSUE_DATE) VALUES (101 , 1001, 20000, TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  378. INSERT INTO PAYSLIP(EMPLOYEE_ID, PAYSLIP_ID, TOTAL_SALARY, ISSUE_DATE) VALUES (102 , 1002, 20000, TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  379. INSERT INTO PAYSLIP(EMPLOYEE_ID, PAYSLIP_ID, TOTAL_SALARY, ISSUE_DATE) VALUES (103 , 1003, 20000, TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  380. INSERT INTO PAYSLIP(EMPLOYEE_ID, PAYSLIP_ID, TOTAL_SALARY, ISSUE_DATE) VALUES (104 , 1004, 20000, TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  381. INSERT INTO PAYSLIP(EMPLOYEE_ID, PAYSLIP_ID, TOTAL_SALARY, ISSUE_DATE) VALUES (105 , 1005, 20000, TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  382.  
  383. -----------------------------------Payslip End-------------------------------------------
  384.  
  385.  
  386. -------------------------------Inter slip Bonus start-----------------------------------
  387. CREATE TABLE INTER_SLIP_BONUS(
  388. PAYSLIP_ID NUMBER ,
  389. BONUS_ID NUMBER ,
  390. CONSTRAINT PAYSLIP_ID_FK FOREIGN KEY(PAYSLIP_ID) REFERENCES PAYSLIP(PAYSLIP_ID) ,
  391. CONSTRAINT BONUS_ID_FK FOREIGN KEY(BONUS_ID) REFERENCES ADDITIONAL_SALARY(ADDSAL_ID)
  392. );
  393.  
  394. INSERT INTO INTER_SLIP_BONUS(PAYSLIP_ID, BONUS_ID) VALUES (1001 , 1);
  395. INSERT INTO INTER_SLIP_BONUS(PAYSLIP_ID, BONUS_ID) VALUES (1002 , 2);
  396. INSERT INTO INTER_SLIP_BONUS(PAYSLIP_ID, BONUS_ID) VALUES (1003 , 3);
  397. INSERT INTO INTER_SLIP_BONUS(PAYSLIP_ID, BONUS_ID) VALUES (1004 , 4);
  398. INSERT INTO INTER_SLIP_BONUS(PAYSLIP_ID, BONUS_ID) VALUES (1005 , 2);
  399. INSERT INTO INTER_SLIP_BONUS(PAYSLIP_ID, BONUS_ID) VALUES (1001 , 4);
  400. INSERT INTO INTER_SLIP_BONUS(PAYSLIP_ID, BONUS_ID) VALUES (1003 , 2);
  401. INSERT INTO INTER_SLIP_BONUS(PAYSLIP_ID, BONUS_ID) VALUES (1002 , 1);
  402.  
  403. -------------------------------Inter slip Bonus end-----------------------------------
  404.  
  405. -------------------------------AVAILABLE_PREPAID_SIM START-----------------------------------
  406.  
  407. CREATE TABLE AVAILABLE_PREPAID_SIM(
  408. SIM_NO VARCHAR2(100) PRIMARY KEY
  409. );
  410.  
  411. --************ PROCEDURE CAN MAKE 1000 RANDOM NUMBER AT A TIME 019 + '1-5' + XXXXXXX = PREPAID NO.
  412.  
  413.  
  414. -------------------------------AVAILABLE_PREPAID_SIM END-----------------------------------
  415.  
  416. -------------------------------AVAILABLE_POSTPAID_SIM START-----------------------------------
  417.  
  418. CREATE TABLE AVAILABLE_POSTPAID_SIM(
  419. SIM_NO VARCHAR2(100) PRIMARY KEY
  420. );
  421. --************ PROCEDURE CAN MAKE 1000 RANDOM NUMBER AT A TIME 019 + '6-9' + XXXXXXX = PREPAID NO.
  422.  
  423.  
  424. -------------------------------AVAILABLE_POSTPAID_SIM END-----------------------------------
  425.  
  426.  
  427. ----------------------------------PENDING SIM TABLE START------------------------------
  428.  
  429.  
  430.  
  431. CREATE TABLE PENDING_SIM_REQ(
  432. USER_NAME VARCHAR2(100) NOT NULL,
  433. NID_NO number CONSTRAINT USER_PK3 PRIMARY KEY,
  434. DATE_OF_BIRTH VARCHAR2(100) NOT NULL,
  435. NAME_OF_FATHER VARCHAR2(100) NOT NULL,
  436. NAME_OF_MOTHER VARCHAR2(100) NOT NULL,
  437. STREET_ADDRESS VARCHAR2(100) NOT NULL,
  438. CITY VARCHAR2(100) NOT NULL,
  439. IS_EMPLOYEE VARCHAR2(100) NOT NULL,
  440. SIM_TYPE_ID NUMBER NOT NULL,
  441. ISSUED_BY NUMBER ,
  442. CONSTRAINT PSTI_FK FOREIGN KEY (SIM_TYPE_ID) REFERENCES SIM_TYPE(TYPE_ID) ON DELETE CASCADE,
  443. CONSTRAINT PSRIB_FK FOREIGN KEY (ISSUED_BY) REFERENCES EMPLOYEES(EMPLOYEE_ID) ON DELETE CASCADE
  444. );
  445.  
  446. INSERT INTO PENDING_SIM_REQ (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,SIM_TYPE_ID,ISSUED_BY) VALUES ('Dante',1924,'May 18, 1989','Stone','Pamela','P.O. Box 317, 4049 Hendrerit Street','Beez','NO',1,101);
  447. INSERT INTO PENDING_SIM_REQ (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,SIM_TYPE_ID,ISSUED_BY) VALUES ('Tanner',1925,'Jan 2, 1920','Hammett','Hilda','P.O. Box 682, 5083 Interdum St.','Yorkton','NO',1,101);
  448. INSERT INTO PENDING_SIM_REQ (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,SIM_TYPE_ID,ISSUED_BY) VALUES ('Samuel',1926,'Sep 27, 1971','Leroy','Cherokee','471-3004 Et, St.','Gianico','NO',2,102);
  449. INSERT INTO PENDING_SIM_REQ (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,SIM_TYPE_ID,ISSUED_BY) VALUES ('Chaney',1927,'Sep 28, 1953','Tate','Deirdre','855-2842 Consectetuer Ave','Tiruvottiyur','NO',2,102);
  450. ----------------------------------PENDING SIM TABLE END------------------------------
  451.  
  452. CREATE TABLE PENDING_JOB_REQ(
  453. EMP_NAME VARCHAR2(100) NOT NULL,
  454. FATHER_NAME VARCHAR2(100) NOT NULL,
  455. MOTHER_NAME VARCHAR2(100) NOT NULL,
  456. BIRTH_DATE VARCHAR2(100) NOT NULL,
  457. STREET_ADDRESS VARCHAR2(100) NOT NULL,
  458. CITY VARCHAR2(100) NOT NULL,
  459. NID_NO NUMBER PRIMARY KEY ,
  460. EMAIL VARCHAR2(100) NOT NULL,
  461. ACC_NO NUMBER UNIQUE ,
  462. SSC_YEAR NUMBER NOT NULL ,
  463. SSC_GPA NUMBER NOT NULL ,
  464. HSC_YEAR NUMBER NOT NULL ,
  465. HSC_GPA NUMBER NOT NULL ,
  466. UNIVERSITY VARCHAR2(100) NOT NULL,
  467. CGPA NUMBER NOT NULL,
  468. EXPERIENCE VARCHAR2(100) NOT NULL,
  469. JOB_ID NUMBER NOT NULL ,
  470.  
  471. ISSUED_BY NUMBER ,
  472. HIRE_DATE TIMESTAMP ,
  473. SALARY NUMBER ,
  474.  
  475. CONSTRAINT EMP_JID_FK FOREIGN KEY (JOB_ID) REFERENCES JOBS(JOB_ID) ON DELETE CASCADE ,
  476. CONSTRAINT EMP_ISSUE_FK FOREIGN KEY (ISSUED_BY) REFERENCES EMPLOYEES(EMPLOYEE_ID) ON DELETE CASCADE
  477. );
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!