Question

Part 1Write a narrative that describes what information you will store in a database. Be as descriptive as you can be.

Part 2Create an E-R diagram that corresponds to yournarrative. If you have fewer than three entities in your E-R diagram, revise your narrative!Remember that foreign key values should not be in your entities.Make sure that you include maximum and minimum cardinalities for your relationships.You can use a tool to create the E-R diagram or draw it by hand and photograph it.You can use any style of E-R diagram that you like.

Part 3Convert your E-R diagram into a set of tables. All tables should be 3NF.

Part 4Write an SQLscript to create this database. Your table definitions should contain primary key and foreign key constraints. There shouldbe at least one CHECK constraint.There should be at least one index defined.Your script should include enough INSERT commandsso thatat least one table has at least 20 records, and all tables have at least 3 records.

Part 5Create 10 queries for your database. Show both the query and the result of the query. Write an English language description of each query. Make sure that your tables contain enough records to show the result of your query. Your queries should include:

•2 examples of GROUP BY

•3 queries with INNERJOINs, one of which has at least three tables joined together

•1 query with a LEFT JOIN

•2nested queries

•1 example of a nested query that includes a NOT IN

•1 example of a query with an aggregate function

Answer 1

Part-1:

Sailor Database:

Sailor database has 3 tables:

1.Sailor:

   This table has all details related to Sailor i.e.,SailorID,FName,LName,....

2.Boat:

   This table has all details related to boats.i.e.,BoatID,Colour,....

3.Reserves table:

   This table contains details about registrations i.e.,Reservedate,SailorID,BoatID.

Part-2:

ER diagram:

Part-3:

Tables:

1.Sailor(SailorID,SName,Rating,Age);

2.Boat(BoatID,BName,Colour);

3.Reserves(SailorID,BoatID,Date);

Part-4:

SQL commands for creation of database and tables :

1.create table Sailor(SID varchar(10),SName char(20),Rating float,Age int, check(Age>=18) );

   alter table Sailor add Primary Key(SID);

2.create table Boat(BID varchar(10),BName char(30),Colour char(20));

   alter table Boat add Primary Key(BID);

3. create table Reserves(RBID varchar(10),RSID varchar(10),Date date);

alter table Reserves add Foreign key(RSID) references Sailor(SID);

   alter table Reserves add Foreign key(RBID) references Boat(BID);

* Tables created and keys were added...

mysql> Desc SAilor;
+--------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| SID    | varchar(10) | NO   | PRI | NULL    |       |
| SName | char(20)    | YES |     | NULL    |       |
| Rating | float       | YES |     | NULL    |       |
| Age    | int(11)     | YES |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.08 sec)

mysql> Desc Reserves;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| RSID | varchar(10) | YES | MUL | NULL    |       |
| RBID | varchar(10) | YES | MUL | NULL    |       |
| Date | date        | YES |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> Desc Boat;
+--------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| BID    | varchar(10) | NO   | PRI | NULL    |       |
| BName | char(30)    | YES |     | NULL    |       |
| Colour | char(20)    | YES |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

Inserting values into tables:

Sailor table:

mysql> insert into Sailor values('S160144','Praveena',4.5,18);
Query OK, 1 row affected (0.32 sec)

mysql> insert into Sailor values('S160507','Sravani',4.6,18);
Query OK, 1 row affected (0.14 sec)

mysql> insert into Sailor values('S160619','Uma Maheswari',4.7,19);
Query OK, 1 row affected (0.07 sec)

mysql> insert into Sailor values('S160879','Sai Durga',4.8,18);
Query OK, 1 row affected (0.08 sec)

mysql> insert into Sailor values('S160609','Anjali',4.9,19);
Query OK, 1 row affected (0.10 sec)

mysql> insert into Sailor values('S160646','Anusha',5,20);
Query OK, 1 row affected (0.14 sec)

mysql> insert into Sailor values('S160431','Kalyani',4.8,19);
Query OK, 1 row affected (0.06 sec)

mysql> insert into Sailor values('S160446','Kavya',5.0,19);
Query OK, 1 row affected (0.09 sec)

Boat table:

mysql> insert into Boat values('B2','K10','Blue');
Query OK, 1 row affected (0.06 sec)

