The OpenAI ChatGPT can be used to design and create a database. We tested this out to see how far we can go with automating the process of creating tables using only natural speaking language and allowing ChatGPT to create the actual code.
The results are amazing and any MSSQL Database developer can see the time saving benefits especially when prototyping a new DB or or writing complex queries.

Please note that ChatGPT does not do “everything” for us and the T-SQL code is not always correct. We need an understanding of database, DB design and T-SQL to be sure to catch issues and make modifications where needed.

Prompt Examples:
1. Can you provide a T-SQL code to create the database in single user mode and Simple recovery
2. Please create the T-SQL code for a database to keep track of customers, their contact info, social media and history of last contact.
3. Please add company information to these tables
4. Add a field to each table to define when it was last updated
5. Please create a trigger and table to store whenever a new customer has been added
6. Recreate the table t-sql code and make all of the VARCHAR datatypes a  varchar(max)
7. Create a user defined function that join together the first and last names

In our example below we allowed the bot to handle naming conventions, all code and design. In reality you will need to review the code, naming convention and check for errors regarding things like Primary and Foreign Keys or simply spelling or camel case.

The Car Dealership Database by ChatGPT

We will create an MSSQL database using natural speaking language to define the requirements around it. In this case we used the “design” term so the AI did not produce any code at first.

Note: Complete code is provided at the bottom of this page. Actual responses will include explanations too but are not included here for brevity. 

Prompt:
act as a database developer. Please create a database design for a car dealership that includes customer information, car sales and discounts, document fees and any other tables needed to run a dealership.

Response Sample:
Sure! Here's a database design for a car dealership that includes customer information, car sales and discounts, document fees, and other necessary tables:

Entities and their attributes:

[Customers]

    Customer_ID (Primary Key)
    First_Name
    Last_Name
    Email_Address
    Phone_Number
    Address
    City
    State
    Zip_Code

[Vehicles]

    Vehicle_ID (Primary Key)
    Make
    Model
    Year
    Body_Style
    Color
    Mileage
    VIN_Number
...

Modify the Design Request

In the design we missed the option to “add” featured to a car sale So we asked the bot to include another table for this option. It recreates the entire design and includes an “Additional_Features” table.

Prompt:
Please add a table to include additional features added to the car at the time of sale by the customer

Response Sample:
...
[Additional_Features]

    Feature_ID (Primary Key)
    Feature_Name
    Feature_Price
...

Notice that the table is rather simple and does not include things like service or product and a relationship back to the car.

We asked it to make these changes and instead of changing the existing table it created 2 tables, one for the features and their prices as well as a relational table to associate the cars to multiple features also known as many-to-one and many-to-many relationship.

Prompt:
please redesign the "Additional_Features" and add a relationship to the car as well as product and service features that could be added to the car.

...
[Features]

    Feature_ID (Primary Key)
    Feature_Name
    Feature_Type
    Feature_Price

[Vehicle_Features]

    Vehicle_Feature_ID (Primary Key)
    Vehicle_ID (Foreign Key)
    Feature_ID (Foreign Key)

This is very helpful at the design phase of a new database. We can also get more granular with the AI bot and ask it to add or modify any of the designed tables including adding more fields or relationships or removing things.

But we will do that (db tales com) when as ask the Chat GPT AI bot to create the T-SQL code for this database.

Can ChatGPT Create The Tables

The bot can create T-SQL code to create the tables we just had it design for us. This is where the Database Developer can save time on design and coding. Keep in mind that we may need to modify the names, data-types or relationships but the bot did most of the writing for us so this is a great (and quick) start. Also be aware of the version of SQL or other database you are writing for (SQL 2022, MySQL 10, MariaDB).

Prompt:
please create the t-sql code for these tables

