Liceul de Informatica Grigore C Moisil Iasi and STRABAG collaborate for dream database project

1. Vlad Manea Drago-Mihail Vasiliu Ioana-Loredana Marin Andreea-Corina Dumea Romania . LICEUL DE INFORMATICA GRIGORE C. MOISIL IASI we make your dream database come true project manager : prof. Emanuela Cerchez e-mail : emanuela.cerchez@gmail.com worl db ase

2. Company: Profile worl db ase STRABAG STRABAG is one of Europes leading construction groups. With nearly 60,000 employees, STRABAG expects to post revenue of about 11 billion Euros this financial year. From its core markets of Austria and Germany, STRABAG, via its numerous subsidiaries, is present in all countries of Eastern and South-East Europe, in selected markets in Western Europe, on the Arabian Peninsula, as well as in Canada, Chile, China , ( and) India. STRABAG generates more than 80% of its construction output in markets in which it holds one of the top three positions. These include the Czech Republic, Hungary, Slovakia and Poland. 2 / 32

3. We were determined to search for a capable person that could have provided the necessary information. We talked to our colleague, Dipl. Eng. Sarghe MARIUS. At the first interview, he exposed the current STRABAG problems and shown us some contract samples to explain how the business works. STRABAG Our team received from our supervisor the task of organizing our companys business. The STRABAG website www.strabag.com helped us know STRABAGs main activities and values. We also looked at similar businesses websites, to f ind out about construction companies, in general: Synergy , Summa SA , Rotary Constructii , Transilvania Constructii . worl db ase Research: References 3 / 32 STRABAG S.R.L. DIRECTION I.F. ENVIRONMENTAL TECHNOLOGY INTERNATIONAL _____________________________________________________________________________________________ Dipl. Eng. Sarghe MARIUS Romania Business Development Deputy Project Manager IASI ROMANIA, Str. Sos. Iasi-Holboca; Statia de Epurare Phone: +40(0)332/108 858 Fax: +40(0)332/108 859 Mobile: +40(0)728/859 106 E-mail: marius.sarghe@strabag.com www.strabag.com

4. STRABAG worl db ase Research: References STRABAGs magazine , inform , was a helpful source of information for successful projects and cover stories. The magazine also show s how our company involves in social and cultural activities for its employees (art festivals, sports competitions, photo contests etc.): - inform December 2006 - inform May 2007 - inform September 2007 Annual reports show our the growth of our company in time and gave us an overview of the importance of some key elements for the global STRABAG business: - 2004 report - 2005 report - 2006 report We also consulted an Investor Presentation where we found financial information about our company and particularly about its profitable growth. 4 / 32 In order to complete the information provided by our interviewee, we searched for useful resources on the STRABAG website.

5. STRABAG Research: Problem ? It has become clear that conventional informational systems for organizing the business requirements could not keep up with the ever expanding business. worl db ase Roads Bridges Tunnels Services Civil Industrial We came up with a solution that co nsisted in a database that would organize the business in a handy manner. 5 / 32

6. STRABAG The key element for STRABAGs business is the project. For each project, a contract is signed between our company (through a STRABAG employee assigned as project manager) and the company that orders the construction (client). worl db ase Each contract contains a special mention that allows possible delays due to meteorological factors beyond control. An estimated value of the project is specified. STRABAG divides each project into project stages, depending on its size and complexity. Our company establishes the start date of the construction and estimates a deadline for the whole project and also for each project stage. Research: Business Rules 6 / 32 project project stages contract

7. STRABAG How does someone get hired at STRABAG? How does STRABAG cooperate with other companies for specific project stages? How does STRABAG use equipment on project stages? worl db ase When a position at STRABAG is vacant, candidates are selected according to their CVs and recommendation letters. Afterwards, the selected candidates are invited to an interview. If a candidate is hired, a medical record of all his major diseases and medical indication is made. Each employee has a job history. When a hired person is promoted (demoted), the function and salary are changed. For some project s tages, STRABAG may subcontract specific task to specialized . In order to choose the best companies, an auction takes place. STRABAG evaluates the candidates by criteria such as offers, financial status and experience. The selected companies sign a subcontract with STRABAG. STRABAG provides its own equipment to project stages for certain periods of time. In order to use equipments that are not in its possession, our company signs subcontracts. Research: Business Rules 7 / 32

