Welcome to IDBS Center

1. Welcome to IDBS Center Innovative Data Base Solutions presents: Comat Auto Service Project Project Manager: Emanuela Cerchez

2. REFERENCES • Interview with mr. Valeriu Sarbusca, manager of Comat Auto Service Address: Str.Serg.Grigore Ioan, no. 7, Iasi, Romania Phone number:+40232.241.000Fax: +40232.240.699 • Websites referring to similar business: http://www.jiffylube.com/ http://datsonville.alldatashop.com/ Innovative Data Base Solutions :: Comat Auto Service ::

3. BUSINESS DESCRIPTION • Comat Auto Service provides services for Daewoo car models. They have a special license which allows them to provide certificated mechanical treatments and company warranties. • Working with such a company gives them the opportunity to obtain original car parts, directly from the company’s factories. • Because they work with many clients, some of them owners of more than one car (e.g. Taxi Companies) they need a way to keep track of their employees, clients, repaired cars, jobs done, warranties offered, and many more. Innovative Data Base Solutions :: Comat Auto Service ::

4. PROPOSED SOLUTION We offer them the solution they need, that consists in an optimized database model, which is the base for further applications development. Innovative Data Base Solutions :: Comat Auto Service ::

5. BUSINESS NEEDS • Comat Auto Service needs to hold details about their clients like: full name, phone number, address, an optional e-mail address and, only if the client is a firm, the fiscal code, account number. • A client can make appointments and the service needs to hold the appointments’ dates. • They need to hold information about their clients’ cars that were repaired, what was repaired (the labor and parts used for the rapairs) and the issued bills. • They also need to keep track of the parts they have in their storehouse. Innovative Data Base Solutions :: Comat Auto Service ::

6. BUSINESS NEEDS • The service needs to hold historical information such as: bills paid and issued over time and also information that modifies over time such as: price per hour of labor, price per km of transport, salaries paid for an employee (salaries and bonus-payments/penalties) and time-offs taken by these. • About its employees the service needs to know their CNP (the correspondent of Social Security Number), full name, phone number, address and job description. For its mechanics the service must keep track of worked hours. Innovative Data Base Solutions :: Comat Auto Service ::

7. FIRST INTERVIEW – INTIAL ERD Four main areas of interest for the service were identified after the interview: the center with its employees, the clients with their cars, jobs done for cars and the parts in the service’s storehouse. Therefore we modeled an initial ERD to include this four areas of interest (separated by position and color). Innovative Data Base Solutions :: Comat Auto Service ::

8. INITIAL ERD Innovative Data Base Solutions :: Comat Auto Service ::

9. INITIAL ERD – STRUCTURAL RULES • For the center we retained: name (name followed by city), which is its unique identifier, phone number, address, support phone number, website. • For the employees we retained as mandatory attributes: cnp, first name, last name, phone number, address, number of days off (maximum number of days off an employee could take in one year – specified in his employment contract) and as optional attributes: e-mail and special notes. • The cnp attribute of employee composed with the center’s id (name) form the employee’s entity unique identifier. Innovative Data Base Solutions :: Comat Auto Service ::

10. INITIAL ERD – STRUCTURAL RULES • For clients we retained as mandatory attributes: id (unique identifier), first name, last name, phone number, address and as optional attributes: e-mail and special notes (general description of the client). • For cars: we retained: registration number, chassis number, model, color (standard code that corresponds to the color of the car) and as optional attribute special notes. • The registration number and the chassis number attributes of car entity, together with client’s id attribute form the car’s entity unique identifier. • For job’s we retain: bill code, price and warranty (number of days the job is guaranteed). Innovative Data Base Solutions :: Comat Auto Service ::

11. INITIAL ERD – STRUCTURAL RULES • The job’s unique identifier is formed of the bill code attribute together with the car’s unique identifier (registration number, chassis number plus the client’s id). • For parts we retain: id (unique identifier), name, special notes (optional). Innovative Data Base Solutions :: Comat Auto Service ::

