RELATIONAL DATABASES - LOGICAL DESIGN
Tutorial - Data modelling using Entity-Relationship Diagrams
1An ERD developed for the BUYIT System is shown in Figure 1.
1.1.1Name the entities in the ERD for the BUYIT System.
1.1.2Name the attributes in the ERD for the BUYIT System.
Date of Occurrence
Purchase Officer ID
Purchase Order No
Purchase Order No
Quantity on hand
1.1.3List the primary keys for each entity in the ERD for the BUYIT System.
ITEMVendor ID + Purchase Order No
PURCHASE ORDERPurchase Order No
PROBLEMID_Number + PO_Number + Date of Occurrence
1.1.4List the composite attributes in the ERD for the BUYIT System.
VENDOR.VendorName = First-name + Middle-Initial + Last-name + Person-business-code
VENDOR.Vendor Address = VendorStreet + VendorCity + VendorState + VendorZip
1.1.5List the relationships in the ERD for the BUYIT System.
VENDOR sells ITEM
PURCHASE ORDER orders ITEM
PURCHASING OFFICER writes PURCHASE ORDER
PURCHASING OFFICER submits PROBLEM
ITEM is a bad item with a PROBLEM
1.1.6Write Enterprise Rules that describe the degree and membership for each relationship in the ERD for the BUYIT System.
A VENDOR sells one or many ITEMs
An ITEM is sold by one and only one VENDOR
A PURCHASE ORDER orders one or many ITEMs
An ITEM may be ordered by zero, one or many PURCHASE ORDERs
A PURCHASING OFFICER writes one or many PURCHASE ORDERs
A PURCHASE ORDER is wtitten by zero, one or many PURCHASING OFFICERs
A PURCHASING OFFICER submits one or many PROBLEMs
A PROBLEM is submitted by zero, one or many PURCHASING OFFICERs
An ITEM is a bad item with zero, one or many PROBLEMs
A PROBLEM may occur with one or many ITEMSs
1.1.7Use the Check List for Entity-Relationship Diagrams to see if the rules for constructing ERD have been followed.An entity name can appear only once in a data model
Each entity must be assigned a primary key
No entity occurrences are included in the data model.
An attribute must be associated with an entity
?A primary key has a unique value for each occurrence of the entity
Can not be established without a study of data values
An attribute name can only appear once in a data model unless it is part of a primary key
No values for attributes are included in the data model.
Each relationship must be labelled
Degree and membership class must be established for each end of relationships
Entities do not contain any multi-valued attributes
All MANY:MANY relationships are decomposed into two 1:MANY relationships
1.1.8Write a brief description of the BUYIT System in no more than fifty words which captures the meaning of the data model.
Description must include 5 entities and 5 relationships and all attributes as well as enterprise rules.