Response Sample:
...
CREATE TABLE Customers (
    Customer_ID INT PRIMARY KEY,
    First_Name VARCHAR(50),
    Last_Name VARCHAR(50),
    Email_Address VARCHAR(100),
    Phone_Number VARCHAR(20),
    Address VARCHAR(100),
    City VARCHAR(50),
    State VARCHAR(50),
    Zip_Code VARCHAR(20)
);

CREATE TABLE Vehicles (
    Vehicle_ID INT PRIMARY KEY,
    Make VARCHAR(50),
    Model VARCHAR(50),
    Year INT,
    Body_Style VARCHAR(50),
    Color VARCHAR(50),
    Mileage INT,
    VIN_Number VARCHAR(50)
);

CREATE TABLE Sales (
    Sale_ID INT PRIMARY KEY,
    Customer_ID INT FOREIGN KEY REFERENCES Customers(Customer_ID),
    Vehicle_ID INT FOREIGN KEY REFERENCES Vehicles(Vehicle_ID),
    Sale_Date DATE,
    Sale_Price DECIMAL(18, 2)
);
...

We can also request to make changes to the T-SQL code and be more specific on what we want.

Prompt: 
please add a field to each table to show when it was last updated and create the T-SQL code for this table design.

...
CREATE TABLE Sales (
    Sale_ID INT PRIMARY KEY,
    Customer_ID INT FOREIGN KEY REFERENCES Customers(Customer_ID),
    Vehicle_ID INT FOREIGN KEY REFERENCES Vehicles(Vehicle_ID),
    Sale_Date DATE,
    Sale_Price DECIMAL(18, 2),
    Last_Updated DATETIME
);

CREATE TABLE Discounts (
    Discount_ID INT PRIMARY KEY,
    Discount_Name VARCHAR(50),
    Discount_Percentage DECIMAL(18, 2),
    Last_Updated DATETIME
);
...

Create Read Update Delete

The next obvious request will be to create the CRUD statements for these tables.
Notice that the bot is using the basic T-SQL and the primary keys to create these statements.

Prompt:
Create the INSERT, UPDATE and DELETE statements for these tables

Response Sample:
INSERT INTO Customers (Customer_ID, First_Name, Last_Name, Email_Address, Phone_Number, Address, City, State, Zip_Code)
VALUES (1, 'John', 'Doe', 'johndoe@example.com', '555-1234', '123 Main St', 'Anytown', 'CA', '12345');

INSERT INTO Vehicles (Vehicle_ID, Make, Model, Year, Body_Style, Color, Mileage, VIN_Number)
VALUES (1, 'Toyota', 'Camry', 2021, 'Sedan', 'Silver', 10000, 'ABC1234567890');

...

UPDATE Discounts
SET Discount_Percentage = 15.00
WHERE Discount_ID = 1;

UPDATE Document_Fees
SET Fee_Amount = 250.00
WHERE Document_Fee_ID = 1;

UPDATE Features
SET Feature_Price = 1200.00
WHERE Feature_ID = 1;

...

DELETE FROM Sale_Document_Fees
WHERE Sale_ID = 1 AND Document_Fee_ID = 1;

DELETE FROM Sales
WHERE Sale_ID = 1;

DELETE FROM Customers
WHERE Customer_ID = 1;
...

“Add a New Car” Stored Procedure

The next step is to get our cars added to the database so we will need a procedure that does this.

Prompt:
Can you create a stored procedure to add a new car to the database.

Response:
CREATE PROCEDURE AddNewCar (
    @Make VARCHAR(50),
    @Model VARCHAR(50),
    @Year INT,
    @Body_Style VARCHAR(50),
    @Color VARCHAR(50),
    @Mileage INT,
    @VIN_Number VARCHAR(50)
)
...

Execution Example:
EXEC AddNewCar 'Toyota', 'Camry', 2023, 'Sedan', 'Silver', 0, 'ABC1234567890';

“Sell a Car” Stored Procedure

Now that we have the ability to add a car we need to be able to sell it too.

Prompt:
Can you create a stored procedure to sell one of the cars in the database

