Need help with supertype/subtype table, please!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help with supertype/subtype table, please!

Author

Topic

eplugplay
Starting Member

2 Posts

Posted - 2011-03-29 : 02:38:28

Hello, I am new to the SQL world and I am having the toughest time trying to figure this one out! I have a project that is due in two weeks and I still cannot for the life of me get the relationships correctly done in SQL.

Here is my problem, I am creating a database and the series of tables that I am having problems with are the Jewelry and the types of Jewelry.

So far my tables are:

JEWELRY
Jewelry_ID PK
Metal_ID
Price_Sold
Gem_ID
Jewelry_Type

BRACELET
JEWELRY_ID PK,FK
Bracelet_Type

Pretty much my JEWELRY table is my supertype table and my BRACELET table is my subtype table. I have 4 more tables like BRACELET but are NECKLACE, RING, WATCH, EARRING.

My question is that I understand the subtype table inherits the supertype's table, in this case the JEWELRY table's Jewelry_ID attribute is the primary key and it is also the foreign key in the BRACELET table. But how do I actually create this in sql?

I started with the create table scripts:

Create Table JEWELRY
(
JEWELRY_ID Int Not Null,
METAL_ID Int Not Null,
GEM_ID Int Null,
PRICE_SOLD decimal(8,2) Not Null,
JEWELRY_TYPE Char(1) Not Null,
)
Go

Create Table BRACELET
(
JEWELRY_ID Int Not Null,
JEWELRY_TYPE CHAR(1) DEFAULT 'B' Not Null,
JEWELRY_DESCRIPTION CHAR(15) NOT NULL,
)
Go

Then I did my alter tables for jewelry and bracelet:

ALTER TABLE JEWELRY WITH NOCHECK
ADD CONSTRAINT PK_JEWELRY_ID PRIMARY KEY CLUSTERED
(JEWELRY_ID)
ON [PRIMARY]
GO

ALTER TABLE BRACELET WITH NOCHECK
ADD CONSTRAINT PK_B_JEWELRY_ID PRIMARY KEY CLUSTERED
(JEWELRY_ID)
ON [PRIMARY]
GO

ALTER TABLE BRACELET WITH CHECK
ADD CONSTRAINT FK_JEWELRY_ID
FOREIGN KEY
(JEWELRY_ID)
REFERENCES JEWELRY(JEWELRY_ID)
GO

I got them done but is this all I need?

Pretty much the Jewelry_ID is the unique key here and can get all the information on the jewelry about the type of metal, gem(s) that it uses and also the jewelry_type attribute in the Jewelry table is used to store one character for 'B' for Bracelet and 'N' for Necklace and etc.. BUT my problem is that I want to also go further and get more specific in distinguishing the different types of bracelets such as link bracelets, bangles, tennis... etc.. I also want to do the same for the types of Rings, Necklace, Watches and etc..

So do I add a Bracelet Type table to distinguish the specific types of Bracelets such as the bangles and tennis and etc..?

Please help on how to go about doing this in SQL, I've been reading and googling online for a week now and I always come back to the drawing board! Any help is MUCH appreciated!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-29 : 07:39:21

Try here: http://www.w3schools.com/sql/sql_foreignkey.asp

You've got the syntax correct, you just need to create the new tables.

eplugplay
Starting Member

2 Posts

Posted - 2011-03-29 : 11:55:18

Thanks for the link but I already know how to create the tables and set the foreign and all of that its just I dont get the physical aspects of implementing the logical aspect of the Jewelry to the Bracelet tables.

I want to add a bracelet type to the mix but not sure where this goes. So far I've added Bracelet_Type attribute inside of the JEWELRY table and made a BRACELET_TYPE table that links to it listing all the types of bracelets with a auto generated number.

Inside the BRACELET_TYPE table I have:

BRACELET_TYPE
BRACELET_TYPE_ID
LINK
BANGLE
TENNIS

Just added the BRACELET_TYPE_ID attribute inside the JEWELRY table as the foreign key. Since my situation is a disjoint supertype subtype problem, do I add the foreign key inside the BRACELET_TYPE table or the JEWELRY table? Also do I keep the original BRACELET table to have the JEWELRY_ID as the primary key and still have the attribute BRACELET_TYPE inside the BRACELET table? If so is the BRACELET table connected to the JEWELRY table via the JEWELRY_ID with the foreign and primary key in the BRACELET table as the JEWELRY_ID and then make another BRACELET_TYPE table to list the specific types of bracelets and then have it linked to the JEWELRY table and add the foreign key (BRACELET_TYPE_ID) inside the JEWELRY table??

I've tried so many different types of combinations and all its doing is just making me more confused! Please, any advice would be much appreciated.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-03-29 : 12:31:56

Couple of things.

1. the Foreign KEY from your sub-type to your super-type should include the classification. In this case the JEWELRY_TYPE. That way you can't have sub-types classified incorrectly.

2. The BRACELET_TYPE is, or should, be an attribute of the BRACELET entity not he JEWELRY entity.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-29 : 22:19:49

The classic scenario calls for a root class with all the common attributes and then specialized sub-classes under it. As an example, let's take the class of Vehicles and find an industry standard identifier (VIN), and add two mutually exclusive sub-classes, Sport utility vehicles and sedans ('SUV', 'SED').

CREATE TABLE Vehicles
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) NOT NULL
CHECK(vehicle_type IN ('SUV', 'SED')),
UNIQUE (vin, vehicle_type),
..);

Notice the overlapping candidate keys. I then use a compound candidate key (vin, vehicle_type) and a constraint in each sub-class table to assure that the vehicle_type is locked and agrees with the Vehicles table. Add some DRI actions and you are done:

CREATE TABLE SUVs
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT 'SUV' NOT NULL
CHECK(vehicle_type = 'SUV'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Vehicles(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

CREATE TABLE Sedans
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT 'SED' NOT NULL
CHECK(vehicle_type = 'SED'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Vehicles(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

I can continue to build a hierarchy like this. For example, if I had a Sedans table that broke down into two-door and four-door sedans, I could a schema like this:

CREATE TABLE Sedans
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT 'SED' NOT NULL
CHECK(vehicle_type IN ('2DR', '4DR', 'SED')),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Vehicles(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

CREATE TABLE TwoDoor
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT '2DR' NOT NULL
CHECK(vehicle_type = '2DR'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Sedans(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

CREATE TABLE FourDoor
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT '4DR' NOT NULL
CHECK(vehicle_type = '4DR'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Sedans (vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

The idea is to build a chain of identifiers and types in a UNIQUE() constraint that go up the tree when you use a REFERENCES constraint. Obviously, you can do variants of this trick to get different class structures.

If an entity doesn't have to be exclusively one subtype, you play with the root of the class hierarchy:

CREATE TABLE Vehicles
(vin CHAR(17) NOT NULL,
vehicle_type CHAR(3) NOT NULL
CHECK(vehicle_type IN ('SUV', 'SED')),
PRIMARY KEY (vin, vehicle_type),
..);

Now start hiding all this stuff in VIEWs immediately and add an INSTEAD OF trigger to those VIEWs.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources