Lab | MySQL

Introduction

As a data expert you work at a car dealership company which sells new cars of various brands and models. Your company is small and new but it has branches in several countries. Since the establishment of the company your colleagues have sold several cars to the customers. Now your boss realized your company imperatively needs a database to keep the records about the cars, salespersons, customers, and invoices. Your boss trusts you very much so he assigned you the challenge to design, create, and manage the database.

Challenge 1 - Design the Database

Using pen and paper, design a database to meet the minimal requirements of your boss. The minimal information to be recorded is described below:

1. Cars - e.g. the vehicle identification number (VIN), manufacturer, model, year, and color of the cars in your company's inventory.

2. Customers - e.g. the customer ID, name, phone number, email, address, city, state/province, country, and zip/postal code of the customers.

3. Salespersons - e.g. staff ID, name, and the store at your company.

4. Invoices - e.g. the invoice number, date, car, customer, and salesperson related to each car sale.

Before solving this challenge, review your lesson about database structure and design then ask yourself:

• What entities and attributes should be included in the database?

  • For each attribute, what data type is most suitable?
  • Note that in MySQL typically each entity table should have an auto-increment numeric ID. The auto-increment ID is different from the customer ID or staff ID.
  • Also note that some attributes are required while other ones can be blank.

• What are the relations between these entities? Which relations are one-to-one vs one-to-many vs many-to-many?

Use pen and paper to create the design, take a picture with your phone and send the image to yourself. If you use software to create the database diagram, export in the image format (JPG or PNG). Either way, the image will be submitted as one of the deliverables.

Challenge 2 - Create the Database and Tables

1. Create a MySQL database for this lab. You can do so in the command line like this:

   $ mysql -u your_username -p
   mysql> CREATE DATABASE lab_mysql;
   Query OK, 1 row affected (0.02 sec)
   mysql> USE lab_mysql;
   Database changed

   Note: If you have set a password for the database user, you need to supply -p in the MySQL connection command. Once you hit enter, MySQL will ask you to type the password. If you didn't set the password for the database user, you don't need to supply -p in the connection command.

2. Now, based on the database design you created, write the SQL query to create the tables and columns. You will be using the CREATE TABLE statement for which you can find reference here.

   You can test your CREATE TABLE statement in either SQL command line or the database software you installed (e.g. Sequel Pro or MySQL Workbench). Remember after creating each table, you need to delete it in order to test the same CREATE TABLE statement again.

3. After writing and testing all the statements, create a text file with the name create.sql. At the beginning of the file, indicate which database you want to use e.g.:

   USE lab_mysql;

4. Then following the USE statement, write all your CREATE TABLE statements. You'll need one CREATE TABLE statement for each table you decide to create. Make sure to end each statement with a semicolon.

This .sql file will be one of your deliverables. A .sql file is often used by software/data engineers to automate database operations. For example, to execute all the commands in the create.sql file, you can simply execute source create.sql; in MySQL command line. Many programming languages such as Python and PHP can also execute .sql files.

Challenge 3 - Seeding the Database

The purpose of database seeding is to provide some dummy data for an empty database so that software development can be started based on the dummy data. In this challenge you will create seeding.sql which inserts dummy data rows into the tables of your new database.

You'll be using the INSERT INTO statement for this purpose. A tutorial you can refer to can be fine here.

For your convenience, we provide you some example dummy data. These dummy data may not readily work with your database depending on how you have designed your database. You may need to change them to the appropriate form.

Note: by default, the AUTO-INCREMENT attribute in MySQL starts from 1, and for these tables you'll need it to start from 0, in order to do that you'll need to run this snippet: SET sql_mode = "NO_AUTO_VALUE_ON_ZERO";

Cars

ID	VIN	Manufacturer	Model	Year	Color
0	3K096I98581DHSNUP	Volkswagen	Tiguan	2019	Blue
1	ZM8G7BEUQZ97IH46V	Peugeot	Rifter	2019	Red
2	RKXVNNIHLVVZOUB4M	Ford	Fusion	2018	White
3	HKNDGS7CU31E9Z7JW	Toyota	RAV4	2018	Silver
4	DAM41UDN3CHU2WVF6	Volvo	V60	2019	Gray
5	DAM41UDN3CHU2WVF6	Volvo	V60 Cross Country	2019	Gray

Customers

ID	Customer ID	Name	Phone	Email	Address	City	State/Province	Country	Postal
0	10001	Pablo Picasso	+34 636 17 63 82	-	Paseo de la Chopera, 14	Madrid	Madrid	Spain	28045
1	20001	Abraham Lincoln	+1 305 907 7086	-	120 SW 8th St	Miami	Florida	United States	33130
2	30001	Napoléon Bonaparte	+33 1 79 75 40 00	-	40 Rue du Colisée	Paris	Île-de-France	France	75008

Salespersons

ID	Staff ID	Name	Store
0	00001	Petey Cruiser	Madrid
1	00002	Anna Sthesia	Barcelona
2	00003	Paul Molive	Berlin
3	00004	Gail Forcewind	Paris
4	00005	Paige Turner	Mimia
5	00006	Bob Frapples	Mexico City
6	00007	Walter Melon	Amsterdam
7	00008	Shonda Leer	São Paulo

Invoices

ID	Invoice Number	Date	Car	Customer	Sales Person
0	852399038	22-08-2018	0	1	3
1	731166526	31-12-2018	3	0	5
2	271135104	22-01-2019	2	2	7

Bonus Challenge - Updating and Deleting Database Records

Now you find an error you need to fix in your existing data - in the Salespersons table, you mistakenly spelled Miami as Mimia for Paige Turner. Also, you received the email addresses of the three customers:

Name	Email
Pablo Picasso	[email protected]
Abraham Lincoln	[email protected]
Napoléon Bonaparte	[email protected]

Create update.sql to update your existing data.

In addition, you also find a duplicated car entry for VIN DAM41UDN3CHU2WVF6. You want to delete car ID #4 from the database. Create delete.sql to perform the deletion.

Deliverables

• Your database structure design diagram in the form of image.

• create.sql and seeding.sql

• [OPTIONAL] update.sql and delete.sql

Submission

1. Upon completion, add the deliverables to git, commit changes, and submit to github. Make sure you submit to your own forked repo.

   cd your-code
   git add .
   git commit -m "Lab MySQL solutions"
   git push origin master

2. Navigate to your forked repo and create a Pull Request to the class repo.

   • If you don't remember how to do this, review the lesson in the Prework titled Git Forking.
   • Also your instructor and TA will provide support to you.

3. Create a pull request with title following this format: [<lab_name>] <your_name>

   • For example, if the lab is lab-mysql and your name is Marc Pomar, your pull request name should be something like [lab-mysql] Marc Pomar.

4. If you have successfully created the pull request you are done! CONGRATS :)

References

Database seeding

MySQL Reference: Creating a Table

MySQL Reference: INSERT Syntax

MySQL Reference: UPDATE Syntax

MySQL Reference: DELETE Syntax