Response Sample:
CREATE PROCEDURE SellCar (
    @Sale_Date DATE,
    @Sale_Price DECIMAL(18, 2),
    @Customer_ID INT,
    @Vehicle_ID INT,
    @Discount_ID INT,
    @Document_Fee_ID INT
)
AS
BEGIN
...

Table Indexes

Now that we have a defined table structure with the bot, another item we can have Chat GPT tackle is to create logical indexes on these tables so we don’t slow down as the database grows.

Prompt:
This database could get very large, please provide indexes that should keep it running fast.

Response Sample:
...
-- Index for Customers table
CREATE INDEX IX_Customers_Last_Name ON Customers (Last_Name);

-- Index for Vehicles table
CREATE INDEX IX_Vehicles_Make_Model_Year ON Vehicles (Make, Model, Year);

-- Index for Sales table
CREATE INDEX IX_Sales_Sale_Date ON Sales (Sale_Date);
...

ChatGPT Generated – Car Dealership T-SQL Code

This is the code generated by ChatGPT based on our prompts, nothing has been modified.

CREATE TABLE Customers (
    Customer_ID INT PRIMARY KEY,
    First_Name VARCHAR(50),
    Last_Name VARCHAR(50),
    Email_Address VARCHAR(100),
    Phone_Number VARCHAR(20),
    Address VARCHAR(100),
    City VARCHAR(50),
    State VARCHAR(50),
    Zip_Code VARCHAR(20),
    Last_Updated DATETIME
);

CREATE TABLE Vehicles (
    Vehicle_ID INT PRIMARY KEY,
    Make VARCHAR(50),
    Model VARCHAR(50),
    Year INT,
    Body_Style VARCHAR(50),
    Color VARCHAR(50),
    Mileage INT,
    VIN_Number VARCHAR(50),
    Last_Updated DATETIME
);

CREATE TABLE Sales (
    Sale_ID INT PRIMARY KEY,
    Customer_ID INT FOREIGN KEY REFERENCES Customers(Customer_ID),
    Vehicle_ID INT FOREIGN KEY REFERENCES Vehicles(Vehicle_ID),
    Sale_Date DATE,
    Sale_Price DECIMAL(18, 2),
    Last_Updated DATETIME
);

CREATE TABLE Discounts (
    Discount_ID INT PRIMARY KEY,
    Discount_Name VARCHAR(50),
    Discount_Percentage DECIMAL(18, 2),
    Last_Updated DATETIME
);

CREATE TABLE Sale_Discounts (
    Sale_Discount_ID INT PRIMARY KEY,
    Sale_ID INT FOREIGN KEY REFERENCES Sales(Sale_ID),
    Discount_ID INT FOREIGN KEY REFERENCES Discounts(Discount_ID),
    Last_Updated DATETIME
);

CREATE TABLE Document_Fees (
    Document_Fee_ID INT PRIMARY KEY,
    Fee_Type VARCHAR(50),
    Fee_Amount DECIMAL(18, 2),
    Last_Updated DATETIME
);

CREATE TABLE Sale_Document_Fees (
    Sale_Document_Fee_ID INT PRIMARY KEY,
    Sale_ID INT FOREIGN KEY REFERENCES Sales(Sale_ID),
    Document_Fee_ID INT FOREIGN KEY REFERENCES Document_Fees(Document_Fee_ID),
    Last_Updated DATETIME
);

CREATE TABLE Features (
    Feature_ID INT PRIMARY KEY,
    Feature_Name VARCHAR(50),
    Feature_Type VARCHAR(20),
    Feature_Price DECIMAL(18, 2),
    Last_Updated DATETIME
);

CREATE TABLE Vehicle_Features (
    Vehicle_Feature_ID INT PRIMARY KEY,
    Vehicle_ID INT FOREIGN KEY REFERENCES Vehicles(Vehicle_ID),
    Feature_ID INT FOREIGN KEY REFERENCES Features(Feature_ID),
    Last_Updated DATETIME
);

