2

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

enter image description here

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)

enter image description here

Question

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

  • @ypercubeᵀᴹ thanks for the answer. is everything else correct? – ZEBRA Sep 28 '17 at 11:03
  • 1
    Added as a proper answer. I don't see any problem with the rest of the design. Seems good. – ypercubeᵀᴹ Sep 28 '17 at 11:07
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).

  • sorry for the inconsistencies. purchase_materials is actually a many to many relationship. didn't have time to map it into the schema. jobs is actually the name for printingjobs. I don't quite understand this part "Since there is a foreign key from Item to Purchase (or Purchase_Orders in the image) and a foreign key from Purchase to Jobs, you don't need a direct foreign key. Consistency is enforced through this indirect relationship." – ZEBRA Sep 28 '17 at 11:19
  • That means tha tyou don't need a foreign key from Items / PublishedMaterials to Jobs (such a link appears in the image) – ypercubeᵀᴹ Sep 28 '17 at 11:24
  • Purchase_materials is actually many to many relationship table. sorry for the inconsistencies I just started learning access a week ago and just had this assignment thrown at me. I would fix this. – ZEBRA Sep 28 '17 at 11:25
  • Yes, it is obviously a many to many table. Isn't it the same as Items? Or is Items the same as Materials? – ypercubeᵀᴹ Sep 28 '17 at 11:26
  • items is the same as materials – ZEBRA Sep 28 '17 at 11:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.