12. INITIAL ERD – STRUCTURAL RULES – RELATIONSHIPS • The center must be the employer of one or more employees. • Each employee must be employed by one and only one center. • The center must be the owner of one or more parts. • Each part must be owned by one and only one center. • Each part may be used in one ore more jobs. • Each job may use one ore more parts. • Each job must be for one ore more cars. • Each car must have one ore more jobs. • Each client must be the owner of one ore more cars. • Each car must be owned by one and only one client. Innovative Data Base Solutions :: Comat Auto Service ::

13. CLOSER ANALYSIS While we were documenting all the business rules we started getting a better idea about the business and its complexity. We started from the Initial ERD and we identified other entities and relationships. We noticed that the whole business works around those four areas of interest identified earlier so we modeled our ERD piece by piece, reuniting them at the end in the Final ERD. Next we will present you, in detail, the four Partial ERDs modeled by us. Innovative Data Base Solutions :: Comat Auto Service ::

14. FIRST PARTIAL ERD – NEEDS The main new entities found and modeled in the First Partial ERD are: center, hour price, due, employee, salary, time off, extra payment. In modeling this first part of the business we needed to take into account questions like: • Can we track the employee’s salary modifications over time? • Can an employee be rewarded with prizes for his work? • Can we track the evolution of the price per hour practiced? • Can we find out the service’s expenses for a certain period of time? • How can we obtain an employee classification depending of their specialization? and many more. Innovative Data Base Solutions :: Comat Auto Service ::

15. FIRST PARTIAL ERD Innovative Data Base Solutions :: Comat Auto Service ::

16. FIRST PARTIAL ERD – STRUCTURAL RULES • Like in the Initial ERD, for the center entity we retained: name, which is its unique identifier, phone number, address, support phone number, website. • For the hour price entity, which models price per hour of labor modifications over time, we retained: start date (the date when we started using that price) and price. • The start date attribute, together with the center’s id (name) forms the hour price’s entity unique identifier. • For the due entity, which represents the bills that the Auto Service has to pay to other services providers (e.g. electricity, water), we retained: bill code (unique identifier) and date (the date when the bill was issued by the providers). Innovative Data Base Solutions :: Comat Auto Service ::

17. FIRST PARTIAL ERD – STRUCTURAL RULES • We added three subtypes to the initial employee entity, which make the difference between different types of employees, identified by the employee entity’s unique identifier (cnp). For the engineer subtype we retained: specialization, for the mechanic: id (another unique identifier besides the employee’s id - cnp) and domain (of activity) and for the other subtype: job description. • For the salary entity we retained: start date, value and an optional attribute description (contains eventual explanations). • The start date attribute of the salary entity together with the employee entity’s id (cnp) form the salary entity’s unique identifier. Innovative Data Base Solutions :: Comat Auto Service ::

18. FIRST PARTIAL ERD – STRUCTURAL RULES • For the extra payment we retained: date (the date when it was given) and the value (if positive it represents an extra payment and if negative it represents a penalty). • The occasion entity is an intersection entity resolving the “many to many” relationship between employee and extra payment. We retain for it an attribute description containing the description of the occasion the extra payment/penalty was given. Its unique identifier comes from the combination of the employee id (cnp) and the extra payment id (date). • For the time off entity we retained: start date, an optional end date. Innovative Data Base Solutions :: Comat Auto Service ::

19. FIRST PARTIAL ERD – STRUCTURAL RULES • An employee could be either an engineer or mechanic or other (office staff or maintenance staff). • We don’t need an end date attribute in the hour price entity because there is no period of time when a price doesn’t exist. So the start date for the following price will represent the end date for the current price. • The employee’s salary can change over time. • For the engineer and other types of employees the salary (plus eventual extra payments/penalties) represents the whole payment but for the mechanic we must add the cost of the hours of labor he has (hours of labor times price per hour of labor at the time he worked them). Innovative Data Base Solutions :: Comat Auto Service ::

