الدرس · مقدمة في النموذج العلائقي · IT1220

مقدمة الفصل

ماذا ستتعلم في هذا الفصل

The structure of a relation: attributes, tuples, domains, atomic values and null.
Relation schema and instance; relations are unordered.
Keys: superkey, candidate key, primary key and foreign key.
Relational algebra: select, project, union, set difference, intersection, Cartesian product, rename, composition and natural join.

أقسام الفصل

1Relations, attributes and tuplesالعلاقات والصفات والصفوف
2Relation schema and instanceمخطط العلاقة ونسختها
3Keysالمفاتيح
4Relational query languagesلغات الاستعلام العلائقية
5Select and projectالاختيار والإسقاط
6Union, set difference and intersectionالاتحاد والفرق والتقاطع
7Cartesian product, rename and compositionالضرب الديكارتي وإعادة التسمية وتركيب العمليات
8Natural joinالربط الطبيعي

تلميح: استخدم أسهم لوحة المفاتيح للتنقل بين الشرائح.

Attributes, tuples and domainsالصفات والصفوف

Relations, attributes and tuples

Example of a relation: instructor (slide 2) — the attributes are the columns, the tuples are the rows

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

Attributes (columns)الصفات

The columns of a relation.

Tuples (rows)الصفوف

The rows of a relation.

Domainالنطاق

The set of allowed values for each attribute (e.g., gender).

Attribute values are (normally) required to be atomic; that is, indivisible.
The special value null is a member of every domain; it indicates that the value is "unknown".
The null value causes complications in the definition of many operations.

Relation schema and instanceالمخطط والنسخة

Relation schema

If A1, A2, …, An are attributes, R = (A1, A2, …, An) is a relation schema — e.g., instructor = (ID, name, dept_name, salary).

Formally, given sets D1, D2, …, Dn, a relation r is a subset of D1 × D2 × … × Dn — a set of n-tuples (a1, …, an) where each ai ∈ Di.
The current values (relation instance) are specified by a table; an element t of r is a tuple, represented by a row.
Relations are unordered: the order of tuples is irrelevant (tuples may be stored in an arbitrary order).

instructor relation with unordered tuples (slide 5) — the same twelve tuples as slide 2, in another order

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

Superkeys, candidate keys and primary keysالمفاتيح

Superkeyمفتاح فائق

K ⊆ R is a superkey if values for K are sufficient to identify a unique tuple of each possible relation r(R). Example: {ID} and {ID, name} are both superkeys of instructor.

Candidate keyمفتاح مرشح

A superkey K is a candidate key if K is minimal. Example: {ID}.

Primary keyمفتاح أساسي

One of the candidate keys is selected to be the primary key.

Foreign keysالمفتاح الأجنبي

Foreign key constraint

A value in one relation must appear in another. The relation holding the foreign key is the referencing relation; the other is the referenced relation.

من الشرائح

dept_name in instructor is a foreign key from instructor referencing department.

Schema of the university databaseمخطط قاعدة الجامعة

Keys

Figure 2.9 (slide 8) and the schema diagram (slide 7). **Bold** = underlined on the slides (the primary key). The last column lists the arrows of slide 7 (foreign key → referenced relation).

Relation	Attributes	Arrows on slide 7
classroom	building, room_number, capacity	لا تذكر الشرائح شيئًا هنا.
department	dept_name, building, budget	لا تذكر الشرائح شيئًا هنا.
course	course_id, title, dept_name, credits	dept_name → department
instructor	ID, name, dept_name, salary	dept_name → department
section	course_id, sec_id, semester, year, building, room_number, time_slot_id	course_id → course building, room_no → classroom
teaches	ID, course_id, sec_id, semester, year	ID → instructor course_id, sec_id, semester, year → section
student	ID, name, dept_name, tot_cred	dept_name → department
takes	ID, course_id, sec_id, semester, year, grade	ID → student course_id, sec_id, semester, year → section
advisor	s_ID, i_ID	s_id → student i_id → instructor
time_slot	time_slot_id, day, start_time, end_time	لا تذكر الشرائح شيئًا هنا.
prereq	course_id, prereq_id	course_id → course prereq_id → course

Relational query languagesلغات الاستعلام

Query languages are procedural vs. non-procedural (declarative).
"Pure" languages: relational algebra, tuple relational calculus, domain relational calculus — equivalent in computing power.
Relational algebra is not Turing-machine equivalent.

