linkedin DB

linkedin.sql

CREATE TABLE Users (
    UserID INT AUTO_INCREMENT PRIMARY KEY,
    FirstName VARCHAR(255) NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    Username VARCHAR(255) UNIQUE NOT NULL,
    Password VARCHAR(255) NOT NULL
);

CREATE TABLE Schools (
    SchoolID INT AUTO_INCREMENT PRIMARY KEY,
    SchoolName VARCHAR(255) NOT NULL,
    SchoolType VARCHAR(255) NOT NULL,
    Location VARCHAR(255),
    YearFounded YEAR
);

CREATE TABLE Companies (
    CompanyID INT AUTO_INCREMENT PRIMARY KEY,
    CompanyName VARCHAR(255) NOT NULL,
    Industry VARCHAR(255) NOT NULL,
    Location VARCHAR(255)
);

CREATE TABLE Connections (
    ConnectionID INT AUTO_INCREMENT PRIMARY KEY,
    UserID1 INT NOT NULL,
    UserID2 INT NOT NULL,
    ConnectionDate DATE,
    FOREIGN KEY (UserID1) REFERENCES Users(UserID),
    FOREIGN KEY (UserID2) REFERENCES Users(UserID),
    UNIQUE (UserID1, UserID2)
);

CREATE TABLE SchoolAffiliations (
    AffiliationID INT AUTO_INCREMENT PRIMARY KEY,
    UserID INT NOT NULL,
    SchoolID INT NOT NULL,
    StartDate DATE NOT NULL,
    EndDate DATE,
    DegreeType VARCHAR(255),
    FOREIGN KEY (UserID) REFERENCES Users(UserID),
    FOREIGN KEY (SchoolID) REFERENCES Schools(SchoolID)
);

CREATE TABLE CompanyAffiliations (
    AffiliationID INT AUTO_INCREMENT PRIMARY KEY,
    UserID INT NOT NULL,
    CompanyID INT NOT NULL,
    StartDate DATE NOT NULL,
    EndDate DATE,
    Title VARCHAR(255),
    FOREIGN KEY (UserID) REFERENCES Users(UserID),
    FOREIGN KEY (CompanyID) REFERENCES Companies(CompanyID)
);

INSERT INTO Users (FirstName, LastName, Username, Password) VALUES 
('Alan', 'Garber', 'alan', 'password'),
('Reid', 'Hoffman', 'reid', 'password');

INSERT INTO Schools (SchoolName, SchoolType, Location, YearFounded) VALUES 
('Harvard University', 'University', 'Cambridge, Massachusetts', 1636);

INSERT INTO Companies (CompanyName, Industry, Location) VALUES 
('LinkedIn', 'Technology', 'Sunnyvale, California');

INSERT INTO SchoolAffiliations (UserID, SchoolID, StartDate, EndDate, DegreeType) VALUES 
(1, 1, '1973-09-01', '1976-06-01', 'BA');

INSERT INTO CompanyAffiliations (UserID, CompanyID, StartDate, EndDate, Title) VALUES 
(2, 1, '2003-01-01', '2007-02-01', 'CEO and Chairman');