• 0

[MySQL] Vehicle Database


Question

Create a vehicle database that:

- Contains 4 tables (make, model, color, complete_vehicle)

- Appropriately link the 4 tables together using primary and foreign keys

- Fill each table with a minimum of 5 records each

I have created the make, model, and color tables, and set each to have corresponding columns: make_id, model_id, and color_id. Now I am curious as to how I can create the complete_vehicle table and have it pull the records from the other three tables.

Any help would be greatly appreciated.

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

So far I have done the following:

CREATE DATABASE vehicle;

USE vehicle;

mysql> CREATE TABLE make (make_id SMALLINT(5) NOT NULL DEFAULT 0, make_name CHAR(128) DEFAULT NULL, PRIMARY KEY (make_id));

mysql> CREATE TABLE model (model_id SMALLINT(5) NOT NULL DEFAULT 0, model_name CHAR(128) DEFAULT NULL, PRIMARY KEY (model_id));

mysql> CREATE TABLE color (color_id SMALLINT(5) NOT NULL DEFAULT 0, color_name CHAR(128) DEFAULT NULL, PRIMARY KEY (color_id));

mysql> CREATE TABLE complete_vehicle (make_id SMALLINT(5) NOT NULL DEFAULT 0, model_id SMALLINT(5) NOT NULL DEFAULT 0, color_id SMALLINT(5) NOT NULL DEFAULT 0, complete_name CHAR(128) DEFAULT NULL, PRIMARY KEY (make_id,model_id,color_id,complete_name));

mysql> INSERT INTO make VALUES (0, "Ford");

mysql> INSERT INTO make VALUES (1, "Chevrolet");

mysql> INSERT INTO make VALUES (2, "Chrysler");

mysql> INSERT INTO make VALUES (3, "Honda");

mysql> INSERT INTO make VALUES (4, "Toyota");

mysql> INSERT INTO model VALUES (0, "Mustang");

mysql> INSERT INTO model VALUES (1, "Corvette");

mysql> INSERT INTO model VALUES (2, "Crossfire");

mysql> INSERT INTO model VALUES (3, "Civic SI");

mysql> INSERT INTO model VALUES (4, "Supra");

mysql> INSERT INTO color VALUES (0, "Black");

mysql> INSERT INTO color VALUES (1, "Red");

mysql> INSERT INTO color VALUES (2, "Blue");

mysql> INSERT INTO color VALUES (3, "Green");

mysql> INSERT INTO color VALUES (4, "White");

How do I go about reading corresponding data from each table into a new table called complete_vehicle?

Link to comment
Share on other sites

  • 0

You don't, you just insert the ID values into the complete_vehicle table...

Then when you retrieve the data, you join the tables together based on teh ID numbers and bring back the values from the child tables. This is called Database Normalisation.

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.