The 6 basic operations of relational algebra (slide 9)

select
project
union
set difference
Cartesian product
rename

Notes (slide 23)

Each query input is a table (or set of tables); each query output is a table.
All data in the output table appears in one of the input tables.
Aggregate functions: SUM, AVG, MAX, MIN.

Select (σ): choosing rowsالاختيار σ

Select and project

Select (σ)

Selection of rows (tuples): returns rows of the input relation that satisfy the predicate.

Relation r (slide 10)

A	B	C	D
α	α	1	7
α	β	5	7
β	β	12	3
β	β	23	10

المسألة

σ A=B ∧ D > 5 (r)

Project (Π): choosing columnsالإسقاط Π

Select and project

Project (Π)

Selection of columns (attributes): outputs the specified attributes from all rows of the input relation and removes duplicate tuples from the output.

Relation r (slide 11)

A	B	C
α	10	1
α	20	1
β	30	1
β	40	2

المسألة

Π A,C (r)

Union, difference and intersectionالاتحاد والفرق والتقاطع

Union, set difference and intersection

Relation r (slides 12–14)

A	B
α	1
α	2
β	1

Relation s (slides 12–14)

A	B
α	2
β	3

Operationالعملية	Resultالناتج
r ∪ s (union)	(α, 1), (α, 2), (β, 1), (β, 3)
r − s (set difference)	(α, 1), (β, 1)
r ∩ s (intersection)	(α, 2)

Note (slide 14): r ∩ s = r − (r − s).

Cartesian product (×)الضرب الديكارتي

The Cartesian product r × s pairs every row of r with every row of s (slide 15).

Relation r (slide 15)

A	B
α	1
β	2

Relation s (slide 15)

C	D	E
α	10	a
β	10	a
β	20	b
γ	10	b

r × s (slide 15): 2 × 4 = 8 rows

A	B	C	D	E
α	1	α	10	a
α	1	β	10	a
α	1	β	20	b
α	1	γ	10	b
β	2	α	10	a
β	2	β	10	a
β	2	β	20	b
β	2	γ	10	b

Cartesian product: naming issueمشكلة التسمية

Cartesian product, rename and composition

If both relations have an attribute with the same name (here B), the result of r × s keeps both and names them r.B and s.B.

Relation r (slide 16)

A	B
α	1
β	2

Relation s (slide 16)

B	D	E
α	10	a
β	10	a
β	20	b
γ	10	b

r × s (slide 16)

A	r.B	s.B	D	E
α	1	α	10	a
α	1	β	10	a
α	1	β	20	b
α	1	γ	10	b
β	2	α	10	a
β	2	β	10	a
β	2	β	20	b
β	2	γ	10	b

Rename (ρ) and compositionإعادة التسمية والتركيب

Rename (ρ)

Allows us to refer to a relation (say E) by more than one name: ρ x (E) returns the expression E under the name X. Example (slide 17): r × ρ s (r) has the attributes r.A, r.B, s.A, s.B.

r × ρs(r) (slide 17), with r = (A, B): (α, 1), (β, 2)

r.A	r.B	s.A	s.B
α	1	α	1
α	1	β	2
β	2	α	1
β	2	β	2

المسألة

Composition (slide 18): σ A=C (r × s), with r and s from slide 15

Natural join (⋈)الربط الطبيعي

Natural join

For r on schema R and s on schema S, r ⋈ s is a relation on schema R ∪ S: for each pair of tuples tr from r and ts from s, if they have the same value on each of the attributes in R ∩ S, add a tuple t with tr's values on r and ts's values on s.

Relation r (slide 20)

A	B	C	D
α	1	α	a
β	2	γ	a
γ	4	β	b
α	1	γ	a
δ	2	β	b

Relation s (slide 20)

B	D	E
1	a	α
3	a	β
1	a	γ
2	b	δ
3	b	ε

المسألة

r ⋈ s = Π A, r.B, C, r.D, E (σ r.B = s.B ∧ r.D = s.D (r × s))

Worked example: courses and instructorsمثال المقررات

Natural join

Course (slide 21) — InstructorID is marked FK

Course#	Course_Name	Credit_Hours	InstructorID
320	Database	3	111
200	Introduction to IS	4	222
350	DSS	3	222

