Name: Tanishka Kumthekar

[Link].:U24CS001
B. Tech. II CSE Sem-3

Database Management Systems (DBMS) (CS205)

Lab Assignment 6

In reference to the Bus database given below, attempt the following questions:
The queries to create the tables and the data to be inserted are listed below. Run
these queries against the database to have your tables and data ready.
Some notes on terms used:
+ table “bushalts” contains one row for every halt of a bus.
-- id : id of the bus which is the primary key.
-- seqno : The halt number. Assume that the starting station has seqno as 0.
-- stcode : station code of this halt.
-- timein : time at which the bus arrives at this station. (will be null for the starting station of a
bus) -- timeout: time at which the bus departs this station. (will be null for the terminating
station of a bus)
-- If a bus passes through a station without stopping, then there will be an entry with
timein = timeout.
+ table “track” stores the distances between directly connected stations stcode1 and stcode2.
-- Assume that this represents a directed track. i.e., for two stations A and B, there will be an
entry corresponding to (A, B, distance) and another for (B, A, distance).
Script to be run to set up the tables and insert the respective data with the following

constraints:

- The stcode of table bushalts is the code which is available in the station table
only. - The track table should have distance value greater than 0.
- The seqno of the table bushalts should be automatically generated using the “SEQUENCE”.
insert into bushalts values ('KP11' , 0 , 'ST' , NULL,
'20.23');
insert into bushalts values ('KP11' , 1 , 'BYC' ,
'20.31', '20.32');
insert into bushalts values ('KP11' , 2 ,
'DR' , '20.41', '20.42');
insert into bus values ('KP11' ,'ST-KYN');
insert into bus values ('KP11L' ,'ST-KYN_LOCAL');
insert into bus values ('T129' ,'ST-TNA_LOCAL');
insert into bus values ('A63' ,'ST-DL_LOCAL');
insert into bus values ('K101' ,'ST-KYN_LOCAL');
insert into bus values ('N27' ,'ST-TNA_LOCAL');
insert into bus values ('S33' ,'ST-KGR_LOCAL');
insert into bus values ('A65' ,'ST-AMR_LOCAL');
insert into station values ('ST' ,'MUMBAI');
insert into station values ('BYC' ,'BYCULLA');
insert into station values ('DR' ,'DADAR');
insert into station values ('KRL' ,'KURLA');
insert into station values ('GPR' ,'GHATKOPAR');
insert into station values ('TNA' ,'THANE');
insert into station values ('DL' ,'DOMBIVALI');
insert into station values ('AMR' , 'AMBARNATH');
insert into station values ('KYN' ,'KALYAN');
insert into station values ('KSR' ,'KASARA');
insert into track values ('ST' ,'BYC', 5);

insert into track values ('ST' ,'DR', 9);

insert into track values ('ST' ,'KRL', 16);
insert into track values ('ST' ,'GPR', 20);
insert into track values ('ST' ,'TNA', 34);
insert into track values ('ST' ,'DL', 49);
insert into track values ('ST' ,'KYN', 54);
insert into track values ('ST' ,'KSR', 77);
insert into track values ('ST' ,'AMR', 65);
insert into track values ('BYC' ,'DR', 4);
insert into track values ('BYC' ,'KRL', 11);
insert into track values ('GRP' ,'TNA', 14);
insert into track values ('DR' ,'TNA', 25);
insert into track values ('KRL' ,'KYN', 38);
insert into track values ('TNA' ,'KYN', 20);
insert into track values ('TNA' ,'KSR', 43);

insert into bushalts values ('KP11' ,

3 , 'GPR' , '20.52', '20.53');
insert into bushalts values
('KP11' , 4 , 'GPR' , '20.52', '20.53');
insert into bushalts
values ('KP11' , 5 , 'DR' , '20.41', '20.42');
insert into
bushalts values ('KP11' , 6 , 'GPR' , '20.58', '20.59');
insert

into bushalts values ('KP11' , 7 , 'TNA' , '21.21', '21.22');

insert into bushalts values ('KP11' , 8 , 'DL' , '21.45', '21.46');
insert into bushalts values ('KP11' , 9 , 'KYN' , '21.54', NULL);
insert into bushalts values ('A65' , 0 , 'ST' , NULL , '20.52');
insert into bushalts values ('A65' , 1 , 'BYC' , '21.00' , '21.01');
insert into bushalts values ('A65' , 2 , 'DR' , '21.10' , '21.11');
insert into bushalts values ('A65' , 3 , 'KRL' , '21.22' , '21.23');
insert into bushalts values ('A65' , 4 , 'GPR' , '21.28' , '21.29');
insert into bushalts values ('A65' , 5 , 'TNA' , '21.49' , '21.50');
insert into bushalts values ('A65' , 6 , 'DL' , '22.13' , '22.14');
insert into bushalts values ('A65' , 7 , 'KYN' , '22.22' , '22.23');
insert into bushalts values ('A65' , 8 , 'AMR' , '22.36' , NULL);

After the database tables and data are set; write the following queries:
1. Display all the pairs of stations with total distance for given source and destinations.
SELECT [Link] as station1, [Link] as station2, [Link]
FROM track t
JOIN station s1 on t.stcode1=[Link]
JOIN station s2 on t.stcode2=[Link];

station1 station2 distance

BYCULLA DADAR 4

BYCULLA KURLA 11

DADAR THANE 25
 KURLA KALYAN 38

MUMBAI AMBARNATH 65

MUMBAI BYCULLA 5

MUMBAI DOMBIVALI 49

MUMBAI DADAR 9

MUMBAI GHATKOPAR 20

MUMBAI KURLA 16

MUMBAI KASARA 77

MUMBAI KALYAN 54

MUMBAI THANE 34

THANE KASARA 43

THANE KALYAN 20

[Link] the pairs of stations (station codes) which have a track with distance more than 15km
between them.

SELECT [Link] as station1, [Link] as station2, [Link]

FROM track t
JOIN station s1 on t.stcode1=[Link]
JOIN station s2 on t.stcode2=[Link]
WHERE distance>15;
 station1 station2 distance

DADAR THANE 25

KURLA KALYAN 38

MUMBAI AMBARNATH 65

MUMBAI DOMBIVALI 49

MUMBAI GHATKOPAR 20

MUMBAI KURLA 16

MUMBAI KASARA 77

MUMBAI KALYAN 54

MUMBAI THANE 34

THANE KASARA 43

THANE KALYAN 20

3. Find the IDs of all the buses which have a stop at DADAR.
SELECT DISTINCT [Link]
FROM bus b
JOIN bushalts h ON [Link] = [Link]
JOIN station s ON [Link] = [Link]
WHERE [Link] = 'DADAR';

4. Find the ordered list of names of all buses that start at THANE.
SELECT [Link]
 FROM bus b
JOIN bushalts h ON [Link] = [Link]
JOIN station s ON [Link] = [Link]
WHERE [Link] = 0 AND [Link] = 'THANE'
ORDER BY [Link];

→empty set

5. List all the stations in order of visit by the bus 'ST-DL_LOCAL'.

mysql> SELECT [Link], [Link] AS station_name

-> FROM bushalts h
-> JOIN station s ON [Link] = [Link]
-> JOIN bus b ON [Link] = [Link]
-> WHERE [Link] = 'ST-DL_LOCAL'
-> ORDER BY [Link];
Empty set (0.00 sec)
SELECT DISTINCT [Link]
-> FROM bus b
-> JOIN bushalts h ON [Link] = [Link]
-> JOIN station s ON [Link] = [Link]
-> WHERE [Link] = 'KALYAN' AND [Link] < 3;
Empty set (0.00 sec)

6. Find the name of the buses which stop at KALYAN, before the 3rd stop in the route of the
Bus.
SELECT DISTINCT [Link]
-> FROM bus b
-> JOIN bushalts h ON [Link] = [Link]
-> JOIN station s ON [Link] = [Link]
-> WHERE [Link] = 'KALYAN' AND [Link] < 3;
Empty set (0.00 sec)

7. Display the pair of stations (i.e. station names) having maximum distance between them.
SELECT [Link] AS station1, [Link] AS station2, [Link]
FROM track t
JOIN station s1 ON t.stcode1 = [Link]
JOIN station s2 ON t.stcode2 = [Link]
WHERE [Link] = (SELECT MAX(distance) FROM track);
[Link] id of the bushalt having the highest time out.
SELECT [Link], [Link], [Link]
FROM bushalts h
WHERE [Link] IS NOT NULL
ORDER BY STR_TO_DATE(REPLACE([Link],'.',':'), '%H:%i') DESC
LIMIT 1;

9. Remove Track “ST” from the track table. Note: If any track is removed from the track
table, then that track related information also should be removed from the other tables.
-- Delete all rows from track that have 'ST'
DELETE t
FROM track t
JOIN station s ON t.stcode1 = [Link] OR t.stcode2 = [Link]
WHERE [Link] = 'ST';

10. Remove Track “KP11” from the bus table. If any bus is removed from the bus table that
track related information also should be removed from the other tables.
> DELETE b
-> FROM bus b
-> WHERE [Link] = 'KP11';
Query OK, 1 row affected (0.02 sec)
11. Delete the train "KP11" and all halt stations of that train.
-- Bushalts will auto-delete because of ON DELETE CASCADE
mysql> DELETE b
-> FROM bus b
-> WHERE [Link] = 'KP11';
Query OK, 0 rows affected (0.00 sec)