1An ERD Developed for the BUYIT System Is Shown in Figure 1

RELATIONAL DATABASES - LOGICAL DESIGN
Tutorial - Data modelling using Entity-Relationship Diagrams

ANSWERS

1An ERD developed for the BUYIT System is shown in Figure 1.

1.1.1Name the entities in the ERD for the BUYIT System.

ITEM

PROBLEM

PURCHASE ORDER

PURCHASING OFFICER

VENDOR

1.1.2Name the attributes in the ERD for the BUYIT System.

Action Taken

Agency

Agency_ID

Date Purchased

Date of Occurrence

Date

Delivery Date

Description

First-name

ID_Number

Item No

Last-name

Middle-initial

Name

Person-business-code

PO_ID

PO_Number

Principal Title

Principal Name

Problem_Type

Purchase Officer ID

Purchase Order No

Quantity on hand

Unit Cost

Vendor Address

Vendor ID

VendorCity

VendorID

VendorName

VendorState

VendorStreet

VendorZip

ANSWERS (ctd.)

1.1.3List the primary keys for each entity in the ERD for the BUYIT System.

VENDORVendorID

ITEMVendor ID + Purchase Order No

PURCHASE ORDERPurchase Order No

PURCHASING OFFICERPO_ID

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.