Traduction en algèbre relationnelle

Considérez les 2 relations suivantes venant de la base de données university.

Table: instructor

ID name dept_name salary

10101 Srinivasan Comp. Sci. 65000

12121 Wu Finance 90000

15151 Mozart Music 40000

22222 Einstein Physics 95000

32343 El Said History 60000

33456 Gold Physics 87000

45565 Katz Comp. Sci. 75000

58583 Califieri History 62000

76543 Singh Finance 80000

76766 Crick Biology 72000

83821 Brandt Comp. Sci. 92000

98345 Kim Elec. Eng. 80000

Table: department

dept_name building budget

Biology Watson 90000

Comp. Sci. Taylor 100000

Elec. Eng. Taylor 85000

Finance Painter 120000

History Painter 50000

Music Packard 80000

Physics Watson 70000

Table: instructor
ID	name	dept_name	salary
10101	Srinivasan	Comp. Sci.	65000
12121	Wu	Finance	90000
15151	Mozart	Music	40000
22222	Einstein	Physics	95000
32343	El Said	History	60000
33456	Gold	Physics	87000
45565	Katz	Comp. Sci.	75000
58583	Califieri	History	62000
76543	Singh	Finance	80000
76766	Crick	Biology	72000
83821	Brandt	Comp. Sci.	92000
98345	Kim	Elec. Eng.	80000

Table: department
dept_name	building	budget
Biology	Watson	90000
Comp. Sci.	Taylor	100000
Elec. Eng.	Taylor	85000
Finance	Painter	120000
History	Painter	50000
Music	Packard	80000
Physics	Watson	70000

Répondez aux questions suivantes. Faites bien attention à la formulation de chaque question, à utiliser les noms précis et à ce qui est demandé.

Lim denne kommandoen inn i din terminal:

Passord for å koble til er

Spørsmål 1:

Noms des instructeurs appartenant au département Physics.

\(\pi_{name}(\sigma_{dept\_name='Physics'}(instructor))\)

\(\pi_{name}(instructor \bowtie_{instructor.dept\_name=department.dept\_name} department)\)

\(\sigma_{building='Physics'}(department)\)

\(\pi_{dept\_name}(\sigma_{name='Physics'}(instructor))\)

\(\sigma_{dept\_name='Physics'}(\pi_{name}(instructor))\)

Spørsmål 2:

Noms des instructeurs travaillant dans un département situé dans le bâtiment Watson.

\(\pi_{name}(\sigma_{dept\_name='Watson'}(instructor))\)

\(\pi_{building}(instructor)\)

\(\sigma_{building='Watson'}(department)\)

\(instructor \times department\)

\(\pi_{name}(\sigma_{building='Watson'}(instructor \bowtie_{instructor.dept\_name=department.dept\_name} department))\)

Spørsmål 3:

Noms des instructeurs ayant un salaire strictement supérieur à 90000.

\(\pi_{name}(department)\)

\(\sigma_{budget>90000}(department)\)

\(\pi_{salary}(\sigma_{name>90000}(instructor))\)

\(\sigma_{salary>90000}(\pi_{name}(instructor))\)

\(\pi_{name}(\sigma_{salary>90000}(instructor))\)

Spørsmål 4:

Nom des départements dont le budget est inférieur à 80000.

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

\(\pi_{budget}(department)\)

\(\sigma_{budget<80000}(\pi_{dept\_name}(department))\)

\(department \bowtie_{instructor.dept\_name=department.dept\_name} instructor\)

\(\pi_{dept\_name}(\sigma_{budget<80000}(department))\)

Spørsmål 5:

Noms des instructeurs ne travaillant pas dans le département Physics.

\(\pi_{name}(\sigma_{building='Physics'}(department))\)

\(\sigma_{dept\_name\neq'Physics'}(instructor)\)

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

\(\pi_{name}(instructor) - \pi_{name}(\sigma_{dept\_name='Physics'}(instructor))\)

\(instructor \bowtie_{instructor.dept\_name=department.dept\_name} department\)

Spørsmål 6:

Noms des départements situés dans le bâtiment Painter.

\(\pi_{dept\_name}(\sigma_{building='Painter'}(department))\)

\(\sigma_{building='Painter'}(\pi_{dept\_name}(instructor))\)