8. STRABAG worl db ase Research: Business Rules 8 / 32 Certain amounts of m aterials are bought from other companies at specific prices. Well determined amounts of materials are taken from stockpiles for different project stages. How does STRABAG use materials for a specific project stage? companies materials project stages quantity price X quantity

9. STRABAG Weather is the most important factor that may affect the business. The meteorological conditions influence the building processes in different ways. Our database must retain and allow retrieval of all the required weather measurements. workers avoid working on the electric systems of skyskrapers. concrete receive s special treatments to harden faster. deadlines must be changed if some activities are delayed. Samples of STRABAG safety measures in a rainy day: worl db ase Research: Weather Influences 9 / 32

10. STRABAG STRABAG uses weather information in order to have equipments and workers ready to meet the safety standards. Weather conditions determine the choice of adequate materials for a specific task (e.g. concrete type or additive selection is influenced by temperature and precipitations). worl db ase STRABAG measures three times a day factors such as temperature, humidity, wind speed, precipitations at the location of every project stage. STRABAG has agreements with local meteorological institutes for weather forecasts. Weather forecast is sent by e-mail, once for a specific day, registration time being considered 00:00AM (when STRABAG does not execute weather measurements). Research: Weather Influences 10 / 32 Meteorological Institute e-mail forecast temperature humidity wind speed precipitations measurement

11. STRABAG The business is based on the synergy between the three main segments: - Building construction & Construction engineering - Road construction - Tunneling & Services Our database must be a reliable platform for any of these activities. worl db ase Our database model provides quick answers to a wide set of questions such as: - Which materials, equipment, subcontractors were put together to bring a specific project to life? - Based on STRABAG experts previous works, which of them should be assigned to design a tunnel in the Alps? - Which materials were used to build the last giant power plant in the tundra? - Which workers are able to perform a specific task and are not under any medical indication that forbids the task? - What are the free market countries with the highest rate of finalized projects in the last year? - What equipment should be sent to do a task for a project stage if the weather forecast predicts a torrential rain? Research: Business Needs 11 / 32

12. STRABAG In order to fulfill the business needs, our database must store data about the following elements: - contracts - projects - project stages - employees - subcontracts - clients - materials - equipment - locations - weather worl db ase STRABAG retains each past or current project, its afferent contract, client, and specific project stages. For each project stage, all subcontractors and the afferent contracts must be known. Materials, workers, equipments, locations and weather conditions for each day of a project stage are also required. It is required that materials have known suppliers and prices. Materials are used for specific project stages in certain quantities. It is indicated that STRABAG knows other companies background, expertise, balance sheet and turnover. The business requires personal data, contact information, job history and medical record for each employee . His activities in the company must be tracked as well. Research: Required Information 12 / 32

14. STRABAG worl db ase Structural rules STRABAG needs to keep track of all companies they had worked in the past or are workin g with. The name and profile of every such company is retained. The profile and the date founded attributes help to select only experienced companies in a specific field. The balance sheet describes the income of a company in the last year while the turnover refers to the amount of money it circulates. Each company is identified by its unique registration code ( cui ). It is important to know that cui contains the initials of the companys country. Modeling: Companies COMPANY # cui * name * profile * address * date founded * balance sheet * turnover 14 / 32

