CODEWITHSUNDEEP
sqlsql-servermappingrelational
kesahli
kesahli

Reputation: 3

EER diagram with mandatory sub-classes to SQL tables

I am working on an assignment to create a database for fast food outlet and am stuck with generating a relational schema from my EER which has mandatory subclasses.

I have a CustOrderDetails(OrderNo {PK}, EmployeeNo, DiscCode,....) entity with subclasses DeliveryOrder(CallStart,...) and PickUpOrder(PickUpName,...). These are "Maandatory/Disjoint" so CustOrderDetails MUST be one of these and can ONLY be one of these. My textbook and lecture notes imply I need to create separate tables to do this, with all attributes of CUstOrderDetails AND attributes of DeliveryOrder in one table, and all attributes of CustOrderDetails AND attributes PickUpOrder in another, as per Option 1 below.

My problem is that I cannot work out how to enforce the "mandatory" sideof things, and still be able to link the table CustOrderItems(OrderNo, ItemCode, ItemPrice, ItemQty) which shows the individual items for the order.

I have looked at a number of Q&A on SO, but nothing seems to specifically reference the mandatory/or participation constraint. Using option 2 (which has been suggested in other questions) seems simple enough to link the CustOrderItems with PK as (OrderNo, ItemCode) but I don't know that this deals with the Mandatory/Or constraint effectively.

My attempts at code are below. Can anyone help with a method of doing this.

Please consider that this is my first introduction to DB design and SQL coding.

Option 1

 DeliveryCustOrder(
 OrderNo    CHAR(7) PRIMARY KEY,
 EmployeeNo CHAR(7),
 DiscCode   CHAR(7),
 ...,
 CallStart  TIME DEFAULT GETDATE(),
 ...,
 FOREIGN KEY (DiscCode) references DiscProg(DiscCode)  ON UPDATE
 CASCADE ON DELETE NO ACTION
 )

 PickUpCustOrder(
 OrderNo    CHAR(7) PRIMARY KEY,
 EmployeeNo CHAR(7),
 DiscCode   CHAR(7),
 ...,
 PickUpName VARCHAR(25),
 ...,
 FOREIGN KEY (DiscCode) references DiscProg(DiscCode)  ON UPDATE
 CASCADE ON DELETE NO ACTION,
 FOREIGN KEY (Employee) references Employee(EmployeeNo) ON UPDATE
 CASCADE ON DELETE NO ACTION
 )

Option 2

 CustOrderDetail(
 OrderNo    CHAR(7) PRIMARY KEY,
 EmployeeNo CHAR(7),
 DiscCode   CHAR(7),
 ...,
 FOREIGN KEY (Employee) references Employee(EmployeeNo) ON UPDATE
 CASCADE ON DELETE NO ACTION,
 FOREIGN KEY (DiscCode) references DiscProg(DiscCode)  ON UPDATE
 CASCADE ON DELETE NO ACTION
 )

 DeliveryOrder (
 OrderNo   CHAR(7) PRIMARY KEY,
 CallStart TIME DEFAULT GETDATE(),
 ...,
 FOREIGN KEY (OrderNo) references CustOrderDetail(OrderNo) ON UPDATE
 CASCADE ON DELETE NO ACTION
 )

 PickUpOrder (
 OrderNo   CHAR(7) PRIMARY KEY,
 PickUpName VARCHAR(25),
 ...,
 FOREIGN KEY (OrderNo) references CustOrderDetail(OrderNo) ON UPDATE
 CASCADE ON DELETE NO ACTION
 )

P.S. I hope I have gotten my "jargon" correct! Thanks in advance.

Upvotes: 0

Views: 396

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

I can think of two ways.

  1. In the CustOrderDetail table, add two columns: SubTypeTable (which contains either "DeliveryOrder" or "PickupOrder", and SubTypeID (which contains the PK of the subtype table). Both columns are NOT NULL, which enforces that an order must relate to one and only one row from one of the SubType tables. The disadvantage is that you can't use an FK since the SubTypeID could relate to either of two tables. You could only enforce RI with a trigger.

  2. In the CustOrderDetail table, add two columns: DeliveryOrderID and PickupOrderID. The columns can be NULL, and will each have an FK to its associated subtype table. The disadvantage is that you can only enforce the "One and Only One" rule with a CHECK constraint or TRIGGER. Also you will have some built-in sparsity since one of the two SubTypeIDs will always be NULL.

Upvotes: 1

Related Questions