Database Summary
Uploaded by
ScribdTranslationsDatabase Summary
Uploaded by
ScribdTranslationsSummary of databases
A database represents a set of company data stored by a computer.
which is used by many people and whose organization is governed by a model of
data.
Computer representation of a 'system' from the real world: a company, a
administration, a university, a hospital, ...
The system includes entities, relationships between entities, and operations or
transactions on entities and relationships
Two types of information:
The information to be stored themselves (data)
Information for managing stored data
DBMS Database Management System: Software for structuring, storing,
of maintenance, updating, and consulting of databases
Objectives of a DBMS:
Reduce redundancies
Reduce inconsistencies, maintain integrity
Share the data
Provide access to the data
Maintain security
Functions of a DBMS
Data Definition Language (DDL): Description of data
Data Manipulation Language (DML): Data retrieval, Update of
data, Data transformation.
Data Control Language (DCL): Data integrity control, Management of
transactions and security.
Entity-relationship model;
Graphical representation of data and their relationships. Two basic concepts:
Entity: Object in a broad sense: A concrete notion like an individual or abstract like a
bank account:
Attribute: Characteristic or property of an entity, for example the name, the first name of the
professor
Identifier: Simple or composite attribute uniquely characterizing a
occurrence of an entity, for example the CIN or DOTI number for the
professor
Association: Link between entities, described by:
The entities playing a role in this association
A set of optional attributes
One dimension: unary, binary, etc.
A cardinality
Cardinalities: Concept that completes the conceptual model and clarifies the links between entities and
associations. Measured by a pair of values:
Minimum cardinality
maximum cardinality
Minimum and maximum number of possible participations of an occurrence of the entity and
the association. Four types of couples: 0, 1–1, 1–0, N–1, N
Procedure to follow to produce the MCD:
1) Identify the entities
2) Identify the associations between entities
3) Identify the attributes of each entity and each association
4) Evaluate the cardinalities of the associations
2. Relational model:
Simplicity of representation, data in the form of tables, Rigor of concepts (algebra of
relations), Lack of computer concepts, therefore conducive to the description of the MCD
Attribute: Basic information that can be manipulated by the designer. Described by a name or a
range of values
Relation: Description of entities or associations by sets of attributes,
Represented by a table containing a set of values, the columns are
The attributes, The lines are tuples or n-tuples.
Key of a relationship: Minimum set of attributes that unambiguously identifies a row of a
relation. Several types of key:
Candidate key: set of attributes that meets the definition of the key
Primary key: key held to constitute the key of the relationship
Foreign key: primary key in another relation
Integrity constraints: Set of rules for controlling the consistency of the taken values:
By the attributes with respect to their range of values (integrity constraint of
domain), Digital, Text, Memo… under Access
Through the primary keys of relationships (unique and non-null)
Through the foreign keys of the relationships in relation to the values of the primary keys of
other relationships to which they are linked (referential integrity constraint).
3. Transition from MCD to Relational
Each entity becomes a relationship. The attributes of the entity become attributes of the
The identifier of the entity becomes the primary key of the relation;
Each 1-N association is taken into account by including the primary key of the relationship.
whose maximum cardinality is N as a foreign key in the other relation;
Each M-N association is taken into account by creating a new relationship whose
Primary key is the concatenation of the primary keys of the participating relations.
attributes of the association are inserted into this new relationship.
4. Normalization
Objectives of standardization:
Suppression of update issues
Storage space optimization (elimination of redundancies)
Functional dependencies:
Let R(X, Y, Z) be a relation where X, Y, and Z are sets of attributes. Z can be
see.
Definition: Y depends functionally on X and we denote (X Y) if it's still there
the same value of Y that is associated with X in the relationship R.
First normal form:
A relation is in 1NF if every attribute is atomic. Example: the Professor relation
(Name,First Names,Age) is not in 1NF if the attribute First Names is of type [Mohammed,Amine]
Second normal form:
A relation is in 2NF if:
She is in 1FN
Every non-primary key attribute is dependent on the entire primary key.
Example: the Client relationship (CodeCli, Name, FirstName, DateOfBirth, Street, PostalCode, City) is in 2NF.
Third normal form:
A relationship is in 3NF if:
She is in 2NF.
There is no functional dependency between two non-primary key attributes.
Example: the relationship Company(Flight, Plane, Pilot)
With the DF Vol Airplane, Airplane Pilot and Flight Pilot is in 2NF, but not in 3NF
5. SQL Language:
It allows the definition, manipulation, and control of a relational database. It is based on
on relational algebra.
You can create constraints of type:
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK