The bond between successful business and efficient databases is behind a door.

1. The bond between successful business and efficient databases is behind a door. Be sure to knock at us! Alex, Ioli, Dan and Joanne Project manager: Emanuela Cerchez

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

3. 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/

4. 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.

5. 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.

6. 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.

7. 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.

8. 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.

9. 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.

10. CLIENT # ID card no * first name * last name place placed by ORDER # date time * copies no REGULAR receive 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 received by PART OF ORDER # date time * quantity handle TYPOGRAPHY assigned handled by of ERD: Employees. Clients. Orders

11. 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.

12. 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 SALARY # date time * salary have for SHIFT # date time * number of hours work in for ERD: Employees. Salaries. Shifts

13. 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.

14. required by PRODUCT # id * name * price per unit SERVICE # id * name ° range start ° range end * price per unit ° description require on ORDER # date time * copies list SERVICES LIST * count have REGULAR of TYPOGRAPHY ERD: Orders. Services. Products

15. 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.

16. PRODUCT # id * name * price per unit LOCATION # name * address SUPPLIER # id * name STOREHOUSE listed on the provider of COPY CENTER for provided by STOCK TRANSFER # date time * quantity ° price have have of of ERD: Locations. Products. Suppliers

17. 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. 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 TRANSFER # date time * quantity ° price order STOREHOUSE ordered by COPY CENTER supervised by responsible for assigned work in

18. 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.

19. 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.

20. LOCATION # name * address STOREHOUSE EQUIPMENT # id COPY CENTER COMPUTER * processor * monitor * RAM * HDD ° CD-R ° CD-RW ° DVD-RW on list PRINTER * type ° description EQUIPMENT LIST # date time on list have inside COMPONENT CHANGE # date time * value COMPONENT # id * name have occur to ERD: Location. Equipment

21. 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.

22. 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.

23. 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.

24. 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.

25. 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.)

26. Assumptions. Constraints ASSUMPTION • Now there is only one STOREHOUSE to keep track of. We assume that in the future there may be more than one STOREHOUSE. CONSTRAINTS • 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).

27. 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.

28. required by PRODUCT # id * name * price per unit SERVICE # id * name ° range start ° range end * price per unit ° description ERD require SUPPLIER # id * name CLIENT # ID card no * first name * last name listed on the provider of place LOCATION # name * address for provided by have have STOCK TRANSFER # date time * quantity ° price placed by on of of ORDER # date time * copies no STOREHOUSE list by SERVICES LIST * count EQUIPMENT # id have REGULAR order of COPY CENTER supervised by receive 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 COMPUTER * processor * monitor * RAM * HDD ° CD-R ° CD-RW ° DVD-RW responsible for received by PART OF ORDER # date time * quantity handle TYPOGRAPHY assigned assigned handled by work in of on SALARY # date time * salary list have PRINTER * type ° description EQUIPMENT LIST # date time on for list SHIFT # date time * number of hours work in have for inside COMPONENT CHANGE # date time * value COMPONENT # id * name have occur to

29. Sample Mapping EMPLOYEE (EPE) LOCATION (LCN) PRODUCT (PDT) SERVICE (SVE)

30. Sample Data EMPLOYEE LOCATION PRODUCT SERVICE The dotted lines skip attributes we did not consider very important.

31. Report: Schedule Irina Pintilie Schedule for the current week 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).

32. Report: Salaries Salaries September 1st – November 1st Employees with qualification greater than 1. The user needs to select the employees and time range.

33. Report: Transfers Transfers September 1st – November 1st Location: 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”.

34. Report: Transfers Transfers September 1st – November 1st Consumptions as chart. Location: HABITAT Product: A4 White Paper The user needs to select the location, the product and the time range.

35. Date Client ID card Client name Copies 6 TUE 16 Nov, 2004 MX 758461 Elena Safta 1 Service Media (product) Count Total price per unit A4 Print (30-149) A4 White Paper 132 550 Binding Arc 16 mm 1 12,000 TUE 16 Nov, 2004 MX 347667 Iuliana Apostol 3 3 TUE 16 Nov, 2004 MX 156916 Silvian Melinte 1 3 Report: Orders Orders Active orders.

36. PIM Copy Center Even 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.

37. The bond between successful business and efficient databases is behind a door. Be sure to knock at us! www.liis.ro/~spider/live/nefladabee Ioana Comănici (Joanne) Motto: “The greatest trick the devil ever pulled was convincing the world he didn't exist.” (The Usual Suspects) 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) Liceul de Informatică “Grigore C. Moisil”, Iasi, România