Advertisement
Guest User

Untitled

a guest
Feb 20th, 2019
93
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 3.66 KB | None | 0 0
  1. --drop tables
  2. DROP TABLE BIKERECORD;
  3. DROP TABLE PART;
  4.  
  5. DROP TABLE MANUFACTURERPART;
  6.  
  7. DROP TABLE MAINTAINANCE;
  8. DROP TABLE DEALER;
  9.  
  10. DROP TABLE MANUFACTURER;
  11.  
  12. DROP TABLE RENTAL;
  13. DROP TABLE RESERVATION;
  14.  
  15. DROP TABLE CUSTOMER;
  16.  
  17.  
  18.  
  19. --Create Tables
  20. CREATE TABLE CUSTOMER ( --works
  21. CustomerID NUMBER(9) NOT NULL,
  22. CustomerName VARCHAR2(30) NOT NULL,
  23. CustomerAddress VARCHAR2(80) NOT NULL,
  24. Postcode VARCHAR2(7),
  25. CustomerNo VARCHAR2(11),
  26.  
  27. PRIMARY KEY(CustomerID)
  28. );
  29.  
  30. CREATE TABLE RESERVATION ( -- doesnt work
  31. ReservationNo NUMBER(9) NOT NULL,
  32. CustomerID NUMBER(9) NOT NULL,
  33. BikeNo NUMBER(9) NOT NULL,
  34. BikeClass VARCHAR2(15) NOT NULL,
  35. BikeSize VARCHAR2(20) NOT NULL,
  36. RentDate DATE,
  37.  
  38. PRIMARY KEY(ReservationNo),
  39. FOREIGN KEY(CustomerID) REFERENCES CUSTOMER(CustomerID),
  40. FOREIGN KEY(BikeNo) REFERENCES BIKERECORD(BikeNo)
  41. );
  42.  
  43. CREATE TABLE RENTAL ( -- doesnt work
  44. RentID NUMBER(9) NOT NULL,
  45. BikeNo NUMBER(9) NOT NULL,
  46. CustomerID NUMBER(9) NOT NULL,
  47. RentDate DATE,
  48. CustomerName VARCHAR2 (30) NOT NULL,
  49. CustomerAddress VARCHAR2(80) NOT NULL,
  50. PostCode VARCHAR2(7) NOT NULL,
  51. PhoneNo VARCHAR2(11),
  52. BikeClass VARCHAR2(9) NOT NULL,
  53. BikeSize VARCHAR2(15) NOT NULL,
  54. Price NUMBER(6,2) NOT NULL,
  55.  
  56. PRIMARY KEY(RentID),
  57. FOREIGN KEY(CustomerID) REFERENCES CUSTOMER(CustomerID),
  58. FOREIGN KEY(BikeNo) REFERENCES BIKERECORD(BikeNo)
  59. );
  60.  
  61. CREATE TABLE MANUFACTURER( -- works
  62. ManufacturerID NUMBER(9) NOT NULL,
  63. ManuAddress VARCHAR2(80) NOT NULL,
  64. ManuPostcode VARCHAR2(7) NOT NULL,
  65. ManuTelephone VARCHAR2(11) NOT NULL,
  66.  
  67. PRIMARY KEY(ManufacturerID)
  68. );
  69.  
  70. CREATE TABLE DEALER( --works
  71. DealerID NUMBER(9) NOT NULL,
  72. DealerName VARCHAR2(30) NOT NULL,
  73. DealerAddress VARCHAR2(80),
  74. DealerNo VARCHAR2(11) NOT NULL,
  75.  
  76. PRIMARY KEY(DealerID)
  77. );
  78.  
  79. CREATE TABLE MAINTAINANCE ( --doesnt work
  80. MaintainanceID NUMBER(9) NOT NULL,
  81. BikeNo NUMBER(9) NOT NULL,
  82. LastService DATE,
  83. FaultDate DATE,
  84. FaultDetails VARCHAR2(250) NOT NULL,
  85. ActionTaken VARCHAR2(250) NOT NULL,
  86. ActionDate DATE,
  87.  
  88. PRIMARY KEY(MaintainanceID),
  89. FOREIGN KEY(BikeNo) REFERENCES BIKERECORD(BikeNo)
  90.  
  91. );
  92.  
  93. CREATE TABLE MANUFACTURERPART ( --doesnt work
  94. ManufacturerID NUMBER(9) NOT NULL,
  95. PartID NUMBER(9) NOT NULL,
  96. Price NUMBER(6,2) NOT NULL,
  97.  
  98. PRIMARY KEY (ManufacturerID),
  99. FOREIGN KEY (ManufacturerID) REFERENCES MANUFACTURER(ManufacturerID),
  100. FOREIGN KEY (PartID) REFERENCES PART(PartID)
  101. );
  102.  
  103. CREATE TABLE PART ( --works
  104. PartID NUMBER(9) NOT NULL,
  105. Supplier VARCHAR2(30),
  106. Quantity NUMBER(3) NOT NULL,
  107. PartModel VARCHAR2(15) NOT NULL,
  108. PartClass VARCHAR2(9) NOT NULL,
  109.  
  110. PRIMARY KEY(PartID)
  111. );
  112.  
  113. CREATE TABLE BIKERECORD ( --works
  114. BikeNo NUMBER(9) NOT NULL,
  115. ModelID NUMBER(9) NOT NULL,
  116. ManufacturerID NUMBER(9) NOT NULL,
  117. DatePurchased DATE,
  118. Price NUMBER(6,2),
  119. BikeClass VARCHAR2(9),
  120. BikeSize VARCHAR2 (15),
  121. DealerID NUMBER(9) NOT NULL,
  122.  
  123. PRIMARY KEY(BikeNo),
  124. FOREIGN KEY(ManufacturerID) REFERENCES MANUFACTURER(ManufacturerID),
  125. FOREIGN KEY(DealerID) REFERENCES DEALER(DealerID)
  126. );
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement