newark lawn care (nlc). serviceemployee contractproperty salespersonmanager 1,1 customer 0,n cash...
Post on 03-Jan-2016
215 Views
Preview:
TRANSCRIPT
Newark Lawn Care (NLC)
Service Employee
Contract Property
salespersonmanager
1,1Customer
0,N
CashReceipt
Truck
Equipment
CashDisbursementLease
Cash
EquipmentType
Maintenance
Schedule
MaintenanceType
Vendor
references
0,1
0,N
LL
0,N
1,1
1,N 1,N
0,N0,1
1,1
0,N
1,1 0,N
0,1
0,N
1,1
0,N
1,N 1,N
0,N 1,1
1,1
1,N
0,N 0,N 0,N1,1
amount
1,1
0,N
1,N 1,N
1,1 1,1
0,N
1,N
0,N
1,1
duration
cost
1,N 0,N
HL
0,N
1,N
LL
0,N 1,1
HL
code name address tel
Customer
CUSTOMER (references) CUSTOMER (referenced by)
ReferenceS S
# description start-date end-date
Contract
CUSTOMER
S
ticket-# date
Service
CONTRACT
S
price
id address description
Property
PROPERTY CONTRACT
E
E 1
1
number name
Employee
address date-hired
SALESPERSON MANAGER O
S S
Property-Contract
start-time end-time
code model-name
Truck
descriptionSERVICE TRUCK
E
Service-Truck
EMPLOYEE SERVICE
Employee-ServiceE 2
2
E
CASH-RECEIPT O
S
rano date amount CASH
Cash Receipt
S 3
3 number description
Cash
SCUSTOMER
4
4
S
EQUIPMENT SERVICE
Equipment-Service
code description
Equipment
5
E
5
id start-date end-date
Lease
description amount EQUIPMENT
E
VENDOR
id name address
VENDOR
5
S
6
7
8
EquipmentType
S 9
10
S
check-# date
Cash Disbursement
CASH
LEASE CASH DISBURSEMENT
Lease-CashDisbursement
amount
S
6 S
S 7VENDOR
8 S
code description
EquipmentType
code description
MaintenanceType
9 S code frequency
Schedule
code description
Maintenance
MAINTENANCE MAINTENANCE TYPE
Maintenance-MaintenanceType
cost duration
S
EQUIPMENT
E
10
EquipmentType MaintenanceType
E
E
A We give a substantial discount if a customer references another customer and the latter signs a contract.
1
Customers can be referenced only once (by another customer); we only consider the first reference we receive for a customer.
Customerreferences
0,NA
B
0,1
D E
Most customers do not provide references (< 20%). B
A few customers have given us more than one reference. C
C
Most customers that sign a contract with us have not been referenced by another customer (<15%). D
E
LL
A We record all potential customers in our system – i.e., people we have contacted but have not signed a contract yet.
B There is exactly one customer per contract.
C We have a very high renewal rate (as I said, our customers are happy with our service). Most of our customers sign a new contract every year.
2
Contract Customer
0,N
CA
1,1
B
A It is impossible to record a contract without recording its properties.
B We only record the properties that we service (and thus for which a contract exists).
C We work with one-year contracts and the contracts need to be renewed for each property every year.
3
Contract Property
1,N
CB
1,N
A
Employee
0,N
A There is exactly one manager per contract.
F
4
C Most of our managers manage more than one contract
B It sometimes takes more than two weeks to find a manager for a contract (after it has been signed)
E
Contract
salesperson
manager
1,1
A
D When a contract is signed (and recorded), we record the salesperson. There is exactly one salesperson per contract.
0,1
E We expect salespeople to sign at least 20 (!) contracts every year.
F Employees who are salespeople can not be managers and vice versa.
0,N
F CB
D
G Obviously, we will have manager information for most contracts (>98%).
HLG
A Most contracts are signed (and recorded into our system) days or weeks before the first service.
B Depending on the weather, there are somewhere between thirty and forty services per contract.
C
5
Contract Service
0,N
BA
1,1
C
We never cut a lawn (i.e. provide a service) without having a contract. There is exactly one contract per service.
A Our trucks are being used for hundreds of services.
B
C
6
Service Truck
1,N
DC
1,N
AB
We only consider trucks that have been used for at least one service.
We need transportation (and thus a truck) for every service.
D For some services we need more than one truck.
A We use two or more employees for most services.
B
C
7
Service Employee
1,N
AB
0,N
CD
D
It is impossible to have a service without an employee.
The same employee can cut more than five lawns a day (5 services).
Salespeople or managers never cut lawns (and are thus not involved in services).
A At the end of the month, ….
B
C
8
ServiceCash
Receipt
0,1
BA
0,N
CD
D
We ask them to pay the full amount within thirty days; no installments.
… for all services we performed that month.
Some of our customers pay a fixed amount at the beginning of the year that covers all their expenses. We give a substantial discount for such prepayments.
E Obviously, we have recorded payment (cash receipt) information for most services (>95%).
E
HL
A There is exactly one customer per cash receipt.
B
C
9
Cash Receipt Customer
0,N
CB
We record all potential customers in our system – i.e., people we have contacted but have not signed a contract yet.
We have a very high renewal rate (as I said, our customers are happy with our service). Most of our customers sign a new contract every year. At the end of the month, we ask customers to pay for all services we performed that month.
1,1
A
A We immediately deposit all checks from customers into one account (the same account).
B
10
CashReceipt
Cash
0,N
AC
C
We immediately deposit all checks from customers into one account (the same account). It is impossible to record a cash receipt without recording its account.
We actually opened a second account last month but we haven’t used it yet (no deposits or cash receipts; no withdrawals or cash disbursements).
1,1
B
A First, at least one piece of equipment is required for each service.
B
C
11
Service Equipment
1,N 0,N
BCA
Most pieces of equipment are used for thousands of services.
We leased a tractor earlier this month that we haven’t used in a service yet.
A We need to record the leases for each piece of equipment. Last month, we renewed the lease for one of our tractors (we got a really good deal).
B
C
12
Equipment Lease
1,N 1,1
BCA
We need to record a separate lease for each piece of equipment.
We lease equipment exclusively; i.e., we don’t lease anything else.
A There is exactly one vendor per lease.
B
C
13
Lease Vendor
1,1 0,N
BCA
We record all vendors in the Newark area that lease equipment (and there are many more than you might think). We have done business with less than 15% of them.
We lease the two tractors mentioned above (with the 5 year leases) from the same vendor.
LL
A
14
LeaseCash
Disbursement
0,N 0,N
A
Sometimes we pay at the beginning of the lease period, sometimes we pay at the end of the lease period, sometimes we pay the full amount, sometimes we use installments, sometimes we make prepayments, sometimes we pay for multiple leases with one check, … , anything goes!
amount
A Again, we use the same account for deposits and withdrawals.
B
15
CashDisbursement
Cash
0,N
AC
C
There is exactly one (cash) account for each cash disbursement.
We actually opened a second account last month but we haven’t used it yet (no deposits or cash receipts; no withdrawals or cash disbursements).
1,1
B
A There is exactly one vendor for each cash disbursement.
B
16
CashDisbursement
Vendor
0,N
CB
C
We record all vendors in the Newark area that lease equipment (and there are many more than you might think). We have done business with less than 15% of them.
Obviously, there can be more than one payment for the same vendor.
1,1
A
A There is exactly one “equipment type” for each piece of equipment.
B
17
EquipmentEquipment
Type
1,1 0,N
BC
C
As mentioned above, we have multiple tractors.
One of the equipment types in our system is “push lawn mower.” However, we have never leased one and we thus don’t have one. We might lease one down the road.
A
A There is at least one schedule per equipment type.
B
18
EquipmentType
Schedule
1,N 1,1
C
There is exactly one equipment type per schedule.
There is at least one schedule per “maintenance type.”
A
MaintenanceType
1,1
DB
1,N
C
D There is exactly one “maintenance type” per schedule.
A We have a brand new tractor that hasn’t required maintenance yet.
B
C
19
Equipment Maintenance
0,N
BA
1,1
However, some of our older tractors have had maintenance hundreds of times.
There is exactly one piece of equipment per maintenance
C
A At least one “maintenance type” is required per maintenance.
B
C20
MaintenanceMaintenance
Type
0,N
B
1,N
We want to record all possible “maintenance types” in our system, even when we don’t need them.
A
duration cost
C The MAX-Cardinality is “N” since there are relationship attributes.
top related