### Built With
The Project uses the below Tech Stack
Clone The Above Repo in your Local System.
- Go to SQL client Terminal
- mysql -u [username] -p
- src [exact-path-of-dump-file]
Name of dump file - DB_A8.sql
Run the Following command in the terminal given that node is installed in the system
- npm install
- cd cricket
- npm install
- cd ..
- cd Backend
- npm install
- In Cricket Folder run npm start
- In Backend Folder run nodemon
Q1
CREATE TABLE person_hash_index AS SELECT * FROM person;
create index player_index using hash on person_hash_index(first_name(10));
set profiling =1;
(SELECT * FROM person WHERE first_name like 'Y%'
UNION
SELECT * FROM person WHERE last_name like 'S%');
-- optimized query
(SELECT * FROM person_hash_index WHERE first_name like 'Y%'
UNION
SELECT * FROM person_hash_index WHERE last_name like 'S%');
show profiles;
explain (SELECT * FROM person_hash_index WHERE first_name like 'Y%'
UNION
SELECT * FROM person_hash_index WHERE last_name like 'S%');
Q2
set profiling =1;
SELECT * FROM person WHERE first_name like 'R%';
-- optimized query
SELECT * FROM person_hash_index WHERE first_name like 'R%';
show profiles;
explain SELECT * FROM person_hash_index WHERE first_name like 'R%';
Q3
CREATE TABLE player_id_hash_index AS SELECT * FROM person;
create unique index player_id_index using hash on player_id_hash_index(id);
ALTER TABLE player_id_hash_index
modify id tinyint;
set profiling =1;
select * from person where id = 1;
-- optimized query
select * from player_id_hash_index where id =1;
show profiles;
explain select * from player_id_hash_index where id =1;
Q4
alter table match_info
add date_on_match_played date;
update match_info set date_on_match_played = '2003-05-20' where match_id =1001;
update match_info set date_on_match_played = '2008-01-17' where match_id =1002;
update match_info set date_on_match_played = '2011-10-04' where match_id =1003;
update match_info set date_on_match_played = '2013-06-26' where match_id =1004;
update match_info set date_on_match_played = '2014-11-30' where match_id =1005;
CREATE TABLE match_info_btree_index AS SELECT * FROM match_info;
create unique index date_index using btree on
match_info_btree_index(date_on_match_played);
set profiling =1;
(select * from match_info where date_on_match_played > '2013-11-30'
union
select * from match_info where date_on_match_played < '2004-11-30');
-- optimized query
(select * from match_info_btree_index where date_on_match_played > '2013-11-30'
union
select * from match_info_btree_index where date_on_match_played < '2004-11-30');
show profiles;
explain (select * from match_info_btree_index where date_on_match_played > '2013-11-30'
union
select * from match_info_btree_index where date_on_match_played < '2004-11-30');
Q5
CREATE TABLE match_duplicate AS SELECT * FROM match_info;
select * from match_duplicate;
Update match_duplicate
Set india_total_runs = NULL where match_id = 1003 ;
Select Count(*) from match_duplicate where india_total_runs = true;
Q6
The Caching has been removed in 8.0 version so the command like
SHOW VARIABLES LIKE 'query_cache_size';
Does not return anything except the null value
and query SHOW VARIABLES LIKE
'have_query_cache'; 
Returns the NO
Although the performance is increased in the Caching value but it has a scalability issue. It cannot scale with high throughput workloads on multicore machines. The scalability may be improved but with caching the performance will be increased for only those queries which hit the cache. While the rest remains the same performance. So it is difficult to improve the predictability of performance.
Q7
-- index already created in Q4
set profiling = 1;
select _ from match_info natural join venue_info where date_on_match_played = '2008-01-17';
-- optimized query
select _ from match*info_btree_index natural join venue_info where date_on_match_played = '2008-01-17';
show profiles;
explain select * from match*info_btree_index natural join venue_info where
date_on_match_played = '2008-01-17';
The query runs faster in join as compared to subquery. The sql have main property of join removing it will make it similar to excel sheet. Joining the table helps in using the defined relation. In 2NF or higher normalization, the table is been divided into parts to avoid data redundancy and optimize the process. Since the data will be less in 2NF or more so search time will reduce.
Disadvantages
With Join the time complexity increases and the spaces for the intermediate table are created. In multiple joins the complexity increases which means for maintaining the table proper care is required for foreign key changing and will have more constraints. Reduces the readability of query
-- Q8
CREATE TABLE person_id_hash_index AS SELECT * FROM person;
create unique index personid_index using hash on person_id_hash_index(id);
CREATE TABLE player_match_info_hash_index AS SELECT * FROM playermatch_info;
create index player_match_info_index using hash on
player_match_info_hash_index(match_id);
CREATE TABLE stadium_name_hash_index AS SELECT * FROM venueinfo;
create unique index stadium_name_index using hash on
stadium_name_hash_index(stadium_name(20));
set profiling =1;
select * from person where id in (select id from playermatch_info where
match_id = (select match_id from venue_info where stadium_name = 'Wankhede Stadium' ));
-- optimized query
select * from person_id_hash_index where id in (select id from
player_match_info_hash_index where
match_id = (select match_id from stadium_name_hash_index where stadium_name = 'Wankhede Stadium' ));
show profiles;
explain select * from person_id_hash_index where id in (select id from
player_match_info_hash_index where
match_id = (select match_id from stadium_name_hash_index where stadium_name = 'Wankhede Stadium' ));
| Name | Roll No | Contribution % |
|---|---|---|
| Aman | 18110014 | 16.6 |
| Bhanu Pratap Singh | 18110034 | 16.6 |
| Lovepreet Singh | 18110094 | 16.6 |
| Mrityunjay Saraf | 18110103 | 16.6 |
| Sumit Kumar | 18110167 | 16.6 |
| Vaibhav Dilip Khandare | 18110180 | 16.6 |