⚠️ 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.
- First Name
- Last Name
- Make of the car
- 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)
|Scientist Table||data type|
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|
|Bicycle Table||data type|
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.
|ScientistCar Table||data type|
|ScientistBicyle Table||data type|
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.
|1||Tesla Model 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.
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.
Here are some screenshots that show the linking tables and a diagram of the table relationships.