Lesson 8
Pulling It All Together: Building the Vinyl Record Shop Database

In this code‐along lesson, you will work through the steps to create a database for a Vinyl Record Shop, a small business that specializes in vinyl albums. As you go through each step of creating the database structure, you should save the step to a SQL script. By the end of this lesson, you will have pulled together all the ideas learned to this point, and you will have a script that you can use (and reuse!) to create the Vinyl Record Shop database structure, including all tables and relationships, as defined in an ERD.

---

NOTE SQL scripts can be used for a variety of things including to back up an existing database or to transfer a database to another server.

---

---

Learning Objectives

By the end of this lesson, you will be able to:

• Review a database structure
• Build a database using SQL
• Determine the primary tables within a database
• Construct the related tables within a database
• Show the tables you have created and their fields

---

As you walk through this project and code along, you will go through the following steps, which align with the objectives:

• Step 1:Examine the database's structure and organize the tables.
• Step 2:Create the database.
• Step 3:Create the primary tables.
• Step 4:Create the related tables.
• Step 5:Finalize the script.

STEP 1: EXAMINE THE STRUCTURE

Before starting to build any database, you should go through the steps to normalize the structure and create an ERD or a list of tables and fields that you can use as a road map for this process. For this lesson, this step has already been done for you. Figure 8.1 shows the ERD for the Vinyl Record Shop database that will be built.

Figure 8.1 The Vinyl Record Shop database ERD

In list format, the database structure looks like this:

• song
  • songId (PK) int
  • songTitle string(100)
  • videoUrl string(100)
  • bandId (FK) int
• songAlbum
  • songId (PK, FK) int
  • albumId (PK, FK) int
• album
  • albumId (PK) int
  • albumTitle string(100)
  • label string(50)
  • releaseDate date
  • price float(5,2)
• band
  • bandId (PK) int
  • bandName string(50)
• bandArtist
  • bandId (PK, FK) int
  • artistId (PK, FK) int
• artist
  • artistId (PK)
  • artistFirstName string(25)
  • artistLastName string(50)

---

NOTE Remember that fields in bold are not nullable.

---

The following are additional expectations for this database:

• All primary key fields are auto‐incrementing integer fields.
• The data types defined in the design steps must be converted to appropriate MySQL data types. For example, strings will use VARCHAR, and floats must use DECIMAL in MySQL.

Organize the Tables

Because of referential integrity, you must create the primary tables (tables that do not include foreign keys) before you can create the related tables that depend on existing primary keys. Scan through the tables described in the ERD or the previous list and identify the ones you believe should be created first.

The primary tables are as follows:

• album
• artist
• band

These are primary tables because they do not have any foreign keys. The related tables in the database include foreign keys that depend on other tables.

• song references band.
• songAlbum references both song and album.
• bandArtist references both band and artist.

Note that the related tables must be prioritized as well. In this case, the songAlbum table depends on the song table, so the song table must be created before you can create the songAlbum table. Because song depends on band, the band table must be created before you can create the song table.

You can create the primary tables in any order, as long as all of them are created before you create the dependent related tables. In this lesson, the tables will be created in the order they were listed earlier.

Create the Script File

The end result of this lesson will be a script that can be used to rebuild the database structure anytime it is needed. To this end, create a new file in a code editor or text editor and save the file as vinylrecordshop‐schema.sql.

Note that the term schema references the structure of the database. Including this in the filename indicates the purpose of the file. The .sql extension is used for SQL scripts. Relational database management system (RDBMS) interfaces (like MySQL Workbench) can recognize these files and open them automatically. You can run the script directly in the RDBMS interface after saving any changes.

For basic documentation, you should add your name in a comment on the first line of the script, with a current date on the second line. There are two ways to comment within a SQL script. The first way is to start a line with a double hyphen, which is simply a double dash (‐‐). This will cause the rest of the line to be treated as a comment.

 -- This is a comment in a SQL Script
 

The second way to comment is to use /* and */. The comment will start with the use of /* and then end when */ is reached. All text in between, which can be on multiple lines, will be treated as a comment. The following is an example of a multiline comment for a SQL script:

 /* 
 Script written by: John Smith
 Date written: March 21, 2023
 */
 

STEP 2: CREATE THE DATABASE

---

NOTE For the remaining steps in this lesson, you should write the SQL statement in your SQL interface (such as MySQL Workbench). You can then test your SQL to verify that it runs correctly. Once it works as expected, copy and paste it at the end of the script. You can then run the script to set up the database for the next SQL statement.

---

Before you can create tables, a database space must be created to hold the tables. In this case, name the database vinylrecordshop. You will want to be sure that you are starting with an empty database. For this reason, you want to remove an existing database with that name before creating the new one.

Run the following command in MySQL:

 DROP DATABASE vinylrecordshop;
 

