Transcript Entity-Relationship Diagrams

Entity-Relationship Diagrams
Solutions to Homework #1
Project Partners, Inc.
Data Flow Diagram
Level 0 DFD -- Context Diagram
Client & Project Data
Invoice
Client
0
Billing &
Reporting
Time Sheet &
Expense Report
Consultant
Payment
Project
Assignment
Data
Project
Manager
Correction Request:
Missing & Invalid Data
Project Partners, Inc.
Data Flow Diagram
Level 1 DFD -- Billing & Reporting System
Client & Project Data
Client
Project
Manager
Project Assignment Data:
(proj., consultant, dates, rate)
Correction Request:
Missing & Invalid Data
Consultant
Time Sheet Data
Expense
Report
Data
Invoice
Client
Payment
Data
1
Record Project
Information
Client & Project Data
Client &
ProjectData
Client &
Project Id
2
Update Project
Assignments
Project Assignment Data
Project
Assignment
Assignment
Data
3
Record &
Validate
Timesheets
4
Record
Expense
Data
5
Prepare
Invoice
6
Receive
Payment
Time Sheet Data
Time Log
Time
Sheet
Data
Expense Report
Expense
Reports
Project
Expense
Data
Invoice Data
Invoice
Balance
Invoice Status
Invoice
Project Partners, Inc.
Data Flow Diagram
Level 2 DFD -- Record & Validate Timesheets
3.3
Find Missing
Timesheets
Timesheet Request
Timesheet Activity Data
Consultant
Correction Request
Client &
ProjectData
Employee
Data
Client & Project Id
Employee Id
Current Employees
Employee
Data
Current Time Log Entries
3.1
Capture &
Record Activity
Data
Time
Log
Entries
3.2
Validate
Timesheets
Timesheet Activity Data
Time Log
Time Log
Validation
Status
Project Assignments
Activity Data
Project
Assignment
Activity
Project Partners, Inc.
Process Details
Process 3.1 -- Capture & Record Timesheet Activity
Record the Timesheet records as prepared by the consultants. Save this information in the Time
Log data store. Also, make sure the status of each Time Log record indicates it has not yet
been validated.
Note that there should be a separate process which ensures that each consultant provided time
records for each day that they are employed (Process 3.3).
Project Partners, Inc.
Process Details
Process 3.2 -- Validate Timesheets
Read Employee record to validate Employee ID.
Read Client record to validate Client ID.
Read Project record to validate Project ID.
Read Activity record to validate Activity ID.
IF the Activity indicates it is an overhead activity,
Ensure that the Timesheet indicates it is non-billable.
IF Timesheet indicates a billable time record,
Read the Project Assignment record
to verify the consultant can bill against this project
and the Activity indicates it can be billable
and the Timesheet date should be within the approved work dates,
and the Project Assignment should indicate it is billable.
IF the total hours for a day is less than 8,
Request the consultant to account for remaining time
(alternatively, check for the total hours for a week being < 40).
IF the total hours for a day is greater than 10 (or some other appropriate reasonable number),
Verify with the consultant that the total is correct.
IF each time log entry has pass all validations,
Update the Tiime Log status for each entry indicating it is OK.
Project Partners, Inc.
Process Details
Process 3.3 -- Find Missing Timesheets
For a time period for which timesheets are collected (e.g., each week), find the consultants who
have not turned in a Timesheet. This action can be performed by:
1. Obtain a list of all individuals required to file timesheets (from the Employee data store);
2. Obtain a list of all individuals who have filed timesheets (from the Time Log data store);
3. Match the 2 lists, discarding those individuals who appear on both lists. The remaining
people from the Employee data store have missing Timesheets.
Project Partners
Information Requirements
• Consider this list to be the minimum set of data.
• Client information (Company name, address)
• Project name and description.
– This includes the starting and ending dates of the engagement; identifier
of the underlying contract and an indication of the type of engagement
(I.e., fixed price vs time & materials); and the client’s project manager
information (their name, address, phone, and fax).
• List of PPI’s standard project activities
– Each of these activities includes its activity id, description, and an
indicator of whether it is normally billable. Activities which are normally
billable include analysis, design, programming, etc. Some activities (e.g.,
proposal preparation, vacation, sick time, etc.) are always considered to be
overhead and hence are never billable.
Project Partners
Information Requirements
• List of PPI’s standard project activities
– Note that it is PPI’s policy to “charge” anything to a project that can be
attributable to a project or engagement, irrespective of whether it is
billable and therefore to be included on an invoice. Please note that these
are not specific instances of consultant’s activities, but a list of possible
activities that could be entered by someone using the time sheet entry
system.
• Standard billing rate for each class of consultant
– Other information related to the class of consultant should include a title
and brief description.
• Assignment information
– Each consultant who can record billable time for a project should have
information pertaining to the beginning and ending dates of their billable
involvement along with their actual billing rate for the project. Nonbillable time may be charged by the consultants or by other individuals to
a given project. This insures that all time spent on a project is recorded
even if it is never included in an invoice to the client.
Project Partners
Information Requirements
• Timesheet information
– The information which appears on a consultant’s timesheet includes:
• Client company identifier and name
• project id and name; client’s project manager name, address, and phone
number
• type of work performed (generally drawn from a list of standard activities,
such as analysis, design, programming, testing, implementation, etc.)
• brief narrative description of the work performed
• the amount of time, in hours and fractions, spent on each activity
• an indication of whether the time should be billed to the client
• Consultant information
– Information about each consultant should include their id, name,
consultant billing class, and phone number where they can be reached at
the client site. Remember that a consultant may work at more than one
client in any given period of time.
Entity-Relationship Diagrams
Entity-Relationship Diagram
• A model that represents system data by
entity and relationship sets.
ERD Elements
• Entities
Things about which you collect information
• Relationships
Means of association between entities
• Identifiers
Unique attributes of entities
• Attributes
Characteristic or property of the entity that is of interest
ERD Symbols
• Entities: rectangle
• Relationships: diamond on a line with
cardinality indicated (1 to M) or lines with
“chicken feet”
• Identifiers: underlined text
• Attributes: text by the entity
(if shown at all on diagram)
Sample ERDs
ORDER
Entity attributes:
ORDER: #, DATE, PART #, QUANTITY
Order
1
CAN
HAVE
1
PART: #, DESCRIPTION, UNIT PRICE,
SUPPLIER #
PART
Part
M
CAN
HAVE
1
SUPPLIER: #, NAME, ADDRESS
SUPPLIER
Supplier
Entity Sets
• Each individual object is called an entity. A
collection of such entities is an entity set.
– Example: Joe, Jill, and Mary represent entities.
They are all ascribed to the entity set,
PERSON.
– Example: A collection of projects is the project
entity set.
Relationship
• Relationship
– One interaction between one or more entities
– For example: if a person works on a project,
there is a relationship between that person and
the project
• Relationship set
– A collection of such relationships.
– A component in an E-R diagram that represents
a set of relationships with the same properties.
Notes on Entities and
Relationships
• We can actually see entities, but we cannot
see relationships.
– For example:
• Entity sets: Person; project
• Relationship: People work on projects. This
becomes the relationship set “Work”
Persons
The set of people, set of projects and
set of working relationships.
Work
Projects
Entity-Relationship Structures
Persons
Persons are in departments
Persons work on projects
Parts
Supply
Suppliers
Projects use parts
Suppliers supply parts
Warehouses hold parts
Are-In
Work-On
Use
Hold
Warehouses
Depts
Projects
More complex relationship:
See persons and parts
Entities can have multiple
relationships
Companies
Leases
Owns
Vehicles
More than two entity sets can be
associated with the same
relationship set
Customers
Buy
Relationship sets that include only two
entities are known as binary. More than
two are known as N-ary.
Stores
Each relationship in this set includes a person, a part bought
by the person, and the store where the purchase was made.
Parts
A person, a part bought by the person,
and the store where the purchase was made
It is always possible to remove an
N-ary relationship by replacing it
with an entity set.
Purchase
Of
Parts
By
Customer
From
Stores
Exercise
• Employees are in departments
• Each department has sections.
Departments
Have
Sections
Employ
Persons
Are in is not needed
Are in
Departments
Have
Sections
Employ
Persons
Are in is not needed
Identifiers
• One of the attributes of an entity or
relationship set is called the identifier
• It has one important property: its values
identify unique entities in the entity set.
Identifiers are underlined here
Persons
PERSON-ID
NAME
ADDRESS
Work
PERSON-ID
PROJECT-ID
TIME-SPENT
Projects
PROJECT-ID
START-DATE
BUDGET
The set of people, set of projects and
set of working relationships.
Convention for Identifiers in
Relationships
• Use the identifiers of the entities that
participate in the relationship as the
relationship identifiers.
• Identifiers are not file keys here. At this
stage, they are the identifiers of entities that
participate in the relationship.
Cardinality
• The number of relationships in which one
entity can appear.
• An entity can appear in:
– one (1) relationship;
– any variable number (N) of relationships; and
– a maximum number of relationships
Cardinality - Example
Persons
N
Work
PERSON-ID
NAME
ADDRESS
PERSON-ID
PROJECT-ID
TIME-SPENT
A persona can appear in more than one
WORK relationship, and so can a project.
If there was a limit to the number of times an
entity can take part in the relationship, then
N or M would be replaced by the actual
maximum number.
M
Projects
PROJECT-ID
START-DATE
BUDGET
The set of people, set of projects and
set of working relationships.
Cardinality - Example
Manager
1
Manage
MANAGER-ID
NAME
ADDRESS
PERSON-ID
PROJECT-ID
TIME-SPENT
Here a project has one (1) manager, whereas
a manager can manage any number (N) of
projects.
N
Projects
PROJECT-ID
START-DATE
BUDGET
The set of people, set of projects and
set of working relationships.
Cardinality - Example
Manager
1
Manage
MANAGER-ID
NAME
ADDRESS
PERSON-ID
PROJECT-ID
TIME-SPENT
The denotes optional participation on the
project. If it is mandatory, then there is no
placed there.
N
Projects
PROJECT-ID
START-DATE
BUDGET
The set of people, set of projects and
set of working relationships.
Issues in building an ERDiagram
• How to choose entities, relationships and
attributes
• How to choose names
• What steps should be followed
Choosing Atrributes
• Attributes, just like entity and relationship
sets, should express simple concepts.
• E-R diagrams should not contain
multivalued or structured attributes
– For example:
PERSONS
Non-simple attributes
PERSON-ID
DATE-OF-BIRTH
QULIFICATION* (asterisk means it is multivalued-repeating)
ADDRESS
(NUMBER, STREET, SUBURB)
Addresses has structured attributes.
These should be replaced in the final diagram
by relationships.
Ex: Removing multivalued and
structured attributes
Persons
PERSON-ID
QUALIFICATION
HAVE
PERSON-ID
DATE-OF-BIRTH
LIVE-AT
PERSON-ID
NUMBER
STREET
SUBURB
QUALIFICATION
QUALIFICATIONS
ADDRESSES
NUMBER
STREET
SUBURB
Choosing Object Set Names
• Remember, that one goal of E-R modeling
is to produce a model that is easily
understood by users as well as computer
personnel.
– Entity sets are labeled as nouns
– Relationship sets are labeled by verbs
– Relationship sets are structured as prepositions
when modeling structural relationships (see
PURCHASES example [building has rooms])
Where to begin
• Start with entity sets
• Look at how entities interact with each
other and model this in terms of relationship
sets
• Then add cardinality to the system
• Add attributes and choose identifiers
Dependent Entities
• A dependent entity set depicts a set of
entities whose existence depends on other
entities.
INVOICES
INVOICELINES
INVOICE-NO
INVOICE-DATE
INVOICE-NO
LINE-NO
AMOUNT
PROJECTS
TASKS
Note: dependent entities have composite identifiers
PROJECT-ID
DATE-STARTED
PROJECT-ID
TASK-NO
BUDGET
Machines
Machines
N
Machine
Availability
Use
N
M
Use
M
Projects
PROJECTS
SUBSETS
• Example:
– generic: loan applications
– Different types of loan applications which have
unique attributes
Occurrence diagram for entity set PERSONS
and its subsets
PERSON-ID
NAME
ADDRESS
PERSONS
TEACHERS
PERSON-ID
MAJOR
PERSON-ID
DATE-HIRED
STUDENTS
N
N
TEACHES
PERSON-ID
COURSE-NO
SEMESTER-TAUGHT
PERSON-ID
COURSE-NO
SEMESTER-TAKEN
RESULT
TAKES
M
COURSES
COURSE-NO
COURSE-NAME
M
STAFF
COMBINING
SUBSETS
FROM A NUMBER
OF ENTITIES
STAFF-ID
DATE-JOINED
MEMBER-NO.
CLUB-MEMBERS
STUDENTS
MEMBER-NO.
MEMBER-GRADE
DATE-JOINED-CLUB
N
USE
M
COURTS
STUDENT-ID
MAJOR
MEMBER-NO
MEMBER-NO
COURT-NO
NO-TIMES-USED
COURT-NO
LOCATION