|
Databases design : stage 1
Analyze of documents
The first stage, in the construction of a database,
consists in gathering all the documents representative of the data
which one wishes to model.
The term "documents" must be here taken in a broad sense. It can
involve either paper documents, or magnetic documents (recording
of conversations, disc or computer floppy disk) or any other support
usable to store information.
Working documents
To illustrate the various stages of database design,
we will use as a typical example, the management of students who
follow the various educations of a diploma.
The working documents used are the following ones:
|
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 :
-A subject is taught only by one teacher.
-A student has only one mark per subject.
Data Dictionary
Starting from documents, the analysis phase consists
in extracting all the elementary information (non decomposable)
which are going to constitute the database.
The combination of the set of elementary data, that
one calls attributes or fields, constitutes the data
dictionary. Each attribute (field) of the data dictionary can be
characterized by the following properties:
|
Property
|
Meaning
|
|
Mnemonic
|
Abbreviation for the name of the attribute.
|
|
Wording
|
Contains precise meaning and role of the attribute.
|
|
Type of data
|
Type of the attribute : integer, real, string,
date...
|
|
Integrity constraints
|
List the constraints for the possible values
of the attribute
|
|
Calculation rule
|
Calculation rule for the corresponding attribute.
|
Relating to the preceding documents , the data dictionary
is the following one:
|
Mnemonic
|
Wording
|
Type
|
Constraints
|
Calculation rule
|
|
Ancient
|
Seniority in rank
|
Integer
|
> =0
|
|
|
Cdsexe
|
Code sex
|
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
|
