Exercice 4 linkedin

gistfile1.txt

USE linkedin;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(100),
    username VARCHAR(100) UNIQUE,
    password VARCHAR(100)
);

CREATE TABLE schools (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    type VARCHAR(100),
    location VARCHAR(100),
    year_founded INT
);

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

CREATE TABLE user_connections (
    user_id1 INT,
    user_id2 INT,
    PRIMARY KEY (user_id1, user_id2),
    FOREIGN KEY (user_id1) REFERENCES users(id),
    FOREIGN KEY (user_id2) REFERENCES users(id)
);

CREATE TABLE user_schools (
    user_id INT,
    school_id INT,
    start_date DATE,
    end_date DATE,
    degree VARCHAR(50),
    PRIMARY KEY (user_id, school_id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (school_id) REFERENCES schools(id)
);

CREATE TABLE user_companies (
    user_id INT,
    company_id INT,
    start_date DATE,
    end_date DATE,
    title VARCHAR(100),
    PRIMARY KEY (user_id, company_id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (company_id) REFERENCES companies(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, year_founded) VALUES 
('Harvard University', 'University', 'Cambridge, Massachusetts', 1636);

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

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

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