This statement will delete an existing database named vinylrecordshop. If you have this database in your MySQL instance, the statement will run without problem, but if the database does not exist, MySQL will throw an error because you cannot delete a database that does not exist.

The script needs to be flexible enough that anyone can use it, even if they are creating a new database rather than replacing an existing database. For this reason, you should modify the statement to the following:

 DROP DATABASE IF EXISTS vinylrecordshop;
 

The IF EXISTS clause tells MySQL to ignore the DROP command if the named database does not exist, so it will not throw an error.

Try both statements a few times to see how they work and what they do before going on. Remember that you can use the SHOW DATABASES; statement to see a list of available databases in MySQL to verify that the vinylrecordshop database is deleted or doesn't exist.

Next, let's create the database.

 CREATE DATABASE vinylrecordshop;
 

Use SHOW DATABASES to verify that the database was created. Once its existence is confirmed, make it the active database.

 USE vinylrecordshop;
 

Add the three statements under the comments you added to the .sql file you created in step 1. Execute the script to make sure it works without error before continuing to the next step. Your vinylrecordshop‐schema.sql file should look similar to Listing 8.1.

---

LISTING 8.1
vinylrecordshop‐schema.sql with the Create Logic

 /* 
  * Script written by: John Smith
  * Date written: March 21, 2023
 */
  
 DROP DATABASE IF EXISTS vinylrecordshop;
  
 CREATE DATABASE vinylrecordshop;
  
 USE vinylrecordshop;
 

---

STEP 3: CREATE THE PRIMARY TABLES

At this point, you have a script that can delete an existing database named vinylrecordshop and replace it with a new (empty) database with the same name. The next step is to create the tables themselves. Because the organizational step was done first, you know the order in which the tables should be created to avoid problems with referential integrity. For each table, you will define the table name and all fields in the table, along with appropriate field properties (data type, size, and NULL status) and key fields.

Start with the album table, which looks like this:

• album
  • albumId (PK) int
  • albumTitle string(100)
  • label string(50)
  • releaseDate date
  • price float(5,2)

You will use the CREATE statement in MySQL to create the album table. The statement will look like the code in Listing 8.2.

---

LISTING 8.2
Creating the album Table

 CREATE TABLE album (
     albumId INT AUTO_INCREMENT,
     albumTitle VARCHAR(100) NOT NULL,
     label VARCHAR(50),
     releaseDate DATE,
     price DECIMAL(5,2),
     CONSTRAINT pk_album
         PRIMARY KEY (albumId)
 );
 

---

This statement defines the table using the expected parameters.

• The albumId field auto‐increments, which means that the database engine will automatically assign a sequential number to each new record, if no value is specified. This ensures that each record will have a different primary key value.
• The string values are defined using VARCHAR, specifying the maximum number of characters.
• releaseDate is a DATE field. In MySQL, the default format for a date is yyyy‐mm‐dd. This format is important to know when data is going to be added to the field, but right now, you can simply specify the field as DATE.
• The price is a DECIMAL field with a maximum value of 999.99, which is appropriate for the data that will be stored for this solution.
• price, releaseDate, and label are nullable fields.
• The PRIMARY KEY constraint is defined on albumId.

Use DESCRIBE to verify that the table is defined correctly. If the statement works as expected, add it at the bottom of your script, save the script, and run it to make sure it works to delete and re‐create the database and rebuild the table.

Column Order

Does the column order matter?

One of the basic rules of relational database design is that the column order is not important. That said, primary keys are generally put first because this helps speed up retrieval from the table. This placement is less important for foreign keys, but some database designers will add foreign key fields immediately after the primary key.

The CONSTRAINT definitions can also appear in any order, as long as the column the constraint references is defined first. This means the statement shown in Listing 8.3 could be used to define the album table instead of the one used in Listing 8.2.

---

LISTING 8.3
Modified SQL for Creating the album Table

 CREATE TABLE album (
     albumId INT AUTO_INCREMENT,
     CONSTRAINT pk_album
        PRIMARY KEY (albumId),
     albumTitle VARCHAR(100) NOT NULL,
     label VARCHAR(50),
     releaseDate DATE,
     price DECIMAL(5,2)
 );
 

---

Remember to put a comma after each column and constraint defined in a table (except the last one), because that is how MySQL differentiates each item in the table.

On Your Own

Use the model for the album table to create the other primary tables (artist and band) on your own. Here are the table descriptions again:

• artist
  • artistId (PK)
  • artistFirstName string(25)
  • artistLastName string(50)
• band
  • bandId (PK) int
  • bandName string(50)

Remember that you can use DESCRIBE tableName; to verify that a table's structure is correct, as well as DROP TABLE tableName; to delete an existing table if you need to rebuild it. After verifying that each CREATE TABLE statement works, add them to your SQL script.

---

