Databases Design: step 1

Analyze of documents

The first step, in the construction of a database, is to gather all the documents representative of the data we want to model. The term "documents" has a broad meaning. They can be paper documents, magnetic documents (voice recordings, disks or computer disks) or any other medium that can be used to store information.

Working documents

To illustrate the different stages of database design, we will use as a typical example, the management of students who follow the different training of a diploma.

The working documents used are the following:

List of students
N ° of student Name of the student Date of birth Sex
1 Dupont, Charles 18-03-1981 H (Homme)
2 Dubois, Jules 02-11-1982 H (Homme)
3 Favier, Isabelle 02-02-1979 F (Femme)
... ... ... ...
Marks sheet
N ° student : 1     Dupont, Charles
N ° of subject Name Coefficient Mark / 20
1 Mathematiques 3 10
2 Informatique 2 9
3 Sociologie 2 12,5
4 Histoire 1 13
5 Géographie 1 7
Average mark to diploma 10,3    
List of teachers
N ° of teacher Name Rank Seniority Subject taught
10 Bertrand, Pierre ASS 2 Sociologie
11 Dupont, Auguste MCF 3 Mathematiques
... ... ... ... ...
15 Simon, Etienne ASS 5 Histoire
Géographie

Rules :

-Only one teacher teaches a given subject.
-A student has only one mark per subject.

Data Dictionary

From the documents, the analysis phase consists of extracting all the elementary (non-decomposable) information that will constitute the database.

The set of all elementary data, known as attributes or fields, is the Data Dictionary. Each attribute (field) in the data dictionary can have the following characteristics:

Property Meaning  
Mnemonic Mnemonic of the attribute.  
Wording Attribute wording.  
Data type Attribute type: integer, real, string, date...  
Integrity constraint Possible values of the attribute.  
Calculation rule Calculation rule of the attribute.  

With regard to the preceding documents, the data dictionary is as follows:

Mnemonic Wording Type Constraints Calculation rule
Ancient Seniority in rank Integer > =0  
Cdsexe Gender code String (1) H or F  
Coeff Coefficient of subject Integer > 0 and < 6  
Dtnaiss Date of birth Date    
Grade Rank of teacher String (3) ASS or MCF or PR  
Lbsexe Wording of the sex String (7) Homme or Femme  
Moyenne Average mark to the diploma Real > =0 and < =20 SUM( Note*Coeff)/SUM(Coeff)
Nomat Subject name String (15)    
Nomens Name of the teacher String ( 20 )    
Nometu Name of the student String ( 20 )    
Note Mark obtained in the subject Real > =0 and < =20  
Numat Subject number Integer > 0  
Numens N ° of the teacher Integer > 0  
Numetu N ° of the student Integer > 0  

Next stage | DB Design