Organization database. Management systems

1.

Organization Database
Management Systems
Practic and Lab_w03&w04
02/2024
__________________________________________
Mohammed A. Saleh

2.

VIEW
• CREATE VIEW B_Students (name, sid, course)
AS SELECT S.name, S.sid
FROM Students
• CREATE VIEW B_Students2 (name, sid, course)
AS SELECT S.name, S.sid, E.cid
FROM Students S, Enrolled E
WHERE S.sid = E.studid AND E.grade = 'B';
• DROP view B_Students2;

3.

Indexing
• create index inddx on students(sid);
• drop index inddx on students;
• SELECT did, gpa, sum(gpa) from students group by did;
• SELECT did, gpa, AVG(gpa) from students group by did;

4.

Union
• select * from students union select * from students2;
• select sid, name from students union select sid, name from
students2;

5.

Intersection and Subtraction(Set‐di erence)
• We can't use the INTERSECT AND SUBTRACT operator in MySQL but
we can use IN/NOT IN instead.
• Example 1(intersect)
SELECT *
FROM students
WHERE (sid, name, login, age, gpa, did)
IN (SELECT * FROM students2);
• Example 2(subtract/-)
FROM students
WHERE (sid, name, login, age, gpa, did)
NOT IN (SELECT * FROM students2);

6.

Projection
• Projection means choosing which columns (or expressions) the query
shall return.
• Selection means which rows are to be returned.
• Examples:
1. SELECT sid
FROM students;
2. SELECT sid
FROM students
WHERE sid > 53700;

7.

Cross-product / cross join
SELECT * FROM t1
CROSS JOIN t2
WHERE t1.id = t2.id;

8.

Division
S1
R1
S2
sid
sname
rating
age
sid
sname
rating
age
sid
bid
day
22
Dustin
7
45.0
28
yuppy
9
35.0
22
101
10/10/96
31
Lubber
8
55.5
31
Lubber
8
55.5
58
103
11/12/96
58
Rusty
10
35.0
44
guppy
5
35.0
58
Rusty
10
35.0
• Find the sailors’ names who reserved boat with
bid=101
• πsname (σ
(S.sid = R1.sid) ⋂ (R1.bid=101)
((S1 ⋃ S2)×R1))

9.

How the Tables Look Like Step by Step
1
3
4
(S1 ⋃ S2)
2
(S1 ⋃ S2) × R1
sid
sname
rating
age
(sid)
sname
rating
age
(sid)
bid
day
22
Dustin
7
45.0
22
Dustin
7
45.0
22
101
10/10/96
31
Lubber
8
55.5
22
Dustin
7
45.0
58
103
11/12/96
58
Rusty
10
35.0
31
Lubber
8
55.5
22
101
10/10/96
28
yuppy
9
35.0
31
Lubber
8
55.5
58
103
11/12/96
44
guppy
5
35.0
58
Rusty
10
35.0
22
101
10/10/96
58
Rusty
10
35.0
58
103
11/12/96
28
yuppy
9
35.0
22
101
10/10/96
σS.sid=R1.sid((S1 ⋃ S2) × R1)
sid
sname
rating
age
sid
bid
day
28
yuppy
9
35.0
58
103
11/12/96
22
Dustin
7
45.0
22
101
10/10/96
44
guppy
5
35.0
22
101
10/10/96
58
Rusty
10
35.0
58
103
11/12/96
44
guppy
5
35.0
58
103
11/12/96
πsname (σ(S.sid = R1.sid) ⋂ (R1.bid=101) ((S1 ⋃ S2)×R1))
sname
Dustin
A×B=C
Cardinality(C) = Cardinality(A) × Cardinality(B)
Degree(C) = Degree(A) + Degree(B)

10.

Division
• Step 1: S1 U S2 = S:
create table s select * from s1 union select * from s2;
• Step 2 and 3: S × R1 = T1, when S.sid=R1.sid
CREATE table T1 SELECT sid, sname, rating, age, bid, day FROM S
CROSS JOIN r1 WHERE s.sid = r1.sid1;
• Step 4 : select the name who reserves boat no. 101
select sname from t1 where bid =101;