Business computing

1. Business computing Databases (3) 13 january 2004

2. Databases are containers for structured data • In business we mostly work with structured data • The data are organised into several tables • The tables are the containers of the data • The data are displayed in forms and reports • I.e. the functions « store the data » and « display the data » are separate • That is why in databases we are often wondering « where is the data ? », because the organisation is not intuitive

3. Structured information • A body of information is structured when it is made of very many items with the same structured information on each • For instance : an address book • For instance : 830 orders received and treated over the course of 3 years by a sales dept

4. Classroom examples • Last time we constructed a two table database containing the information of an address book • Today we shall study bdcomptoir.mdb : an example created and distributed by Microsoft to illustrate the possibilities of Access • It contains the information on 3 years of orders of a food wholesaler

5. bdcomptoir.mdb • It is a database (that is a container of data) from the sales dept of a wholesaler of food • It contains all the sales data over 3 years of sales, order by order (830 orders) • For each order, it contains • Building blocks : Sub-order per product • Quantities, client, supplier, sales clerk, date, etc…

6. Architecture of bdcomptoir.mdb • bdcomptoir.mdb is a classroom example of what Access can do, distributed by Microsoft • The designers of bdcomptoir.mdb chose to organise the data into eight tables : • A central table the records of which are elementary blocks of orders (2155 elementary blocks) • And 7 other tables : Orders, Clients, Products, Suppliers, Categories, Messengers, Sales clerks)

7. Bdcomptoir and Bd_ex2 • The logic of the distribution of information into different tables is the same for bdcomptoir and for bd_ex2 • Just (a bit) more complicated • If we understand well bd_ex2 it becomes easy to understand bdcomptoir

8. Learning databases • Just like we learn Accounting not to become accountants, but to be able to understand accounting reports • We learn databases not to become databases specialists, but to be able to manage databases specialists work, and understand what they can do

9. Multi-table databases • Whenever the same information appears again and again on many different records of one table it should be stored into another table, and related to the first table with a link • In our small « address book » database, persons is one table, cities is another one, and departments can be made into a third one

10. Purpose • Monitor the activity of the sales department : • Obtain summary information on clients • Same on products, • On suppliers • Make marketing plans • Manage our sales team • …be as efficient as possible (cf. Amazon) • And make as much profit as possible…

11. Amazon • When we ask information about one particular book on Amazon, Amazon tells us which other books clients, interested in this book, bought : efficient use of a sales information system

12. Chosen architecture for bdbomptoir.mdb • The architects of bdcomptoir chose to create a first table of elementary information, made of PARTS of each order • Each order contains several lines, one line per product • These lines will form the « basic table » with 2155 records • All the remaining information will be organised into « satellite tables » (7 of them)

13. Basic table of « building blocks » • In the address book database the « basic table » is « persons » (7 persons) • Cities (and departments) are listed in another table, linked to « persons » • In bdcomptoir the basic table is « détails commandes » (2155 items, building blocks of orders), it is linked to « products » and to « commandes », and these in turn are linked to other tables

14. Basic table of « building blocks » (2) • In the table « persons » we have cities mentioned, but no information about department. That information is in the table « cities » • In the table « detail commandes » we have only five fields (order number, product, price, quantity, rebate) • Everything else (on clients, suppliers, category, clerk, etc.) is in other tables

15. Basic table of « building blocks » (3) • These are considerations on how the data are stored • It says nothing of how the data are displayed : indeed we can display data the way we like • Do not confuse « storing » and « displaying »

16. Displays • The two main ways to display data from a database are • Forms (also used to enter data) • Reports, to create standard documents

17. Exports • Information displayed can be exported into other Office softwares • For example a report can be exported into a Word document

18. Imports • Well organized information into an Excel sheet can be imported into an Access database

19. Dynamic link Excel/PPT

20. Asking questions to a database • Two ways • The simple way : use a filter • The more elaborate (and more powerful) way : construct a query

21. Use of bdcomptoir.mdb • Filter, queries, and reports • We can « ask questions » to the database • And we can also produce « reports » • For example : • How many times the product « chang » was ordered ? • What total quantity of « chang » product did we sell ?