\(\pi_{dept\_name}(department) - \pi_{dept\_name}(\sigma_{building='Painter'}(department))\)

\(\pi_{dept\_name}(instructor \bowtie_{instructor.dept\_name=department.dept\_name} department)\)

\(\pi_{building}(\sigma_{dept\_name='Painter'}(department))\)

Spørsmål 7:

Noms des instructeurs travaillant dans le bâtiment Taylor.

\(\pi_{name}(\sigma_{dept\_name='Taylor'}(instructor))\)

\(\sigma_{building='Taylor'}(\pi_{name}(instructor))\)

\(\pi_{name}(instructor) \cap \pi_{dept\_name}(\sigma_{building='Taylor'}(department))\)

\(\pi_{name}(\sigma_{building='Taylor'}(instructor \bowtie_{instructor.dept\_name=department.dept\_name} department))\)

\(\pi_{name}(\sigma_{building='Taylor'}(department))\)

Spørsmål 8:

Identifiants (ID) des instructeurs du département History.

\(\sigma_{dept\_name='History'}(\pi_{ID}(department))\)

\(\pi_{ID}(\sigma_{dept\_name='History'}(department))\)

\(\pi_{ID}(instructor \bowtie_{instructor.dept\_name=department.dept\_name} department) - \pi_{ID}(department)\)

\(\pi_{dept\_name}(\sigma_{ID='History'}(instructor))\)

\(\pi_{ID}(\sigma_{dept\_name='History'}(instructor))\)

Spørsmål 9:

Noms des instructeurs travaillant soit en Finance soit en History.

\(\pi_{name}(instructor) \cap \pi_{dept\_name}(\sigma_{building='Painter'}(department))\)

\(\pi_{name}(\sigma_{dept\_name='Finance'}(department)) \cup \pi_{name}(\sigma_{dept\_name='History'}(department))\)

\(\pi_{name}(\sigma_{dept\_name='Finance' \land dept\_name='History'}(instructor))\)

\(\pi_{name}(\sigma_{dept\_name='Finance'}(instructor)) \cup \pi_{name}(\sigma_{dept\_name='History'}(instructor))\)

\(\pi_{name}(\sigma_{building='Painter'}(department))\)

Spørsmål 10:

Noms des instructeurs ayant un salaire au moins égal à 80000.

\(\pi_{name}(\sigma_{salary\ge 80000}(instructor))\)

\(\pi_{name}(\sigma_{budget\ge 80000}(department))\)

\(\pi_{salary}(\sigma_{name\ge 80000}(instructor))\)

\(\sigma_{salary\ge 80000}(\pi_{dept\_name}(instructor))\)

\(\pi_{name}(instructor) - \pi_{name}(\sigma_{salary\ge 80000}(instructor))\)

Spørsmål 11:

Couples (name, dept_name) de tous les instructeurs.

\(\pi_{name,dept\_name}(instructor)\)

\(\pi_{dept\_name,name}(instructor \bowtie_{instructor.dept\_name=department.dept\_name} department)\)

\(\pi_{name}(instructor) \times \pi_{dept\_name}(department)\)

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

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

Spørsmål 12:

Noms des instructeurs dont le salaire est compris entre 70000 et 90000 (inclus).

\(\pi_{name}(\sigma_{salary<70000 \land salary>90000}(instructor))\)

\(\sigma_{70000\le salary \land salary\le 90000}(\pi_{dept\_name}(instructor))\)

\(\pi_{name}(\sigma_{70000\le budget \land budget\le 90000}(department))\)

\(\pi_{name}(instructor) - \pi_{name}(\sigma_{70000\le salary \land salary\le 90000}(instructor))\)

\(\pi_{name}(\sigma_{70000\le salary \land salary\le 90000}(instructor))\)

Spørsmål 13:

Noms des instructeurs travaillant dans un département dont le budget est inférieur à 80000.

\(\pi_{name}(\sigma_{budget<80000}(instructor \bowtie_{instructor.dept\_name=department.dept\_name} department))\)

\(\pi_{name}(instructor) \cap \pi_{dept\_name}(\sigma_{budget<80000}(department))\)

\(\sigma_{budget<80000}(\pi_{name}(instructor))\)

\(\pi_{name}(\sigma_{budget<80000}(department))\)

