Auto Shop Database

In the following example - Database for an auto shop business, we have a list of departments, employees, customers and customer cars. We are using foreign keys to create relationships between the various tables.

Live example: SQL fiddle

Relationships between tables

Each Department may have 0 or more Employees
Each Employee may have 0 or 1 Manager
Each Customer may have 0 or more Cars

Departments

| Id | Name | |:—|:——| | 1 | HR | | 2 | Sales | | 3 | Tech |

SQL statements to create the table:

CREATE TABLE Departments (
    Id INT NOT NULL AUTO_INCREMENT,
    Name VARCHAR(25) NOT NULL,
    PRIMARY KEY(Id)
);

INSERT INTO Departments
    ([Id], [Name])
VALUES
    (1, 'HR'),
    (2, 'Sales'),
    (3, 'Tech')
;

Employees

| Id | FName | LName | PhoneNumber | ManagerId | DepartmentId | Salary | HireDate | |:—|:–––––|:———|:————|:–––––|:———––|:—––|:———–| | 1 | James | Smith | 1234567890 | NULL | 1 | 1000 | 01-01-2002 | | 2 | John | Johnson | 2468101214 | 1 | 1 | 400 | 23-03-2005 | | 3 | Michael | Williams | 1357911131 | 1 | 2 | 600 | 12-05-2009 | | 4 | Johnathon | Smith | 1212121212 | 2 | 1 | 500 | 24-07-2016 |

SQL statements to create the table:

CREATE TABLE Employees (
    Id INT NOT NULL AUTO_INCREMENT,
    FName VARCHAR(35) NOT NULL,
    LName VARCHAR(35) NOT NULL,
    PhoneNumber VARCHAR(11),
    ManagerId INT,
    DepartmentId INT NOT NULL,
    Salary INT NOT NULL,
    HireDate DATETIME NOT NULL,
    PRIMARY KEY(Id),
    FOREIGN KEY (ManagerId) REFERENCES Employees(Id),
    FOREIGN KEY (DepartmentId) REFERENCES Departments(Id)
);

INSERT INTO Employees
    ([Id], [FName], [LName], [PhoneNumber], [ManagerId], [DepartmentId], [Salary], [HireDate])
VALUES
    (1, 'James', 'Smith', 1234567890, NULL, 1, 1000, '01-01-2002'),
    (2, 'John', 'Johnson', 2468101214, '1', 1, 400, '23-03-2005'),
    (3, 'Michael', 'Williams', 1357911131, '1', 2, 600, '12-05-2009'),
    (4, 'Johnathon', 'Smith', 1212121212, '2', 1, 500, '24-07-2016')
;

Customers