databases-week2-Exercise

Exercise 4-linkedin.sql

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

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

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

-- Create Connections table for mutual connections between users
CREATE TABLE Connections (
    user1_id INT,
    user2_id INT,
    PRIMARY KEY (user1_id, user2_id),
    FOREIGN KEY (user1_id) REFERENCES Users(user_id),
    FOREIGN KEY (user2_id) REFERENCES Users(user_id)
);

-- Create Affiliations table for users and schools
CREATE TABLE SchoolAffiliations (
    user_id INT,
    school_id INT,
    start_date DATE NOT NULL,
    end_date DATE,
    degree VARCHAR(50),
    PRIMARY KEY (user_id, school_id),
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (school_id) REFERENCES Schools(school_id)
);

-- Create Employment table for users and companies
CREATE TABLE Employment (
    user_id INT,
    company_id INT,
    start_date DATE NOT NULL,
    end_date DATE,
    title VARCHAR(50),
    PRIMARY KEY (user_id, company_id),
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (company_id) REFERENCES Companies(company_id)
);