home page Spanish course curso de inglés accounting course video clips mathématiques cours d'anglais cours d'espagnol cours d'allemand games

 

 

New Technologies in Information and Communication

Lesson 5

 

Information and databases (cont'd)

 

Introduction :

With this lesson we reach the heart of this course : 

information : how to gather it, store it, organise it, distribute it and use it.

Indeed this course, as the title says, is about Information, Communication and Technologies. It is traditional to name it NTIC, but as the years pass it is less and less New.

The automated processing of information began in the thirties in the US with machines designed to perform various treatments on census data. 

This was one of the impetus to invent computers. Others were the need to make large calculations in physics, mathematics, as well as in military applications.

As we saw ealier in this course, when it was realised, in the fifties and sixties, that computers were not only powerful calculators but also quite useful "repositories" of information this naturally lead to the attempt to link them together to share the information they each contained.

So in the mid-sixties the first long distance links, via telephone lines, between two computers were established. 

And from then on the development of Internet was inescapable. But of course, even though today it is rather easy to present it as a very logical development of computers, in those times, in the sixties, only a few visionary people were able to foresee some sort of a worldwide network that would make all sorts of information available to "everyone". For one thing Internet owes a lot to micro-computers, and yet, in the sixties, these did not exist, and people who tried to construct them were thought of as odd characters with little future.

The TECHNOLOGY part of this course is the part that dealt with :

  • Infrastructures of networks (see, for instance, Paul Baran for an understanding of the concerns that lead to the present day structures)

  • Packet switching

  • TCP/IP protocol

  • HTML

  • HTTP protocol

  • Operating softwares : Unix, Dos, Windows, MacOS, Linux (the likely future system, because it is Open Source)

  • Languages : Javascript, C, C++, Java, Perl, CGI, Php, XML, etc.

  • Applications softwares : Basic, Word, Access, Oracle, MySql

  • Other tools : E-mail, FTP, NewsGroups, Google, etc.

 

The COMMUNICATION part of this course is the part that deals more specifically with the transmission of information, just like a newspaper, a newsletter, or the "snail" mail do.

We shall have more to say about communication later, but before that we must stop and study in greater depth the INFORMATION part of the topics covered by this course.

INFORMATION, as said above, is at the heart of this course on NTIC. 

Modern times have revolutionized the role of information in contemporary societies. The major role of information in the functioning of societies has come forth slowly over the past four or five centuries. As for many other concepts that changed life, the "strategic" role of information was only known of rulers and of military people in historical times. The Renaissance in the West had to do with the renewal and dissemination of information, but it was limited to a small fringe of society. (By the way, it is the somewhat ill-explained thirst of the Western world as opposed to other societies for information that in part explains its prominent position nowadays.)

The perception of the value of information and the need for its efficient treatment grew with the Industrial Revolution and its offshoot : the capitalistic organisation of western societies. As usual with profound novelties, the needs and the means about information grew together, without which one lead the other always being clear.

This leads us to the times around WWII which saw the onset of the computer industry.

Parallel to the development of Internet, in the sixties and seventies, the automated treatment of information pervaded various fields of economic activities, one after the other.

The first economic activity to be profoundly modified by automated treatment of information was accounting in the 60's.

Then came manufacturing in the 70's, with the automated control of machinery and later on of whole factories, and for instance with the JIT (Just In Time) production organisation. This lead to today's SCM : Supply Chain Management.

The sales departments were naturally another target of computers after accounting, since they are close to the accounting services.

Later on, in the 90's, with more powerful computers and techniques, this lead to CRM : Customer Relationship Management.

At the heart of all these profound changes in the way to conduct business one always finds INFORMATION, more precisely "organized information".

ORGANIZED INFORMATION is information that has a repetitive character, a repetitive pattern (and to use an image that you can store on cards in a shoe box).

This type of information (as opposed say to the Iliad, or the Gilgamesh epic) is most usually stored in DATABASES.

 

DATABASES are the softwares (as well as the resulting files) 

to gather, store, treat and use organized information.

 

Large databases are used in almost every activities of large modern firms, and also now in most smaller firms : large databases or sets of connected databases, used in firm operations, are called Information Systems.

They are even used in top executive functions, where they are called, in french, Systèmes d'Information d'Aide à la Décision (SIAD).

As you know, managing a firm involves constant decision making.

Decisions are taken with some objective in mind, some optimization pursuit : this always boils down to the health and wealth of the firm, and, to summarize, to profit.

In order to take good decisions, managers use information : external information and internal information. A good deal of it is of an organized nature, therefore it is stored in databases ; and databases are used to answers various questions.

The logic behind information contained in a database is the same be the database a very small one (a football players cards database), and average one (we shall study the bdcomptoir.mdb, a database storing order data of a firm, downloadable at https://lapasserelle.com/isc/bdcomptoir.mdb ), or a big one : an information system :

  • gather information

  • store information in an organized manner

  • display or publish information

  • answer questions (called queries in the database lingo)

 

Databases (revisited) :

  • Review of the first principles :
    • To store structured information
    • Tables = "cardboard boxes"
    • Records = "cards"
    • Fields = "items on a card"
  • Simple databases = one-table databases = two-dimensional databases
  • More elaborate databases : Relational databases = multi-table databases
  • There are other types of databases : Object oriented databases

 

The database bdcomptoir.mdb

 

The relations among the 8 tables, into which the data are organized in bdcomptoir.mdb

 

Relational databases 1 : the basic principles

  • The advantages of relational databases over simple databases
    • Clearer organization
    • Less storage space used
    • Less risk of wrong data entering
    • Easier to work with, to make evolve, to enrich
  • Let's import three tables from Excel
  • Let's create relations between them
  • The concept of primary key
  • Indexing : to speed up queries
  • The objectives of a database :
    • To store structured information
    • To answer queries, i.e. to provide analyses
  • Various types of relations : one-to-one, one-to-many, many-to-one (and even many-to-many)
  • The architecture of a relational database : building a database is an art not a science, we use UML (Unified Modelling Language)

 

Relational databases 2 : editing and presenting

  • Basic editing principles, and tools
  • Editing the data : the data entry mode
  • Table display versus form display

An example of form used in bdcomptoir.mdb to enter and to display client information :

  • Input mask
  • Validation. Example : numerical field >=0 And <100
  • Error messages
  • Navigating within the table
  • Freeze some columns
  • Move columns
  • Hide columns
  • The contextual menu (right button of the mouse)

 

Relational databases 3 : modify the structure

  • Modify the structure of a database
  • Editing the structure : the design mode

An example of the design mode for one table :

  • Columns and fields : same things
  • Careful : with Access, some suppress actions are not dangerous, but others are irreversible and can create great damage

 

Relational databases 4 : simple searches

  • Simple searches : search, sort, filter
  • Search : Ctrl + F
  • Parameterize the search
  • Sort : ascending sort, descending sort
  • Filters :
    • Filter via a selection
    • Filter via a form
  • Form filter via the form display or via the table display
  • The function OR
  • Advanced filter

 

Queries : a database is useful only if it is properly designed for queries

  • What is a query ?
  • How do we construct a query ?
  • The field/line "Operation"
  • The item/choice "Group"
  • The item/choice "Count" : an example by country

 

Examples of queries :

  • Sums

 

 

Some calculations :

  • Some fields can be the result of calculations
  • Example : price with a discount

  • Using bdcomptoir.mdb : how many orders (table "commandes") were treated by Anne Dodsworth ?
  • Use the tool : filter by selection. Answer : 43
  • What quantity of product "Guaraná Fantástica" was altogether ordered ?
  • Construct a query using two tables : "produits" and "details commandes"

  • Answer : 1125
  • In how many orders ? Ungroup your answer by adding a field in your query.

  • Answer : 51
  • What was the biggest order ? Answer : 110.
  • Show it as a Form

  • Print the invoice (imprimer facture)
  • What were the total sales of a total category ? Or, say, of the product "Côte de Blaye" ?
  • Construct a query using the tables : "catégories", "produits", "détails commandes"

  • Answer : 706 983,67F
  • Split this figure by year. Add a table in your query and include "date of order" in your query...
  • Export the result to Excel...
  • The sales for 1993 were 124 372,00
  • The sales for 1994 were 245 990,42
  • The sales for 1995 were 336 621,25

 

Reports : examples

 

To summarize :

We see that bdcomptoir.mdb is the beginning of an information system for the sales department of a wholesaler in the Food industry.