2U892rdm

1-- Initializes database and tables.
2-- To run: `cat initialize-database.sql | mysql -u root -p`
3
4DROP DATABASE IF EXISTS euphoria;
5
6GRANT USAGE ON *.* TO 'euphoria'@'localhost';
7DROP USER 'euphoria'@'localhost';
8
9CREATE DATABASE euphoria;
10
11USE euphoria;
12
13SET GLOBAL time_zone = '+0:00';
14
15CREATE USER 'euphoria'@'localhost';
16
17GRANT ALL PRIVILEGES ON euphoria.* To 'euphoria'@'localhost' IDENTIFIED BY 'euphoria';
18
19-- educationLevel is one of: "NOHIGHSCHOOL",
20--                           "HIGHSCHOOL",
21--                           "GED",
22--                           "SOMECOLLEGE",
23--                           "ASSOCIATES",
24--                           "BACHELORS",
25--                           "MASTERS",
26--                           "PHD",
27--                           "MD",
28--                           "JD"
29CREATE TABLE users (userId INT AUTO_INCREMENT PRIMARY KEY,
30                    name VARCHAR(40) NOT NULL,
31                    email VARCHAR (40) NOT NULL,
32                    phoneNumber VARCHAR(20) NOT NULL,
33                    educationLevel VARCHAR(20) NOT NULL,
34                    description TEXT NOT NULL,
35                    dateCreated DATETIME NOT NULL); 
36
37CREATE TABLE companies (companyId INT AUTO_INCREMENT PRIMARY KEY,
38                        name VARCHAR(40) NOT NULL,
39                        website VARCHAR (40) NOT NULL,
40                        description TEXT NOT NULL,
41                        dateCreated DATETIME NOT NULL);
42
43-- location is one of "NEWYORK",
44--                    "LONDON",
45--                    "HONGKONG",
46--                    "BERLIN",
47--                    "BEIJING",
48--                    "WASHINGTON"
49-- industry is one of "EDUCATION",
50--                    "ENERGY",
51--                    "FINANCE",
52--                    "FOOD",
53--                    "HEALTHCARE",
54--                    "INSURANCE",
55--                    "MEDIA",
56--                    "RETAIL",
57--                    "SERVICES",
58--                    "TECHNOLOGY",
59--                    "TRANSPORT",
60--                    "UTILITIES"
61-- skillLevel is one of "INTERNSHIP",
62--                      "ENTRYLEVEL",
63--                      "ASSOCIATE",
64--                      "SENIOR",
65--                      "DIRECTOR",
66--                      "EXECUTIVE"
67CREATE TABLE postings (postingId INT AUTO_INCREMENT PRIMARY KEY,
68                       companyId INT NOT NULL,  -- FOREIGN KEY(companyId) REFERENCES companies (companyId),
69                       jobTitle VARCHAR (30) NOT NULL,
70                       description TEXT NOT NULL,
71                       location VARCHAR(20) NOT NULL,
72                       industry VARCHAR(20) NOT NULL,
73                       skillLevel VARCHAR(20) NOT NULL,
74                       dateCreated DATETIME NOT NULL);
75
76INSERT INTO postings
77    (companyId, jobTitle, description, location, industry, skillLevel, dateCreated)
78VALUES
79    (123, 'Underwater Basket Weaver', 'Must lift.', 'WASHINGTON', 'SERVICES', 'INTERNSHIP', "2018-07-10 02:30:00"),
80    (456, 'Frontend Developer', 'Must know everything about React.js.', 'NEWYORK', 'TECHNOLOGY', 'EXECUTIVE', "2019-02-12 12:00:00"),
81    (789, 'Backend Developer', 'Must know nothing about React.js.', 'NEWYORK', 'TECHNOLOGY', 'INTERNSHIP', "2019-03-16 23:59:59");
82
83CREATE TABLE applications (applicationId INT AUTO_INCREMENT PRIMARY KEY,
84                           postingId INT NOT NULL,  -- FOREIGN KEY(postingId), REFERENCES postings (postingId),
85                           userId INT NOT NULL, -- FOREIGN KEY(userId), REFERENCES users (userID), 
86                           resume TEXT NOT NULL,
87                           coverLetter TEXT NOT NULL);
88
89CREATE TABLE authentication (username VARCHAR(30) NOT NULL,
90                             passwordHash VARCHAR(40) NOT NULL,
91                             isUser BOOLEAN NOT NULL);