20. FIRST PARTIAL ERD – STRUCTURAL RULES • When an employee is fired or he quits, a new salary with the value zero will be created for him and the special notes will contain “fired” or “quit”. • An employee can be extra paid or penalized. • We can’t make more than one extra payment at the exact same time for the same employee. • We don’t need an end date attribute in the salary entity because there is no period of time when an employee doesn’t have a salary. The start date for the following salary value will represent the end date for the current salary value. • The date attribute everywhere in the project contains year, month, day, hour, minute and second. Innovative Data Base Solutions :: Comat Auto Service ::

21. FIRST PARTIAL ERD – STRUCTURAL RULES – RELATIONSHIPS • The center must practice one ore more hour prices. • Each hour price must be used by the center. • The center must pay one or more dues. • Each due must be paid by the center. • The center must be the employer of one or more employees. • Each employee must be employed by the center. • Each employee must be paid with one or more salaries (over time). Innovative Data Base Solutions :: Comat Auto Service ::

22. FIRST PARTIAL ERD – STRUCTURAL RULES – RELATIONSHIPS • Each salary must be for one and only one employee. • Each employee may have one or more occasions • Each occasion must be for one and only one employee. • Each occasion must be for one and only one extra payment. • Each extra payment must have one or more occasions. • Each employee may take one or more time offs. • Each time off must be for one and only one employee. Innovative Data Base Solutions :: Comat Auto Service ::

23. SECOND PARTIAL ERD – NEEDS The main new entities found and modeled in the Second Partial ERD are: client, appointment, account, car. In modeling this part of the business we needed to take into account questions like: • Can we know if a client has any appointments? • Can we find out how a bill was paid? Innovative Data Base Solutions :: Comat Auto Service ::

24. SECOND PARTIAL ERD Innovative Data Base Solutions :: Comat Auto Service ::

25. SECOND PARTIAL ERD – STRUCTURAL RULES • For the client entity we retained as mandatory attributes: id (represented by an unique code which is assigned for each client), first name, last name, phone number, address and as optional attributes: e-mail and fiscal code (fiscal code will be filled in only if the client is a firm). • For each appointment a client may make, we retain as mandatory attributes: appointment date (the date of the appointment) which together with the client’s id form the entity’s unique identifier, and as optional attribute: description (a possible description of the client’s problem). Innovative Data Base Solutions :: Comat Auto Service ::

26. SECOND PARTIAL ERD – STRUCTURAL RULES • For the account entity we retained as mandatory attributes: account number and bank (the bank which holds the account) which combined represent the entity’s unique identifier. • Like in the Initial ERD, for cars we retained: registration number, chassis number, model, color and as optional attribute special notes (short description). The registration number and the chassis number attributes of car entity, together with client’s id attribute form the car’s entity unique identifier. Innovative Data Base Solutions :: Comat Auto Service ::

27. SECOND PARTIAL ERD – STRUCTURAL RULES •A bill can be paid either with cash or using an account. If the bill is in relation with an account that bill was paid with that account. If a bill doesn’t have any relation with any account, the bill was paid with cash. • When a new client is created a code will be generated for his id attribute. • If a car changes its registration number or its owner it will be stored as another car in the database. • The mandatory attribute color of car’s entity represents a standard code that corresponds to the color of the car. Innovative Data Base Solutions :: Comat Auto Service ::

28. SECOND PARTIAL ERD – STRUCTURAL RULES – RELATIONSHIPS • Each client may have one or more appointments. • Each appointment must be for one and only one client. • Each client may be the owner of one or more accounts. • Each account must be owned by one and only one client. An account cannot be transferred to another client (the relationship is non-transferable). • Each client must be the owner of one or more cars. • Each car must be owned by one and only one client. • An account may pay one or more bills. • A bill may be paid with an account. Innovative Data Base Solutions :: Comat Auto Service ::

