1. Business computing Databases 14 December 2004

2. The course is about information treatment • The objective of the course Business computing is to gain some basic proficiency with the main information treatment software of MS Office • To gain proficiency with • Word • Excel • PowerPoint • Access • (to a smaller extent) MS Explorer

3. Information treatment • Word : a software to prepare letters and reports, to transmit information • Excel : a software to make simple calculations, and display them in a useful manner • PowerPoint : simple slides to present a story line • Access : (a software to create databases) to store, retrieve and display strongly STRUCTURED informatio • Access is the core of the course Business computing

4. The work of managers • Firm’s managers deal with information and communication • That’s why they must be familiar with NTIC (New Technologies in Information and Communication) • Managers do not operate machines • (Even on the shopfloor in modern plants most of the work has to do with information)

5. The work of middle-managers • Middle managers receive information, treat it, and present it to the decision makers (the so-called « top executives ») • And they implement plans, that is use communication to make other people do things according to plans : either suppliers and clients, or workers in the firm.

6. Type of information we deal with • Unstructured information : • (typically) the selling speech of salespeople • The speech to motivate a team • Information about competitors • Structured information : • Accounting information • Market research • Production plans • CRM, and SCM… • Information systems (sets of large databases) • Escem’s information system is named « Alienor »

7. General tendancy toward structuring information • A sale team is more efficient if it has PROCEDURES as to how to approach prospects, discuss with them, etc. • Human Resources also tries to implement procedures in the way to deal with the employees • Even R&D dept try to apply procedures on testing new products and technologies

8. Balance between procedures and creativity • Modern large firms constantly try to maintain a balance between specifying precisely what procedures the middle-managers should follow, and leaving them a margin of creativity • The same is true in the Army • In fact there are many similarities between a firm and a military organisation

9. Databases and structured information • Firms deal a lot with structured information • Accounting information is highly structured : accounts, values credited, values debited, etc. All this is kept in an accounting system that is very much like a database • Simple accounting softwares are built with Excel in the background, and more elaborate ones with a big database in the back • Production plans, and reports, and more generally speaking production softwares deal with highly structured information (to pilot assembly plants) • Banks deal with structured information • Sales dept too

10. What is structured information ? • It is a mass of data that can be viewed as elements with the same pattern • It is the extension of a shoebox with cards in it, and on each card some information (with the same structure) about the element covered by that card • Examples : • Personal contacts • Cooking recipes • A card collection on football players • Prospects • Employees • Etc.

11. A very basic database • A very basic database is equivalent to a shoebox, with cards in it, and on each card the same structure of information for the element corresponding to that card • Example : football player collection • Lastname • Firstname • Date of birth • Club • Club coach • Club address • Picture • Position in the field • Price at last transfer • Past history of clubs

12. Vocabulary • A simple database is equivalent to a Table (in Excel for instance) • One card is called a Record (and in Excel, usually a Row) • The container of one item of information on one card is called a Field • Indeed a simple database can be organised into Excel (a row is a record, and a column corresponds to a field)

13. Let’s create a very simple database with Excel • A row is a record about one player • A column holds the information on one field • The whole table is a simple « one-table » database • Excel has some simple database functionalities

14. The basic concepts in databases • The simple Excel one-table database on soccer players allows us to illustrate the basic concepts in databases • Distinguish the DATA and their DISPLAY • All the data are always in the background (in tables), and we may want to display only part of them • We can make REQUESTS on the data (simple requests, and elaborate requests) • All these will be achievable in much more elaborate ways within Access

15. When do we want several tables ? • If on my records for each player I want to record several fields of information on the Club of the player, it is a waste of time and a risk of error to repeat on each card all this information • It is better to prepare another table concerned with Clubs, and establish some « link » between the player-table, and the club-table • Excel cannot do that, we need a fullfledged database software (Access, 4D, dbIII, oracle, etc.)

16. Relational databases • A database that implements this idea of not reproducing the same information on several cards, but of creating a separate table for this repetitive info is called « a relational database » (aka a multi-table database) • When we have structured information to store into a multi-table database, the choice of structure is an art (there is no unique structure to store information into a relational database). A language (called UML) is sometimes used to create the architecture of the complex database.

17. A sales dept database • The sales manager of a firm wants to keep track of many pieces of information concerning each selling act. • It needs more information than what is recorded in Accounting : • In accounting we record the date, the selling price ; if the sale was on credit we record the name of the debtor, and that’s it • The sales manager needs more : name of client, region, name of salesperson, some information about the client, past history, past visits, etc. • All this lead to CRM (Customer Relationship Management)

18. Accounting is only one of the information systems of the firm • Running a firm requires to have a performant accounting system (including cost accounting), but it is not enough • We need information about the production process, which is not of a monetary nature • We need information about our clients, suppliers, and competitors which is not kept within the accounting system • In fact the job of an exective is to read many reports every day, and of course take decisions.

19. The example of bdcomptoir.mdb • bdcomptoir.mdb is a school example built by Microsoft to illustrate the construction of an information system for the sales department of a food wholesaler. • It records all sales activity over the past 3 years. • What is the basic element in this database ? • It is a line concerning one product in an order by a client. • There are many side tables around it.

20. Let’s create a simple database with Access • It will be a database of personal contacts • Another example is a database of job search : for each search : the firm, the contact, the date, the discussion, etc. • It’s a bit like a sales dept database

21. Structure • Before entering data we need to build the structure of our database. Here it is going to be a simple table with seven fields