use Schema43;

LOAD DATA local infile '/Users/mac/Desktop/publisher.txt' INTO TABLE publisher FIELDS TERMINATED BY ','  LINES STARTING BY 'xxx';
LOAD DATA local infile '/Users/mac/Desktop/borrower.txt' INTO TABLE borrower FIELDS TERMINATED BY ','  LINES STARTING BY 'xxx';
LOAD DATA local infile '/Users/mac/Desktop/LIBRARY_BRANCH.txt' INTO TABLE library_branch FIELDS TERMINATED BY ','  LINES STARTING BY 'xxx';
LOAD DATA local infile '/Users/mac/Desktop/BOOK.txt' INTO TABLE book FIELDS TERMINATED BY ','  LINES STARTING BY 'xxx';
LOAD DATA local infile '/Users/mac/Desktop/BOOK_AUTHORS.txt' INTO TABLE bookAuthor FIELDS TERMINATED BY ','  LINES STARTING BY 'xxx';
LOAD DATA local infile '/Users/mac/Desktop/BOOK_COPIES.txt' INTO TABLE book_copies FIELDS TERMINATED BY ','  LINES STARTING BY 'xxx';
LOAD DATA local infile '/Users/mac/Desktop/BOOK_LOANS.txt' INTO TABLE bookloans FIELDS TERMINATED BY ','  LINES STARTING BY 'xxx';

SELECT  table_schema as `Schema43`,
     table_name AS `publisher`,
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
WHERE (TABLE_SCHEMA = "Schema43")
ORDER BY (data_length + index_length) DESC;


optimize table publisher;
optimize table borrower;
optimize table library_branch;
optimize table book;
optimize table bookAuthor;
optimize table book_copies;
optimize table bookloans;

SELECT  table_schema as `Schema43`,
     table_name AS `publisher`,
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
WHERE (TABLE_SCHEMA = "Schema43")
ORDER BY (data_length + index_length) DESC;

grant all privileges on *.* to 'HR'@'%%' identified by 'engineerOMAR2019';