15. STRABAG worl db ase Structural rules In order to know the work quality of another company, STRABAG wants to study its PORTFOLIO PROJECTS . They are also interested in the period of time when the portfolio project took place. A PORTFOLIO PROJECT is identified by its name and its LOCATION . Each PORTFOLIO PROJECT must be placed at one and only one LOCATION and each LOCATION may be the place of one or more PORTFOLIO PROJECTS . However, some companies could still have no expertise. Sometimes, two or more companies work for the same PORTFOLIO PROJECT . Thus, we created the EXPERTISE entity (a list of companies involvement into projects) to solve the M:M relationship. For each company, we store the tasks achieved in each PORTFOLIO PROJECT and the achievement period. The attribute end date , in both PORTFOLIO PROJECT and EXPERTISE entities, is optional as it might be unknown for current projects. have belong to refer to appear on placed at the place of Modeling: Companies 15 / 32 PORTFOLIO PROJECT # name * start date o end date o quality EXPERTISE # start date o end date * task LOCATION # id * country o address o longitude o latitude COMPANY # cui * name * profile * address * date founded * balance sheet * turnover

16. STRABAG worl db ase Structural rules A company may be: - the signer (client) of one or more CONTRACTS , - the signer (subcontractor) of one or more SUBCONTRACTS , - the supplier (seller) of one or more material ACQUISITIONS . The CONTRACT must be signed by one and only one company. The client cannot be changed once the CONTRACT is signed. The company that signs a SUBCONTRACT cannot be changed either. Thus, we made these two relationships nontransferable. Modeling: Companies the supplier of signed by the signer of signed by the signer of made from 16 / 32 COMPANY # cui * name * profile * address * date founded * balance sheet * turnover CONTRACT # id * category * purpose * estimated value * start date * proposed end date * description SUB CONTRACT # id * task * value * description ACQUISITION # date * quantity * price

17. STRABAG worl db ase Structural rules When STRABAG has a new client, our company creates a contract in which the purpose and description of the work, the period of time between the moment the contract is signed and the one the contract is finished, the estimated value of the entire project are retained. A contract is identified by its id . The category attribute indicates one of the three activity sectors of STRABAG (Building construction & Construction engineering, Road construction, Tunneling & Services) or O ther, when the project belongs to none of the sectors. The construction projects are divided into PROJECT STAGES which should be accomplished in a predefined period of time. PROJECT STAGES are part of a contract and each one has its own id . The proposed end date for a CONTRACT / PROJECT STAGE is the date when the entire project/ a specific project stage should be finished. include part of Modeling: Contracts 17 / 32 CONTRACT # id * category * purpose * estimated value * start date * proposed end date * description PROJECT STAGE # id * start date * proposed end date o bill

18. STRABAG worl db ase Structural rules A subcontract is realized between STRABAG and another COMPANY for a PROJECT STAGE . When a subcontract is signed, the following information is stored: its task , value and description . The value attribute refers to the estimated cost of the subcontract. We identify the subcontract by the COMPANY that signed it, the PROJECT STAGE it was closed for, and also by an artificial unique identifier, id , which permits more subcontracts between STRABAG and a COMPANY for the same PROJECT STAGE . the subject of signer of signed by closed for Modeling: Subcontracts 18 / 32 COMPANY # cui * name * profile * address * date founded * balance sheet * turnover PROJECT STAGE # id * start date * proposed end date o bill SUB CONTRACT # id * task * value * description

19. STRABAG Structural rules If there are STRABAG workers assigned to do specific tasks for a project stage, then a WORK SCHEDULE is related. An EQUIPMENT SCHEDULE is retained in the case of STRABAG EQUIPMENT involvement. For specific project stages, COMPANIES may be subcontracted. Therefore, a project stage may be the subject of one or more SUBCONTRACTS . A USAGE LIST is also related if the project stage requires materials that are in our companys possession. Each project stage is identified by the id of the CONTRACT it is part of, and its own id . The proposed end date is the date when the project stage should be finished, according to the CONTRACT . The bill represents the amount of money STRABAG receives when a project stage is finished. Modeling: Project Stages closed for the subject of for for the subject of the subject of for the subject of for the subject of worl db ase 19 / 32 EQUIPMENT SCHEDULE # start date # start time o end date o end time PROJECT STAGE # id * start date * proposed end date o bill SUB CONTRACT # id * task * value * description INFLUENCE * description USAGE LIST # time * quantity WORK SCHEDULE # start date # start time o end date o end time * task

