1
\$\begingroup\$

The objective is to implement basic mySQL and relational principles, like how to handle and query one-to-many and many-to-many relationships.

The database consists of 5 tables:

  1. car (car_id, model_id, plate)
  2. model (model_id, maker_id, name)
  3. maker (maker_id, name)
  4. feature (feature_id, description)
  5. car_feature (car_id, feature_id)

The one-to-many relationships between car-model and model-maker is dealt with foreign keys. And the many-to-many relationship is dealt with the bridge table car_feature.

It looks like this:

Entity-Relationship Diagram

The objective of the review is to see if any of these processes can be improved (written more concisely, optimized, using common conventions, etc):

  1. Design of the database (the relationships, not the columns, since the later are just examples and not important to the exercise).
  2. Creation of the database.
  3. Population of the database.
  4. Query the database for cars and features.

In general, to check if there's reasons to do things in another way. For example, ¿would you deal with the many-to-many relationship in another way?, or ¿are the joins used correctly, would you do it differently?


Creating the database

Create database

CREATE DATABASE car_dealership;

Create feature table

CREATE TABLE feature (
    feature_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    description VARCHAR(128) NOT NULL,
    PRIMARY KEY (feature_id) );

Create maker table

CREATE TABLE maker (
    maker_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(128) NOT NULL,
    PRIMARY KEY (maker_id) );

Create model table

CREATE TABLE model (
    model_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    maker_id SMALLINT UNSIGNED NOT NULL,
    name VARCHAR(128) NOT NULL,
    PRIMARY KEY (model_id),
    FOREIGN KEY fk_maker (maker_id) REFERENCES maker (maker_id) );

Create car table

CREATE TABLE car (
    car_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    model_id SMALLINT UNSIGNED NOT NULL,
    plate CHAR(16),
    PRIMARY KEY (car_id),
    FOREIGN KEY fk_model (model_id) REFERENCES model (model_id) );

Create car_feature table (bridge table)

CREATE TABLE car_feature (
    car_id INT UNSIGNED NOT NULL,
    feature_id SMALLINT UNSIGNED NOT NULL,
    FOREIGN KEY fk_car (car_id) REFERENCES car (car_id),
    FOREIGN KEY fk_feature (feature_id) REFERENCES feature (feature_id),
    PRIMARY KEY (car_id, feature_id) );

Populating the database

Add makers

INSERT INTO maker VALUES
    (NULL, "Audi"), (NULL, "Mercedes"), (NULL, "Honda");

Add models

INSERT INTO model (maker_id, name) VALUES
    (3, "Civic"), (1, "A8"), (2, "Class-M");

Add features

INSERT INTO feature (description) VALUES 
    ("stereo"), ("internet"), ("air conditioning");

Add cars

INSERT INTO car (model_id, plate) VALUES
    (2, "KACA672"), (1, "TER814"), (3, "GWN294");

Add features to cars

INSERT INTO car_feature VALUES
    (1, 3), (1, 2),
    (2, 2),
    (3, 1), (3, 2), (3, 3);

Querying the database

Here we want three simple queries:

  1. List all cars as plate, model, and maker.
  2. List all cars and their features as car, feature.
  3. List all features of a specific car.

Query to list all cars as plate, model, and maker

SELECT plate, model.name AS model, maker.name AS maker
    FROM car
    INNER JOIN model ON car.model_id = model.model_id
    INNER JOIN maker ON model.maker_id = maker.maker_id;
+---------+---------+----------+
| plate   | model   | maker    |
+---------+---------+----------+
| TER814  | Civic   | Honda    |
| KACA672 | A8      | Audi     |
| GWN294  | Class-M | Mercedes |
+---------+---------+----------+

Query to list all cars and their features

SELECT car.plate, feature.description
    FROM car
    INNER JOIN car_feature ON car.car_id = car_feature.car_id
    INNER JOIN feature ON car_feature.feature_id = feature.feature_id
    ORDER BY plate;
+---------+------------------+
| plate   | description      |
+---------+------------------+
| GWN294  | stereo           |
| GWN294  | air conditioning |
| GWN294  | internet         |
| KACA672 | air conditioning |
| KACA672 | internet         |
| TER814  | internet         |
+---------+------------------+

Query to list the features of a specific car

SELECT car.plate, feature.description
    FROM car
    INNER JOIN car_feature ON car.car_id = car_feature.car_id
    INNER JOIN feature ON car_feature.feature_id = feature.feature_id
    WHERE car.plate = "KACA672";
+---------+------------------+
| plate   | description      |
+---------+------------------+
| KACA672 | internet         |
| KACA672 | air conditioning |
+---------+------------------+

What do you think? All feedback is welcome, and I'm specially interested in seeing if the many-to-many relationship is being dealt with correctly and the correct use of joins in the queries, or perhaps a better way of constructing queries.

\$\endgroup\$
1
\$\begingroup\$

I'm pleased to see you included DDL and dataload SQL. This makes reviewing a bliss.

Design

There isn't really much I can say about this design. You wondered whether your many-to-many relation is well being dealt with. Well, you created a composite primary key, which is correct for this design because a car and a feature are not strongly dependent on each other. In other words, a car can have many features and a feature can be deployed on numerous cars.

PRIMARY KEY (car_id, feature_id)

It is also correct to create a foreign key for each of the fields that make up the primary key.

FOREIGN KEY fk_car (car_id) REFERENCES car (car_id),
FOREIGN KEY fk_feature (feature_id) REFERENCES feature (feature_id),

In addition, I would like to comment on the choice of names. You use the table name in the name of the primary key (feature -> feature_id). I find this good practice. It adds readability, specially when you reuse the same name for foreign keys to this field in other tables.

INNER JOIN feature ON car_feature.feature_id = feature.feature_id

One extension on the design you could make, is to add a table model_feature to describe which features are compatible with a model. This way, car_feature could have a constraint on model_feature.

Formatting

I would prefer more symmetrical alignment of statements, again for readability.

CREATE TABLE maker (
    maker_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(128) NOT NULL,
    PRIMARY KEY (maker_id) 
);
REATE TABLE maker (
maker_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(128) NOT NULL,
PRIMARY KEY (maker_id) );

Or with insert statements:

INSERT INTO car (model_id, plate) VALUES
    (2, "KACA672")
  , (1, "TER814")
  --, (3, "GWN294") this makes it easier to comment out lines
;
INSERT INTO car (model_id, plate) VALUES
    (2, "KACA672"), (1, "TER814"), (3, "GWN294");
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.