SHARE
TWEET

Untitled

a guest Feb 27th, 2020 99 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE People(
  2. P_Num INT NOT NULL,
  3. P_FirstName VARCHAR2(20 CHAR) NOT NULL,
  4. P_LastName VARCHAR2(20 CHAR) NOT NULL,
  5. P_Gender CHAR(1) NOT NULL,
  6. P_DOB DATE NOT NULL,
  7. P_Address VARCHAR2(150 BYTE) NOT NULL,
  8. P_Nationality VARCHAR2 (15 CHAR) NOT NULL,
  9. P_Postcode INT NOT NULL,
  10. P_Photo CHAR(1) NOT NULL,
  11. P_IC_Passport VARCHAR2(12 CHAR) NOT NULL,
  12. Primary KEY (P_NUM)
  13. );
  14.  
  15.  
  16. CREATE TABLE Customer (
  17. C_ID INT NOT NULL,
  18. C_Phone INT NOT NULL,
  19. C_Occupation VARCHAR2(20 CHAR) NOT NULL,
  20. C_EmergencyContact INT NOT NULL,
  21. C_HomeNum INT NOT NULL,
  22. C_Age NUMBER(3) NOT NULL,
  23. C_Signature CHAR(3) NOT NULL,
  24. C_PickupService CHAR(3) NOT NULL,
  25. C_DateOfJoin DATE NOT NULL,
  26. C_CriminalRec CHAR(3) NOT NULL,
  27. P_NUM INT NOT NULL,
  28. Primary KEY (C_ID),
  29. Foreign key (P_NUM) REFERENCES People(P_Num)
  30. );
  31.  
  32. CREATE TABLE Staff (
  33. S_ID INT NOT NULL,
  34. S_MaritalStatus VARCHAR2(11 CHAR) NOT NULL,
  35. S_HasVehicle CHAR(3) NOT NULL,
  36. S_HireDate DATE NOT NULL,
  37. S_LevelOfStudy VARCHAR2(20 CHAR) NOT NULL,
  38. S_EmploymentStatus VARCHAR2(9 CHAR) NOT NULL,
  39. S_AccNo VARCHAR2(20 CHAR) NOT NULL,
  40. S_BankName VARCHAR2(20 CHAR) NOT NULL,
  41. S_WorkSection VARCHAR2(15 CHAR) NOT NULL,
  42. S_Title VARCHAR2(9 CHAR) NOT NULL,
  43. P_NUM INT NOT NULL,
  44. Primary key (S_ID),
  45. Foreign key (P_NUM) REFERENCES People(P_Num)
  46. );
  47.  
  48. CREATE TABLE Class_(
  49. Class_ID INT NOT NULL,
  50. Class_Num INT NOT NULL,
  51. Class_Capacity NUMBER(2) NOT NULL,
  52. Class_Location VARCHAR2(17 CHAR) NOT NULL,
  53. Class_Name VARCHAR2(17 CHAR) NOT NULL,
  54. Class_Type VARCHAR2(17 CHAR) NOT NULL,
  55. Class_Description VARCHAR2(150 BYTE) NOT NULL,
  56. Class_Facilities VARCHAR2(150 BYTE) NOT NULL,
  57. Class_Period NUMBER(2,1) NOT NULL,
  58. Class_Time DATE NOT NULL,
  59. PRIMARY KEY (Class_ID)
  60. );
  61.  
  62. CREATE TABLE Salary_Slip (
  63. SS_Receipt INT NOT NULL,
  64. SS_HrsPerMonth NUMBER(3) NOT NULL,
  65. SS_Date DATE NOT NULL,
  66. SS_GrossSalary NUMBER(7,2) NOT NULL,
  67. SS_TotalEarning NUMBER(9,2) NOT NULL,
  68. SS_NetIncome NUMBER(7,2) NOT NULL,
  69. SS_Tax NUMBER(4,2) NOT NULL,
  70. SS_Address VARCHAR2(150 BYTE) NOT NULL,
  71. SS_Rate NUMBER(5,2) NOT NULL,
  72. SS_Bonus NUMBER(7,2) NOT NULL,
  73. S_ID INT NOT NULL,
  74. Primary Key(SS_Receipt),
  75. Foreign key (S_ID) REFERENCES Staff(S_ID)
  76. );
  77.  
  78. CREATE TABLE Certification (
  79. Cert_SerialNum INT NOT NULL,
  80. Cert_Holder VARCHAR2(40 CHAR) NOT NULL,
  81. Cert_ValidityPeriod VARCHAR(8 CHAR) NOT NULL,
  82. Cert_Type VARCHAR2(7 CHAR) NOT NULL,
  83. Cert_Description VARCHAR2(150 BYTE) NOT NULL,
  84. Cert_Signature CHAR(3) NOT NULL,
  85. Cert_Photo CHAR(3) NOT NULL,
  86. Cert_Address VARCHAR2(150 BYTE) NOT NULL,
  87. Cert_Logo CHAR(3) NOT NULL,
  88. Cert_IssuedDate DATE NOT NULL,
  89. S_ID INT NOT NULL,
  90. Class_ID INT NOT NULL,
  91. Primary key (Cert_SerialNum),
  92. Foreign key (S_ID) REFERENCES Staff(S_ID),
  93. Foreign key (Class_ID) REFERENCES Class_(Class_ID)
  94. );
  95.  
  96. CREATE TABLE Cert_Class (
  97. Cert_SerialNumber INT NOT NULL,
  98. Class_ID INT NOT NULL,
  99. Foreign key (Class_ID) REFERENCES Class_(Class_ID),
  100. Foreign key (Cert_SerialNumber) REFERENCES Certification(Cert_SerialNum)
  101. );
  102.  
  103. CREATE TABLE People_Class(
  104. P_Num INT NOT NULL,
  105. Class_ID INT NOT NULL,
  106. Foreign KEY (P_num) REFERENCES People(P_num),
  107. Foreign KEY (Class_ID) REFERENCES Class_(Class_ID)
  108. );
  109.  
  110. CREATE TABLE Module (
  111. M_ID INT NOT NULL,
  112. M_Date DATE NOT NULL,
  113. M_Time DATE NOT NULL,
  114. M_Progress VARCHAR2 (10 CHAR) NOT NULL,
  115. M_Comment VARCHAR2(100 BYTE) NOT NULL,
  116. M_Name VARCHAR2(50 BYTE) NOT NULL,
  117. M_Language VARCHAR2(15 CHAR) NOT NULL,
  118. M_Type VARCHAR2(7 CHAR) NOT NULL,
  119. M_Vol NUMBER(2) NOT NULL,
  120. M_YearOfPubl DATE NOT NULL,
  121. PRIMARY KEY (M_ID)
  122. );
  123.  
  124. CREATE TABLE Attendance (
  125. ATT_Num INT NOT NULL,
  126. ATT_Name VARCHAR2(20 CHAR) NOT NULL,
  127. ATT_Time DATE NOT NULL,
  128. ATT_Department VARCHAR2(20 CHAR) NOT NULL,
  129. ATT_Position VARCHAR(20 CHAR) NOT NULL,
  130. ATT_Rate NUMBER(5,2) NOT NULL,
  131. ATT_Workday CHAR(3) NOT NULL,
  132. ATT_TotalHour NUMBER(4,2) NOT NULL,
  133. ATT_Deduction NUMBER(7,2),
  134. ATT_BalanceDue NUMBER(7,2),
  135. Class_ID INT NOT NULL,
  136. P_Num INT NOT NULL,
  137. PRIMARY KEY (ATT_NUM),
  138. FOREIGN KEY (CLASS_ID) REFERENCES Class_(CLass_ID),
  139. FOREIGN KEY (P_num) REFERENCES People(P_num)
  140. );
  141.  
  142. CREATE TABLE Insurance (
  143. Ins_IdNum INT NOT NULL,
  144. Ins_Type VARCHAR (15 CHAR),
  145. Ins_CardNum VARCHAR2(20 CHAR) NOT NULL,
  146. Ins_Date DATE NOT NULL,
  147. Ins_Time DATE NOT NULL,
  148. Ins_AmtofDamage VARCHAR2(15 CHAR) NOT NULL,
  149. Ins_Description VARCHAR2(200 BYTE) NOT NULL,
  150. Ins_Coverage VARCHAR2(200 BYTE) NOT NULL,
  151. Ins_ExpDate DATE NOT NULL,
  152. Ins_TotalCost NUMBER(7,2),
  153. PRIMARY KEY (Ins_idNum)
  154. );
  155.  
  156. CREATE TABLE Invoice (
  157. I_Id INT NOT NULL,
  158. I_DateSent DATE NOT NULL,
  159. I_Status VARCHAR2(10 CHAR) NOT NULL,
  160. I_Type VARCHAR2(10 CHAR) NOT NULL,
  161. I_TotalAmount NUMBER(7,2),
  162. I_ModifiedBy VARCHAR2(20 CHAR) NOT NULL,
  163. I_ModifiedDate DATE NOT NULL,
  164. I_CreatedDate DATE NOT NULL,
  165. I_CreatedBy VARCHAR2(20 CHAR) NOT NULL,
  166. I_PaymentTerm VARCHAR2(200 BYTE) NOT NULL,
  167. M_ID INT NOT NULL,
  168. PRIMARY KEY (I_id),
  169. Foreign key (M_ID) REFERENCES Module(M_ID)
  170. );
  171.  
  172. CREATE TABLE Payment (
  173. PY_ID INT NOT NULL,
  174. PY_Amount NUMBER(7,2) NOT NULL,
  175. PY_Date DATE NOT NULL,
  176. PY_Type VARCHAR2(20 CHAR) NOT NULL,
  177. PY_Description VARCHAR2(150 BYTE) NOT NULL,
  178. PY_Fee NUMBER(7,2) NOT NULL,
  179. PY_Status VARCHAR2(20 CHAR) NOT NULL,
  180. PY_Method VARCHAR2(20 CHAR) NOT NULL,
  181. PY_Subtotal NUMBER(7,2) NOT NULL,
  182. PY_Change NUMBER(7,2) NOT NULL,
  183. C_ID INT NOT NULL,
  184. I_Id INT NOT NULL,
  185. M_ID INT NOT NULL,
  186. PRIMARY KEY (PY_ID),
  187. FOREIGN KEY (C_ID) REFERENCES Customer(c_ID),
  188. FOREIGN KEY (I_ID) REFERENCES Invoice(I_iD),
  189. FOREIGN KEY (M_ID) REFERENCES Module(M_id)
  190. );
  191.  
  192. CREATE TABLE Building (
  193. B_ID INT NOT NULL,
  194. B_NoOfRoom NUMBER(2) NOT NULL,
  195. B_NoOfParkingSpace NUMBER(2)  NOT NULL,
  196. B_NoOfCounter NUMBER(2) NOT NULL,
  197. B_NoOfDepartment NUMBER(2) NOT NULL,
  198. B_NoOfFurniture NUMBER(2) NOT NULL,
  199. B_NoOfRestRoom NUMBER(2) NOT NULL,
  200. B_Canteen CHAR(3) NOT NULL,
  201. B_NoOfVendingMach NUMBER(2)  NOT NULL,
  202. B_NoOfFloor NUMBER(2) NOT NULL,
  203. B_Pantry CHAR (3) NOT NULL,
  204. Ins_IdNum INT NOT NULL,
  205. PRIMARY KEY (B_ID),
  206. FOREIGN KEY (Ins_idnum) REFERENCES Insurance(Ins_idnum)
  207. );
  208.  
  209. CREATE TABLE Equipment (
  210. EQ_ID INT NOT NULL,
  211. EQ_Type VARCHAR2(15 CHAR) NOT NULL,
  212. EQ_Name VARCHAR2(20 CHAR) NOT NULL,
  213. EQ_Location VARCHAR2(30 CHAR) NOT NULL,
  214. EQ_Warranty VARCHAR2(8 CHAR) NOT NULL,
  215. EQ_Weight NUMBER(4,1) NOT NULL,
  216. EQ_Height NUMBER(3,1) NOT NULL,
  217. B_ID INT NOT NULL,
  218. PRIMARY KEY (EQ_ID),
  219. FOREIGN KEY (B_ID) REFERENCES Building(B_ID)
  220. );
  221.  
  222. CREATE TABLE GPS (
  223. GPS_ID INT NOT NULL,
  224. GPS_SerialNum VARCHAR2(8 CHAR) NOT NULL,
  225. GPS_Time DATE NOT NULL,
  226. GPS_Date DATE NOT NULL,
  227. GPS_Heading NUMBER (4,1) NOT NULL,
  228. GPS_Speed NUMBER (3) NOT NULL,
  229. GPS_Longitude VARCHAR2(11 CHAR) NOT NULL,
  230. GPS_Latitude VARCHAR2(11 CHAR) NOT NULL,
  231. GPS_Brand VARCHAR2(20 CHAR) NOT NULL,
  232. GPS_ModelNum VARCHAR2(15 CHAR) NOT NULL,
  233. EQ_ID INT NOT NULL,
  234. Ins_IdNum INT NOT NULL,
  235. PRIMARY KEY (GPS_ID),
  236. Foreign KEY (EQ_ID) REFERENCES Equipment(EQ_ID),
  237. Foreign KEY (Ins_Idnum) REFERENCES Insurance(Ins_idnum)
  238. );
  239.  
  240.  
  241.  
  242. DROP TABLE People CASCADE CONSTRAINTS;
  243. DROP TABLE Customer CASCADE CONSTRAINT;
  244. DROP TABLE STAFF CASCADE CONSTRAINTS;
  245. DROP TABLE CLASS_ CASCADE CONSTRAINTS;
  246. DROP TABLE Salary_slip;
  247. DROP TABLE Certification CASCADE CONSTRAINTS;
  248. DROP TABLE CERT_CLASS;
  249. DROP TABLE People_Class;
  250. DROP TABLE Module CASCADE CONSTRAINTS;
  251. DROP TABLE Attendance;
  252. DROP TABLE Insurance CASCADE CONSTRAINTS;
  253. DROP TABLE Invoice CASCADE CONSTRAINTS;
  254. DROP TABLE Payment;
  255. DROP TABLE BUILDING CASCADE CONSTRAINT;
  256. DROP TABLE Equipment Cascade Constraint;
  257. DROP TABLE GPS;
RAW Paste Data
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
Top