Instructor (slide 21) — InstructorID is marked PK

InstructorID	Instructor_Name	Hours_Load	Salary
111	Sara	12	9000
222	Reem	16	10000
333	Haya	14	12000

المسألة

Π Course#, Course_name (σ credit_hours < 4 (Course))

المسألة

Course ⋈ instructor

Summary of relational algebra operatorsملخص العمليات

Natural join

Summary of relational algebra operators (slide 24; × follows slide 15 — see the source note)

Symbol (name)الرمز (الاسم)	Example of useمثال	What it outputsالناتج
σ (Selection)	σ salary >= 85000 (instructor)	Rows of the input relation that satisfy the predicate
Π (Projection)	Π ID, salary (instructor)	Specified attributes from all rows duplicate tuples removed
× (Cartesian product)	instructor × department	Every pair of rows from the two input relations (slide 15)
∪ (Union)	Π name (instructor) ∪ Π name (student)	The union of tuples from the two input relations
− (Set difference)	Π name (instructor) − Π name (student)	The set difference of tuples from the two input relations
⋈ (Natural join)	instructor ⋈ department	Pairs of rows that have the same value on all attributes that have the same name

الخلاصة

أنهيت الدرس

الخطوة التالية: ثبّت المعلومات بالبطاقات، ثم اختبر نفسك بالأسئلة.

البطاقات التدريب

ما راجعته في هذا الدرس

Relations, attributes and tuplesالعلاقات والصفات والصفوف
Relation schema and instanceمخطط العلاقة ونسختها
Keysالمفاتيح
Relational query languagesلغات الاستعلام العلائقية
Select and projectالاختيار والإسقاط
Union, set difference and intersectionالاتحاد والفرق والتقاطع
Cartesian product, rename and compositionالضرب الديكارتي وإعادة التسمية وتركيب العمليات
Natural joinالربط الطبيعي

Intro to Relational Modelمقدمة في النموذج العلائقي

مقدمة الفصل

ماذا ستتعلم في هذا الفصل

أقسام الفصل

Attributes, tuples and domainsالصفات والصفوف

Relation schema and instanceالمخطط والنسخة

Superkeys, candidate keys and primary keysالمفاتيح

Foreign keysالمفتاح الأجنبي

Schema of the university databaseمخطط قاعدة الجامعة

Relational query languagesلغات الاستعلام

Select (σ): choosing rowsالاختيار σ

Project (Π): choosing columnsالإسقاط Π

Union, difference and intersectionالاتحاد والفرق والتقاطع

Cartesian product (×)الضرب الديكارتي

Cartesian product: naming issueمشكلة التسمية

Rename (ρ) and compositionإعادة التسمية والتركيب

Natural join (⋈)الربط الطبيعي

Worked example: courses and instructorsمثال المقررات

Summary of relational algebra operatorsملخص العمليات

الخلاصة

أنهيت الدرس

ما راجعته في هذا الدرس

خريطة الفصل

A	B	C	D	E
α	1	α	10	a
α	1	β	10	a
α	1	β	20	b
α	1	γ	10	b
β	2	α	10	a
β	2	β	10	a
β	2	β	20	b
β	2	γ	10	b

A	r.B	s.B	D	E
α	1	α	10	a
α	1	β	10	a
α	1	β	20	b
α	1	γ	10	b
β	2	α	10	a
β	2	β	10	a
β	2	β	20	b
β	2	γ	10	b

A	B	C	D	E
α	1	α	10	a
α	1	β	10	a
α	1	β	20	b
α	1	γ	10	b
β	2	α	10	a
β	2	β	10	a
β	2	β	20	b
β	2	γ	10	b

A	r.B	s.B	D	E
α	1	α	10	a
α	1	β	10	a
α	1	β	20	b
α	1	γ	10	b
β	2	α	10	a
β	2	β	10	a
β	2	β	20	b
β	2	γ	10	b

A	B	C	D	E
α	1	α	10	a
α	1	β	10	a
α	1	β	20	b
α	1	γ	10	b
β	2	α	10	a
β	2	β	10	a
β	2	β	20	b
β	2	γ	10	b

A	r.B	s.B	D	E
α	1	α	10	a
α	1	β	10	a
α	1	β	20	b
α	1	γ	10	b
β	2	α	10	a
β	2	β	10	a
β	2	β	20	b
β	2	γ	10	b