Entity-Relationship Diagrams
Solutions to Homework #1
Project Partners, Inc.Data Flow Diagram
Client
0 Billing & Reporting Consultant
Time Sheet & Expense Report
Invoice
Payment
Project Manager
Project Assignment Data
Level 0 DFD -- Context Diagram
Client & Project Data
Correction Request: Missing & Invalid Data
Project Partners, Inc.Data Flow Diagram
Client
5 Prepare Invoice
Consultant Time Sheet Data
Invoice
Payment Data
Project Manager
Client & Project Data1
Record Project Information
Client & ProjectData
Client & Project Data
2 Update Project Assignments
Project Assignment Data: (proj., consultant, dates, rate) Project
AssignmentProject Assignment Data
3 Record & Validate
Timesheets
Time LogTime Sheet Data
Assignment Data
Time Sheet Data
InvoiceInvoice Data
4 Record Expense
Data
Expense Reports
Expense Report
Expense Report Data
6 Receive Payment Invoice Status
Project Expense Data
Level 1 DFD -- Billing & Reporting System
Invoice Balance
Client
Client & Project Id
Correction Request: Missing & Invalid Data
Project Partners, Inc.Data Flow Diagram
ConsultantTimesheet Activity Data
3.1 Capture &
Record Activity Data
Time LogTimesheet Activity Data
3.2 Validate
TimesheetsProject
AssignmentProject Assignments
Time Log
Entries
Client & ProjectData
Client & Project Id
Correction Request
Employee Data
Employee IdActivity
Activity Data
3.3 Find Missing Timesheets
Timesheet Request
Employee Data
Current Employees
Level 2 DFD -- Record & Validate Timesheets
Current Time Log Entries
Time Log Validation
Status
Project Partners, Inc.Process Details
Process 3.1 -- Capture & Record Timesheet ActivityRecord the Timesheet records as prepared by the consultants. Save this information in the TimeLog data store. Also, make sure the status of each Time Log record indicates it has not yetbeen validated.
Note that there should be a separate process which ensures that each consultant provided timerecords for each day that they are employed (Process 3.3).
Project Partners, Inc.Process Details
Process 3.2 -- Validate TimesheetsRead 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 TimesheetsFor a time period for which timesheets are collected (e.g., each week), find the consultants whohave 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 remainingpeople from the Employee data store have missing Timesheets.
Project PartnersInformation 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 PartnersInformation 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. Non-billable 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 PartnersInformation 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
• EntitiesThings about which you collect information
• RelationshipsMeans of association between entities
• IdentifiersUnique attributes of entities
• AttributesCharacteristic 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
1
1
M
1
ORDER
CAN HAVE
PART
SUPPLIER
CAN HAVE
Entity attributes:
ORDER: #, DATE, PART #, QUANTITY
PART: #, DESCRIPTION, UNIT PRICE, SUPPLIER #
SUPPLIER: #, NAME, ADDRESS
Order
Part
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
Projects
WorkThe set of people, set of projects and set of working relationships.
Entity-Relationship Structures
Persons
Warehouses
SuppliersParts
ProjectsDepts
Are-In Work-On Use Hold
Supply
More complex relationship:See persons and parts
Projects use partsSuppliers supply partsWarehouses hold parts
Persons are in departmentsPersons work on projects
Entities can have multiple relationships
Companies
Vehicles
Owns Leases
More than two entity sets can be associated with the same
relationship set
Customers
Parts
StoresBuy
Each relationship in this set includes a person, a part boughtby the person, and the store where the purchase was made.
Relationship sets that include only twoentities are known as binary. More thantwo are known as N-ary.
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
StoresCustomerParts
Of By From
Exercise
• Employees are in departments
• Each department has sections.
Departments
Sections
Persons
Have
Employ
Are in
Are in is not needed
Departments
Sections
Persons
Have
Employ
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
Projects
Work
The set of people, set of projects and set of working relationships.
PERSON-IDNAMEADDRESS
PERSON-IDPROJECT-IDTIME-SPENT
PROJECT-IDSTART-DATEBUDGET
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
Projects
Work
The set of people, set of projects and set of working relationships.
PERSON-IDNAMEADDRESS
PERSON-IDPROJECT-IDTIME-SPENT
PROJECT-IDSTART-DATEBUDGET
N
M
A persona can appear in more than oneWORK relationship, and so can a project.If there was a limit to the number of times anentity can take part in the relationship, thenN or M would be replaced by the actualmaximum number.
Cardinality - Example
Manager
Projects
Manage
The set of people, set of projects and set of working relationships.
MANAGER-IDNAMEADDRESS
PERSON-IDPROJECT-IDTIME-SPENT
PROJECT-IDSTART-DATEBUDGET
1
N
Here a project has one (1) manager, whereasa manager can manage any number (N) of projects.
Cardinality - Example
Manager
Projects
Manage
The set of people, set of projects and set of working relationships.
MANAGER-IDNAMEADDRESS
PERSON-IDPROJECT-IDTIME-SPENT
PROJECT-IDSTART-DATEBUDGET
1
N
The denotes optional participation on theproject. If it is mandatory, then there is no placed there.
Issues in building an ER-Diagram
• 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:
PERSONSPERSON-IDDATE-OF-BIRTHQULIFICATION* (asterisk means it is multivalued-repeating)ADDRESS(NUMBER, STREET, SUBURB)Non-simple attributes
Addresses has structured attributes.These should be replaced in the final diagramby relationships.
Ex: Removing multivalued and structured attributes
Persons
ADDRESSESQUALIFICATIONS
HAVE LIVE-AT
PERSON-IDDATE-OF-BIRTH
PERSON-IDNUMBERSTREETSUBURB
NUMBERSTREETSUBURB
PERSON-IDQUALIFICATION
QUALIFICATION
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.
PROJECTS
TASKS
PROJECT-IDDATE-STARTED
PROJECT-IDTASK-NOBUDGET
INVOICES
INVOICE-LINES
INVOICE-NOINVOICE-DATE
INVOICE-NOLINE-NOAMOUNT
Note: dependent entities have composite identifiers
Machines
Use
ProjectsPROJECTS
MachineAvailability
Machines
Use
N
M
N
M
SUBSETS
• Example: – generic: loan applications– Different types of loan applications which have
unique attributes
PERSONS
COURSES
TEACHERS STUDENTS
TEACHES TAKES
PERSON-IDDATE-HIRED
PERSON-IDMAJOR
PERSON-IDCOURSE-NOSEMESTER-TAUGHT
PERSON-IDCOURSE-NOSEMESTER-TAKENRESULT
COURSE-NOCOURSE-NAME
N
MM
N
PERSON-IDNAMEADDRESS
Occurrence diagram for entity set PERSONSand its subsets
STAFF STUDENTS
CLUB-MEMBERS
COURTS
USE
STUDENT-IDMAJORMEMBER-NO
STAFF-IDDATE-JOINEDMEMBER-NO.
MEMBER-NO.MEMBER-GRADEDATE-JOINED-CLUB
MEMBER-NOCOURT-NONO-TIMES-USED
COURT-NOLOCATION
N
M
COMBININGSUBSETSFROM A NUMBEROF ENTITIES