In this article we are going to create a hypothetical company database system with end-to-end workflow. We are going to start with Entity Relationship Diagram (ERD), then we will create database using SQL script and finally run some queries to test workflow.

You need MS SQL Server & Management Studio to run code samples given in this article.

Designing:

Let's take example of employee management system of a company. Following is the potential list of entities (tables) to store data about pretty much everything in the system.

  1. Employee
  2. Technician
  3. Engineer
  4. Secretary
  5. Salaried Employee
  6. Hourly Employee
  7. Employee Department
  8. Trade Union
  9. Works On
  10. Project
  11. Location
  12. Department
  13. Employee Department
  14. Department Location
Company Entity Relationship Diagram (ERD)
Company ERD

Schema

Following is the set of SQL command needs to be executed to create database with aforementioned ERD diagram. Open SQL Server Management Studio, create new query, copy and past following script and hit run.

Create Database CompanyDB
GO

use CompanyDB
GO
 
Create Table Employee(
ssn      char(9) Primary Key not null,
Fname    varchar(15) not null, 
Minit    varchar(1),
Lname    varchar(15) not null,  
Birth_date    date,
Address  varchar(50),
sex      char,
Superssn char(9) foreign key references Employee(ssn)
)

Create Table Department(
Dnumber int Primary Key not null,
Dname varchar(50) not null, 
)

Create Table Employee_department(
Essn char(9) not null foreign key references Employee(ssn),
Dnumber int not null foreign key references Department(Dnumber), 
)

Create Table Location(
LocationId int Primary Key not null,
Location_name varchar(50) not null, 
)

Create Table Dept_location(
Dnumber int Primary Key not null foreign key references Department(Dnumber),
LocationId int not null foreign key references Location(LocationId), 
)

Create Table Project(
Pnumber int Primary Key not null,
Pname varchar(50), 
LocationId int not null foreign key references Location(LocationId), 
Dnumber int not null foreign key references Department(Dnumber),  
)

Create Table Works_on(
Essn char(9) not null foreign key references Employee(ssn),
Pnumber int,
Hours decimal,
)

Create Table Hourly_employee(
Eid int Primary Key not null,
Essn char(9) not null foreign key references Employee(ssn),
Pay_scale decimal, 
)

Create Table Trade_union(
Eid int not null foreign key references Hourly_employee(Eid),
Union_name varchar(50), 
)

Create Table Saliried_employee(
Eid int Primary Key not null,
Essn char(9) not null foreign key references Employee(ssn),
Salary decimal, 
)

Create Table Secritary( 
Essn char(9) not null foreign key references Employee(ssn),
Typing_speed varchar(50), 
)

Create Table Technition( 
Essn char(9) not null foreign key references Employee(ssn),
Tgrade varchar(50), 
)

Create Table Engineer( 
Essn char(9) not null foreign key references Employee(ssn),
Eng_type varchar(50), 
)

Queries

Database is ready now, lets add some data to the tables & play with queries. You can use GUI to add some dummy data in each table or use SQL statements such as following.

INSERT statement can be used to add data to the table, lets see the example.

INSERT INTO Employee(SSN,Fname) values(123456,'John'); 
SQL INSERT Statement

SELECT statement are used to fetch the data from tables, lets look at the list of select statements in following code block.

Select * from Secritary
Select * from Engineer
Select * from Technition
Select * from Saliried_employee
Select * from Trade_union
Select * from Hourly_employee
Select * from Dept_location
Select * from Employee_department  
Select * from Works_on
Select * from Project
Select * from Department  
Select * from Employee  
Select * from [Location]  
SQL SELECT Statement

JOIN statements can be used to get data from two or more tables such as following:

SELECT E.Fname,E.Lname 
FROM EMPLOYEE E 
INNER JOIN WORKS_ON as W ON E.ssn=W.Essn
INNER JOIN PROJECT as P ON W.Pnumber=P.Pnumber
INNER JOIN DEPARTMENT as D on P.Dnumber=D.Dnumber
where P.Pname='ProductX'
SQL JOIN or INNER JOIN Statement

GROUP BY clause can be used to group data from two or more tables based on condition, following query show total hours per project.

SELECT P.Pnumber, sum(W.Hours) as TotalHours
FROM EMPLOYEE E 
INNER JOIN WORKS_ON as W ON E.ssn=W.Essn
INNER JOIN PROJECT as P ON W.Pnumber=P.Pnumber 
Group By P.Pnumber  
SQL GROUP BY Statement

OUTER JOIN can be expressed as following query which show result for employees who work or not work on any project.

SELECT E.Fname, E.Lname, W.Hours
FROM EMPLOYEE E 
FULL OUTER JOIN WORKS_ON as W ON E.ssn=W.Essn 
SQL OUTER JOIN Statement