Last updated Unknown

Entity Relationship Diagrams (ERDs) are a fundamental part of database design and architecture. They provide a visual representation of the data and how entities within a database relate to each other. Using ERDs can make it much easier to grasp the structure of a database without having to examine each table individually. In this section you will learn to read these diagrams, understand the relationships between tables, and apply this knowledge to create and query tables.

Click into each of the lessons below for the practice questions

# Entity Relationship Diagram Structure

# Representing Relationships

# Data Types and Nullability

# Querying from Entity Relationship Diagrams

Full List of Data Types - SQL Data Types for MySQL, SQL Server, and MS Access

Create a new database called Reservations for part 2. Make sure to USE it when writing queries.

Write the CREATE and INSERT statements for the tables below. Use your best judgement to assign appropriate data types and constraints to your columns. After each CREATE statement, write a brief comment to give your reasoning for using (or not using) constraints on each of the columns.

Create a new database called Cars for part 3. Make sure to USE it when writing queries. Create 2nd SQL file for your part 3 scripts.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

/* Create a new database first. Make sure to USE the right database! */

DROP TABLE IF EXISTS cars;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
    CustomerId INT,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    Phone VARCHAR(20) UNIQUE,

    PRIMARY KEY (CustomerId)
);

CREATE TABLE cars (
    CarId INT,
    Make VARCHAR(50) NOT NULL,
    Model VARCHAR(50) NOT NULL,
    Year INT,
    Price DECIMAL(10, 2),
    PurchaserId INT,
    
    PRIMARY KEY (CarId),
    FOREIGN KEY (PurchaserId) REFERENCES customers(CustomerId)
);

INSERT INTO customers (CustomerId, FirstName, LastName, Email, Phone) VALUES
    (1, 'Aisha', 'Patel', 'aisha.patel@email.com', '555-1234'),
    (2, 'John', 'Smith', 'john.smith@email.com', '555-5678'),
    (3, 'Mei', 'Chen', 'mei.chen@email.com', '555-9012'),
    (4, 'Kwame', 'Osei', 'kwame.osei@email.com', '555-3456'),
    (5, 'Sarah', 'Johnson', 'sarah.johnson@email.com', '555-7890');

INSERT INTO cars (CarId, Make, Model, Year, Price, PurchaserId) VALUES
    (1, 'Toyota', 'Camry', 2022, 25000.00, 1),
    (2, 'Honda', 'Civic', 2021, 22000.00, 5),
    (3, 'Ford', 'Mustang', 2023, 35000.00, NULL),
    (4, 'Chevrolet', 'Malibu', 2022, 27000.00, 3),
    (5, 'Tesla', 'Model 3', 2023, 45000.00, 3);

Without using CREATE or INSERT, write SQL commands to handle the following scenarios for this car dealership database.

1. The dealership has decided to start tracking the color of each car.
2. There was a mistake when processing a recent order. The Honda Civic was actually sold to John Smith.
3. Mei Chen has decided to return her Tesla Model 3. The dealership has agreed to take it back.
4. Aisha Patel has changed her email address. Her new email is aishap@gmail.com.
5. Due to a special promotion, the dealership has decided to reduce the price of the Ford Mustang by $2000.
6. Kwame has decided to close their account. Data privacy laws require the company to permanently remove their information.

You will have 3 deliverables for this assignment:

1. For part 1, paste in your answers to the text area or upload a PDF/Word document or a .txt or .sql file.
2. For part 2, submit a SQL script with your CREATE and INSERT statements.
3. For part 3, submit a SQL script with the table CREATEs and INSERTs provided as well as your queries for questions 1-6.

Upload / submit these files for the “M6 Practice - Entity Relationship Diagrams and Table Creation” assignment.