Q 3. Draw individual ERD’s for the following
i. A hospital has a large number of registered physicians. Attributes of PHYSICIAN include Physician ID (the identifier) and Specialty. Patients are admitted to the hospital by physicians. Attributes of PATIENT include Patient ID (the identifier) and Patient Name. Any patient who is admitted must have exactly one admitting physician. A physician may optionally admit any number of patients. Once admitted, a given patient must be treated by at least one physician. A particular physician may treat any number of patients, or may not treat any patients. Whenever a patient is treated by a physician, the hospital wishes to record the details of the treatment (Treatment Detail). Components of Treatment Detail include Date, Time, and Results
ii. A SUPPLIER may supply many ITEMs (by “may supply,” we mean the supplier may
not supply any items). Each ITEM is supplied by any number of SUPPLIERs (by “is supplied,” we mean that the item must be supplied by at least one supplier).
iii. Each ITEM must be used in the assembly of at least one PRODUCT and may be used in many products. Conversely, each PRODUCT must use one or more ITEMs.
iv. ASUPPLIER may send many SHIPMENTs. However, each shipment must be sent
by exactly one SUPPLIER. Notice that sends and supplies are separate concepts.
A SUPPLIER may be able to supply an item, but may not yet have sent any shipments of that item.
v. A SHIPMENT must include one (or more) ITEMs. An ITEM may be included on
several SHIPMENTs.
vi. ACUSTOMER may submit any number of ORDERs. However, each ORDER must
be submitted by exactly one CUSTOMER. Given that a CUSTOMER may not have
submitted any ORDERs, some CUSTOMERs must be potential, inactive, or some
other customer possibly without any related ORDERs.
vii. An ORDER must request one (or more) PRODUCTs. A given PRODUCT may not
be requested on any ORDER, or may be requested on one or more orders.