Newark Lawn Care (NLC)

1
Newark Lawn Care (NLC)
2
1,N
1,N
references
Equipment Type
Maintenance Type
LL
1,1
1,1
0,1
1,1
0,N
Schedule
Customer
0,N
duration
0,N
1,N
1,N
cost
1,N
Contract
Property
Maintenance
1,1
0,N
salesperson
manager
1,1
0,N
0,1
0,N
0,N
1,1
0,N
HL
1,N
1,N
Service
Employee
Truck
1,N
0,N
1,N
1,1
0,1
1,1
HL
0,N
0,N
0,N
Cash
Cash Receipt
Equipment
1,1
0,N
1,N
amount
1,1
LL
0,N
1,1
0,N
1,1
0,N
0,N
Cash Disbursement
Lease
Vendor
0,N
1,1
3
CUSTOMER (references)
CUSTOMER (referenced by)
id
address
description
Reference
S
Property
S
E
code
name
address
tel
PROPERTY
CONTRACT
4
Customer
Property-Contract
S
E
1

description
start-date
end-date
CUSTOMER
price
1
SALESPERSON
MANAGER O
Contract
S
S
S
S
3
2
ticket-
date
CONTRACT
number
name
address
date-hired
start-time
end-time
CASH-RECEIPT O
5
Service
Employee
E
E
S
SERVICE
TRUCK
EMPLOYEE
SERVICE
code
model-name
description
Employee-Service
Truck
Service-Truck
E
2
7
rano
date
amount
CASH
number
description
CUSTOMER
3
S
Cash Receipt
Cash
4
S
10
S
E
5
9
8
S
EQUIPMENT
SERVICE
code
description
EquipmentType
5
id
name
address
Equipment
Equipment-Service
VENDOR
E
S
id
start-date
end-date
description
amount
EQUIPMENT
VENDOR
6
Lease
4
8
S
check-
date
CASH
VENDOR
S
7
Cash Disbursement
S
6
LEASE
CASH DISBURSEMENT
amount
S
Lease-CashDisbursement
E
9
code
description
S
code
frequency
EquipmentType
MaintenanceType
EquipmentType
Schedule
E
code
description
S
MaintenanceType
code
description
MAINTENANCE
MAINTENANCE TYPE
cost
duration
EQUIPMENT
Maintenance-MaintenanceType
Maintenance
10
E
5
A
We give a substantial discount if a customer
references another customer and the latter signs
a contract.
B
Most customers do not provide references (lt 20).
C
A few customers have given us more than one
reference.
D
Most customers that sign a contract with us have
not been referenced by another customer (lt15).
Customers can be referenced only once (by another
customer) we only consider the first reference
we receive for a customer.
E
B
C
0,N
A
Customer
references
0,1
LL
D
E
1
6
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.
C
A
B
0,N
1,1
Contract
Customer
2
7
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.
C
B
A
1,N
1,N
Contract
Property
3
8
A
There is exactly one manager per contract.
B
It sometimes takes more than two weeks to find a
manager for a contract (after it has been signed)
C
Most of our managers manage more than one
contract
D
When a contract is signed (and recorded), we
record the salesperson. There is exactly one
salesperson per contract.
E
We expect salespeople to sign at least 20 (!)
contracts every year.
F
Employees who are salespeople can not be managers
and vice versa.
G
Obviously, we will have manager information for
most contracts (gt98).
D
F
E
salesperson
1,1
0,N
Employee
Contract
0,1
0,N
HL
G
manager
A
B
4
F
C
9
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.
We never cut a lawn (i.e. provide a service)
without having a contract. There is exactly one
contract per service.
C
B
A
C
0,N
1,1
Contract
Service
5
10
A
Our trucks are being used for hundreds of
services.
B
We only consider trucks that have been used for
at least one service.
C
We need transportation (and thus a truck) for
every service.
D
For some services we need more than one truck.
D
C
A
B
1,N
1,N
Service
Truck
6
11
A
We use two or more employees for most services.
B
It is impossible to have a service without an
employee.
C
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).
D
A
B
C
D
1,N
0,N
Service
Employee
7
12
A
At the end of the month, .
B
We ask them to pay the full amount within thirty
days no installments.
C
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.
D
Obviously, we have recorded payment (cash
receipt) information for most services (gt95).
E
B
A
C
D
E
0,1
0,N
Service
Cash Receipt
HL
8
13
A
There is exactly one customer per cash receipt.
We record all potential customers in our system
i.e., people we have contacted but have not
signed a contract yet.
B
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. At
the end of the month, we ask customers to pay for
all services we performed that month.
C
B
A
0,N
1,1
Cash Receipt
Customer
9
14
A
We immediately deposit all checks from customers
into one account (the same account).
B
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 havent used it yet (no deposits or cash
receipts no withdrawals or cash disbursements).
C
A
C
B
0,N
1,1
Cash Receipt
Cash
10
15
A
First, at least one piece of equipment is
required for each service.
B
Most pieces of equipment are used for thousands
of services.
C
We leased a tractor earlier this month that we
havent used in a service yet.
B
C
A
1,N
0,N
Service
Equipment
11
16
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
We need to record a separate lease for each piece
of equipment.
C
We lease equipment exclusively i.e., we dont
lease anything else.
B
C
A
1,N
1,1
Equipment
Lease
12
17
A
There is exactly one vendor per lease.
We lease the two tractors mentioned above (with
the 5 year leases) from the same vendor.
B
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.
B
C
A
1,1
0,N
Lease
Vendor
LL
13
18
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!
A
0,N
0,N
Lease
Cash Disbursement
amount
14
19
A
Again, we use the same account for deposits and
withdrawals.
B
There is exactly one (cash) account for each cash
disbursement.
We actually opened a second account last month
but we havent used it yet (no deposits or cash
receipts no withdrawals or cash disbursements).
C
A
C
B
0,N
1,1
Cash Disbursement
Cash
15
20
A
There is exactly one vendor for each cash
disbursement.
B
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.
C
Obviously, there can be more than one payment for
the same vendor.
C
B
A
0,N
1,1
Cash Disbursement
Vendor
16
21
A
There is exactly one equipment type for each
piece of equipment.
B
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 dont have one. We might lease
one down the road.
C
B
C
A
1,1
0,N
Equipment
Equipment Type
17
22
A
There is at least one schedule per equipment type.
B
There is exactly one equipment type per schedule.
C
There is at least one schedule per maintenance
type.
D
There is exactly one maintenance type per
schedule.
A
D
B
C
1,N
1,1
1,1
1,N
Equipment Type
Schedule
Maintenance Type
18
23
A
We have a brand new tractor that hasnt required
maintenance yet.
B
However, some of our older tractors have had
maintenance hundreds of times.
C
There is exactly one piece of equipment per
maintenance
B
A
C
0,N
1,1
Equipment
Maintenance
19
24
A
At least one maintenance type is required per
maintenance.
B
We want to record all possible maintenance
types in our system, even when we dont need
them.
C
The MAX-Cardinality is N since there are
relationship attributes.
B
A
0,N
1,N
Maintenance
Maintenance Type
duration
cost
C
20