mysql> insert into Boat values('B1','K9','Black');
Query OK, 1 row affected (0.10 sec)

mysql> insert into Boat values('B4','K8','Red');
Query OK, 1 row affected (0.07 sec)

mysql> insert into Boat values('B3','K6','Green');
Query OK, 1 row affected (0.11 sec)

Reserves table:

mysql> insert into Reserves values('S160646','B4','2008-09-19');
Query OK, 1 row affected (0.08 sec)

mysql> insert into Reserves values('S160619','B4','2009-09-19');
Query OK, 1 row affected (0.09 sec)

mysql> insert into Reserves values('S160609','B1','2019-09-19');
Query OK, 1 row affected (0.14 sec)

mysql> insert into Reserves values('S160446','B1','2018-09-19');
Query OK, 1 row affected (0.14 sec)

mysql> insert into Reserves values('S160431','B2','2012-09-19');
Query OK, 1 row affected (0.08 sec)

mysql> insert into Reserves values('S160144','B2','2013-09-19');
Query OK, 1 row affected (0.15 sec)

mysql> insert into Reserves values('S160507','B3','2011-09-19');
Query OK, 1 row affected (0.06 sec)

mysql> insert into Reserves values('S160879','B3','2010-09-19');
Query OK, 1 row affected (0.09 sec)

Content of tables:

1.Sailor table:

mysql> select * from Sailor;
+---------+---------------+--------+------+
| SID     | SName         | Rating | Age |
+---------+---------------+--------+------+
| S160144 | Praveena      |    4.5 |   18 |
| S160431 | Kalyani       |    4.8 |   19 |
| S160446 | Kavya         |      5 |   19 |
| S160507 | Sravani       |    4.6 |   18 |
| S160609 | Anjali        |    4.9 |   19 |
| S160619 | Uma Maheswari |    4.7 |   19 |
| S160646 | Anusha        |      5 |   20 |
| S160879 | Sai Durga     |    4.8 |   18 |
+---------+---------------+--------+------+
8 rows in set (0.03 sec)

2.Boat table:

mysql> select * from Boat;
+-----+-------+--------+
| BID | BName | Colour |
+-----+-------+--------+
| B1 | K9    | Black |
| B2 | K10   | Blue   |
| B3 | K6    | Green |
| B4 | K8    | Red    |
+-----+-------+--------+
4 rows in set (0.00 sec)

3.Reserves table:

mysql> select * from Reserves;
+---------+------+------------+
| RSID    | RBID | Day        |
+---------+------+------------+
| S160646 | B4   | 2008-09-19 |
| S160619 | B4   | 2009-09-19 |
| S160609 | B1   | 2019-09-19 |
| S160446 | B1   | 2018-09-19 |
| S160431 | B2   | 2012-09-19 |
| S160144 | B2   | 2013-09-19 |
| S160507 | B3   | 2011-09-19 |
| S160879 | B3   | 2010-09-19 |
+---------+------+------------+
8 rows in set (0.00 sec)

Part-5:

Queries:

Nested queries:

1..Retrieve the youngest sailor from sailor table.

mysql> select * from Sailor where Age=(Select min(Age) from Sailor);
+---------+-----------+--------+------+
| SID     | SName     | Rating | Age |
+---------+-----------+--------+------+
| S160144 | Praveena |    4.5 |   18 |
| S160370 | Kaushik   |      5 |   18 |
| S160507 | Sravani   |    4.6 |   18 |
| S160879 | Sai Durga |    4.8 |   18 |
+---------+-----------+--------+------+
4 rows in set (0.00 sec)

2.Find the sailor ids of sailors whose rating is better than some sailor called Uma Maheswari.

mysql> select * from Sailor where rating>(select rating from Sailor where SName='Uma Maheswari');
+---------+-----------+--------+------+
| SID     | SName     | Rating | Age |
+---------+-----------+--------+------+
| S160370 | Kaushik   |      5 |   18 |
| S160431 | Kalyani   |    4.8 |   19 |
| S160446 | Kavya     |      5 |   19 |
| S160609 | Anjali    |    4.9 |   19 |
| S160646 | Anusha    |      5 |   20 |
| S160879 | Sai Durga |    4.8 |   18 |
+---------+-----------+--------+------+
6 rows in set (0.00 sec)

