The bond between successful business and

efficient databases is behind a door.Be sure to knock at us!

Alex, Ioli, Dan and JoanneProject manager: Emanuela Cerchez

PIM Copy CenterWe have studied the PIM Copy Center businessin order to model an efficient database to suit its needs.

PIM Copy Center. References

• PIM Copy Center websitewww.pimcopy.ro

• Interview withAndrei Tudorean, PIM Copy Center managerPhone: +40 232 261 851 Mobile phone: +40 788 332 831Email: andrei@pimcopy.ro

• FedEx Kinko’s – Office and Print ServicesIn order to find a similar business, visit the “In-Store Services” section of www.fedex.com/us/officeprint/main/

PIM Copy Center. Business needs

• PIM Copy Center is a grown business that has 5 copy centers in Iaşi and two in other cities. One of the copy centers is also a typography. We need to track data about these centers and about the employees that work there.

• Among with copy services, PIM sells different kinds of products like blank paper, staples, blank CDs and so on. We need to know the quantity in stock of these products and track this data in time.

• PIM also handles orders from clients, for either copy centers or the typography. We need to be able to access details and status about these orders.

• We therefore need an efficient, well organized database.

Information Requirements

• Business’s employees work in copy centers, make the use of specialized equipment, interact with different kinds of clients.

• Each copy center has an employee responsible of it.• Each employee is paid a salary every two weeks. The

salary is preset at hiring, according to his schedule, and may be recalculated when he changes his schedule. Employees’ extra hours and penalties modify the salary.

• Employees work in shifts which are scheduled every week. For every employee we set a personal schedule. We don’t need to keep track of schedules longer than four weeks.

Information Requirements

• Although we have many kinds of equipment we only need to track data about printers and computers.

• We’re only interested in some of the computer components. For printers we need to remember changes for basic components.

• All the copy centers are supplied by one storehouse. We must also know every company that provides our storehouse with materials and the buying price of those materials.

Information Requirements

• We keep track of incoming/outgoing materials and product from copy centers and storehouse.

• The business provides a set of services that may or may not involve the products from the stock of one copy center.

• Clients use the services via orders. We only keep track of the orders that can’t be handled immediately, therefore we remember only the clients who place this kind of orders.

• We need to mention that there are two types of orders as the business also provides typography services.

Structural Rules: Employees. Clients. Orders

• For each EMPLOYEE we need to know their first name, last name, address, contract number, ID card number, birth date, mobile phone number, home phone number, civil status, studies, professional experience, psychological evaluation, qualification, availability. We assign an artificial id to each EMPLOYEE.

• For each CLIENT we need to know their first name and last name and the ID card number.

• EMPLOYEEs may receive or handle ORDERs. CLIENTs place ORDERs. We don't keep track of CLIENTs that don't place any ORDERs.

Structural Rules: Employees. Clients. Orders

• For each ORDER we need to know the date and time (as part of the unique identifier) it was requested, number of copies ordered. And order must be REGULAR or TYPOGRAPHY. For each TYPOGRAPHY ORDER we keep track of its due date.

• Each TYPOGRAPHY ORDER may be assigned one or more PARTs OF ORDER. Each PART OF ORDER has a date time attribute (as part of the unique identifier) and a quantity telling how many copies were given to the CLIENT.

ERD: Employees. Clients. Orders

placed by

EMPLOYEE# id* first name* last name* address(#1) contract no(#2) ID card no* birth date* mobile phone* home phone* civil status* studies* prof_exp* psycho_eval* qualification* availability

ORDER# date time* copies no

REGULAR

TYPOGRAPHYhandle

handled by

receive

received by

assigned

of

PART OF ORDER# date time* quantity

place

CLIENT# ID card no* first name* last name

Structural Rules: Employees. Salaries. Shifts

• Each EMPLOYEE must have one or more SALARY entries. Each SALARY has a date time attribute (as part of the unique identifier) and a value. The SALARY entity models historical data.

• Each EMPLOYEE must work in one or more SHIFTs in time. Each SHIFT has a date time attribute (as part of the unique identifier) and the number of hours for that shift.

ERD: Employees. Salaries. Shifts

work in

EMPLOYEE# id* first name* last name* address(#1) contract no(#2) ID card no* birth date* mobile phone* home phone* civil status* studies* prof_exp* psycho_eval* qualification* availability for

SHIFT# date time* number of hours

