Exercices Algèbre Relationnelle ENSA

BOULAALAM @2020

Soit le schéma de base de données "university" suivant:

classroom(building, room_number, capacity)

department(dept_name, building, budget)

course(course_id, title, dept_name, credits)

instructor(ID, name, dept_name, salary)

section(course_id, sec_id, semester, year, building, room_number, time_slot_id)

teaches(ID, course_id, sec_id, semester, year)

student(ID, name, dept_name, tot_cred)

takes(ID, course_id, sec_id, semester, year, grade)

advisor(s_ID, i_ID)

time_slot(time_slot_id, day, start_time, end_time)

prereq(course_id, prereq_id)

Question: Considérez la contrainte de clé étrangère de l'attribut dept_name  de la relation instructor à la relation department. Donnez des exemples d'insertions et de suppressions à ces relations qui peuvent provoquer une violation de la contrainte de clé étrangère.

Réponse: Par exemple essayant d’insérer le tuple suivant dans la relation instructor (10101, 'boulaalam', 'economics', 10000) , cette insertion viole la contrainte de clé étrangère si le département economics n’existe pas dans la relation department.  

Pour la suppression : essayant de supprimer le tuple, par exemple, ('Biology', 'FesSais', 100 000) où au moins un enregistrement dans la table student ou la table constructor a l'attribut dept_name = Biology. 

 Question@SI : Donner le MCD de ce schéma de bases de données en appliquant les règles de passage MCD -> MLD/MPD à l'inverse (reverse engineering). 

Question: Considérez la relation time_slot (créneau horaire). Étant donné qu'un créneau horaire particulier peut se rencontrer plus d'une fois par semaine, expliquez pourquoi le jour et l'heure de début font partie de la clé primaire de cette relation, alors que l'heure de fin ne l'est pas. 

Réponse: Puisqu'une classe (cours) particulière qui commence à une heure particulière et un jour particulier ne peut pas se terminer à plus d'un temps -une seule heure de fin.

Question: Dans l'instance de la relation instructor présentée en extension ci-après, deux instructeurs n'ont pas le même nom. De cela, pouvons-nous conclure que le nom peut être utilisé comme clé primaire de cette relation?

Réponse: Non même si dans cette instance il n y a pas de nom en doublon. Mais ce n'est pas la cas général sauf dans le scénario où l'université ne recrute pas deux instructeurs ayant le même nom!!!

Question: Quel est le résultat de la première exécution du produit cartésien de student et advisor, puis de l'exécution d'une opération de sélection avec les prédicats id = ID? (En utilisant les notations possibles de l'algèbre relationnelle, écrire  cette requête).

Réponse: La relation résultante du produit cartésien se compose de tous les attributs de student suivi de tous les attributs de advisor ==> les tuples ont cette forme (ID, name, dept_name, tot_cred,s_ID, i_ID). cette relation contient l'ensemble des tuples de toutes les possibilités des 2 relations : on parle de résultat exhaustif. Pour la partie 'puis de l'exécution d'une opération de sélection avec les prédicats id = ID', avec ce prédicat les tuples n'ayant pas i_id=id serons éliminer (c'est très logique et réel). En plus, les étudiants n'ayant pas un advisor seront aussi éliminer, et les étudiants ayant plus qu'un advisor ils seront afficher plusieurs fois (le nbr d'advisor) 

Soit le schéma de base donnée employee :

employee (person_name, street, city)

works (person_name, company_name, salary)

        company (company_name, city)

Donnez une expression en algèbre relationnelle pour répondre aux demandes suivantes:

Question: Trouvez le nom de chaque employé qui habite dans la ville «Rabat»

Réponse: Πperson_name (σcity =“Rabat”(employee))

Question: Trouvez le nom de chaque employé dont le salaire est supérieur à 100 000.

Réponse: Πperson_name (σsalary>100000(employee|x|works))

Question: Trouvez le nom de chaque employé qui habite à «Rabat» et dont le salaire est supérieur à 100 000.

 Réponse:Πperson_name (σsalary>100000 AND city =“Rabat”(employee|x|works))

 Soit le schéma de base donnée bank  suivant:

branch(branch_name, branch_city, assets)

customer (ID, customer_name, customer_street, customer_city)

loan (loan_number, branch_name, amount)

borrower (ID, loan_number)

account (account_number, branch_name, balance)

depositor (ID, account_number)

Donnez une expression en algèbre relationnelle pour répondre aux demandes suivantes

Question: Trouvez le nom de chaque succursale (branch) située à «Chicago»

Réponse:Πbranch_name (σbranch_city="Chicago"(branch))

Question: Trouvez l'ID de chaque emprunteur (borrower) qui a un prêt (loan) dans la succursale «Downtown»

Réponse:ΠID (σbranch_name="downtown"(loan |x| borrower ))

Soit le schéma de base donnée employee précédent 

Donnez une expression en algèbre relationnelle pour répondre aux demandes suivantes:

Question: Trouvez l'ID et le nom de chaque employé qui ne travaille pas pour «BigBank»

    Démarche/idée (Pour trouver des employés qui ne travaillent pas pour BigBank, trouver d'abord tous ceux qui travaillent pour bigBank. Ce sont exactement les employés qui ne font pas partie du résultat souhaité. Nous utilisons ensuite la différence d'ensemble pour trouver l'ensemble de tous les employés moins les employés qui ne devraient pas figurer dans le résultat.)

Réponse:

Sur le schéma il n y pas d'attribut ID pour emloyee dans ce cas vous id = person_name. Si non nous ajouterons un ID dans employee. Prenant le 2eme scénario.  employee aura cette forme 

employee (ID, person_name, street, city) et 

works (ID, company_name, salary)Remarque: Éliminer person_name de works pour éviter la redondance.

ΠID,person_name(employee) - 

               ΠID,person_name(σcompany_name="bigBank"(employee|x|works))

Question: Trouvez l'ID et le nom de chaque employé qui gagne au moins autant que chaque employé dans la base de données.

Réponse:

ΠID,person_name(employee) - 

        Πi.ID,i.person_name(ρi(employee) |x|i.salary<j.salary ρj(employee))