MySQL Part 3: Creating Tables

-



Introduction

In this article, we will walk through how to create three (3) MySQL tables.  For more information about setting up MySQL through the goormIDE, please check out Part 1 of this series.


Create Tables and Insert Data

In Part 2 of this series, we created a database.  For more information about this article, click the link below.


In Part 3, we will create three (3) bike shop tables.  Each of these tables will have its own primary key and its own individual setup.

If you started a new MySQL session, please type the following into the Terminal and press <Enter>.

mysql-ctl cli

Please ensure that the bike_shop.sql file is selected and available.  If not, double-click it on the left-hand side.  This is where we are going to type most of our code.  If there is information saved in there, go ahead and clear out that information.  















Bikes Table

Before we create the table, we have to define which database we are using.  The first line of code below ensures we are using the bike_shop_app database.

The Bikes Table consists of four (4) columns: bike_id, model, description, and price.  Both bike_id and price can be loaded as integers - INT.  The model and description of the bike are both character-type data, so those will be loaded as VARCHARs.  The description can get quite long, so we will allow for 150 characters in that column.  

Since we want an ID for every bike model, we will also state that this data cannot be NULL, meaning the unique ID must be identified.  Lastly, we will set the bike_id as the Primary Key.

USE bike_shop_app;

DROP TABLE IF EXISTS bikes;

CREATE TABLE bikes
	(
		bike_id INT NOT NULL,
		model VARCHAR(100),
		description VARCHAR(150),
		price INT,
		PRIMARY KEY(bike_id)
	);
 
INSERT INTO bikes (bike_id, model, description, price)
 
VALUES
(1, 'Supersix Evo Black Inc.', 'Road - Elite Road - Carbon', 12790),
(2, 'Supersix Evo Hi-Mod Team', 'Road - Elite Road - Carbon', 10660),
(3, 'Supersix Evo Hi-Mod Dura Ace 1', 'Road - Elite Road - Carbon', 7990),
(4, 'Supersix Evo Hi-Mod Dura Ace 2', 'Road - Elite Road - Carbon', 5330),
(5, 'Supersix Evo Hi-Mod Utegra', 'Road - Elite Road - Carbon', 4260),
(6, 'Supersix Evo Red', 'Road - Elite Road - Carbon', 3940),
(7, 'Supersix Evo Ultegra 3', 'Road - Elite Road - Carbon', 3200),
(8, 'Supersix Evo Ultegra 4', 'Road - Elite Road - Carbon', 2660),
(9, 'Supersix Evo 105', 'Road - Elite Road - Carbon', 2240),
(10, 'Supersix Evo Tiagra', 'Road - Elite Road - Carbon', 1840);

After successfully copying and pasting the code into the goormIDE, press Ctrl+S to save the file, then, as we have done previously, go down to the Terminal section and type the following code, and press <Enter> to run the source file.

SOURCE bike_shop.sql;


Primary Key Defined

According to the article found here, https://www.mysqltutorial.org/mysql-primary-key/, "a primary key is a column or a set of columns that uniquely identifies each row in the table.  The primary key follows these rules:
  • A primary key must contain unique values. If the primary key consists of multiple columns, the combination of values in these columns must be unique.
  • A primary key column cannot have NULL values. Any attempt to INSERT or UPDATE NULL to primary key columns will result in an error. Note that MySQL implicitly adds a NOT NULL constraint to primary key columns.
  • A table can have one and only one primary key."
Although we do not do so in the code above, you can set the primary key up to auto-increment.  However, since we have that information already a part of our code, there is no need to do so.

Customers Table

The Customers table consists of two (2) columns: bike_shop_id, bike_shop_name, description, and price).  The bike_id column is loaded as an INTand the bike_shop_name is defined as a VARCHAR data type.  For this table, the bike_shop_id is the Primary Key.
 
USE bike_shop_app;

DROP TABLE IF EXISTS customers;

CREATE TABLE customers
	(
		bike_shop_id INT NOT NULL,
		bike_shop_name VARCHAR(100),
		PRIMARY KEY(bike_shop_id)
	);
 
INSERT INTO customers (bike_shop_id, bike_shop_name)
 
VALUES
(1, 'Pittsburgh Mountain Machines'),
(2, 'Ithaca Mountain Climbers'),
(3, 'Columbus Race Equipment'),
(4, 'Detroit Cycles'),
(5, 'Cincinnati Speed'),
(6, 'Louisville Race Equipment'),
(7, 'Nashville Cruisers'),
(8, 'Denver Bike Shop'),
(9, 'Minneapolis Bike Shop'),
(10, 'Kansas City 29ers'),
(11, 'New York Cycles'),
(12, 'Dallas Cycles'),
(13, 'Oklahoma City Race Equipment'),
(14, 'Albuquerque Cycles'),
(15, 'Austin Cruisers'),
(16, 'Phoenix Bi-peds'),
(17, 'Las Vegas Cycles'),
(18, 'Los Angeles Cycles'),
(19, 'San Francisco Cruisers'),
(20, 'Portland Bi-peds'),
(21, 'Seattle Race Equipment'),
(22, 'Ann Arbor Speed'),
(23, 'Philadelphia Bike Shop'),
(24, 'Providence Bi-peds'),
(25, 'New Orleans Velocipedes'),
(26, 'Miami Race Equipment'),
(27, 'San Antonio Bike Shop'),
(28, 'Wichita Speed'),
(29, 'Indianapolis Velocipedes'),
(30, 'Tampa 29ers');

