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.