Datamodel Real-Estate Agency

This chat is about an assignment that our students have to do in semester 2. After a brief introduction to provide context, the prompt text is copied almost verbatim from the assignment. Students have to provide an ERD and SQL create table scripts for this assignment. GenAI, the free version of ChatGPT in this case, indicated that it could not generate the ERD as a diagram, but could do so in structured text. I later checked with the paid version of ChatGPT (ChapGPT Plus, same LLM model but DALL-E included) and it also indicated that it could not do it. I don't know if this is because ChatGPT (DALL-E) can only generate bitmaps and not vector graphics, but if that is the reason, I don't really understand why this is a limitation (yet?). I solved the problem by asking ChatGPT to generate Mermaid generation code for an ERD. I had to modify it slightly to make it work, though.

I had difficulty importing generated XML and XMI in Visual Paradigm (VP), but reverse engineering a generated SQL DDL script with CREATE TABLE statements worked fine. Reverse engineering a DDL script results by definition in a physical data model.

Here is the chat.

Below is the logical datamodel based on the generated Mermaid code.

erDiagram
    CLIENT ||--o{ HOUSE : "sells"
    CLIENT ||--o{ BID : "places"
    CLIENT }o--o{ VIEWING : "participates in"
    HOUSE ||--o{ VIEWING : "has"
    HOUSE ||--o{ BID : "receives"
    HOUSE }o--|| EMPLOYEE : "mediated by"
    BID ||--o| DEED_OF_SALE : "accepted bid for"
    DEED_OF_SALE }o--|| SOLICITOR : "prepared by"

Here's the reverse engineered VP physical data model.

Even VP using a deterministic reverse engineering algorithm is not without its odd behavior. Client_Viewing being a standard junction table has odd one-to-one relationships with its parents. Here is the DDBL code generated by GenAI for all three tables that was reverse engineered by VP?

-- Create the Client table
CREATE TABLE Client (
    clientno INT PRIMARY KEY,
    name NVARCHAR(100) NOT NULL,
    city NVARCHAR(100) NOT NULL,
    role NVARCHAR(20) CHECK (role IN ('seller', 'buyer', 'both')) DEFAULT 'buyer'
);

-- Create the Viewing table
CREATE TABLE Viewing (
    viewingno INT PRIMARY KEY,
    viewing_date DATE NOT NULL,
    houseno INT NOT NULL,
    FOREIGN KEY (houseno) REFERENCES House(houseno)
);

-- Create the Client_Viewing junction table
CREATE TABLE Client_Viewing (
    clientno INT NOT NULL,
    viewingno INT NOT NULL,
    PRIMARY KEY (clientno, viewingno),
    FOREIGN KEY (clientno) REFERENCES Client(clientno),
    FOREIGN KEY (viewingno) REFERENCES Viewing(viewingno)
);