\(\pi_{name}(\sigma_{salary<80000}(instructor))\)

Spørsmål 14:

Noms des instructeurs travaillant dans un département dont le budget est égal à 70000.

\(\pi_{name}(\sigma_{salary=70000}(instructor))\)

\(\pi_{dept\_name}(\sigma_{budget=70000}(department))\)

\(\sigma_{budget=70000}(\pi_{name}(instructor))\)

\(\pi_{name}(instructor) - \pi_{name}(\sigma_{budget=70000}(instructor \bowtie_{instructor.dept\_name=department.dept\_name} department))\)

\(\pi_{name}(\sigma_{budget=70000}(instructor \bowtie_{instructor.dept\_name=department.dept\_name} department))\)

Spørsmål 15:

Noms des instructeurs travaillant à Watson et ayant un salaire strictement supérieur à 90000.

\(\pi_{name}(\sigma_{building='Watson'}(instructor \bowtie_{instructor.dept\_name=department.dept\_name} department))\)

\(\pi_{name}(\sigma_{building='Watson' \land budget>90000}(instructor \bowtie_{instructor.dept\_name=department.dept\_name} department))\)

\(\pi_{name}(\sigma_{building='Watson' \land salary>90000}(instructor \bowtie_{instructor.dept\_name=department.dept\_name} department))\)

\(\pi_{name}(\sigma_{building='Watson'}(department))\)

\(\pi_{name}(\sigma_{salary>90000}(instructor))\)

Spørsmål 16:

Noms des départements qui n’ont aucun instructeur.

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

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

\(\pi_{dept\_name}(\sigma_{salary=0}(instructor))\)

\(\pi_{dept\_name}(department \bowtie_{department.dept\_name=instructor.dept\_name} instructor)\)

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

Spørsmål 17:

Noms des instructeurs travaillant dans un département dont le budget est au moins 100000.

\(\pi_{name}(instructor) \cap \pi_{dept\_name}(\sigma_{budget\ge 100000}(department))\)

\(\pi_{name}(\sigma_{salary\ge 100000}(instructor))\)

\(\pi_{name}(\sigma_{budget\ge 100000}(instructor \bowtie_{instructor.dept\_name=department.dept\_name} department))\)

\(\pi_{dept\_name}(\sigma_{budget\ge 100000}(department))\)

\(\sigma_{budget\ge 100000}(\pi_{name}(instructor))\)

Spørsmål 18:

Noms des instructeurs appartenant à un département situé soit à Watson soit à Taylor.

\(\pi_{name}(\sigma_{building='Watson' \land building='Taylor'}(instructor \bowtie_{instructor.dept\_name=department.dept\_name} department))\)

\(\sigma_{building='Watson'}(department) \cup \sigma_{building='Taylor'}(department)\)

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

\(\pi_{name}(\sigma_{building='Watson'}(instructor \bowtie_{instructor.dept\_name=department.dept\_name} department)) \cup \pi_{name}(\sigma_{building='Taylor'}(instructor \bowtie_{instructor.dept\_name=department.dept\_name} department))\)

\(\pi_{name}(instructor) \cap \pi_{building}(\sigma_{building='Watson'}(department))\)

Spørsmål 19:

Noms des instructeurs travaillant dans un département dont le budget est strictement inférieur à celui du département Physics.

\(\pi_{name}(\sigma_{salary<70000}(instructor))\)

\(\pi_{dept\_name}(\sigma_{budget<70000}(department))\)

\(\pi_{name}(\sigma_{budget<70000}(instructor \bowtie_{instructor.dept\_name=department.dept\_name} department))\)

\(\pi_{name}(\sigma_{budget<\pi_{budget}(\sigma_{dept\_name='Physics'}(department))}(instructor \bowtie_{instructor.dept\_name=department.dept\_name} department))\)

\(\pi_{name}(\sigma_{dept\_name='Physics'}(instructor))\)

Spørsmål 20:

Noms des instructeurs qui travaillent dans un département apparaissant dans la table department.

\(\pi_{name}(\sigma_{budget>0}(department))\)

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

\(instructor \times department\)

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

\(\pi_{name}(instructor \bowtie_{instructor.dept\_name=department.dept\_name} department)\)

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

Informasjon

Logg inn