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

Dán dòng lệnh này vào terminal của bạn:

Mật khẩu để kết nối là

Câu hỏi 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)\)

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

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

Câu hỏi 2:

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

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

\(instructor \times department\)

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

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

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

Câu hỏi 3:

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

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

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

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

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

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

Câu hỏi 4:

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

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

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

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

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

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

Câu hỏi 5:

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

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

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

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

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

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

Câu hỏi 6:

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

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

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

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

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

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

Câu hỏi 7:

Noms des instructeurs travaillant dans le bâtiment Taylor.

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

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

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

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

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

Câu hỏi 8:

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

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

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

Câu hỏi 9:

Noms des instructeurs travaillant soit en Finance soit en History.

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

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

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

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

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

Câu hỏi 10:

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

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

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

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

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

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

Câu hỏi 11:

Couples (name, dept_name) de tous les instructeurs.

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

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

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

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

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

Câu hỏi 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}(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}(\sigma_{salary<70000 \land salary>90000}(instructor))\)

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

Câu hỏi 13:

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

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

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

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

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

Câu hỏi 14:

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

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

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

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

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

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

Câu hỏi 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'}(department))\)

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

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

Câu hỏi 16:

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

\(\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_{dept\_name}(instructor) - \pi_{dept\_name}(department)\)

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

Câu hỏi 17:

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

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

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

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

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

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

Câu hỏi 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))\)

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

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

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

Câu hỏi 19:

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

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

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

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

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

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

Câu hỏi 20:

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

\(instructor \times department\)

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

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

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

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

Tác giả	Quentin Cappart
Hạn chót	Không có hạn chót
Giới hạn nộp bài	Không có giới hạn

Thông tin

Đăng nhập