Job Ready Python

Appendix DInstalling Vinyl DB

The Vinyl DB is a database that is used in the ETL program presented in Lessons 24 and 25 of this book. This appendix provides you with structure for the database and the code for a script you can use to create the SQL database.

DATABASE STRUCTURE

The database itself references the inventory for a vinyl record store that employees can use to look up songs, albums, and artists. The structure of the database is presented in Figure D.1.

Figure D.1 The Vinyl Database structure

In list format, the database structure looks like the following:

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)

CREATE THE DATABASE

The script in Listing D.1 creates the database. You can run this script in any MySQL interface, including MySQL Server with MySQL Workbench.

LISTING D.1Script to create Vinyl Database

-- 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; ...