Relational database courses and exercises

Contents

Introduction

1.1. Database
1.1.1. Definition
1.1.2. The description of a database
1.1.2.1. Definitions
1.2. Database Management System
1.2.1. Definition
1.2.2. The aims and properties of these systems are manifold

2.1. Introduction
2.2. basic concepts
2.2.1. Attribute
2.2.2. Field
2.2.3. Schematic relationship
2.2.4. Relationship
2.2.5. Degree
2.2.6. Occurrence
2.2.7. cardinality
2.2.8. candidate key
2.2.9. primary key
2.2.10. foreign key
2.3. relational model
2.3.1. Translation of the conceptual model into relational model
2.3.1.1. Rules passages MC objects NCM Relations
2.3.1.2. passing rules of associations MC NCM Relations

3.1. The basic operations
3.2. Expression of the relational algebra

4.1. functional dependence
4.1.1. Introduction
4.1.2. Definition
4.1.3. Graphical representation of functional dependencies
4.1.4. Properties (Armstrong axioms)
4.1.5. elementary functional dependence
4.1.6. direct functional dependence
4.2. The transitive closure
4.3. The minimum coverage
4.4. Closure of a set of attributes
4.4.1. Definition
4.4.2. A closure algorithm
4.5. Research Process key candidates
4.6. poor design
4.7. The decomposition
4.7.1. Definition
4.7.2. Decomposition Lossless Information
4.7.3. Decomposition without loss of DF
4.8. Why normalize?
4.8.1 First Normal Form
4.8.2. Second Normal Form
4.8.3. Third Normal Form
4.8.4. normal form Boyce-Codd
4.9 Decomposition into 3NF
4.10. valid decomposition BCNF
4.11. Decomposition without loss of information: Ullman algorithm
4.11.1. Formalism of the algorithm:
4.11.2. Sample Application

5.1. Basic Structure
5.1.2 Select clause
5.1.3. Where clause
5.1.4. The From clause
5.1.5. The variables tuples
5.1.6. The Order by clause
5.1.7 set operators
5.2. The aggregate functions
5.2.1 aggregates and group by
5.2.2. Aggregates and the having clause
5.2.3. nested queries
5.2.4. The views
5.2.5. Changing relationships
5.3. Sql as ldd
5.3.1. Areas
5.3.2. Creating tables
5.3.3. Schema Manipulation
5.3.4. foreign key
5.3.5. outer join
5.3.6. Mechanism of Rights
5.3.7. The rights in sql
5.3.8. Using Views

TP 1: Creating the database

TP 2: Inserting data

TP 3: Importing and exporting data

TP 4: Selecting Data

TP 5: Data Update

TP 6: Data Deletion

TP 7: Relations between tables

Exercises

Exercises Solutions

Introduction

This course is intended for computing sophomores and aims at presenting basic principles of relational DBMS and the practice of these fundamentals. The course content is mainly the following:

Chapter 1: Introduction to databases

Chapter 2: Relational Model

Chapter 3: Relational Algebra

Chapter 4: Standardization

Chapter 5: SQL Language

Chapter 6: Practical work

A set of exercises are included at the end of the document. We added a tutorial section and directed to allow students to apply the concepts learned in the five chapters.

For any questions or suggestions, you can contact me by email at the following address:

ouahabk@yahoo.fr

1. Introduction to databases

1.1. Database

1.1.1. Definition

A database is a set of interrelated information stored on a storage medium accessible by one or more applications, without duplication and structured independently of any application to meet the needs of different users. [3]

Example:

In a university details: students, teachers, courses presented and general resources can be pooled and made available to many users (teaching service, training services, scientific advice etc.).

1.1.2. The description of a database

1.1.2.1. Definitions

1. A schema is simply a description of the data in the database. This description is consistent with a data model that offers descriptive tools (structures, constraints and operations).
2. An instance is the actual content of the database at a given time.

The description of a database is done at three levels [1]:

1. External level: This level concerns the definition of types of users who can each have a separate view of the same base. Each of these views is derived from the conceptual schema.
2. The Internal or physical level: this level is relative to the developers. Physical data organization and access functions are defined (file organization, index, structure ...).
3. The conceptual level includes all external views without seeking how to save the data (abstract description).

Abbildung in dieser Leseprobe nicht enthalten

1.2. Database Management System

1.2.1. Definition

DBMS or Data Base Management System is a software system for storing and sharing information in a database, ensuring the quality, sustainability and the confidentiality of information while hiding the complexity of operations (wikipedia).

1.2.2. The aims and properties of these systems are manifold

- Give a description of the information stored in the database (you must ensure independence between the data and programs that process). [8]
- Provide an interface to ensure interaction with the database (search, update, delete).
- Providing a data manipulation language (DML) to allow the user to make requests to manipulate the databases.
- Guarantee the physical and logical data independence: the user does not have to know the physical organization of data (sequential access indexed sequential ...) and their logical organization. A change from the implantation of the base should not have any effect on the requests of the user. This independence allows in particular to offer several partial views of the same base in different types of users.
- Minimize the presence of unnecessary redundancy.

There are two types of redundancy:

