SQL Supertype Subtype join Oracle

Transact-SQL
1

h

Hello! I am making a restaurant database and here's what I have:

I have my supertype LOCATIONS and 4 subtypes Boston University, North Eastern, Berklee, and Boston College.

I am having trouble understanding the syntax of creating supertype and subtype connections in Oracle SQL Developer. This is my code so far:

CREATE TABLE LOCATIONS (
Store_Location_ID NUMBER (10,0) NOT NULL,
BostonMA VARCHAR2 (20) NOT NULL,

CONSTRAINT LOCATIONS_PK PRIMARY KEY (Store_Location_ID)
);


CREATE TABLE Boston_University (
    AStore_Location_ID  NUMBER (5,0)  NOT NULL,
    Brigten_Avenue      VARCHAR2(20)        ,

    CONSTRAINT  Boston_University_PK PRIMARY KEY (AStore_Location_ID)
    );

            SELECT BostonMa, Botston_University
            FROM LOCATIONS
            JOIN Boston_University
            ON   LOCATIONS.STORELOCATIONID = Boston_University.ASTORELOCATIONID;

I have no insert code yet and I have no other SELECT, FROM, JOIN, ON command for the other locations, I simply want to know if my syntax is correct because I dont want to continue if my syntax is wrong.

Can anyone help me?

2

Your syntax looks Okay. Your design doesn’t make sense to me.

3

if you break out your subtypes into their own distinct tables then you will eventually hit a wall. this is not a sustainable design if I understood it correctly. what is suddenly you have 50 new location, would you be creating 50 new subtypes? again I might be not understanding your design approach and this might be possible in Oracle.

Is this to manage only one and only one restaurant with many locations? What if tomorrow owner has a new restaurant?

Also this is a Microsoft SQL Server forum.

4

I don't see this as a valid supertype:subtype setup. The "type" refers truly to type.

Business locations are specific instances of a location, but I don't see how each has a different subtype. Unless, let's say, some locations were no-seating, walk-in-out only, then that might be a different subtype.

Or, consider SQL Server (or other dbms) metadata. An "object" in SQL Server is a supertype. The subtypes consist of user table, foreign key, procedure, function, etc.. The specific instance is one, and only one, subtype valid for that supertype. That, to me, is the standard idea of a supertype & subtype.