linkedin

linkedin.sql

CREATE TABLE Users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL
);

CREATE TABLE Schools (
    school_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    type VARCHAR(50) NOT NULL,
    location VARCHAR(100) NOT NULL,
    founded_year INT NOT NULL
);


CREATE TABLE Companies (
    company_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    industry VARCHAR(50) NOT NULL,
    location VARCHAR(100) NOT NULL
);

CREATE TABLE Connections (
    connection_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id1 INT NOT NULL,
    user_id2 INT NOT NULL,
    connected_date DATE NOT NULL,
    FOREIGN KEY (user_id1) REFERENCES Users(user_id),
    FOREIGN KEY (user_id2) REFERENCES Users(user_id),
    UNIQUE (user_id1, user_id2)
);


CREATE TABLE User_School_Affiliations (
    affiliation_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    school_id INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE,
    degree_type VARCHAR(50),
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (school_id) REFERENCES Schools(school_id)
);


CREATE TABLE User_Company_Affiliations (
    affiliation_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    company_id INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE,
    title VARCHAR(100) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (company_id) REFERENCES Companies(company_id)
);


INSERT INTO Users (first_name, last_name, username, password) VALUES
('Alan', 'Garber', 'alan', 'password'),
('Reid', 'Hoffman', 'reid', 'password');

INSERT INTO Schools (name, type, location, founded_year) VALUES
('Harvard University', 'University', 'Cambridge, Massachusetts', 1636);

INSERT INTO Companies (name, industry, location) VALUES
('LinkedIn', 'Technology', 'Sunnyvale, California');

INSERT INTO User_School_Affiliations (user_id, school_id, start_date, end_date, degree_type) VALUES
(1, 1, '1973-09-01', '1976-06-01', 'BA');

INSERT INTO User_Company_Affiliations (user_id, company_id, start_date, end_date, title) VALUES
(2, 1, '2003-01-01', '2007-02-01', 'CEO and Chairman');