1. The same data is present multiple times in different files.

2. Data is physically present in a file but can be inferred from other data.

- Maintaining data consistency through centralized administration supported by the Data Base Administrator (DBA) (validity of data, data dependency ...). These include for example the case:

1. Compulsory redundancy when the update will be "complete" to ensure consistency of the database.

2. Checking integrity constraints that must be constantly checked to ensure consistency of the database. They are verified during updates tuples: value to be necessarily defined range of possible values, typing ...

- Providing multiple access to the data and address the problem of concurrency (the problem of mutual exclusion in, for example, the update "simultaneous").
- Guaranteeing the security of data access (confidentiality, identification of users).
- Manage opportunities failures and make them transparent to the user (checkpoint).
- Provide efficient access to data.

Among the existing DBMS include: Oracle, Ingres, SQL Server, O2, Access, DB2, MySql, MSQL, PostgreSQL

2. Relational model

2.1. Introduction

The relational model is a way to model data in a database that is based on mathematical principles put forward by Codd in 1970 [9].

2.2. basic concepts

2.2.1. Attribute

Definition: An attribute is an identifier (name) describing a recorded data in a database.

Example :

The registration number and the name of a student are attributes.

2.2.2. Field

Definition: The domain of an attribute is the set, finite or infinite, its possible values.

Example :

The registration number attribute field for all combinations of five numbers and domain name for all combinations of letters (string).

2.2.3. Schematic relationship

Definition: A relation schema R, indicated R (A1: D1, A2: D2, ..., An: Dn) is an attribute group. Each attribute Ai is the name of a role played by the domain Di in the relation schema R.

A relation schema R is used to describe a relationship.

Example :

Student (num_insc: Integer, name: String, name: String). We can also write the scheme as follows: Student (num_insc, name).

2.2.4. Relationship

Definition: A relation is a subset of the Cartesian product of n domains attribute (n> 0).

A relationship is represented as a two-dimensional array in which the n attributes included titles of n columns. An example of relationship with three attributes:

Abbildung in dieser Leseprobe nicht enthalten

Student (num_insc: Integer, name: String, name: String).

2.2.5. Degree

Definition: The degree of a relationship is the number of attributes, such as the degree the student relationship is 3.

2.2.6. Occurrence

definition: An instance is a member of the set represented by a relationship.

In other words, a case is a line of the table. For example the triple (00003 Ali, whip).

2.2.7. cardinality

Definition: The cardinality of a relationship is the number of occurrences. For example, the cardinality of the relationship is 4 student.

2.2.8. candidate key

Definition: A candidate key of a relationship is a minimum set of relationship attributes that indexes each line in a differentiated manner.

- The value of a candidate key is to separate all occurrences.
- The candidate keys of a relationship does not necessarily have the same number of attributes.
- A candidate key can be formed of an arbitrary attribute, used only for this purpose.

2.2.9. primary key

Definition : The primary key of a relationship is one of its key candidates.

- The primary key can be selected in a random manner but the context often helps determine which candidate key is to be considered as the primary key.

- Generally, the attributes that form the primary key is stressed.

Example :

Student (Code_per, Name, Surname, Address, Date_nais, Lieu_nais, Num_ASS)

Identify all the key candidates? What is the primary key that you can choose?

2.2.10. foreign key

definition: A foreign key of a relationship is formed of one or more attributes that make up a key in another relationship.

Example :

Student (No. ETUD, name, age);

Courses (NAMEC, schedule, prof);