NOTE If you have trouble creating the tables, you can find the complete script near the end of this lesson in Listing 8.10.

---

STEP 4: CREATE THE RELATED TABLES

The related tables in the database include foreign keys that depend on other tables. These tables and their references are as follows:

• song references band.
• songAlbum references both song and album.
• bandArtist references both band and artist.

Because these tables depend on the primary tables, the primary tables must be created first. However, the songAlbum table also depends on the song table, so the song table must be created before the songAlbum table can be created.

Create the song Table

The song table is the first that we will tackle of the three. This table includes the following fields:

• song
  • songId (PK) int
  • songTitle string(100)
  • videoUrl string(100)
  • bandId (FK) int

The first three columns include a primary key (songID), a required field (songTitle), and a nullable field (videoUrl). In Listing 8.4, the SQL script to create the table with these three fields is presented.

---

LISTING 8.4
Creating the First Three Fields in the song Table

 CREATE TABLE song (
     songId INT NOT NULL AUTO_INCREMENT,
     songTitle VARCHAR(100) NOT NULL,
     videoUrl VARCHAR(100),
     CONSTRAINT pk_song
        PRIMARY KEY (songId)
 );
 

---

You can see in the script that each of the three fields is created with its data type and the attributes defined for the field. The songID is automatically incremented and is required. The songTitle is also required as indicated with NOT NULL and can be up to 100 characters long. The video URL is simply defined as a string of up to 100 characters, and because NOT NULL isn't included, you know the field is optional. The last of the code defines the primary key using songId.

The last column is a foreign key that references the bandId field in the band table. If you have not yet created the band table, you must do so before you can define the foreign key that references that table.

In MySQL, you need to complete two steps to define a foreign key.

1. Step 1:   Define the field as a normal field in the table. In this case, add it after the videoUrl column, as shown in Listing 8.5.

   ---

   LISTING 8.5
   Adding the Normal Field to the Table

    DROP TABLE IF EXISTS song;
    CREATE TABLE song (
        songId INT NOT NULL AUTO_INCREMENT,
        songTitle VARCHAR(100) NOT NULL,
        videoUrl VARCHAR(100),
        bandId INT NOT NULL,
        CONSTRAINT pk_song
           PRIMARY KEY (songId)
    );
    

   ---

2. Step 2:  Add a foreign key constraint following the primary key constraint. This tells MySQL to enforce referential integrity on the bandId field in this table so that any value entered in this field in the song table must first exist in the band table. Add this after the primary key constraint, as shown in Listing 8.6.

   ---

   Listing 8.6
   The song Table Script with the Added Foreign Key Constraint

    DROP TABLE IF EXISTS song;
    CREATE TABLE song (
        songId INT NOT NULL AUTO_INCREMENT,
        songTitle VARCHAR(100) NOT NULL,
        videoUrl VARCHAR(100),
        bandId INT NOT NULL,
        CONSTRAINT pk_song
           PRIMARY KEY (songId),
        CONSTRAINT fk_song_band
           FOREIGN KEY (bandID)
           REFERENCES band(bandId)
    );
    

   ---

Note that the foreign key constraint is named using both tables: the current table and the primary table. This ensures that the constraint name is unique, but it also helps document its purpose.

Verify that the table exists and that it includes the appropriate columns and settings. You can then add the code including the CREATE TABLE statement to your script.

Create the songAlbum Table

Now you're ready for the songAlbum table, which includes the following fields:

• songAlbum
  • songId (PK, FK) int
  • albumId (PK, FK) int

Note the following:

• The table has a composite key: the primary key includes both fields.
• Both of the fields are foreign keys related to separate tables.

Start by defining the columns. While both columns are included in the primary key, their values depend on the related fields in the song and album tables. This means you do not want MySQL to number the columns automatically, so just define them as integers, as shown in Listing 8.7.

---

LISTING 8.7
The Initial songAlbum Table Script with Columns Defined as Integers

 CREATE TABLE songAlbum (
     songId INT,
     albumId INT
 );
 

---

The fields could be specified as being required, but because they are included in the primary key, entity integrity will enforce this. The next step is to add the primary key constraint. When the primary key is a single field, just that field is added into the constraint. For a composite key, all fields are listed in the primary key, separated by commas.

While an ALTER TABLE statement could be used here, the goal is to have a script that can rebuild the database. As such, drop the existing table and rebuild it to include the primary key, as shown in Listing 8.8.

---

LISTING 8.8
The songAlbum Table Script with Primary Key Contraint

 DROP TABLE IF EXISTS songAlbum;
 CREATE TABLE songAlbum (
     songId INT,
     albumId INT,
     CONSTRAINT pk_songAlbum
        PRIMARY KEY (songId, albumId)
 );
 

---

Finally, both foreign key constraints need to be added, as shown in Listing 8.9.

