Databases Design: step 1

Analyze of documents

The first step in creating a database is to collect all the documentation that represents the data we want to model. The term "documents" has a broad meaning. These can be paper documents, magnetic documents (voice recordings, disks or computer disks) or any other media 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

Rules :

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

Data Dictionary

From the documents, the analysis stage consists of extracting all the basic information that will constitute the database.

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

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.  

In relation to the above 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