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.
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)
Can anyone guide me and tell me if I'm correct, what changes I should make, and if I represented the relationships well?