have

for

SALARY# date time* salary

Structural Rules: Orders. Services. Products

• ORDERs make the use of SERVICEs.• For each SERVICE we keep track of a name, the price

per unit and optionally a range start, a range end and a description. It also has an artificial id assigned.

• Some SERVICEs require the use of PRODUCTs. (e.g. A4 Print requires A4 Paper)

• For each PRODUCT we keep track of the name and price per unit. It is also assigned an artificial id.

ERD: Orders. Services. Products

ORDER# date time* copies

REGULAR

TYPOGRAPHY

have

of

SERVICES LIST* count

SERVICE# id* name° range start° range end* price per unit° description

PRODUCT# id* name* price per unit

list

on

required by

require

Structural Rules: Locations. Products. Suppliers

• For each LOCATION we store its shorthand name (as the unique identifier) and the address. Each LOCATIONs must either be a COPY CENTER or a STOREHOUSE.

• The STOCK entity is a joint between LOCATIONs and PRODUCTs, used to refer to PRODUCTs at different LOCATIONs.

• The TRANSFER entity has a date time attribute (as part of the unique identifier) and a quantity value. Each TRANSFER is assigned to a STOCK to determine incoming and outgoing (or sold) stock.

ERD: Locations. Products. Suppliers

the provider of

for provided by

listed on

LOCATION# name* address

STOREHOUSE

COPY CENTER

have

of

STOCK

PRODUCT# id* name* price per unit

TRANSFER# date time* quantity° price

SUPPLIER# id* name

have

of

Structural Rules: Employees. Locations. Transfers

• Each EMPLOYEE may be responsible for one LOCATION. Each EMPLOYEE must work in one and only one LOCATION.

• An EMPLOYEE may order one or more TRANSFERs.

responsible for

EMPLOYEE# id* first name* last name* address(#1) contract no(#2) ID card no* birth date* mobile phone* home phone* civil status* studies* prof_exp* psycho_eval* qualification* availability

LOCATION# name* address

STOREHOUSE

supervised byCOPY CENTER

work in

assigned

TRANSFER# date time* quantity° price

ordered by

order

Structural Rules: Location. Equipment

• For each EQUIPMENT we assign an artificial identifier. EQUIPMENTs must be either COMPUTERs or PRINTERs. We don't keep track of any other kind of equipment. For each COMPUTER we keep track of the processor, monitor, RAM, HDD, CD-R, CD-RW, DVD-RW. For each PRINTER we only need to know its type and an optional description.

• Each PRINTER must have one or more COMPONENTs. For each COMPONENT we keep track of its name and we assign an artificial id. COMPONENTs may be changed in time. When a COMPONENT CHANGE occurs we keep track of the date time (as partial unique identifier) and the value of printer's counter value.

Structural Rules: Location. Equipment

• EQUIPMENTs may be moved from one LOCATION to another. We keep track of where it's been over time. The EQUIPMENT LIST entity handles positioning of EQUIPMENTs at different LOCATIONs in time.

ERD: Location. Equipment

on

LOCATION# name* address

STOREHOUSE

COPY CENTER

list

EQUIPMENT# id

COMPUTER* processor* monitor* RAM* HDD° CD-R° CD-RW° DVD-RW

PRINTER* type° description

EQUIPMENTLIST# date time

on

list

have

inside

COMPONENT# id* name

have

occur to

COMPONENTCHANGE# date time* value

Procedural Rules

• EMPLOYEEs may practice their skills by working, improving their qualification.

• An EMPLOYEE`s SALARY is negotiated with the manager whenever changed. EMPLOYEEs have fixed salaries.

• The schedule for each week is made in advance and the database is populated with EMPLOYEEs shifts.

• When EMPLOYEEs order a transfer they should have an approval.

• Every evening sold stock is quantified and the database is populated with this data.

• Stock may be transferred either from a supplier to the storehouse or from a storehouse to a copy center.

Programmatic Rules

• EMPLOYEE`s current salary is the latest SALARY entry (as of the date time attribute).

• SHIFTs are deleted when they are four weeks overdue.• The total price of a SERVICE is the price of the

PRODUCT it requires plus its own price.• The total price of an ORDER is the sum of all SERVICEs

it requests multiplied by the number of copies.• A REGULAR ORDER is always complete on the next

day after it was placed.• A TYPOGRAPHY ORDER is complete when the sum of

