SHARE
TWEET

Untitled

a guest Oct 3rd, 2017 417 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DROP TABLE SportDetails CASCADE CONSTRAINTS PURGE;
  2. DROP TABLE EventDetails CASCADE CONSTRAINTS PURGE;
  3. DROP TABLE CompetitorDetails CASCADE CONSTRAINTS PURGE;
  4. DROP TABLE CompetitorEventDetails CASCADE CONSTRAINTS PURGE;
  5.  
  6. CREATE TABLE SportDetails
  7. (
  8.     sport_id    NUMBER(2),
  9.     sport_name  VARCHAR2(20) NOT NULL UNIQUE,
  10.    
  11. CONSTRAINT SportD_PK PRIMARY KEY (sport_id)
  12. );
  13.  
  14. CREATE TABLE EventDetails
  15. (
  16.     event_id    NUMBER(2),
  17.     event_name  VARCHAR2(40) NOT NULL,
  18.     event_date  DATE NOT NULL,
  19.     sport_id    NUMBER(2) NOT NULL,
  20.    
  21. CONSTRAINT Event_PK PRIMARY KEY (event_id),
  22. CONSTRAINT Sport_Event_FK FOREIGN KEY (sport_id) REFERENCES
  23. SportDetails (sport_id)
  24. );
  25.  
  26. CREATE TABLE CompetitorDetails
  27. (
  28.     competitor_id    NUMBER(2),
  29.     competitor_name  VARCHAR2(25) NOT NULL,
  30.     competitor_email VARCHAR2(50) NOT NULL UNIQUE,
  31.     competitor_position NUMBER(1) NOT NULL,
  32.        
  33. CONSTRAINT Competitor_PK PRIMARY KEY (competitor_id),
  34. CONSTRAINT Email_Check CHECK (competitor_email LIKE '%_@__%.__%'),
  35. CONSTRAINT Position_Constraint CHECK(competitor_position BETWEEN 1 AND 8)
  36. );
  37.  
  38. CREATE TABLE CompetitorEventDetails
  39. (
  40.     competitor_id NUMBER(2) NOT NULL,
  41.     event_id NUMBER(2) NOT NULL,
  42.     CONSTRAINT comp_fk FOREIGN KEY (competitor_id) REFERENCES
  43.     CompetitorDetails (competitor_id),
  44.     CONSTRAINT event_fk FOREIGN KEY (event_id) REFERENCES
  45.     EventDetails (event_id)
  46. );
  47.  
  48. INSERT INTO SportDetails (sport_id, sport_name)
  49. VALUES (1, 'Athletics');
  50. INSERT INTO SportDetails (sport_id, sport_name)
  51. VALUES (2, 'Swimming');
  52.  
  53. INSERT INTO EventDetails (event_id, event_name, event_date, sport_id)
  54. VALUES (1, 'Mens 100M Final', TO_DATE('2016/08/14', 'YYYY/MM/DD'), 1);
  55. INSERT INTO EventDetails (event_id, event_name, event_date, sport_id)
  56. VALUES (2, 'Womens 100M Final',TO_DATE('2016/08/13', 'YYYY/MM/DD'), 1);
  57. INSERT INTO EventDetails (event_id, event_name, event_date, sport_id)
  58. VALUES (3, 'Mens 100M Freestyle Final', TO_DATE('2016/08/10', 'YYYY/MM/DD'), 2);
  59.  
  60. INSERT INTO CompetitorDetails (competitor_id, competitor_name, competitor_email, competitor_position)
  61. VALUES (1, 'Usain Bolt', 'UB@jam.com', 1);
  62. INSERT INTO CompetitorDetails (competitor_id, competitor_name, competitor_email, competitor_position)
  63. VALUES (2, 'Justin Gatlin', 'JG@usa.com', 2);
  64. INSERT INTO CompetitorDetails (competitor_id, competitor_name, competitor_email, competitor_position)
  65. VALUES (3, 'Andre De Grasse', 'ADG@cam.com', 3);
  66. INSERT INTO CompetitorDetails (competitor_id, competitor_name, competitor_email, competitor_position)
  67. VALUES (4, 'Elaine Thompson', 'ET@jam.com', 1);
  68. INSERT INTO CompetitorDetails (competitor_id, competitor_name, competitor_email, competitor_position)
  69. VALUES (5, 'Tori Bowie', 'TB@usa.com', 2);
  70. INSERT INTO CompetitorDetails (competitor_id, competitor_name, competitor_email, competitor_position)
  71. VALUES (6, 'Shelly-Ann Fraser-Price', 'SAFP@jam.com', 3);
  72. INSERT INTO CompetitorDetails (competitor_id, competitor_name, competitor_email, competitor_position)
  73. VALUES (7, 'Kyle Chambers', 'KC@aus.com', 1);
  74. INSERT INTO CompetitorDetails (competitor_id, competitor_name, competitor_email, competitor_position)
  75. VALUES (8, 'Peter Timmers', 'PT@bel.com', 2);
  76. INSERT INTO CompetitorDetails (competitor_id, competitor_name, competitor_email, competitor_position)
  77. VALUES (9, 'Nathan Adrian', 'NA@usa.com', 3);
  78.  
  79. INSERT INTO Competitor_EventDetails (competitor_id, event_id)
  80. VALUES (1, 1);
  81. INSERT INTO Competitor_EventDetails (competitor_id, event_id)
  82. VALUES (2, 1);
  83. INSERT INTO Competitor_EventDetails (competitor_id, event_id)
  84. VALUES (3, 1);
  85. INSERT INTO Competitor_EventDetails (competitor_id, event_id)
  86. VALUES (4, 2);
  87. INSERT INTO Competitor_EventDetails (competitor_id, event_id)
  88. VALUES (5, 2);
  89. INSERT INTO Competitor_EventDetails (competitor_id, event_id)
  90. VALUES (6, 2);
  91. INSERT INTO Competitor_EventDetails (competitor_id, event_id)
  92. VALUES (7, 3);
  93. INSERT INTO Competitor_EventDetails (competitor_id, event_id)
  94. VALUES (8, 3);
  95. INSERT INTO Competitor_EventDetails (competitor_id, event_id)
  96. VALUES (9, 3);
  97. commit;
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