DEV Community

Kiron Roy
Kiron Roy

Posted on • Updated on

Scientists & Transport (SQL) basics

⚠️ A new article focuses on the query tables: SQL Server Management Studio (Bicycle Example).

Structured Query Language (SQL) is a standard computer language for relational database management and data manipulation. SQL is used to query, insert, update and modify data (Techopedia).

Relational databases store data in rows and columns inside tables. Those tables have relationships between each other. To keep everything nice and tidy, databases strive for normalization. Normalization is the process of reducing or eliminating redundant data to improve data integrity.


I created a database called Transport in SQL Server Management Studio SSMS. For this article, it is not necessary to download and use SSMS. This article is just a basic sketch of a database.

The idea for this database involves Scientists and means of transport. A scientist needs a car and bicycle to travel around different labs to do scientific things. A scientist can have their own car and their own bicycle or they can share a car and bicycle with another scientist. Also, a car or bicycle can have no owner.

Here is a list of Data that will tie together:

  • Scientists
  • Cars
  • Bicycles

List with details broken down further:

  • Scientist

    • First Name
    • Last Name
  • Car

    • Make of the car
  • Bicycle

    • Make of the bicycle

The database needs to be created in a way to keep everything organized and efficient (Normalization). We can do this by creating:

  • Primary key 🔑 (for each table)
  • No Nulls (no empty values)
  • Auto increment (automatically increment each record)

Here are the 3 main tables:

Scientist Table data type
🔑 Id int
FirstName varchar(50)
LastName varchar(50)

Id is a primary key 🔑. A primary key’s main features are: It must contain a unique value for each row of data. It cannot contain null values (techopedia).

The Scientist Table also has a first and last name field for each scientist. The data type for the first and last name is a varchar or Variable Character Field. The term varchar can hold letters and numbers, in this case 50.

The two following tables follow the same pattern:

Car Table data type
🔑 Id int
CarName varchar(50)
Bicycle Table data type
🔑 Id int
BicycleName varchar(50)

The Scientist table stores information on scientists. The Car table stores information on cars. The Bicycle table stores information on bicycles. Currently, these tables have no links (relationships) with each other.

The two linking tables:


ScientistCar Table data type
🔑 Id int
ScientistId int
CarId int
ScientistBicyle Table data type
🔑 Id int
ScientistId int
BicycleId int

The ScientistCar table links scientists to the cars they have access too.
The ScientistId is a foreign key (sometimes referred to as a surrogate key). In this case, the ScientistId points to the Id key (which is the the primary key) from the Scientist Table. The CarId points to the Id key from the Car Table.

The ScientistBicyle table has the same relationship. Again, the ScientistId points to the Id key from the Scientist table and the Bicycle id points to the Id key from the Bicycle Table.

These two tables are necessary to create relationships.


Adding data to the tables

Scientist Table:

🔑 Id FirstName LastName
1 Albert Einstein
2 Nikola Tesla
3 Marie Currie

Car Table:

🔑 Id CarName
1 Tesla Model 3
2 Beetle
3 Ferrari F40
4 Lincoln Towncar

Bicycle Table:

🔑 Id BicycleName
1 Raliegh
2 Trek
3 Bianci
4 Huffy

ScientistCar Table:

🔑 Id ScientistId CarId
1 1 4
2 1 2
3 2 1
4 3 4
5 3 3

In the ScientistCar Table first row: The Scientist of id 1 from the Scientist Table is Albert Einstein. He drives a Lincoln Towncar which has an id of 4 from the Car Table.
In the fourth row: Marie Currie with an id of 3 from the Scientist Table also drives the same Lincoln Towncar (id 4 from the Car Table). Note, Marie Currie also drives a Ferrari F40 which she doesn't share. And of course Nikola Tesla drives a Tesla Model 3.

ScientistBicycle Table:

🔑 Id ScientistId BicycleId
1 1 4
2 3 4
3 2 3

Similar with the Scientist Car Table: Albert Einstein and Marie Carrie share a Huffy bicycle (Id of 4 from the Bicycle table). While Nikola Tesla id of 2 from the Scientist table has a Bianci bicycle (Id of 3 from the Bicycle table).

Note: no scientist rides a Raliegh or Trek bicycle.


SQL Server Management Studio (Screenshots)

Here are some screenshots that show the linking tables and a diagram of the table relationships.

Scientist & bicycle relationships

bikes

Scientist & car relationships

cars

Table relationships

relationship

Discussion (0)