quantities in all associated PARTs of ORDER equals the copies no attribute.

Programmatic Rules

• The current LOCATION of one EQUIPMENT is determined using the most recent of its EQUIPMENT LIST instances.

• The current available stock of one PRODUCT at one LOCATION is determined by summing all TRANSFER quantity values of the STOCK instance for that PRODUCT and LOCATION.

• When the PRINTER is firstly added in the database the values of its components` counters are also added in the database.

Explanations

• Attribute prof_exp for EMPLOYEE is a text describing his/her professional experience.

• Attribute psycho_eval for the same entity is a text describing the result of the psychological evaluation.

• Qualification attribute for EMPLOYEE is either 1, 2, 3. 0 means he ca only make copies, 1 he can also print, 2 he is qualified to use all equipment at the copy center.

• Attribute availability is a number between 1 and 3 describing how available is an EMPLOYEE for being called when there is an emergency.

• REGULAR ORDERs don’t need a due date as their due date is by default the next day.

Explanations

• Attribute copies no for ORDER says how many copies of the entire order (list of services) should be done. (e.g. When you copy a book you might want two copies of it.) Attributes description for both PRINTER and SERVICE is a text with additional explanations.

• The quantity attribute of TRANSFER is positive when counting incoming stock and negative when counting outgoing or sold stock.

• The price for TRANSFER is the price per unit.• Attributes range start and range end are used for the

kind of services that are differentiated by range and cost. The cost can’t be calculated programmatically by range. (e.g. A4Print 1-29 is 1000 lei, while 30-149 is 550 lei.)

Assumptions. ConstraintsASSUMPTIONASSUMPTION• Now there is only one STOREHOUSE to keep track of.

We assume that in the future there may be more than one STOREHOUSE.

CONSTRAINTSCONSTRAINTS• TRANSFERs in relationship with a SUPPLIER must

have the price attribute filled and must be in relationship with one STOREHOUSE (through a STOCK instance).

• TRANSFERs not in relationship with a SUPLLIER must not have the price filled and must be in relationship with one COPY CENTER (through a STOCK instance).

Constraints

• Sum of all quantity attributes from PART OF ORDER has to be lower than or equal to the copies no attribute of their respective TYPOGRAPHY ORDER.

• We don’t need time constraints, as we only keep track of the date and time changes occurred.

for

the provider of

by

provided by

listed on

placed by

on

responsible for

work in

EMPLOYEE# id* first name* last name* address(#1) contract no(#2) ID card no* birth date* mobile phone* home phone* civil status* studies* prof_exp* psycho_eval* qualification* availability for

SHIFT# date time* number of hours

have

for

SALARY# date time* salary

LOCATION# name* address

STOREHOUSE

supervised byCOPY CENTER

work in

assigned

list

EQUIPMENT# id

COMPUTER* processor* monitor* RAM* HDD° CD-R° CD-RW° DVD-RW

PRINTER* type° description

EQUIPMENTLIST# date time

on

list

have

inside

COMPONENT# id* name

have

of

STOCK

ORDER# date time* copies no

REGULAR

TYPOGRAPHYhandle

handled by

receive

received by

assigned

of

PART OF ORDER# date time* quantity

have

of

SERVICES LIST* count

SERVICE# id* name° range start° range end* price per unit° description

PRODUCT# id* name* price per unit

place

CLIENT# ID card no* first name* last name

have

occur to

COMPONENTCHANGE# date time* value

list

onTRANSFER# date time* quantity° price

required by

require

SUPPLIER# id* name

order

have

of

ERD

Sample MappingEMPLOYEE (EPE)

key type optionality column name

pk * id

* fname

* lname

* address

uk * contract_no

uk * IDcard_no

* birthdate

* mobilephone

* homephone

* civilstatus

* studies

* profexp

* psychoeval

* qualification

* availability

fk * LCN_name

LOCATION (LCN)

key type optionality column name

pk * name

* address

* LCN_type

fk, uk ° EPE_id

PRODUCT (PDT)

key type optionality column name

pk * id

* name

* price_per_unit

SERVICE (SVE)

key type optionality column name

pk * id

* name

* priceperunit

° rngstart

° rngend

° description

fk ° PDT_id

Sample DataEMPLOYEE

The dotted lines skip attributes we did not consider very important.

PRODUCT SERVICE

LOCATION

id fname lname qualification availability LCN_name3 Laura Jianu 1 2 HABITAT5 Alexandru Codreanu 1 1 HABITAT

17 Ciprian Moraru 2 3 COPOU62 Petru Olariu 3 2 COPOU72 Irina Pintilie 1 1 HABITAT

id name address LCN_type EPE_id0 STOREHOUSE b-dul Socola, nr. 13 storehouse NULL1 COPOU sos Stefan cel Mare si Sfant, nr. 11 copy center 622 HABITAT b-dul Carol I, nr.4 copy center 53 INDEPENDENTEI b-dul Independentei, nr. 1 copy center 134 TUDOR str Ciurchi, nr. 80 copy center 21

id name price_per_unit13 A4 White Paper 25016 A4 Cardboard 3,00019 A3 White Paper 50025 CD 20,00026 CD-RW 50,000

id name rngstart rngend price_per_unit description PDT_id3 A4 Printing (one side) 1 29 750 134 A4 Printing (one side) 30 1499 300 13

19 Photo Editing (/minute) NULL NULL 10,000 NULL21 Binding NULL NULL 5,000 3723 A4 T-Shirt Printing NULL NULL 100,000 49

Irina PintilieSchedule for the current week

Report: Schedule

NOV 15MON

NOV 16TUE

NOV 17WED

NOV 18THU

NOV 19FRI

NOV 20SAT

NOV 21SUN

8:00

12:00

16:00

20:00

0:00

4:00

The user needs to select an employee and the week to view (current or one of the past four).

Report: Salaries

SalariesSeptember 1st – November 1st

Employees with qualification greater than 1.

The user needs to select the employees and time range.

SEP 1 SEP 15 OCT 1 OCT 15 NOV 1

Ciprian Moraru 5,560,000 5,560,000 5,560,000 5,560,000 5,560,000

Petru Olariu 4,300,000 4,300,000 4,300,000 5,560,000 5,560,000

Report: Transfers

TransfersSeptember 1st – November 1stLocation: HABITAT Product: A4 White Paper

The user needs to select the location, the product and the time range. The database stores quantity as pages and a pack of paper has 500 pages. We store the time with data, but it’s not shown here, as we only show the totals for “ordered” and “consumed”.

Quantity

WED Nov 10, 2004 9 packs ordered

WED Nov 10, 2004 11 packs consumed

THU Nov 11, 2004 11 packs ordered

THU Nov 11, 2004 8 packs consumed

FRI Nov 12, 2004 14 packs ordered

FRI Nov 12, 2004 16 packs consumed

SAT Nov 13, 2004 16 packs ordered

SAT Nov 13, 2004 7 packs consumed

SUN Nov 14, 2004 3 packs ordered

SUN Nov 14, 2004 4 packs consumed

MON Nov 15, 2004 8 packs ordered

MON Nov 15, 2004 15 packs consumed

Report: Transfers

TransfersSeptember 1st – November 1stConsumptions as chart.Location: HABITAT Product: A4 White Paper

The user needs to select the location, the product and the time range.

0

2

4

6

8

10

12

14

16

18

Report: Orders

OrdersActive orders.

1Silvian MelinteMX 156916TUE 16 Nov, 2004

3Iuliana ApostolMX 347667TUE 16 Nov, 2004

1Elena SaftaMX 758461TUE 16 Nov, 2004

CopiesClient nameClient ID cardDate

12,0001Arc 16 mmBinding

550132A4 White PaperA4 Print (30-149)

Total price per unitCountMedia (product)Service

PIM Copy CenterEven with an efficient database model ready,good databases must be brought to life by good

front-end applications.

Thank you for following us through our presentation.

The bond between successful business and efficient databases is behind a door.Be sure to knock at us!

Iolanda Popa (Ioli)

Motto: “Trying to turn the lights on.”

Alexandru Mihai Bîrsan (Alex)

Motto: “Happiness comes of the capacity to feel deeply, to enjoy simply, to think freely, to risk life and be needed.”

(Storm Jamison)

Dan Andrei Diac (Dan)

Motto: “Our greatest glory consists not in never falling, but in rising every time we fall.”

(Confucius)

Ioana Comănici (Joanne)

Motto: “The greatest trick the devil ever pulled was convincing the world he didn't exist.”

(The Usual Suspects)

www.liis.ro/~spider/live/nefladabee

Liceul de Informatică “Grigore C. Moisil”, Iasi, România