|
Databases
design
This presentation is not a complete course on databases design,
but a first approach intended to specify the process and the concepts
necessary to build coherent databases.
The approach and the vocabulary used here are intended
for not specialists readers, without eluding the concepts essential
to the design process: "Ce qui se conçoit bien s'énonce
clairement, et les mots pour le dire arrivent aisément"...
(Boileau)
The approach and the tool (see below) are not "toys"
applications and allow you to build and use real relational databases.
------------------------------------
Comments, in green, are specially dedicated to "databases purist".
The design method, presented herafter, is easy to use (by everybody)
and operational. But easy doesn't mean simplistic. This method,
like all relational databases design methods, relies on the works
of Codd (1970) and Date (1975) and follows the recommandations of
the ANSI-SPARK workshop group (1975). Only the needlessly verbose
aspect of the "traditional" methods has been volontarily
removed.
------------------------------------
IBEasy+, a software
developed by the author
allows you to:
-
Apply, pratically, the databases design stages
presented in these pages
-
Design and use "easily" relational
databases
The reader, most interested by the formal aspects
can see, in these pages, the item "Normalization".
Fundamentals
of Database
A database is a set of structured and
not redundant information, whose organization
is based on a data model.
The two keywords which intervene in the definition of a database
are :
Structuring (by means of the data model) and not repetition
(redundancy or minimal redundancy) of data.
To build a database consists in gathering data in "homogeneous"
sets, ( entities, tables), each entity (table) being constituted
of a finished number of elementary data, Attributes or Fields,
repetition (redundancy) of attributes being minimal. The data model
used in this presentation is the entity-relationship model.
Database
management system (DBMS)
A DBMS is a software which plays the role of interface
between the users and the Database.
A DBMS allows to describe, to handle and to question
the data of a Database. A DBMS must manage all the problems connected
to concurrent accesses, to protection and restoration of data, to
control, integrity and security of data.
Database
and spreadsheet
An error, frequently made by novice users, consists
in confusing a database with a spreadsheet. The tables of a database
are presented indeed in rows and columns, as in a spreadsheet, but
the comparison stops there and differences between the two approaches
are significant and numerous. The following table, summarizes the
main differences between a database and a spreadsheet.
|
Differences on...
|
Spreadsheet
|
Database
|
|
Main use
|
Calculations
|
Data management and processing
|
|
Structuring of data
|
None
|
Structuring and strong consistency
|
|
Integrity Controls of data
|
None
|
Strict checking of the possible values of
each data
|
|
Access to data
|
Single user
|
Multi users
|
|
Data confidentiality
|
No control
|
Checking of the rights of access for each
user
|
|
Data size
|
-One table
-Some dozens of rows
|
-Several tables
-Several thousand of rows by table
|
|
Processing on data
|
Quantitative
|
Qualitative and quantitative
|
|
Interrogations of data
|
Realized by specific procedures
|
"Universal" language : SQL
|
Databases
design
The design of a Database, implies 3 fundamental stages:
-
-
-
|