Don't forget to clear out the previous code before you copy and paste the data above.  

Orderlines Table

The Orderlines Table consists of six (6) columns: order_id, order_line, order_date, customer_id, product_id, and quantity.  All columns are INT data types except for order_date, which is a date data type.

USE bike_shop_app;

DROP TABLE IF EXISTS orderlines;

CREATE TABLE orderlines
	(
	order_id INT NOT NULL,
        order_line INT NOT NULL,
	order_date DATE,
        customer_id INT,	
        product_id INT,	
        quantity INT,
	PRIMARY KEY(order_id, order_line),
        
        FOREIGN KEY (customer_id) 
        REFERENCES customers(bike_shop_id),
        
        FOREIGN KEY (product_id) 
        REFERENCES bikes(bike_id)

	);
 
INSERT INTO orderlines (order_id, order_line, order_date, customer_id, product_id, quantity)
 
VALUES
(1, 1, '2011-01-07', 2, 1, 1),
(1, 2, '2011-01-07', 2, 2, 1),
(2, 1, '2011-01-10', 10, 3, 1),
(2, 2, '2011-01-10', 10, 4, 1),
(3, 1, '2011-01-10', 6, 5, 1),
(3, 2, '2011-01-10', 6, 6, 1),
(3, 3, '2011-01-10', 6, 7, 1),
(3, 4, '2011-01-10', 6, 8, 1),
(3, 5, '2011-01-10', 6, 9, 1),
(4, 1, '2011-01-11', 22, 10, 1),
(5, 1, '2011-01-11', 8, 1, 1),
(5, 2, '2011-01-11', 8, 2, 2),
(5, 3, '2011-01-11', 8, 3, 1),
(5, 4, '2011-01-11', 8, 4, 1),
(6, 1, '2011-01-11', 16, 5, 1),
(6, 2, '2011-01-11', 16, 6, 1),
(6, 3, '2011-01-11', 16, 7, 1),
(6, 4, '2011-01-11', 16, 8, 1),
(7, 1, '2011-01-12', 9, 9, 1),
(7, 2, '2011-01-12', 9, 10, 1),
(7, 3, '2011-01-12', 9, 1, 1),
(7, 4, '2011-01-12', 9, 2, 1),
(7, 5, '2011-01-12', 9, 3, 1),
(7, 6, '2011-01-12', 9, 4, 1),
(7, 7, '2011-01-12', 9, 5, 1),
(7, 8, '2011-01-12', 9, 6, 1),
(7, 9, '2011-01-12', 9, 7, 1),
(7, 10, '2011-01-12', 9, 8, 2),
(7, 11, '2011-01-12', 9, 9, 1),
(7, 12, '2011-01-12', 9, 10, 1),
(7, 13, '2011-01-12', 9, 1, 1),
(7, 14, '2011-01-12', 9, 2, 2),
(8, 1, '2011-01-12', 16, 3, 1),
(8, 2, '2011-01-12', 16, 4, 3),
(9, 1, '2011-01-17', 25, 5, 1),
(9, 2, '2011-01-17', 25, 6, 2),
(9, 3, '2011-01-17', 25, 7, 1),
(9, 4, '2011-01-17', 25, 8, 1),
(9, 5, '2011-01-17', 25, 9, 5),
(10, 1, '2011-01-17', 20, 10, 1);


Next Article

If you made this far with no errors, congratulations.  In the next article, we will utilize various string functions to manipulate some of our data sets.

For a complete list of the entire set of MySQL-related articles, please see the links below.

MySQL Part 1: goormIDE
MySQL Part 2: Create Database
MySQL Part 3: Create Tables
MySQL Part 4: String Functions
MySQL Part 5: Refining Select Statements
MySQL Part 6: Aggregations
MySQL Part 7: Dates
MySQL Part 8: Logical Operations
MySQL Part 9: Joins


Popular posts from this blog

MySQL Part 1: Getting MySQL Set Up in goormIDE

-
Image
Introduction In this article, we will walk through how to set up the goormIDE so that you can utilize it in the examples presented later on my blog.  You can always download a local version of MySQL on your desktop and follow along as well. As a disclaimer, the data in these examples were provided by Business Science University 's DSB4 101-R: Business Analysis with R course.  If you want additional MySQL training, please check out  The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert on Udemy . Both links are provided below. https://www.business-science.io/ https://www.udemy.com/course/the-ultimate-mysql-bootcamp-go-from-sql-beginner-to-expert/ A Few Notes Before We Start The subsequent articles assume that you have a working version of MySQL already downloaded and installed.  However, this article provides links to help you get started in the goorm cloud IDE.  The benefit of using goorm is that it is cloud-based and does not require you to download...
Read more

Do Popular Market Index Returns Follow a Normal Distribution?

-
Image
Introduction Many statistical tests assume normality within the data set.  In other words, before you perform any type of parametric test, one should first view the data sets and run experiments to test this assumption.  This article will show you how to pull several market indices’ historical returns and see if they follow a normal distribution using the Shapiro-Wilk test. The main reason this article is testing the normal distribution validity of portfolio returns is to set up Monte Carlo simulations to project future retirement balances and their respective probabilities in Tableau.   To view the interactive application and estimate your own retirement savings, click here .   Sources:  Normality Test in R Analyzing Performance with tidyquant Market Index Background We were able to pull the following historical index information using a convenient package in R called tidyquant .  We will test the normality of the various benchmarks listed below...
Read more