Joins :

3.Find the colors of boats reserved by Praveena.

mysql> select S.*,Colour from Sailor S,Reserves,Boat where SName='Praveena' and SID=RSID and RBID=BID;
+---------+----------+--------+------+--------+
| SID     | SName    | Rating | Age | Colour |
+---------+----------+--------+------+--------+
| S160144 | Praveena |    4.5 |   18 | Blue   |
| S160144 | Praveena |    4.5 |   18 | Black |
| S160144 | Praveena |    4.5 |   18 | Green |
| S160144 | Praveena |    4.5 |   18 | Red    |
| S160145 | Praveena |    4.7 |   19 | Red    |
+---------+----------+--------+------+--------+
5 rows in set (0.00 sec)

4.find the names of sailors who have reserved atleast two boats.

mysql> select S.* from Sailor S,Reserves where RSID=SID group by RSID having count(RBID)>=2;
+---------+---------------+--------+------+
| SID     | SName         | Rating | Age |
+---------+---------------+--------+------+
| S160144 | Praveena      |    4.5 |   18 |
| S160619 | Uma Maheswari |    4.7 |   19 |
+---------+---------------+--------+------+
2 rows in set (0.00 sec)

5.find the names of sailors who have reserved all boats.

mysql> select S.* from Sailor S,Reserves where RSID=SID group by RSID having count(distinct RBID)=(Select count(BID) from Boat);
+---------+----------+--------+------+
| SID     | SName    | Rating | Age |
+---------+----------+--------+------+
| S160144 | Praveena |    4.5 |   18 |
+---------+----------+--------+------+
1 row in set (0.00 sec)

Group by:

6.for each Blue boat find the number of reservations for that boat.

mysql> select BID,BName,colour,Count(RSID) as Number_Of_Reservations from Reserves,Boat where BID=RBID and colour='Blue' group by RBID;
+-----+-------+--------+------------------------+
| BID | BName | colour | Number_Of_Reservations |
+-----+-------+--------+------------------------+
| B2 | K10   | Blue   |                      2 |
+-----+-------+--------+------------------------+
1 row in set (0.00 sec)

7.for each rating find the average age of sailors at that level of rating.

mysql> select Rating,Avg(Age) as Average_Age from Sailor group by rating order by rating Asc;
+--------+-------------+
| Rating | Average_Age |
+--------+-------------+
|    4.5 |     18.0000 |
|    4.6 |     18.0000 |
|    4.7 |     19.0000 |
|    4.8 |     18.5000 |
|    4.9 |     19.0000 |
|      5 |     19.0000 |
+--------+-------------+
6 rows in set (0.00 sec)

Aggregate functions:

8.find the average age of sailors for each rating level that has atleast two sailors.

mysql> select Rating,avg(Age) as Average_Age from Sailor group by rating having count(SID)>=2;
+--------+-------------+
| Rating | Average_Age |
+--------+-------------+
|    4.7 |     19.0000 |
|      5 |     19.0000 |
|    4.8 |     18.5000 |
+--------+-------------+
3 rows in set (0.00 sec)

Normal queries:

9.find the name and age of sailors whose names starts with “S” and ends with “i” and has atleast three characters.

mysql> select * from Sailor where SName like "s%i" and length(Sname)>=3;
+---------+---------+--------+------+
| SID     | SName   | Rating | Age |
+---------+---------+--------+------+
| S160507 | Sravani |    4.6 |   18 |
+---------+---------+--------+------+
1 row in set (0.00 sec)

10.retrieve the names of 4 sailors who have highest rating.

mysql> select * from Sailor order by rating DESC limit 4;
+---------+---------+--------+------+
| SID     | SName   | Rating | Age |
+---------+---------+--------+------+
| S160646 | Anusha |      5 |   20 |
| S160370 | Kaushik |      5 |   18 |
| S160446 | Kavya   |      5 |   19 |
| S160609 | Anjali |    4.9 |   19 |
+---------+---------+--------+------+
4 rows in set (0.00 sec)