20. STRABAG worl db ase Structural rules STRABAG obtains the necessary materials for a project by buying them. That is why it needs to have a record of the acquired materials and the used ones. The evidence of materials is done depending on the date of ACQUISITION . In addition, our company must know the quantity and price of acquired material. The quantity of used materials at a certain moment is also important and it is stored in a USAGE LIST . Materials are identified by an artificial unique identifier, id . Each material is chosen for specific weather conditions. It is necessary to retain a description of the material because while a project is undergoing, the weather may change and our company would need to change those with more adequate ones. Assumptions The materials are used in the order they are acquired. STRABAG does not have any material loss. Modeling: Materials refer to the subject of the subject of refer to 20 / 32 MATERIAL # id * description USAGE LIST # date * quantity ACQUISITION # date * quantity * price

21. STRABAG worl db ase Structural rules STRABAG uses much equipment in many PROJECT STAGES and it is interested in equipment repartition in time. The description is also important for choosing the best equipment for a specific PROJECT STAGE . The equipment has assigned an artificial identifier, id . We had to solve the M:M relationship by creating the EQUIPMENT SCHEDULE entity, in which we included the time interval ( start date , start time , end date , end time ). STRABAG is interested in retaining an EQUIPMENT SCHEDULE . A schedule is identified by the equipment it refers to and by the PROJECT STAGE for which that equipment is used. Modeling: Equipment the subject of for refer to used by 21 / 32 EQUIPMENT # id * description PROJECT STAGE # id * start date * proposed end date o bill EQUIPMENT SCHEDULE # start date # start time o end date o end time

22. STRABAG worl db ase Structural rules All companies keep track of their employees. STRABAG makes no exception. An Employees file contains personal data such as: first name , last name , date of birth , residence state , residence address , phone number , e-mail . Employees are assigned an unique identifier, id . An employee can also be identified by his social number and residence state . There may be certain situations in which an employee is not allowed to do a specific task, because of a disease that has a medical indication for a certain period of time, defined by a start date and an end date . This data is stored in the MEDICAL RECORD . For retaining the personal evolution of any person in the company, we created the JOB HISTORY entity. In this, a start date , end date , function , salary and evaluation are stored. have have belong to belong to Modeling: Employees 22 / 32 EMPLOYEE # id # 1 social number # 1 residence state * first name * last name * date of birth * residence address o phone number o e-mail MEDICAL RECORD # number * disease * start date o end date o indication JOB HISTORY # start date o end date * function * salary o evaluation

23. STRABAG worl db ase Structural rules An employee may have many functions at the same time: - be in charge of a WORK SCHEDULE with specific tasks , - appear with a task in the DESIGN TEAM for a project, - appear as project manager (pm) on CONTRACTS , - supervise one or more employees. Assumption STRABAG retains a WORK SCHEDULE for tasks related to PROJECT STAGES only. Hired persons who have none of these functions have tasks that depend on their current job. Modeling: Employees appear as pm on in charge of appear in refer to refer to have as pm supervised by supervise 23 / 32 DESIGN TEAM o task CONTRACT # id * category * purpose * estimated value * start date * proposed end date * description EMPLOYEE # id # 1 social number # 1 residence state * first name * last name * date of birth * residence address o phone number o e-mail WORK SCHEDULE # start date # start time o end date o end time * task

