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é.

עליך להדביק את הפקודה הזאת במסוף שלך:

הסיסמה להתחברות אל

שאלה 1:

Noms des instructeurs appartenant au département Physics.

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

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

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

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

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

שאלה 2:

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

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

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

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

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

\(instructor \times department\)

שאלה 3:

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

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

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

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

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

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

שאלה 4:

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

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

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

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

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

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

שאלה 5:

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

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

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

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

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

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

שאלה 6:

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

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

\(\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_{building}(\sigma_{dept\_name='Painter'}(department))\)

שאלה 7:

Noms des instructeurs travaillant dans le bâtiment Taylor.

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

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

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

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

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

שאלה 8:

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

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

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

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

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

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

שאלה 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_{building='Painter'}(department))\)

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

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

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

שאלה 10:

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

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

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

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

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

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

שאלה 11:

Couples (name, dept_name) de tous les instructeurs.

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

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

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

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

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

שאלה 12:

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

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

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

\(\pi_{name}(\sigma_{70000\le salary \land salary\le 90000}(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))\)

שאלה 13:

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

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

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

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

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

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

שאלה 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))\)

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

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

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

שאלה 15:

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

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

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

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

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

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

שאלה 16:

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

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

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

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

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

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

שאלה 17:

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

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

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

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

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

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

שאלה 18:

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

\(\pi_{name}(instructor) \cap \pi_{building}(\sigma_{building='Watson'}(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 \times department)\)

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

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

שאלה 19:

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

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

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

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

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

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

שאלה 20:

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

\(instructor \times department\)

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

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

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

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

יוצרים	Quentin Cappart
מועד הגשה	אין מועד הגשה
מגבלת הגשות	אין הגבלה

מידע

כניסה