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)

Kursliste Logg inn

Informasjon

Forfatter(e)	Quentin Cappart
Frist	Ingen frist
Innleveringsgrense	Ingen begrensning

Logg inn

Vennligst lag bruker eller logg inn for å se komplett liste over kurs, og for å sende inn svar på oppgaver.

Logg inn

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.

Lim denne kommandoen inn i din terminal:

Passord for å koble til er

Spørsmål 1:

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

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

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

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

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

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

Spørsmål 2:

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

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

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

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

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

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

Spørsmål 3:

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

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

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

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

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

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

Spørsmål 4:

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

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

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

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

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

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

Spørsmål 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)\)

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

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

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

Spørsmål 6:

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

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

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

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

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

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

Spørsmål 7:

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

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

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

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

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

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

Spørsmål 8:

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

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

\(\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))\)

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

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

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

Spørsmål 9:

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

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

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

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

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

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

Spørsmål 10:

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

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

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

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

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

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

Informasjon

Logg inn