Follows (# N ° ETUD, #NameC).

2.3. relational model

2.3.1. Translation of the conceptual model into relational model

Entities and associations have resulted in relationships. [2]

2.3.1.1. Rules passages MC objects NCM Relations

1. Any object MC turns into relationship in the MR;

2. All object properties become attributes of the relationship;

3. The ID of the object becomes the key to the relationship.

2.3.1.2. passing rules of associations MC NCM Relations

Case 1: type cardinality (x, 1), (x, n) in a binary combination.

Abbildung in dieser Leseprobe nicht enthalten

1. The object 1 becomes the R1 relationship

2. The object 2 is the relationship R2

3. The object identifier 2 becomes an attribute of the relationship R1, it will be called foreign key.

4. The properties of the association become R attributes.

Example:

Abbildung in dieser Leseprobe nicht enthalten

Customer (Num_cl, Name, family name, addr)

Order (Num_c, Date_c, #Num_cl).

Case 2: Type cardinality (X, N), (X, N) in combination with any degree .

1. All objects become relations

2. The association is a relationship,

3. The identifier of the Association the key to the relationship,

4. The properties of the association become the relationship attributes.

Example :

Abbildung in dieser Leseprobe nicht enthalten

Order (Num_c, Date_c)

Product (Code_P, Des_p)

Contains (#Num_c, #Code_P, Qte_c).

Special cases :

(X, 1), (X, 1):

Id 1 becomes foreign key in the relationship associated with O2;

Id 2 is a foreign key in the relationship associated with O1

Abbildung in dieser Leseprobe nicht enthalten

A) Case reflexive association:

Abbildung in dieser Leseprobe nicht enthalten

R1 (ID_1, # ID_1).

Example :

Abbildung in dieser Leseprobe nicht enthalten

Item (Num_p, Des, #Num_P_Cible).

3. Relational Algebra

3.1. The basic operations

The relational algebra can meet the requests because (theorem) any query can be formulated from the following [10, 11].

The union is binary operations (tables = tuple sets):

- corresponding to the usual operations of set theory
- can be applied on the same table schema and gives a new same pattern table

- The union

The union is binary operations (tables = tuple sets):

- corresponding to the usual operations of set theory
- can be applied on the same table schema and gives a new same pattern table

Abbildung in dieser Leseprobe nicht enthalten

- the difference : Abbildung in dieser Leseprobe nicht enthalten

The difference is an operation on two relationships R1 and R2 having the same pattern and building a third relationship in which the tuples consist of only those located in the R1 relationship. Notation: R1 - R2

Abbildung in dieser Leseprobe nicht enthalten

- projection : Abbildung in dieser Leseprobe nicht enthalten (No redundancy)

Is a unary operation of deleting columns (attributes) of the table and by eliminating duplicate tuples (if an attribute of the primary key has been deleted). Projecting a pattern table of R (a1, a2 ... ap, ap + 1 ... an) in the direction (a1, a2 ... ap) is a schema S table (a1, a2 ... ap) whose tuples are those R which are deleted attributes do not belong to the projection direction and by eliminating duplicate tuples:

Abbildung in dieser Leseprobe nicht enthalten

- the selection : Abbildung in dieser Leseprobe nicht enthalten

Restricting (or selection) of a table according to a criterion of R or Q qualification restriction (which may include one or more attributes of R) is a unary operation. Its result is an R table! The same scheme as R, R tuples are tuples verifying the qualification Q.

Abbildung in dieser Leseprobe nicht enthalten

- Cartesian product: Abbildung in dieser Leseprobe nicht enthalten

It's binary operation, the Cartesian product of R 2 tables and diagrams any S is a table T having the attributes concatenating those of R and S, whose tuples are all a concatenation R tuple in a tuple S

Abbildung in dieser Leseprobe nicht enthalten

- the intersection: Abbildung in dieser Leseprobe nicht enthalten

It is an operation on two relationships R1 and R2 having the same pattern and building a third relationship in which the tuples consist of those belonging to the two relations.

Abbildung in dieser Leseprobe nicht enthalten

- the division : Abbildung in dieser Leseprobe nicht enthalten

The quotient of the division of a table D (a1, a2 ... ap, ap + 1 ... an) by the sub table (ap ... an) is the Q table (a1, a2 ... ap) whose tuples are those concatenated to any of tuple give D tuple

- It allows you to search in a table subtables that are complemented by those of another table
- It provides answers to the queries form "for every x, find it"

Is Abbildung in dieser Leseprobe nicht enthalten and Abbildung in dieser Leseprobe nicht enthalten with Abbildung in dieser Leseprobe nicht enthalten.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung in dieser Leseprobe nicht enthalten

Example : Abbildung in dieser Leseprobe nicht enthalten= Vendors selling all products.

- the join: Abbildung in dieser Leseprobe nicht enthalten

This is a binary operation, the join tables R and S 2 is a table T obtained as follows:

1. achieve the Cartesian product of two tables R and S

2. performing a selection operation (or qualification) between an attribute of the R table and an attribute of the S table called "join attributes"

3. Whether or not to projection operation to reduce the pattern of the resulting table

Note :

1. it performs concatenation tables limited to tables of instances with common values ​​on join attributes
2. it materializes the link between multiple tables or merging multiple tables
3. selection-qualification or "join operator" is generally equal, but can be extended to any logical operators.
4. it can be done on any attribute, without prejudging the semantic relevance of the result, only the Equality knuckle built on the primary key attributes reflect relationships (conceptual).

Is binary-operation, the join of table 2 R and S as a condition is to bring tuples tables 2 R and S to form a third table T which contains the set of all the tuples obtained by concatenating a tuple of R and S tuple satisfying condition

Abbildung in dieser Leseprobe nicht enthalten

- The natural join : Abbildung in dieser Leseprobe nicht enthalten (Special case with equality on common tuples)

The natural join of two tables R and S T is a table whose attributes are the union of R and S attributes whose tuples are obtained by concatenating a tuple of R and S tuple having same values ​​for attributes the same name:

Abbildung in dieser Leseprobe nicht enthalten

3.2. Expression of the relational algebra

Algebraic operations can be combined to form expressions of relational algebra.

example:

Either the database composed of the following relationships:

R (doctor, disease rates);

S (number, ill, illness);

Abbildung in dieser Leseprobe nicht enthalten

The answer to the question "what are the names of the doctors can examine the patient Khaled and price consultations" can be expressed using one of the following two trees:

Abbildung in dieser Leseprobe nicht enthalten

An operations tree interpreter upwards. Algebraic expressions corresponding to each of the previous two shafts are respectively:

(A) Abbildung in dieser Leseprobe nicht enthalten ( Abbildung in dieser Leseprobe nicht enthalten )

(B) Abbildung in dieser Leseprobe nicht enthalten

[...]