Database Systems Exam Summary - UHBC June 2021

General Exam Information

• Course: Database (Bases de données) - L2
• Institution: UHBC (Université Hassiba Benbouali de Chlef)
• Instructor: Aridj med
• Date: June 2021
• Duration: 1 hour and 30 minutes
• Notes: Forbidden documents.

Exercise 1: Conceptual and Logical Modeling for a Media Library

Specification of Needs

• A disc is identified by an $ID$ and consists of a set of tracks (plages).
• Each track contains exactly one work (œuvre).
• One work can span several tracks (for example, a symphony in 4 movements).
• For each track, the performers (interprètes) are known.

Logical Model (MLD)

Based on the requirements, the following logical model is deduced:

• Client Relationship (Work): $\text{Œuvre }(\text{IDO, Titre})$
• Interpreter Relationship (Performer): $\text{Interprete }(\text{IDI, Nom})$
• Track Relationship: $\text{Plage }(\text{IDP, No, Duree, IDO, IDD})$
• Disc Relationship: \text{Disque }(\text{IDD, Date_p})
• Performance Relationship: $\text{Joue }(\text{IDP, IDI})$

Key Identifiers and Constraints

• Composite Key: $\text{IDP, IDI}$ in the relationship $\text{Joue}$.
• Foreign Keys: $\text{IDO}$ and $\text{IDD}$ in the relationship $\text{Plage}$.

Exercise 2: Relational Data Analysis and Synthesis Algorithm

Hotel Relation Characteristics

The provided relation "Hôtel" contains the following columns and data:

• Columns: $\text{IdHotel}$ ($A$), $\text{NumChambre}$ ($B$), $\text{TypeChambre}$ ($C$), $\text{CatégorieHote l}$ ($D$), $\text{Prix}$ ($E$).

Definitions and Metrics

• Rows (Linges): Represent tuples, items, or records.
• Columns: Represent attributes, properties, or fields.
• Degree: The number of columns in the relation. Degree = $05$.
• Cardinality: The number of tuples in the relation. Cardinality = $07$.

Functional Dependencies

Based on the data, the set $F$ of non-trivial functional dependencies is:

• $A \rightarrow D$
• $B \rightarrow C$
• $A, B \rightarrow E$
• Candidate Keys: $A, B$
• Closure of F: $F^+ = F$

Synthesis Algorithm (Iterative Process)

• Iteration 1:
  • Step 1: Start with $F^+ = \{A \rightarrow D, B \rightarrow C, (A,B) \rightarrow E\}$.
  • Step 3: No isolated attributes found.
  • Step 4.1: Determinant $X = \{A, B\}$.
  • Step 5.1: Dependent $Y = \{E\}$.
  • Step 6.1: Resulting relation $R_1(A, B, E)$.
• Iteration 2:
  • Step 4.2: Determinant $X = \{A\}$.
  • Step 5.2: Dependent $Y = \{D\}$.
  • Step 6.2: Resulting relation $R_2(A, D)$.
• Iteration 3:
  • Step 4.3: Determinant $X = \{B\}$.
  • Step 5.3: Dependent $Y = \{C\}$.
  • Step 6.3: Resulting relation $R_3(B, C)$.

Final Relational Schema:

• $R_1(A, B, E)$
• $R_2(A, D)$
• $R_3(B, C)$

Exercise 3: Bank Database (Relational Algebra and SQL)

Database Schema (BANQUE)

• AGENCE: \text{(Num_Agence, Nom, Ville, Actif)}
• COMPTE: \text{(Num_Compte, Num_Agence, Num_Client, Solde)}
• CLIENT: \text{(Num_Client, Nom, Prenom, Ville)}
• EMPRUNT: \text{(Num_Emprunt, Num_Agence, Num_Client, Montant)}

Query R1: Clients who have never taken out a loan

Relational Algebra Approach:

1. R_a \leftarrow \text{proj}(\text{CLIENT, Num_Client})
2. R_b \leftarrow \text{proj}(\text{EMPRUNT, Num_Client})
3. $\text{Result} \leftarrow R_a - R_b$

Query R2: Clients with an account in all agencies

Relational Algebra Approach:

1. R_a \leftarrow \text{proj}(\text{AGENCE, Num_Agence})
2. $R_b \leftarrow \text{DIV}(\text{COMPTE, } R_a)$ (Division operation to find clients with accounts in all agencies)
3. R_c \leftarrow \text{join}(R_b, \text{CLIENT, } R_b.\text{Num_client} = \text{CLIENT.Num_client})
4. $\text{Result} \leftarrow \text{proj}(R_c, \text{Nom, Prenom})$

Query R3: Number of clients with an account in an agency located in their own city

SQL Approach:

SELECT COUNT(DISTINCT Num_client)
FROM COMPTE, AGENCE, CLIENT
WHERE (COMPTE.Num_client = CLIENT.Num_client)
  AND (COMPTE.Num_agence = AGENCE.Num_agence)
  AND (CLIENT.Ville = AGENCE.Ville);

Query R4: Number of agencies per city

SQL Approach:

SELECT COUNT(Num_agence)
FROM AGENCE
GROUP BY Ville;

Query R5: Result Interpretation

SQL Code:

SELECT Num_Client
FROM Compte
WHERE Solde > (SELECT SUM(Actif) FROM Agence WHERE Ville = 'Chlef');

Result Interpretation: This query returns the list of customer numbers (\text{Num_Client}) who have a balance ($\text{Solde}$) strictly greater than the total sum of assets ($\text{Actif}$) of all agencies located in the city of Chlef.