Traduction en algèbre relationnelle

en (English) de (Deutsch) el (ελληνικά) es (Español) fr (Français) he (עִבְרִית) nl (Nederlands) nb_NO (Norsk (bokmål)) pt (Português) vi (Tiếng Việt)

Kurs-Liste Einloggen

Information

Author(s)	Quentin Cappart
Deadline	Keine Frist
Abgabenlimit	No limitation

Einloggen

Please register or sign in to see the complete list of courses and be able to submit answers to problems.

Einloggen

Pour cette question, on va considérer ces relations de la base de données university :

\(\mathsf{course}(\underline{course\_id}, title, dept\_name, credits)\)
\(\mathsf{prereq}(\underline{course\_id},\underline{prereq\_id})\)
\(\mathsf{department}(\underline{dept\_name}, building, budget)\)
\(\mathsf{student}(\underline{ID}, name, dept\_name, tot\_credit)\)
\(\mathsf{instructor}(\underline{ID}, name, dept\_name, salary)\)
\(\mathsf{advisor}(\underline{s\_ID}, i\_ID)\)

Nous n'avons pas besoin de connaître les tuples de la table. Réferez vous au diagramme des schémas pour repérer les référencements via les clefs étrangères.

Répondez aux questions suivantes.

Paste this command into your terminal:

The password to connect is

Question 1:

Donner les noms des étudiants inscrits dans le département au nom Comp. Sci.

\(\pi_{name}(\sigma_{dept\_name='Comp. Sci'}(department))\)

\(\pi_{name}(student \bowtie advisor)\)

\(\sigma_{dept\_name='Comp. Sci'}(student)\)

\(\pi_{name}(\sigma_{dept\_name='Comp. Sci'}(student))\)

\(\pi_{student.name}(student \bowtie_{student.dept\_name=department.dept\_name} \sigma_{dept\_name='Comp. Sci'}(department))\)

Question 2:

Donner les identifiants des étudiants qui ont un superviseur (advisor).

\(\pi_{s\_ID}(advisor)\)

\(\pi_{ID}(student)\)

\(\pi_{ID}(instructor)\)

\(\pi_{ID}(student \bowtie_{student.ID=advisor.s\_ID} advisor)\)

\(\pi_{i\_ID}(advisor)\)

Question 3:

Donner les noms des instructeurs supervisant au moins un étudiant.

\(\pi_{name}(instructor \bowtie_{instructor.ID=advisor.i\_ID} advisor)\)

\(\pi_{instructor.name}(instructor \bowtie_{instructor.ID=advisor.i\_ID} advisor)\)

\(\pi_{name}(instructor)\)

\(\pi_{name}(student)\)

\(\pi_{name}(advisor)\)

Question 4:

Donner les titres des cours offerts par le département au nom Physics.

\(\sigma_{dept\_name='Physics'}(department)\)

\(\pi_{title}(prereq)\)

\(\pi_{title}(department)\)

\(\pi_{course.title}(course \bowtie_{course.dept\_name=department.dept\_name} \sigma_{dept\_name='Physics'}(department))\)

\(\pi_{title}(\sigma_{dept\_name='Physics'}(course))\)

Question 5:

Donner les identifiants des cours ayant au moins un prérequis.

\(\pi_{course.course\_id}(course \bowtie_{course.course\_id=prereq.course\_id} prereq)\)

\(\pi_{course\_id}(prereq)\)

\(\pi_{prereq\_id}(prereq)\)

\(\pi_{course\_id}(course)\)

\(\sigma_{course\_id}(prereq)\)

Question 6:

Donner les identifiants des cours qui sont prérequis d'au moins un autre cours.

\(\sigma_{prereq\_id}(course)\)

\(\pi_{course\_id}(prereq)\)

\(\pi_{course\_id}(course)\)

\(\pi_{prereq\_id}(prereq)\)

\(\pi_{course.course\_id}(course \bowtie_{course.course\_id=prereq.prereq\_id} prereq)\)

Question 7:

Donner les noms des étudiants conseillés par un instructeur du département Physics.

\(\pi_{name}(instructor)\)

\(\pi_{student.name}(\sigma_{dept\_name='Physics'}(instructor) \bowtie_{instructor.ID=advisor.i\_ID} advisor \bowtie_{advisor.s\_ID=student.ID} student)\)

\(\pi_{dept\_name}(student)\)

\(\pi_{student.name}(student \bowtie advisor)\)

\(\pi_{name}(student)\)

Question 8:

Donner les noms des étudiants ayant le même département que leur superviseur.

\(\pi_{name}(student)\)

\(\pi_{student.name}(student \bowtie instructor)\)

\(\sigma_{dept\_name}(advisor)\)

\(\pi_{instructor.name}(advisor)\)

\(\pi_{student.name}(\sigma_{student.dept\_name=instructor.dept\_name}(student \bowtie_{student.ID=advisor.s\_ID} advisor \bowtie_{advisor.i\_ID=instructor.ID} instructor))\)

Question 9:

Donner les noms des départements n'ayant aucun étudiant.

\(\pi_{dept\_name}(student)\)

\(\sigma_{dept\_name}(student)\)

\(\pi_{dept\_name}(department)\)

\(\pi_{dept\_name}(department) - \pi_{dept\_name}(student)\)

\(\pi_{dept\_name}(department \bowtie student)\)

Question 10:

Donner les identifiants des étudiants qui n'ont pas de superviseur.

\(\sigma_{ID}(advisor)\)

\(\pi_{s\_ID}(advisor)\)

\(\pi_{ID}(student \bowtie advisor)\)

\(\pi_{ID}(student) - \pi_{s\_ID}(advisor)\)

\(\pi_{ID}(student)\)

Information

Einloggen