CREATE PROCEDURE AddNewCar (
    @Make VARCHAR(50),
    @Model VARCHAR(50),
    @Year INT,
    @Body_Style VARCHAR(50),
    @Color VARCHAR(50),
    @Mileage INT,
    @VIN_Number VARCHAR(50)
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Vehicle_ID INT;

    -- Add new vehicle record
    INSERT INTO Vehicles (Make, Model, Year, Body_Style, Color, Mileage, VIN_Number, Last_Updated)
    VALUES (@Make, @Model, @Year, @Body_Style, @Color, @Mileage, @VIN_Number, GETDATE());

    -- Get the newly inserted Vehicle_ID
    SET @Vehicle_ID = SCOPE_IDENTITY();

    -- Set Last_Updated field for Vehicle_Features table to the current date and time
    UPDATE Vehicle_Features SET Last_Updated = GETDATE() WHERE Vehicle_ID = @Vehicle_ID;
    
END

EXEC AddNewCar 'Toyota', 'Camry', 2023, 'Sedan', 'Silver', 0, 'ABC1234567890';

CREATE PROCEDURE SellCar (
    @Sale_Date DATE,
    @Sale_Price DECIMAL(18, 2),
    @Customer_ID INT,
    @Vehicle_ID INT,
    @Discount_ID INT,
    @Document_Fee_ID INT
)
AS
BEGIN
    SET NOCOUNT ON;

    -- Add new sale record
    INSERT INTO Sales (Customer_ID, Vehicle_ID, Sale_Date, Sale_Price, Last_Updated)
    VALUES (@Customer_ID, @Vehicle_ID, @Sale_Date, @Sale_Price, GETDATE());

    -- Get the newly inserted Sale_ID
    DECLARE @Sale_ID INT = SCOPE_IDENTITY();

    -- Add sale discount record
    INSERT INTO Sale_Discounts (Sale_ID, Discount_ID, Last_Updated)
    VALUES (@Sale_ID, @Discount_ID, GETDATE());

    -- Add sale document fee record
    INSERT INTO Sale_Document_Fees (Sale_ID, Document_Fee_ID, Last_Updated)
    VALUES (@Sale_ID, @Document_Fee_ID, GETDATE());

    -- Update vehicle mileage and last updated date
    UPDATE Vehicles SET Mileage = Mileage + 1, Last_Updated = GETDATE() WHERE Vehicle_ID = @Vehicle_ID;

    -- Update Last_Updated field for Customer record
    UPDATE Customers SET Last_Updated = GETDATE() WHERE Customer_ID = @Customer_ID;

    -- Update Last_Updated field for Discounts and Document_Fees tables
    UPDATE Discounts SET Last_Updated = GETDATE() WHERE Discount_ID = @Discount_ID;
    UPDATE Document_Fees SET Last_Updated = GETDATE() WHERE Document_Fee_ID = @Document_Fee_ID;
    
END

EXEC SellCar '2023-04-11', 15000.00, 1, 1, 1, 1;

-- Index for Customers table
CREATE INDEX IX_Customers_Last_Name ON Customers (Last_Name);

-- Index for Vehicles table
CREATE INDEX IX_Vehicles_Make_Model_Year ON Vehicles (Make, Model, Year);

-- Index for Sales table
CREATE INDEX IX_Sales_Sale_Date ON Sales (Sale_Date);

-- Index for Sale_Discounts table
CREATE INDEX IX_Sale_Discounts_Sale_ID ON Sale_Discounts (Sale_ID);

-- Index for Sale_Document_Fees table
CREATE INDEX IX_Sale_Document_Fees_Sale_ID ON Sale_Document_Fees (Sale_ID);

-- Index for Vehicle_Features table
CREATE INDEX IX_Vehicle_Features_Vehicle_ID ON Vehicle_Features (Vehicle_ID);
Stored Procedure

---