/* CREATE THE DESIRED SCHEMA */


//In any system open the terminal and issue the command
>> mysql -p -u root [In Windows machine you should be inside the directory you extracted MySQL]

//Bellow we show some of the ddl commands and their usage...

>>show databases; [Prints all the existing databases in your system]
>>create database mydb; [Creating a new database 'mydb']
>>drop database mydb; [Delete the already existing database 'mydb']
>>use mydb; [Making database mydb, the default database into which our command will be issued]


//Command to create a table, enforcing some constraints with CHECK functions
//except the obvious ones imposed during the definition of each column. 
//ENGINE INNODB enforces constraints over attributes that act as foreign keys.
create table Sailors (sid int(50) NOT NULL AUTO_INCREMENT PRIMARY KEY,
sname varchar(100) NOT NULL, rating INT NOT NULL, age INT NOT NULL,
CHECK(rating >= 1 AND rating <= 5), CHECK(age >= 18)) ENGINE INNODB;

//Unfortunately MySQL although it compiles CHECK statements it does not enforces them
//In order to enforce them we are going to use triggers
>>delimiter |
CREATE TRIGGER sailorsIns BEFORE INSERT ON Sailors
FOR EACH ROW BEGIN
DECLARE msg varchar(255);
IF NEW.rating < 1 OR NEW.rating > 5 OR NEW.age < 18 THEN
SET msg = 'Constraints violated!';
SIGNAL sqlstate '45000' set message_text = msg;
END IF;
END 
|
>>delimiter ;

>>delimiter |
CREATE TRIGGER sailorsUpd BEFORE UPDATE ON Sailors
FOR EACH ROW BEGIN
DECLARE msg varchar(255);
IF NEW.rating < 1 OR NEW.rating > 5 OR NEW.age < 18 THEN
SET msg = 'Constraints violated!';
SIGNAL sqlstate '45001' set message_text = msg;
END IF;
END
|
>>delimiter ;

//Create a new table "Boats"
CREATE TABLE Boats (bid int(50) NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(100) NOT NULL, color varchar(100)) ENGINE = INNODB;

//Add also a constraint in the newly created table
//In other database systems this constraint would be enforced
ALTER TABLE Boats ADD CONSTRAINT color_chk CHECK(color IN ('red','blue','white','brown'));

CREATE TABLE Reservations (sid int(50) NOT NULL, bid int(50) NOT NULL,
date DATE NOT NULL, FOREIGN KEY(sid) REFERENCES Sailors(sid), FOREIGN KEY(bid)
REFERENCES Boats(bid), PRIMARY KEY(sid,bid,date)) ENGINE = INNODB;

//Display some commands to modify tables...
ALTER TABLE Sailors ADD lastName varchar(100) NOT NULL after sname;
ALTER TABLE Sailors CHANGE sname firstName varchar(100);
//Display the columns of table 'Sailors'
SHOW COLUMNS from Sailors;
//Condinue with ALTER TABLE commands
// The following one just changes the type and the properties of the column 'firstName'
ALTER TABLE Sailors GHANGE firstName varchar(100) NOT NULL;
//Delete columns
ALTER TABLE Sailors DROP COLUMN fistName;
ALTER TABLE Sailors DROP COLUMN lastName;
//Add column
ALTER TABLE Sailors ADD sname varchar(100) NOT NULL after sid;

/* POPULATE TABLES BELONGING IN OUR DESIRED SCHEMA */

//Try to insert two tuples in our table 'Sailors'
INSERT INTO Sailors (sname,rating,age) VALUES (‘Mike’,’10’,’23’);
INSERT INTO Sailors (sname,rating,age) VALUES(‘Cicely’,’5’,’10’);
//Both commands fail due to the constrained issued by the trigger statements...

//Example of a successfull insertion of a tuple
INSERT INTO Sailors (sname, rating, age) VALUES (‘Cicely’,’5’,’22’);


//Let’s update Cicely’s age:
UPDATE Sailors SET age=’17’ WHERE sname=’Cicely’; 
//The above command fails due to violation of constraints...

//This update command executes successfully
UPDATE Sailors SET age=’18’ WHERE sname=’Cicely’;

//Insert some more tuples in our schema
INSERT INTO Boats (name,color) VALUES (“Maria”,”red);
INSERT INTO Boats (name) VALUES (“Christie”); // Here color has NULL value...
INSERT INTO Reservations VALUES(‘3’,’1’,’2013-06-17’);
INSERT INTO Reservations VALUES(‘4’,’1’,’2013-06-17’); 
//The above command gives us an error: 
//    We are violating the constraint imposed by foreign key [There is no sailor with sid = 4] 
//    {Exception caused by INNODB engine}

//The followin command fails because the primary composite key should be unique in table 'Reservations'
INSERT INTO Reservations VALUES(‘3’,’1’,’2013-06-17’); 

//The following command fails because of the fact that there is a tuple related to this one in table 
//Reservations [constraint imposed by foreign key and enforced by InnoDB engine]
DELETE FROM Sailor WHERE sname=’Cicely’; 

//So before removing any tuple in tables 'Sailors' or 'Boats' we should be sure that we
//have erased their associated entries in table 'Reservations'

//Now we clear table 'Reservations'
DELETE FROM Reservations WHERE 1;
//Do the same for the other tables as well
DELETE FROM Sailors WHERE 1;
DELETE FROM Boats WHERE 1;


/* POPULATING EFFICIENTLY OUR TABLES USING BATCH INSERTIONS */
//exit from command prompt of MySQL... 
>>quit

//Import data from Sailors.txt file to Sailors table.
>>mysqlimport -u root --local mydb Sailors.txt  
// In the above command ’mydb’ is the name of database, while the txt file should have the exact same name as the 
// table in which we want to insert the data. The txt file should have values separated with tabs! This command 
// works fine if you do not have any password for the root user... Otherwise you should consider 
// using: mysqlimport -u root -ptmpassword --local [dbname] [filename].txt 

//Do the exact same thing for Boats relation
>>mysqlimport -u root --local mydb Boats.txt

//mysqlimport command won't work for relations that have attributes defined as foreign keys [in our case: Reservations]

//We resolve this issue by connecting to our MySQL database via command line and typing the following command:
>>LOAD DATA INFILE ‘Reservations.txt’ INTO TABLE Reservations; 
//Notice that here the default path is in /var/lib/mysql/mydb/Reservations.txt and root user in our system 
//should have full access to the file!