BemUB8vN

1DROP DATABASE eventmanager;
2CREATE DATABASE eventmanager;
3USE eventmanager;
4
5DROP TABLE IF EXISTS Event;
6DROP TABLE IF EXISTS Organisation;
7DROP TABLE IF EXISTS Student;
8
9CREATE TABLE Events (
10    event_id INT PRIMARY KEY AUTO_INCREMENT,
11    name VARCHAR(50) NOT NULL,
12    description VARCHAR(160) NOT NULL,
13    event_date DATE NOT NULL
14);
15
16INSERT INTO Events VALUES(0, "Pokehackathon!", "Free for all!", STR_TO_DATE("27,09,2016", "%d,%m,%Y"));
17INSERT INTO Events VALUES(0, "Carrier Day", "Companies from Silicone Valley will be recruiting aspiring engineers, join us!", STR_TO_DATE("04,10,2016", "%d,%m,%Y"));
18INSERT INTO Events VALUES(0, "Dia de los muertos", "Honour our ancestors, Mexican style!", STR_TO_DATE("1,11,2016", "%d,%m,%Y"));
19
20
21CREATE TABLE Organisations (
22    organisation_id INT PRIMARY KEY AUTO_INCREMENT,
23    name VARCHAR(50) NOT NULL,
24    type VARCHAR(50) NOT NULL
25);
26
27INSERT INTO Organisations VALUES(0, "ASCM", "Academic");
28INSERT INTO Organisations VALUES(0, "Hermanos Unidos", "Cultural");
29INSERT INTO Organisations VALUES(0, "MESA", "Honour Society");
30
31
32CREATE TABLE Students (
33    cin INT PRIMARY KEY AUTO_INCREMENT,
34    name VARCHAR(50) NOT NULL,
35    age INT NOT NULL
36);
37
38INSERT INTO Students VALUES(0, "Erik van der Sar", 21);
39INSERT INTO Students VALUES(0, "Giovanni Garcia", 21);
40INSERT INTO Students VALUES(0, "Jessica Palacios-Recinos", 21);
41INSERT INTO Students VALUES(0, "Alejandro Bernal", 21);
42INSERT INTO Students VALUES(0, "Kimberly Rios", 22);
43INSERT INTO Students VALUES(0, "Jonathan Dominguez", 18);
44INSERT INTO Students VALUES(0, "Angie Sanchez", 21);
45
46
47CREATE TABLE RefOrganisationMembers (
48    organisation_id INT NOT NULL,
49    cin INT NOT NULL
50);
51
52INSERT INTO RefOrganisationMembers VALUES(1, 1);
53INSERT INTO RefOrganisationMembers VALUES(1, 2);
54INSERT INTO RefOrganisationMembers VALUES(1, 3);
55INSERT INTO RefOrganisationMembers VALUES(1, 4);
56INSERT INTO RefOrganisationMembers VALUES(1, 5);
57INSERT INTO RefOrganisationMembers VALUES(1, 6);
58INSERT INTO RefOrganisationMembers VALUES(1, 7);
59INSERT INTO RefOrganisationMembers VALUES(2, 1);
60INSERT INTO RefOrganisationMembers VALUES(2, 2);
61INSERT INTO RefOrganisationMembers VALUES(2, 3);
62INSERT INTO RefOrganisationMembers VALUES(2, 4);
63INSERT INTO RefOrganisationMembers VALUES(2, 5);
64INSERT INTO RefOrganisationMembers VALUES(2, 6);
65INSERT INTO RefOrganisationMembers VALUES(2, 7);
66INSERT INTO RefOrganisationMembers VALUES(3, 1);
67INSERT INTO RefOrganisationMembers VALUES(3, 2);
68INSERT INTO RefOrganisationMembers VALUES(3, 3);
69INSERT INTO RefOrganisationMembers VALUES(3, 4);
70INSERT INTO RefOrganisationMembers VALUES(3, 5);
71INSERT INTO RefOrganisationMembers VALUES(3, 6);
72INSERT INTO RefOrganisationMembers VALUES(3, 7);
73
74
75CREATE TABLE RefEventOrganisation (
76    event_id INT NOT NULL,
77    organisation_id INT NOT NULL
78);
79
80INSERT INTO RefEventOrganisation VALUES(1, 1);
81INSERT INTO RefEventOrganisation VALUES(3, 2);
82INSERT INTO RefEventOrganisation VALUES(2, 3);
83
84
85CREATE TABLE RefOrganisationPresident (
86    organisation_id INT NOT NULL,
87    cin INT NOT NULL
88);
89
90INSERT INTO RefOrganisationPresident VALUES(1, 1);
91INSERT INTO RefOrganisationPresident VALUES(2, 3);
92INSERT INTO RefOrganisationPresident VALUES(3, 2);
93
94
95CREATE TABLE RefOrganisationSecretary (
96    organisation_id INT NOT NULL,
97    cin INT NOT NULL
98);
99
100INSERT INTO RefOrganisationSecretary VALUES(1, 2);
101INSERT INTO RefOrganisationSecretary VALUES(2, 3);
102INSERT INTO RefOrganisationSecretary VALUES(3, 7);