Modeling a database for a printing company

Question

Scenario

Create a conceptual schema for the case study assigned to you using the concept of entity relationship model. Specify the cardinality ratio and participation constraint for each relationship type.

The following requirement is for a ABC printing company that handles printing jobs for book publishers.

• The printing company keeps track of publisher's id, publisher's name, Publisher's address composed of street, city, postcode, publisher's telephone number and credit code.

• The company keep track of the printing jobs (books or part of books) and the job id is the unique key for each job. The company also would like to record the date of the printing job, the description and the type.

• A printing job requires the use of materials, such as paper and ink, which are assigned to a job via purchase orders. The purchase orders for each job is recorded for each purchase order the purchase order id (unique) and the date for the purchase order also recorded. Each printing job may have several purchase orders assigned to it. If the job is deleted, you need not keep track of its purchase order any longer.

• The printing company also would like to keep track the details of the materials(items). Each items has item id, description, stock in hand and price.

• Each purchase order for each job can have several items.

Entity-relationship diagram

Schema

My schema is like this

Publisher (PID, PName, street, city, postcode, Ptele,CCode)
p.k = PID
f.k = 


PrintingJobs (JID, Pdate, Description, Type, PID)
P.K = JID
F.K PID


Purchase ( JID, OID, ODate)
P.K = JID, OID
F.K = JID


Item ( IID, stock, Description, Price, JID, OID)
P.K = IID
F.K = JID, OID
  JID REFERENCES PURCHASE (JID)
  OID REFERENCES PURCHASE (OID)

Question

Can anyone guide me and tell me if I'm correct, what changes I should make, and if I represented the relationships well?

Answer 1

This part in Item doesn't look right:

F.K = JID, OID
  JID REFERENCES PURCHASE (JID)
  OID REFERENCES PURCHASE (OID)

I suppose the design is only on paper, as no DBMS would allow that, when the primary key of PURCHASE is (JID, OID). You need one (composite) Foreign Key constraint, not two:

  (JID, OID) REFERENCES PURCHASE (JID, OID)

Now this foreign key belong to Puschase_Materials (or Purchace_Items) and not to Materials (or Item). With that in mind, the corrected schema would be:

PurchaseOrders (JID, OID, ODate)
PK = (JID, OID)
FK1 = (JID) REFERENCES Jobs (JID)

Items (IID, Stock, Description, Price)
PK = IID

Purchases_Items (IID, JID, OID)
PK = (IID, JID, OID)
FK1 = (IID) REFERENCES Items (IID)
FK2 = (JID, OID) REFERENCES PurchaseOrders (JID, OID)

---

The image shows an additional relationship, between Purchase_Materials and Jobs/PrintingJobs, which doesn't appear in the schema - and you don't really need it.

Since there is a foreign key from Purchase_Items to PurchaseOrders (or Purchase_Orders in the image) and a foreign key from PurchaseOrders to Jobs, you don't need a direct foreign key. Consistency is enforced through this indirect relationship.

---

• There is inconsistencies in the naming, between the schema and the images. I assumed the following are renames:

  Schema         - Image              - Code in this answer
  ---------------------------------------------------------
  Jobs           - PrintingJobs       - Jobs
  PurchaseOrders - Purchase           - PurchaseOrders
  Item           - Materials          - Items
    (none)       - Purchase_Materials - Purchases_Items
  

• Some table names are plural and others singular.

• Some names have underscores between name parts, some not.

It would be best if you fixed those and decide on a naming convention (say all table names are plural, there are no underscores except in case X, etc).