---

LISTING 8.9
The full songAlbum Table Script with Foreign Key Contraints Added

 DROP TABLE IF EXISTS songAlbum;
 CREATE TABLE songAlbum (
     songId INT,
     albumId INT,
     CONSTRAINT pk_songAlbum
        PRIMARY KEY (songId, albumId),
     CONSTRAINT fk_songAlbum_song
        FOREIGN KEY (songId)
        REFERENCES song(songId),
     CONSTRAINT fk_songAlbum_album
        FOREIGN KEY (albumId)
        REFERENCES album(albumId)
 );
 

---

Once again, verify that the table exists and that it includes the appropriate columns and settings. Once you've confirmed this, add the code to the vinylrecordshop‐schema.sql script.

Create the bandArtist Table on Your Own

Use the model for the songAlbum table to create the bandArtist table. The model is as follows:

• bandArtist
  • bandId (PK, FK) int
  • artistId (PK, FK) int

Add the statement to your script once you have verified that it works as expected. If you have trouble creating the SQL for this, you can find the code included in the full solution presented in Listing 8.10.

STEP 5: FINALIZE THE SCRIPT

At this point, you should have a complete script that will perform the following tasks:

• Drop the vinylrecordstore database, if it exists.
• Re‐create the database.
• Use the database.
• Define all tables in the database, including the appropriate primary and foreign key fields.
  • album
  • artist
  • band
  • song
  • songAlbum
  • bandArtist

Verify that you can run the script in MySQL. Most database systems (including MySQL Workbench) include an option to open and run a .sql file, but if you can't find it easily, you can open the script you just created, select and copy everything in the file, and then paste the script at a MySQL prompt.

After running the script, use SHOW TABLES and DESCRIBE to verify that all the tables exist and that their structure matches that of the ERD included at the beginning of this exercise. Listing 8.10 shows the version of the completed script.

---

LISTING 8.10
The Complete vinylrecordshop‐schema.sql Script

 /* 
  * Script written by: John Smith
  * Date written: March 21, 2023
  */
 -- Running this script will DELETE the existing database and all data-- it contains.
 -- Use with caution.
  
 DROP DATABASE IF EXISTS vinylrecordshop;
  
 CREATE DATABASE vinylrecordshop;
  
 USE vinylrecordshop;
  
 CREATE TABLE album (
    albumId INT AUTO_INCREMENT,
    albumTitle VARCHAR(100) NOT NULL,
    label VARCHAR(50),
    releaseDate DATE,
    price DECIMAL(5,2),
    CONSTRAINT pk_album 
       PRIMARY KEY (albumId)
 );
  
 CREATE TABLE artist (
    artistId INT NOT NULL AUTO_INCREMENT,
    fname VARCHAR(25) NOT NULL,
    lname VARCHAR(50) NOT NULL,
  
    CONSTRAINT pk_artist 
       PRIMARY KEY (artistId)
 );
  
 CREATE TABLE band (
    bandId INT AUTO_INCREMENT,
    bandName VARCHAR(50) NOT NULL,
    CONSTRAINT pk_band 
       PRIMARY KEY (bandId)
 );
  
 CREATE TABLE song (
    songId INT NOT NULL AUTO_INCREMENT,
    songTitle VARCHAR(100) NOT NULL,
    videoUrl VARCHAR(100),
    bandId INT NOT NULL,
    CONSTRAINT pk_song 
       PRIMARY KEY (songId),
    CONSTRAINT fk_song_band 
       FOREIGN KEY (bandID)
       REFERENCES band(bandId)
 );
  
 CREATE TABLE songAlbum (
    songId INT,
    albumId INT,
    CONSTRAINT pk_songAlbum 
       PRIMARY KEY (songId, albumId),
    CONSTRAINT fk_songAlbum_song
       FOREIGN KEY (songId)
       REFERENCES song(songId),
    CONSTRAINT fk_songAlbum_album
       FOREIGN KEY (albumId)
       REFERENCES album(albumId)
 );
  
 CREATE TABLE bandArtist (
    bandId INT,
    artistId INT,
    CONSTRAINT pk_bandArtist 
       PRIMARY KEY (bandId, artistId),
    CONSTRAINT fk_bandArtist_band 
       FOREIGN KEY (bandId)
       REFERENCES band(bandId),
    CONSTRAINT fk_bandArtist_artist 
       FOREIGN KEY (artistId)
       REFERENCES artist (artistId)
 );
 

---

SUMMARY

This lesson has pulled together what you learned in previous lessons and walked you through the process of converting an ERD into SQL code for creating a database and its tables for a Vinyl Record Shop, a small business that specializes in vinyl albums. Because you created the code in a .sql file, you now have a script that can be shared with others to create the database. Of course, you should be careful because you also included code to drop any existing database by the same name, which will clear any data as well.