24. STRABAG worl db ase Structural rules The weather is measured on specific LOCATIONS in various moments of TIME . We solved the M:M relation by creating the WEATHER MEASUREMENT entity. The measurement consists of: temperature , humidity , wind speed and precipitations . Meteorological data always influences the activities of our company. If the relationship between the WEATHER MEASUREMENT and METEOROLOGICAL INSTITUTE entities does not exist, this is a measurement made by STRABAG. Else, it is a forecast provided by that particular institute. Each TIME must have a forecast, a measurement made by STRABAG, or both. Assumption We only retain TIMES when STRABAG works at PROJECT STAGES . In those TIMES , weather is measured. registered at registered on registration place of registration moment of generate generated by Modeling: Weather forecast forecasted by 24 / 32 TIME # date # time WEATHER MEASUREMENT * temperature * humidity * wind speed * precipitations INFLUENCE * description METEOROLOGICAL INSTITUTE # id * name * country LOCATION # id * country o address o longitude o latitude

25. STRABAG worl db ase General Explanations, Constraints Constraints For a LOCATION , the address is retained when the longitude and latitude are not. A stored COMPANY has at least one of the relations with CONTRACT , SUBCONTRACT or ACQUISITION . Each LOCATION has at least one of the relations with PORTFOLIO PROJECT or WEATHER MEASUREMENT . Each end date / time must be greater than its corresponding start date / time . The same EQUIPMENT cannot be used for more PROJECT STAGES at the same time. An EMPLOYEE cannot be assigned or work for more than one PROJECT STAGE task at a certain moment. A JOB HISTORY must also contain intervals of time that do not overlap. The geographical coordinates of a LOCATION , if they are retained, represent the center of the area. We can identify a LOCATION from a large surface (region) to a specific point, defined by its address or longitude and latitude . Explanations Each end date / time that appears as an optional attribute represents the real, not estimated, end date / time and it is filled when the particular activity is finished. STRABAG experts can evaluate PORTFOLIO PROJECTS / JOB HISTORY through a scale of 1 to 10 and update the result in the quality / evaluation fields. 25 / 32

27. STRABAG worl db ase Sample Mapping: Weather registered at registered on registration place of registration moment of forecast forecasted by 27 / 32 WEATHER MEASUREMENT * temperature * humidity * wind speed * precipitations LOCATION # id * country o address o longitude o latitude TIME # date # time METEOROLOGICAL INSTITUTE # id * name * country

28. STRABAG worl db ase Sample Mapping: Employees have belong have belong supervised by supervise EMPLOYEE # id # 1 social number # 1 residence state * first name * last name * date of birth * residence address o phone number o e-mail JOB HISTORY # start date o end date * function * salary o evaluation MEDICAL RECORD # number * disease * start date o end date o indication 28 / 32

29. STRABAG worl db ase Sample Report: Output STRABAG would like to know the construction output volume (in percents) for 2006. STRABAG wants to find out the output by segment. It is also interested in the output by region, where regions must be divided in: Germany, Austria, CEE and the rest of the world . 29 / 32

30. STRABAG worl db ase Sample Report: Growth STRABAG wants to know the construction growth (in percents) by country CAGR 06-09E*. *All European Euroconstruct countries (EC-19). Source: 63rd Euroconstruct Conference Summary Report. 30 / 32

31. STRABAG worl db ase Sample Report: Weather STRABAG would like to know the locations where projects were finalized later than planned , due to weather conditions. Projects delayed between six and twelve days. Projects delayed more than twelve days. 31 / 32

32. STRABAG worl db ase Conclusions The research for the project helped us learn how to find data about STRABAG activities and how to select only relevant information from it. By studying STRABAG, we learnt the mechanisms of a real world construction company: the making of a contract, the subcontracting procedures, activities of employees and the transition stages from project to structure. We also improved our communication skills, by learning how to interview our reference, how to ask clear questions in order to get complete answers, and how to make our project presentation in front of our classmates: We imagined that we were part of STRABAG INFO SYS division and we were assigned to come up with an informational system to organize its global business. We wanted our database to keep up with increasing business demands, so flexibility and efficiency were the key elements when we imagined it. Our database is a reliable solution that satisfies the actual business needs. 32 / 32