Week2 : Exercise3 : Hitomi

linkedin.sql

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(50),
lastname VARCHAR(50),
username VARCHAR(50),
pass VARCHAR(50)
);

CREATE TABLE schools (
id INT AUTO_INCREMENT PRIMARY KEY,
type_of_school ENUM('Elementary School', 'Middle School', 'High School', 'Lower School', 'Upper School', 'College', 'University', 'Other') NOT NULL,
location VARCHAR(100),
foundation_year INTEGER
);

CREATE TABLE users_schools (
user_id INT,
school_id INT,
start_date DATE,
end_date DATE,
type_of_degree ENUM ('BA', 'MA', 'PhD', 'Other'),
FOREIGN KEY(user_id) REFERENCES users (id),
FOREIGN KEY(school_id) REFERENCES schools (id)
);

CREATE TABLE companies (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
industry VARCHAR(50),
location VARCHAR(100)
);

CREATE TABLE users_companies (
user_id INT,
company_id INT,
start_date DATE,
end_date DATE,
title VARCHAR(50),
FOREIGN KEY(user_id) REFERENCES users (id),
FOREIGN KEY(company_id) REFERENCES companies (id)
);

CREATE TABLE connections (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id1 INT,
user_id2 INT,
UNIQUE (user_id1, user_id2),
FOREIGN KEY (user_id1) REFERENCES users (id),
FOREIGN KEY (user_id2) REFERENCES users (id)
);