29. THIRD PARTIAL ERD – NEEDS The main new entities found and modeled in the Third Partial ERD are: bill, job, transport car,transport price. In modeling this part of thebusiness we needed to take into account questions like: • Can we find out the mechanicsthat were involved in a job? • Can we find out what jobs were done on a car? • Could we know how many hours a mechanic had worked in a month? •Can we know if a job is still in its warranty period? Innovative Data Base Solutions :: Comat Auto Service ::

30. THIRD PARTIAL ERD Innovative Data Base Solutions :: Comat Auto Service ::

31. THIRD PARTIAL ERD – STRUCTURAL RULES • For the bill entitywe retained as mandatory attributes: bill code (unique identifier), date and as optional attribute: description. • For the job entity (the total work of an employee for a car, at a certain visit to the service), we retained as mandatory attributes: number of hours (of labor) and warranty. This intersection entity’s unique identifier is composed of the mechanic’s id and the bill’s id (bill code). This entity resolves the problem with the “many to many” relationship between employee and bill. Innovative Data Base Solutions :: Comat Auto Service ::

32. THIRD PARTIAL ERD – STRUCTURAL RULES • For the transport task intersection entity (resolving the “many to many” relationship between bill and transport car) we retained as mandatory attributes: pick up hour (the hour the client was picked up). This attribute together with the bill code and the transport car id gives us the unique identifier for the transport task entity. We also retained as an optional attribute a description (short description of the route followed). • For the transport car entitywe retained mandatory attributes as: id (internal unique code), registration number, model and also an optional attribute description (e.g. condition, type of combustion). Innovative Data Base Solutions :: Comat Auto Service ::

33. THIRD PARTIAL ERD – STRUCTURAL RULES • For the transport price entity we retained mandatory attributes as: start date, which together with the transport car’s id forms the entity’s unique identifier, and also value. • The price may differ from a transport car to another. • The transport car’s id represents a unique internal code allocated by the service for a easier identification. Innovative Data Base Solutions :: Comat Auto Service ::

34. THIRD PARTIAL ERD – STRUCTURAL RULES – RELATIONSHIPS • Each car must have one or more bills. • Each bill must be for one and only one car. An issued bill cannot be transferred for another car (the relationship is non-transferable). • Each mechanic may do one or more jobs. • Each job must be done by one and only one mechanic. • Each job must be on one and only one bill. • Each bill may contain one or more jobs. Innovative Data Base Solutions :: Comat Auto Service ::

35. THIRD PARTIAL ERD – STRUCTURAL RULES – RELATIONSHIPS • Each bill may contain one or more transport tasks. • Each transport task must appear on one and only one bill. • A transport task must be done by one and only one transport car. • A transport car may do one or more transport tasks. • Each transport car must have one or more transport prices. • Each transport price must be for one and only one transport car. Innovative Data Base Solutions :: Comat Auto Service ::

36. FOURTH PARTIAL ERD – NEEDS The main new entities found and modeled in the Fourth Partial ERD are: part type, part group, acquisition, loss. In modeling this part of thebusiness we needed to take into account questions like: • Can we find out at a certain moment how many parts of the same type are in the warehouse? • Can we find out how many losses the service had in a month? • Can we find out what parts were used for a job? Innovative Data Base Solutions :: Comat Auto Service ::

37. FOURTH PARTIAL ERD Innovative Data Base Solutions :: Comat Auto Service ::

38. FOURTH PARTIAL ERD – STRUCTURAL RULES • For the part group entity we retained: provider’s price, which, together with the acquisition’s id (bill code) and part type’s id forms the entity’s unique identifier, we also retained addition (a percent of the provider’s price), initial number (initial number of parts in that group), and an optional attribute special notes (e.g. quality of the parts in that group). • For the acquisition entity we retained: bill code (the bill’s code with which the part group was bought, which is also the unique identifier of this entity) and date (the day the transaction was made). Innovative Data Base Solutions :: Comat Auto Service ::

