MySQL Exercise: Mall Store Listing

March 15, 2018

As a kid, Carousel Mall was one of my favorite places to go.  Later on (by the time I was moving onto college), it expanded and became DestinyUSA.  This tutorial combines two of my interests, MySQL and a timeline of the mall.  Let’s begin!

Creating the Database

First, of course, let’s create our database and start using it.

CREATE DATABASE CarouselMall;
USE CarouselMall;

Creating the First Set of Tables

For our first table, let’s create one that holds a list of stores.  We’ll set the following requirements:

  • Name of store cannot be null
  • Name of store must be unique
  • Each store will be assigned an id automatically
CREATE TABLE stores (                                                     
    id int NOT NULL AUTO_INCREMENT,
    name varchar(40) NOT NULL,
    PRIMARY KEY(id),
    UNIQUE KEY(name)
);

For our second table, let’s create one that holds a list of anchor stores.  We’ll (again) set some requirements:

  • Table must use store’s id (automatically generated from the stores table)
  • Store id cannot be null
  • Store id can only be in the list once
CREATE TABLE anchors (
 store_id int NOT NULL,
 PRIMARY KEY(store_id),
 FOREIGN KEY(store_id) REFERENCES stores(id)
);

Confirming the Schema

Before we starting inserting data, let’s double check our schema:

SHOW TABLES;
+------------------------+
| Tables_in_CarouselMall |
+------------------------+
| anchors                |
| stores                 |
+------------------------+
 
 
DESCRIBE stores;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(40) | NO   | UNI | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
 
 
DESCRIBE anchors;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| store_id | int(11) | NO   | PRI | NULL    |       |
+----------+---------+------+-----+---------+-------+

Adding Some Date

Now that we have a place to store the name of stores, let’s add them!  We could either do several INSERT statements or one large INSERT statement.  Here’s the latter:

INSERT INTO stores(name) VALUES
    ("JCPenney"), ("Kaufmanns"), ("Steinbach"),
    ("Lechmere"), ("Chappells"), ("Hills"),
    ("Lord & Taylor"), ("Borders Books and Music"),
    ("Nobody Beats the Wiz"), ("CompUSA"),
    ("Best Buy"), ("Kahunaville"), ("Ames"),
    ("DSW Shoe Warehouse"), ("Bally Total Fitness"),
    ("H&M"), ("Kaufmanns Furniture Galleries"),
    ("Circuit City"), ("Sports Authority"),
    ("Steve & Barrys University Sportswear"),
    ("Macys"), ("Ultimate Electronics");

Since I have only added anchor stores at this point, I’m going to copy all of their IDs into the anchors table:

INSERT INTO anchors (SELECT id FROM stores);

Just to confirm, let’s get a list of stores:

SELECT name FROM stores;
+--------------------------------------+
| name                                 |
+--------------------------------------+
| Ames                                 |
| Bally Total Fitness                  |
| Best Buy                             |
| Borders Books and Music              |
| Chappells                            |
| Circuit City                         |
| CompUSA                              |
| DSW Shoe Warehouse                   |
| H&M                                  |
| Hills                                |
| JCPenney                             |
| Kahunaville                          |
| Kaufmanns                            |
| Kaufmanns Furniture Galleries        |
| Lechmere                             |
| Lord & Taylor                        |
| Macys                                |
| Nobody Beats the Wiz                 |
| Sports Authority                     |
| Steinbach                            |
| Steve & Barrys University Sportswear |
| Ultimate Electronics                 |
+--------------------------------------+

Modifying the Database

In 2012, Carousel Mall became DestinyUSA.  Due to that rebranding, we also have to update our database’s name.  Easy!  We’ll exit to a shell, dump the DB, create the new DB, exit back to the shell, import the dump into the new DB, and then delete the old DB.

exit (from MySQL)
mysqldump -uroot -p CarouselMall > dump.sql mysql -uroot -p (login to MySQL again) CREATE DATABASE DestinyUSA; exit (from MySQL) mysql -uroot -p DestinyUSA < dump.sql mysql -uroot -p DROP DATABASE CarouselMall;
USE DestinyUSA;

And if we do a show tables, we’ll see that our schema was imported successfully.  For obvious reasons, please double check to make sure all of your data is in the new DB before dropping the old DB.

Adding More Data

Now that our DB is ready for DestinyUSA stores, let’s add them.  We’ll again do one big INSERT statement:

INSERT INTO stores(name) VALUES
   ("Burlington Coat Factory"), ("Dicks Sporting Goods"),
   ("Saks Fifth Avenue off 5th"), ("P.F. Changs"),
   ("Dave and Busters"), ("WonderWorks"),
   ("Billy Beez Indoor Play Park"),
   ("Revolutions Entertainment"),
   ("Pole Position Indoor Go-Karting"),
   ("Regal Cinemas IMAX"), ("Regal Cinemas RPX"),
   ("Nordstrom Rack");

Since Dick’s Sporting Goods is an anchor, we’ll get its ID and add it to the anchors table:

SELECT id FROM stores WHERE name="Dicks Sporting Goods";
+----+
| id |
+----+
| 24 |
+----+
 
 
INSERT INTO anchors(store_id) VALUES (24);

Viewing Our Data

Let’s get a count of the total number of stores that are in our database:

SELECT COUNT(id) AS "Number of Stores in DB" FROM stores;
+------------------------+
| Number of Stores in DB |
+------------------------+
|                     34 |
+------------------------+

See how we used AS to change the title of the column from “COUNT(ID)” to “Number of Stores in DB?”

Now, let’s grab of list of stores that are in our database but are not anchors:

SELECT name FROM stores LEFT JOIN anchors ON (stores.id=anchors.store_id) WHERE anchors.store_id IS NULL;
+---------------------------------+
| name                            |
+---------------------------------+
| Billy Beez Indoor Play Park     |
| Burlington Coat Factory         |
| Dave and Busters                |
| Nordstrom Rack                  |
| P.F. Changs                     |
| Pole Position Indoor Go-Karting |
| Regal Cinemas IMAX              |
| Regal Cinemas RPX               |
| Revolutions Entertainment       |
| Saks Fifth Avenue off 5th       |
| WonderWorks                     |
+---------------------------------+

See how we did that?  We said to take all of the values from the LEFT table (the first table) and if its ID doesn’t appear in the anchors table, list it!

Adding More Tables

Let’s add a few more tables – one for when the store was opened, one for the category of the store, and one for a list of categories.  Data is useful for historical purposes while category is useful for displaying the stores by category on a website or display.

For our year_opened table:

  • Store ID must be used
  • Year cannot be null

For our categories table:

  • Category ID will be autogenerated and cannot be null
  • Description cannot be null
  • Primary key is the category ID while the description is also a unique value

For our store_category table:

  • Store ID must be used
  • Category ID must be used
  • Store ID can only be listed once
CREATE TABLE year_opened (
    store_id int NOT NULL,
    year YEAR NOT NULL,
    PRIMARY KEY(store_id),
    FOREIGN KEY(store_id) REFERENCES stores(id)
);
 
CREATE TABLE categories (
   id int NOT NULL AUTO_INCREMENT,
   description varchar(15) NOT NULL,
   PRIMARY KEY(id),
   UNIQUE(description)
);
 
CREATE TABLE store_category (
   store_id int NOT NULL,
   cat_id int NOT NULL,
   PRIMARY KEY(store_id),
   FOREIGN KEY(store_id) REFERENCES stores(id),
   FOREIGN KEY(cat_id) REFERENCES categories(id)
);

Adding More Data

Let’s add some categories:

INSERT INTO categories(description) VALUES
    ("Entertainment"), ("Food"), ("Clothing"),
    ("Other"), ("Technology"), ("Sports"),
    ("Furniture");

Let’s assign some categories:

SELECT id, description FROM categories;
+----+---------------+
| id | description   |
+----+---------------+
|  1 | Entertainment |
|  2 | Food          |
|  3 | Clothing      |
|  4 | Other         |
|  5 | Technology    |
|  6 | Sports        |
|  7 | Furniture     |
+----+---------------+
 
 
INSERT INTO store_category(store_id, cat_id) VALUES (1,2), (2,2), (10,5);

Lastly, let’s assign some opening years:

INSERT INTO year_opened(store_id, year) VALUES (1,1990), (2,1990), (7,1994), (9,1996), (10,1998), (13,1999), (16,2000);

Viewing the Data

List of stores that opened in 1990:

SELECT stores.name
    FROM stores
    JOIN year_opened ON (stores.id=year_opened.store_id)
    WHERE year=1990;
+-----------+
| name      |
+-----------+
| JCPenney  |
| Kaufmanns |
| Steinbach |
| Lechmere  |
| Chappells |
| Hills     |
+-----------+

List of entertainment stores:

SELECT stores.name
    FROM stores
    JOIN store_category ON (stores.id=store_category.store_id)
    JOIN categories ON (store_category.cat_id=categories.id)
    WHERE categories.description="Entertainment";
+---------------------------------+
| name                            |
+---------------------------------+
| Borders Books and Music         |
| WonderWorks                     |
| Billy Beez Indoor Play Park     |
| Revolutions Entertainment       |
| Pole Position Indoor Go-Karting |
| Regal Cinemas IMAX              |
| Regal Cinemas RPX               |
+---------------------------------+

And, last but not least, a list of clothing stores that opened in 1990:

SELECT stores.name
    FROM stores
    JOIN store_category ON (stores.id=store_category.store_id)
    JOIN categories ON (store_category.cat_id=categories.id)
    JOIN year_opened ON (stores.id=year_opened.store_id)
    WHERE ( categories.description="Clothing" AND year_opened.year=1990);
+-----------+
| name      |
+-----------+
| JCPenney  |
| Kaufmanns |
+-----------+

That about does it for this lengthy MySQL tutorial!

 
Tags
SQL MySQL

©2024 Tyler Wright