Create the database SBR and the following tables Sailors, Boats, and Reserves which are reproduced as follows:

Sailors(sid:VARCHAR (2) PK, sname:VARCHAR(7), rating: SMALLINT, age: DOUBLE

Boats(bid: VARCHAR (3) PK, bname: VARCHAR (10), colour: VARCHAR(6)

            Reserves(sid: VARCHAR(2) FK, bid: VARCHAR(3) FK, day: DATE)

sid

sname

rating

age

sid

bid

Day

bid

bname

colour

22	Dustin	7	45.0		22	101	10/10/98		101	Interlake	blue
29	Brutus	1	33.0		22	102	10/10/98		102	Interlake	red
31	Lubber	8	55.5		22	103	10/8/98		103	Clipper	green
32	Andy	8	25.5		22	104	10/7/98		104	Marine	red
58	Rusty	10	35.0		31	102	11/10/98
64	Horatio	7	35.0		31	103	11/6/98		Boats
71	Zorba	10	16.0		31	104	11/12/98
74	Horatio	9	35.0		64	101	9/5/98
85	Art	3	25.5		64	102	9/8/98
95	Bob	3	63.5		74	103	9/8/98
Sailors					Reserves

 

Using MySQL Workbench, construct the three tables for the schemas Sailors, Boats, and Reserves accordingly.  Enter the test data for the three tables as shown above, and formulate MySQL queries to answer the following questions.

Lecturers:\nUse the above tables to illustrate to students that a many-to-many relationship “A sailor can reserve many botas and a boat can be reserved by many sailors” can be implemented via “Reserves” bridge entity. Discuss- the need and requirements of a bridge entity and why it is not advisable to implement many- to- many relationships in database directly.

Question 1

Find all sailors with a rating above 7.

Question 2

Find the sailor ID and boat ID of those boats reserved on 10/10/98.

Question 3

Find the sailor ID and boat ID of those boats reserved from 10/10/98 to 1/12/98 inclusive.