Saturday, 24 March 2018

RESTful Web Services Tutorial 5 - Create Employee Database in MS SQL Server

In this tutorial, we are going to work with MS SQL database to set up our Employees database.
Granted, this lesson is not a REST API lesson, but you need to learn how to store data while working with web services and this is going to be very easy and clear.


We would cover the following:



Part 1: Download and Install MS SQL Server 2017 Express Edition for free


MS SQL Server 2017 Express Edition is  free version of MS SQL Server that you can download and use for building small applications.

In this tutorial, I used MS SQL Server 2014.

You can download in from this link: https://www.microsoft.com/en-us/sql-server/sql-server-downloads
In this tutorial, I use SQL Server 2014 and it would also work perfectly.
After downloading it, follow the instructions to install it in your system. After successfully installing it, you will see SQL Server 2014 Management Studio.


Part 2: Create a new Database


1. Lauch the SQL Server 2014 Management Studio or any other version you have.
2. In the left hand side Right-Click on Databases and Select New Database as shown in Figure 1


Figure 1: Create New Database

3. In the Create New Database Wizard Enter the name of the database as EmployeeDB ( you could use any name you want)


Figure 2: New Database Wizard 

4. Click OK after you have entered the name of he database
5. Now expand Databases on the left pane, and you can see the new database you just created

To create a table
1. Expand the database you created in the previous step
2. Right-Click on Table and Select Table as shown in Figure 3

Figure 3: Creating a New Table 


The New Table window appears as shown in Figure 4. Now you can define the field that you table would contain (field is same as column)

3. Define the table according the the data provided against each of the tables. Under Column Name, enter the name of the column and under Data Type, select the data type of the column.

Figure 4: New Table Definition



Part 3: Create the Database Tables


You need to create the following tables in the database. Here we are going to use four tables just to keep it simple.
  • Employees Table
  • Departments Table
  • Countries Table
  • Addresses Table

Employees Table: This table would be the master table that holds the records of all the employees. The Employees table is related to the other Departments table, the Countries table and the Addresses table throught the DepartmentId field, the CountryId field and the AddressId field respectively.
  • Id (int)
  • Firstname (varchar(50))
  • Lastname (varchar(50))
  • Phone  (varchar(50))
  • Email  (varchar(50))
  • CountryId (int)
  • Dateofbirth (date)
  • DepartmentId (int)
  • AddressId (int)
After defining the Employees table save it with the name Employees. The table should look like shown in Figure 5

Figure 5: Employees Table Definition

Departments Table: This table holds a records of the departments in the company which employees may be assigned to. The fields in the Departments table are:
  • Id (int)
  • Name (varchar(50))
  • ManagerId (int)

Countries Table: This table holds a list of countries as well as the country code, the capital and the nationality of that country.
  • Id (int)
  • Code (varchar(3))
  • Name (varchar(50))
  • Capital (varchar(50))
  • Nationality (varchar(50))

Addresses Table: This table holds a list of addresses. The address of the employees are stored in this table. We store address in a separate table since there could be more than one employee staying in the same address. So instead of entering address for for each employee record, we have the address stored once, and the employee record relates to it using the AddressId.
  • Id (int)
  • Street (varchar(50))
  • Number (varchar(50))
  • City (varchar(50))
  • Region (varchar(50))
  • CountryId (int)

Part 4: Create the Database Diagram and the Relationships


You need to create the relationships between the tables.
For example the Employee table is related to the Department table through the DepartmentID column
In this case:
DepartmentID is a Foreign Key (FK) in the Employees table

To create relationships, follow the steps below

1. Expand Databases
2. Expand the EmployeeDB database
3. Expand Database Diagrams. If you try to expand it, it would give a warning, which asks if you would like to create the diagrams.This is shown in Figure 6.

Figure 6: Adding Database Diagram


4. Click on Yes. Now, the Database diagramming is enabled and you can add new diagram.

5. Rigtht-Click on Database Diagram and choose New Database Diagram. The Add Table dialog box is displayed containing a list of all your tables as shown in Figure 7.

Figure 7: Add Table Dialog Box


6. Then close the Add Table dialog box. Now all the tables are added to the diagram.


7. Drag the tables to rearrange it to look like Figure 8

Figure 8: Database diagram with arranged tables


8. Save the Diagram with a name (I used EmployeeDiagram)


Add the Relationships
You create relationship by draging the primary key(PK) on one table to the corresponding foreign key (FK) in the relatedt table.

For example, to create a relationship between the Employees table and the Departments table, drag the Id from the Departments table and place it on the DepartmentId in the Employee Table
If you do this correctly, the dialog box appears as shown in Figure 9

Figure 9: Creating Relationships

A second dialog box appears as shown in Figure 10.
Click OK again in this second dialog box

Figure 10: Foreign Key Relationship


Repeat the same thing for all the relationship between all the tables.
At the end, the final diagram would be as shown in Figure 11


Figure 11: Final Relationships Diagram



Part 5: Notes on Relationships


The type of relationship we created between the Departments table and the Employees table is an example of one-to-many relationship. This means one department to many employees. That is, more than one employee can have the same DepartmentId.
Examine the arrow connecting the two table. One side of the arrow is a key, this is the 'one' side'. The other side of the arrow is an 'infinity' symbol, this is the many side.
Also know that there are other types of relationships which we are not going to cover here. They are:
  • one-to-one
  • many-to-many


Part 6: Next Steps


Now you have successfully created that database we need for our REST API. So I could say congratsπŸ˜ƒπŸ˜ƒπŸ˜ƒπŸ˜ƒπŸ˜ƒ

I still need to teach you one more thing. How to generate the corresponding class diagram in Visual Studio. This is what we are going to do in the next tutorial.

Additionally, we would learn a bit about Entity Framework which allows us to sync out database with visual studio and have updates made automatically from Visual Studio without having to open SQL Server.

For now, I would like to thank you for reading.

If you have any challenge following this tutorial or you have any observation, do mention it in the comment box below.