39. FOURTH PARTIAL ERD – STRUCTURAL RULES • For the loss entity (an entity that tells us the losses, like broken or lost parts) we retained: date (of the loss, which, combined with the part group id gives us the unique identifier for this entity) and value (the number of parts lost or broken). • For the quantity entity (an intersection entity which, besides resolving a “many to many” relationship, tells us how many parts from one group were used for a job) we retained only value (the number of used parts); this entity’s unique identifier is formed of the bill’s id and the part group’s id. Innovative Data Base Solutions :: Comat Auto Service ::

40. FOURTH PARTIAL ERD – STRUCTURAL RULES • For the part type entitywe retained: id (its catalog number if the part is produced by Daewoo factories, or else, a uniquely generated code), name and, as an optional attribute, special notes (short description of the part type). • As in the initial ERD, for the center we retained: name (name followed by city), which is its unique identifier, phone number, address, support phone number, website. Innovative Data Base Solutions :: Comat Auto Service ::

41. FOURTH PARTIAL ERD – STRUCTURAL RULES – RELATIONSHIPS • The center must be the owner of one or more part types. • Each part type must be owned by the center. • Each part type must have one or more part groups. • Each part group must be contained in one and only one part type. • Each part group must be on one and only one acquisition. • Each acquisition must be for one or more part groups. Innovative Data Base Solutions :: Comat Auto Service ::

42. FOURTH PARTIAL ERD – STRUCTURAL RULES – RELATIONSHIPS • Each part group may have one or more losses. • Each loss must be for one and only one part group. • Each part group may have one or more quantities. • Each quantity must be for one and only one part group. • Each quantity must be for one and only one bill. • Each bill may contain one or more quantities. Innovative Data Base Solutions :: Comat Auto Service ::

43. FINAL ERD – STRUCTURAL RULES We modeled all the entities that the business needed in the Partial ERDs and then we had to bring them together just like in a puzzle (you can see the 4 regions of the ERD representing different modules – different colors). Most of the business rules were found during the modeling of the Partial ERDs. We have to mention a few procedural rules that we found, rules that couldn’t be modeled. A single relationship wasn’t described during the partial modeling: • The center may be the owner of one or more transport cars. • Each transport car must be owned by the center. Innovative Data Base Solutions :: Comat Auto Service ::

44. FINAL ERD – PROCEDURAL RULES • A client who has an appointment has priority in front of the other clients. • Only mechanics are authorized to make manual labors. • The final price of a part is the sum of provider’s price and addition percent. • The total cost of a bill is the sum of the final prices of all pieces used in that bill, the total number of labor hours times the cost per hour of labour and, if the car was transported to the service with a transport car, the number of km of transportation times the price per km (depending on the transport car). Innovative Data Base Solutions :: Comat Auto Service ::

45. FINAL ERD – PROCEDURAL RULES • The end date attribute of the time off entity will be completed when the employee returns from vacation and the payment for the vacation is calculated before the employee takes time off. Innovative Data Base Solutions :: Comat Auto Service ::

46. FINAL ERD – PROGRAMMATIC RULES • The attribute value, from quantity entity, must be bigger than zero. • The attribute addition of the part group entity will contain a positive number, representing a percentage of the provider price. • A part group will be empty when the sum of all the parts lost plus the sum of all the parts used from that group equals the initial number (the sum of all the parts lost plus the sum of all the parts used from that group can’t be bigger than the initial number). Innovative Data Base Solutions :: Comat Auto Service ::

47. FINAL ERD Innovative Data Base Solutions :: Comat Auto Service ::

48. ASSUMPTION – CONSTRAINT • Assumption: We assumed that this business will extend in time and will have more than one center. • Constraint: In the time off entity, the start date must always be an earlier date than the end date. Innovative Data Base Solutions :: Comat Auto Service ::

49. FINAL ERD – EXAMPLES Here are some examples of documents that can result from this model: Innovative Data Base Solutions :: Comat Auto Service ::

50. FINAL ERD – EXAMPLES Innovative Data Base Solutions :: Comat Auto Service ::