Using the following relational schema: Sailors (S_ID, S_name, S_rating, S_Age) B

Question

Using the following relational schema:

Sailors (S_ID, S_name, S_rating, S_Age)
Boats (B_ID, B_name, B_color)
Reservations (S_ID, B_ID, R_date)

.Login in to the Linux Oracle server and access the database using sqlplus (refer to Oracle Linux Server Instructions) .Display the SQL commands using: SET ECHO ON Create your homework submission log file using: SPOOL 4-FirstLastName-Lab.txt To turn off explain plan for queries: SET AUTOTRACE OFF (if you want to observe Using the sailors schema that you have already created answer the questions that follow: To terminate log file use: SPOOL OFF command what the resulting tuple set is). Part A: Query Optimization To see execution plans for queries use: SET AUTOTRACE TRACEONLY EXPLAIN 1. Run the following two queries: Query 1 select s.sname, b.bname from sailors s, reserves r, boats b Query 2 select s.sname, b.bname from sailors s, reserves r, boats b where s.sid - r.sid and r.bid - b.bid and b.color -red' (10 points) Looking at the explain plan for each query, describe what you see which query is better, and why? Run the following two queries: Query 1: select s.sname from sailors s, reserves r, reserves 2 where s.sid rl.sid and s.sid-r2.sid and rl.bid r2.bid; Query 2: select distinct s.sname from sailors s, reserves ri, reserves r2 where s.sidrl.sid and s.sid r2.sid and rl.bid r2.bid; (10 points) Looking at the explain plan for each query, describe what you see, which query is better and why?

Explanation / Answer

Please find below answers:

1. Query 1: Whenever Select statement names multiple tables in the FROM Clause, it performs the full JOIN. so from this query you will get A x B x C number of rows.

Lets say sailor table has 5 rows(A)

reserves table has 6 rows(B)

and boat table has 7 rows(C)

So total number of records you will get is 5 x 6 x 7 = 210 rows in result. Using this query you will get multiple same combination for s.sname and b.bname in resultset.

Using this query you wont be able to identify if sailor has reseved that boat or not.

Query 2: In this query we are optimizing the above results by applying conditions in where clause such as applying comparison on same column of different table.

by running this query you will get results of only rows where sid of sailor table matches with the sid of reserves tabl and bid of boats table matches with that of reserves table and boat color is red.

so in short you will get name of sailors and name of his reserved red boat.

2. Query 1: this query performs self join on reserves table where boat id is not same then it shows the sailor's name number of times he has unique reservations against every boat and have more than one reservation.

LEts say sailor 1 has 4 reservations for boat as below

rid 1, bid1

rid2, bid 2

rid3, bid 2,

rid4, bid3,

Then sailor 1 has 3 unique reservations. so you will sailor 1 name three times in resultset.

Query 2: in above query, we are getting sailors name multiple times , so in order to solve this problem we have used distinct on sailor name which will display sailors name only once. so in short you will get names of sailors who have more than one reservation.

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.