CIND110 – Lab 04
CIND110 – Data Organization for Data Analysts Lab 04 – More SQL and Schema Modification
1 Running the MySQL Service 2
2 The Working Database Logical Model 3
3 Comparisons Involving Three-valued Logic 4
4 Nested Queries
5 Applying Boolean Functions
6 Renaming Columns in the Results
7 Joining Tables
8 Aggregate Functions
9 Triggers and Views
Running the MySQL Service
• Check the status of the MySQL service using the following Linux command from the Ter- minal application.
sudo service mysql status
• After ensuring that the MySQL service is active, run the following Linux command to start the MySQL service
mysql –u root -p
CIND110 – Lab 04
In this lab, we will use the LibraryDB database schema described with the following EER diagram.
CIND110 – Lab 04
The Working Database Logical Model
Name CHAR(60) Address VARCHAR(60) Phone VARCHAR(60)
BOOK_COPIES
Book_id INT Branch_id INT
No_of_copies INT
BEF INSERT COPIES_VIOLATION
BOOK_AUTHORS
Book_id INT
Author_name VARCHAR(60)
Book_id INT
Title VARCHAR(255) Publisher_name CHAR(60)
PRIMARY Publisher_name
Card_no INT
Name VARCHAR(60) Address VARCHAR(60) Phone VARCHAR(60)
BOOK_LOANS
Book_id INT Due_date DATE Date_out DATE
Branch_id INT Card_no INT Indexes PRIMARY Branch_id Card_no
LIBRARY_BRANCH
Branch_id INT
Branch_name VARCHAR(60) Address VARCHAR(60)
Download the LibraryDB.sql file from the course shell, then run the following command to import the structure and the data of the LibraryDB database to your machine/server, using the MySQL source command.
You might need to configure the path to the LibraryDBSQLScript.sql file, so the SQL script can be found and executed correctly.
source /home/data/Downloads/LibraryDBSQLScript.sql;
To check whether the database has been correctly imported to your machine/server, you can run the following commands.
SHOW DATABASES;
SHOW TABLES FROM LibraryDB; SELECT *
FROM information_schema.columns WHERE table_schema = ‘LibraryDB’;
Comparisons Involving Three-valued Logic
The following table shows the logical connectives in the three-valued logic we will use in MySQL. As you can see, each individual NULL value is considered to be different from every other NULL value in the various database records. When a record with NULL in one of its attributes is involved in a comparison operation, the result is considered to be NULL, as it might be either an unknown, withheld, or not applicable value.
AND TRUE FALSE NULL OR TRUE FALSE NULL TRUE TRUE SALSE NULL TRUE TRUE TRUE TRUE FALSE FALSE FALSE FALSE FALSE TRUE FALSE NULL NULL NULL FALSE NULL NULL TRUE NULL NULL
TRUE FALSE FALSE TRUE NULL NULL
The following query retrieves all borrowers who do not have a phone number.
SinceSQLconsiderseachNULLvalueasbeingdistinctfromeveryotherNULLvalue;equal- ity comparison is not appropriate. and using the = or <> would not give similar results as IS or IS NOT operators. The following example is using the = operator rather than the NULL operator, and hence it will not display the expected outcome.
CIND110 – Lab 04
USE LibraryDB; SELECT *
FROM BORROWER
WHERE Phone IS NULL;
USE LibraryDB; SELECT *
FROM BORROWER
WHERE Phone = NULL;
Nested Queries
CIND110 – Lab 04
SELECT B.Title
FROM BOOK AS B
WHERE B.Publisher_name IN (SELECT P.Name
FROM PUBLISHER AS P
WHERE P.Name LIKE ‘J%’ OR
P.Phone NOT LIKE ‘333%’);
Anestedqueryisacompleteselect-from-whereblockthatresideswithinanotherSQLquery. We use a nested query when some values in the database need to be retrieved before applying another operation on them.
Thefollowingexampleretrievesthetitleofallbookswithapublisherwhosenamestartswith the letter ’J’ or their phone number does not begin with ’333’.
Inner and outer queries are said to be correlated if the condition in the WHERE clause of one of them refers to an attribute that has been declared in the other. For example, if we try to retrieve each author’s name similar to the borrower’s name, we can use the following correlated queries.
The above example can be expressed as a single select-from-where block, as it is a nested query using one of the comparison operators.
SELECT BA.Author_name
FROM BOOK_AUTHORS AS BA
WHERE BA.Author_name IN(SELECT BR.Name
FROM BORROWER AS BR
WHERE BA.Author_name = BR.Name);
SELECT BA.Author_name
FROM BOOK_AUTHORS AS BA, BORROWER AS BR WHERE BA.Author_name = BR.Name;
SELECT P.Name AS `Publisher’s Name` FROM PUBLISHER AS P;
CIND110 – Lab 04
Applying Boolean Functions
SELECT P.Name
FROM PUBLISHER AS P
WHERE NOT EXISTS(SELECT * FROM BOOK AS B
WHERE P.Name = B.Publisher_name);
SELECT P.Name
FROM PUBLISHER AS P
WHERE EXISTS(SELECT * FROM BOOK AS B
WHERE P.Name = B.Publisher_name);
A Boolean function in SQL is a function that returns either TRUE or FALSE, so it can be used in the WHERE clause.
TheEXISTSBooleanfunctionisusedtocheckwhethertheresultofthenestedquerydoesnot contain any records. The outcome of the EXISTS function is TRUE if the nested query result holds at least one record, or FALSE if the nested query result has no records/rows.
The following example retrieves the names of the publishers who have no books stored in our database.
Ifyouwouldliketoretrievethenamesofpublisherswhohaveatleastonebook,youcanuse the EXISTS Boolean function without negating it with the logical NOT operator, as follows.
Renaming Columns in the Results
In SQL, it is possible to change the name of the header of the resulting attribute/column without changing the actual name in the database. In the following example, we changed the nameoftheattributeNamefromthePUBLISHERtabletobePublisher’s Nameanddisplayed the new name in the results.
Note that, if we display the description of the PUBLISHER table, we will see that the name of the Name attribute has not been changed.
USE LibraryDB; DESCRIBE PUBLISHER;
Joining Tables
In the above example, we specified the joining common attributes (i.e. BL.Branch_id = BC.Branch_id, however, if we use the NATURAL JOIN operation, then there will be no need for any joining attributes to be specified, as MySQL will create a joined table for each pair of attributes with the same name from the involved relations.
Note that the results of applying the NATURAL JOIN operation might be different than the ones from using the JOIN operation if several attributes share the same name. The following example is an alternative script to the one above. As you can see, there is more than one attribute with the same name: Branch_id and Book_id, and to converting a NATURAL JOIN to JOIN operation, we need to mention these common joining attributes explicitly.
Assume that we would like to retrieve the library branch id and the borrower card number of every book with more than ten copies. In this case, we need to specify the JOIN operation between the BOOK_LOANS and the BOOK_COPIES relations in the FROM clause before projecting the Branch_id and the Card_no attributes and filtering the rows according to the required criterion (i.e. more than ten copies).
CIND110 – Lab 04
SELECT DISTINCT BL.Branch_id, BL.Card_no FROM BOOK_LOANS AS BL
BOOK_COPIES AS BC
ON BL.Branch_id = BC.Branch_id
WHERE BC.No_of_copies > 10;
SELECT DISTINCT BL.Branch_id, BL.Card_no FROM BOOK_LOANS AS BL
NATURAL JOIN
BOOK_COPIES AS BC WHERE BC.No_of_copies > 10;
SELECT DISTINCT BL.Branch_id, BL.Card_no FROM BOOK_LOANS AS BL
BOOK_COPIES AS BC
USING (Branch_id, Book_id)
WHERE BC.No_of_copies > 10;
CIND110 – Lab 04
• With the JOIN operation, some records might be included in the results only if matching records exist in the other relation. For example, only authors who are borrowers are included in the results of running the following example.
• Ifweneedtoincludealltheauthorsintheresults,adifferenttypeofjoin,calledOUTER JOIN should be considered. The following example uses the LEFT OUTER JOIN operation to list all the authors in our database with displaying a NULL value if there is no matching name found in the BORROWER table.
• Similarly,ifweareinterestedindisplayingthelistofalltheborrowersintheBORROWERtable, we can use the RIGHT OUTER JOIN rather than the LEFT OUTER JOIN operation.
• The CROSS JOIN operation is used to specify the Cartesian Product operation resulting in all possible record combinations.
SELECT BA.Author_name, BR.Name AS Borrower_Name FROM BOOK_AUTHORS AS BA
BORROWER AS BR
BA.Author_name = BR.Name;
SELECT BA.Author_name, BR.Name AS Borrower_name FROM BOOK_AUTHORS AS BA
LEFT OUTER JOIN
BORROWER AS BR
BA.Author_name = BR.Name;
SELECT BA.Author_name, BR.Name AS Borrower_name FROM BOOK_AUTHORS AS BA
RIGHT OUTER JOIN
BORROWER AS BR
BA.Author_name = BR.Name;
SELECT BA.Author_name, BR.Name AS Borrower_name FROM BOOK_AUTHORS AS BA
CROSS JOIN BORROWER AS BR;
Aggregate Functions
SELECT Branch_id, SUM(No_of_copies) AS `Total Number of Copies` FROM BOOK_COPIES
GROUP BY Branch_id;
SELECT Branch_id, AVG(No_of_copies) AS `Average Number of Copies` FROM BOOK_COPIES
GROUP BY Branch_id
HAVING `Average Number of Copies` < 30;
To summarize the information from multiple records into one single-record summary, an aggregate function can be used. In SQL, there are several built-in aggregate functions that the user can use: COUNT, MAX, MIN, AVG and SUM. Notably, you might need to create subgroups of records using the GROUP clause before applying the aggregate function on these subgroups.
The following example computes the sum of the copies of all books in each library branch.
If we would like to filter the created subgroups, we can introduce a conditional expression within the HAVING clause, so only the subgroups that satisfy the declared condition are re- trieved in the results. The following example displays the average number of books per branch that is below 30.
The following example retrieves the branch id and the total number of copies more than 20 for each branch located in Ontario.
CIND110 - Lab 04
SELECT Branch_id, SUM(No_of_copies) FROM BOOK_COPIES
WHERE No_of_copies > 20
AND Branch_id IN
(SELECT Branch_id
FROM LIBRARY_BRANCH
WHERE Address LIKE ‘%ON%’)
GROUP BY Branch_id;
Triggers and Views
General constraints can be imposed on any database using the CREATE TRIGGER statement. Each trigger consists of three components: Event, Condition and Action. The condition de- termines whether the action should be executed or not based on the event. If no condition is specified, the action will be executed once the event occurs.
For example, to add a constraint to the LibraryDB database stating that the number of copies of any book must not be less than 5, we can design the following trigger that will insert the NULL value if the user tries to insert a value below 5.
CIND110 – Lab 04
CREATE TRIGGER COPIES_VIOLATION
BEFORE INSERT ON BOOK_COPIES
FOR EACH ROW
SET NEW.No_of_copies = IF(NEW.No_of_copies < 5,
NEW.No_of_copies);
INSERT INTO BOOK_COPIES VALUES(7, 3025, 4); INSERT INTO BOOK_COPIES VALUES(9, 3025, 44);
SELECT * FROM BOOK_COPIES; SHOW TRIGGERS;
When a SELECT statement is used frequently, it can be coded with the CREATE VIEW state- ment. The rows of the SELECT statement can then be returned by opening the View.
A View can be created as a virtual table that does not hold any data, as it only includes an SQL query ready to be executed once a view is open. This approach might be inefficient if the View has a complex query; however, it is constantly updated with the most current database state, as it is tied to the base tables in the working database.
Thefollowingexampleshowshowtocreateavirtualtable,followingtheLibraryDBdatabase schema.
CREATE VIEW AVAILABLE_COPIES AS SELECT *
FROM BOOK_COPIES
WHERE No_of_copies BETWEEN 20 AND 80 ORDER BY No_of_copies DESC;
CIND110 - Lab 04 • The script of a view can be displayed using the SHOW CREATE VIEW clause, and a View can
be deleted using the DROP VIEW clause.
SHOW CREATE VIEW AVAILABLE_COPIES; --------------
ALGORITHM = UNDEFINED DEFINER = SQL SECURITY DEFINER
VIEW `available_copies` AS SELECT
`BOOK_COPIES`.`Book_id` AS `Book_id`, `BOOK_COPIES`.`Branch_id` AS `Branch_id`, `BOOK_COPIES`.`No_of_copies` AS `No_of_copies`
`BOOK_COPIES`
(`BOOK_COPIES`.`No_of_copies` BETWEEN 20 AND 80)
ORDER BY `BOOK_COPIES`.`No_of_copies` DESC --------------
• The other approach to designing a view involves creating a permanent table when the View is first queried and storing that table in the working database. This approach requires the DBMS to incrementally update any materialized view table as the working database might evolve over time.
This is the end of lab4 , PhD