School of Science/ Computer Science and Information Technology
ISYS3412 Practical Database Concepts Week 8: Tute/Lab – SQL Programming
Semester 1 2020
1 Objective
The objectives of this tute/lab session are:
●
1.1
Develop advanced SQL programming skills:
➢ CREATE, ALTER and DROP tables;
➢ INSERT, UPDATE and DELETE rows in a table;
➢ Create and use VIEWs;
➢ Define and use TRIGGERs.
Preparation Tasks
You must already have completed Week 7 – 8 Tute/Lab session. If not, before attempting any of the activities in this sheet, make sure you are thoroughly competent with the basic SQL programming tasks included in the Week 7 – 8 Tute/Lab sheet.
2 Build a replica MOVIES database in your workspace
The movies database is built to store information about movies, directors, and stars (i.e. actors and actresses) in a video store. In addition, it stores information on members (who got active memberships with the video store) and their borrowing transactions.
A sketchy ER diagram for the movies database is given below.
School/Department/Area
Document: SQL Tute 3 V1.0.docx Author: Santha Sumanasekara Save Date: 16/09/2018 Page 1 of 11
2.1 Createablankdatabase
Open SQLite Studio and create a new (empty) database, as follows.
Go to Database → Add Database and assign a suitable name (say New Movies) to the new database you are about to create.
It should appear on the database list of left pane, as well as on Database Chooser on the top-centre of the screen. It may not be open at this time. If not opened, open it by double clicking on the database name on the left pane. And thereafter, choose it from the Database Chooser.
Now your database is ready to add new tables and populate them.
Document: SQL Tute 3 V1.0.docx Author: Santha Sumanasekara Save Date: 16/09/2018 School/Department/Area Page 2 of 11
2.2 Buildingtheschemausingascript.
In the Canvas, under Week 9 Module, you will find a zip file (SQL Scripts.zip). Download this zip file into your working directory and unzip it.
It contains the following files.
Borrow.sql
Director.sql
Member.sql
Movie.sql
Moviesddl.sql
Movstar.sql
Star.sql
Moviesddl.sql SQL script contains DDL statements to build all the required tables, except Director.
In this exercise, you learn how to load a (pre-built) SQL script from a file and run it on SQLite Studio.
We first use Moviesddl.sql SQL script to create five tables: Borrow, Movie, Star, MovStar and Member.
On SQLite Studio, click on “Load SQL from File” button from the top icons.
Choose Moviesddl.sql on the dialog box and run SQL statements on it.
You will see that you have 5 tables created in your database space.
2.3 PopulatingtheMoviesDatabase.
In the above step, you created the tables, but they are still empty.
Repeat “Run SQL from file” option for populating these tables using Borrow.sql,
Member.sql, Movie.sql, Movstar.sql, and Star.sql files.
A simple SELECT statement can be used to make sure you have correctly created and populated
these tables.
Document: SQL Tute 3 V1.0.docx Author: Santha Sumanasekara Save Date: 16/09/2018 School/Department/Area Page 3 of 11
SELECT *
FROM movie;
SELECT *
FROM member;
…
2.4 Insertnewrowstoexistingtables
Say, you want to add a new movie into the Movie relation.
Use INSERT INTO table_name VALUES(…) command to insert new tuples.
Note that (1) we have values for ALL attributes; (2) they are listed in correct order as the list of attributes; and (3) character strings begin and end with single quotation marks.
A common problem you encounter if you use a word processor to copy and paste these commands: Some word processors convert single quotation marks into open quote (‘) and end quote (’) which are not valid in SQL. Make sure they are just single quotation marks as you typed on the keyboard.
Write down the complete SQL statement you used in this step.
Was your insertion successful?
Answer: Yes
Now try it again with the following data.
Was your insertion successful?
Answer: No
What is the problem you encountered?
Answer:
Key constraint violation. Mvnumb 25 already exists, and as such another movie cannot use the same mvnumb.
Let’s suppose that you have only a few attribute values for a new movie. Say, ‘Christopher Robin’ was just released.
Discuss with your group how to insert a new row with NULL values for attributes that are unknown. Write down the complete SQL statement you used in this step.
Document: SQL Tute 3 V1.0.docx Author: Santha Sumanasekara Save Date: 16/09/2018 School/Department/Area Page 4 of 11
25 A Beautiful Mind 2000 DRAMA 4 M 6 4 5
INSERT INTO movie
VALUES (25, ‘A Beautiful Mind’, 2000,
‘DRAMA’, 4, ‘M’, 6, 4, 5)
25 Beautiful Lies 2010 DRAMA 4 M 6 4 5
26 Christopher Robin 2018 DRAMA – – – – –
Answer:
insert into movie(mvnumb,mvtitle,yrmade,mvtype) values(26, ‘Christopher Robin’,2018,’DRAMA’,,,,,);
2.5 EditExistingRows
You can use UPDATE statement to change attribute values in a table. For example, if you wish to change the name of a movie:
To
Use the following SQL statement:
Try the following update.
Was your update successful?
Answer: No
What is the problem you encountered?
Answer:
Key constraint violation. Mvnumb 10 already exists, and as such another movie cannot use the same mvnumb. Additionally, there are several Referential Integrity constraint violations now present in adjacent relations to movie.
2.6 DeleteExistingRows
Note that deletions must be used with utmost care. Especially when you delete rows from production-level databases, reversing the effects of a deletion is not always possible, and may even result in unexpected consequences.
You can use DELETE statement to delete rows from a table.
Always test the DELETE command by running it as a SELECT command prior to run the DELETE command.
e.g. Delete all the stars from the Star table who haven’t played in any movie (in the collection). First, test the rows that could impact by the deletion, as follows:
If the above SQL displays the desired list for the deletion, now you can re-run the above SQL, by replacing SELECT * with DELETE.
Document: SQL Tute 3 V1.0.docx Author: Santha Sumanasekara Save Date: 16/09/2018 School/Department/Area Page 5 of 11
14 2001 1968 SCI FI 4 G 2 0 5
14 2001:Aspaceodyssey 1968 SCIFI4 G 2 0 5
UPDATE movie
SET mvtitle = ‘2001: A Space Odyssey’
WHERE mvtitle = ‘2001’;
UPDATE movie
SET mvnumb = 10
WHERE mvnumb = 11;
SELECT *
FROM star
WHERE starnumb NOT IN
(SELECT starnumb
FROM movstar);
SELECT * DELETE FROM star
WHERE starnumb NOT IN
(SELECT starnumb
FROM movstar);
2.7 Createanewtablefromscratch–Directortable.
3 Now it’s time to create the missing table. We will write a CREATE TABLE command to build it will following attributes:
Dirnumb – This attribute holds 3-digit director number
Dirname – This attribute holds director names, Names are up to 20 characters long. Dirborn – This attribute holds the year a director was born. So, assume it is a 4-digit number. Dirdied – same as the above dirborn.
Explore the data types available in SQLite that you can use in your CREATE TABLE command. Visit: https://www.sqlite.org/datatype3.html
to review all of the possibilities.
1. What is the maximum precision (number of digits) of the NUMERIC type?
2. What is the difference between NUMERIC type and DECIMAL type?
3. What is the difference between VARCHAR and CHAR?
4. What is the maximum string length that can be accommodated in a VARCHAR attribute?
5. How dates and times are stored in SQLite?
First create the table with no constraints, as follows:
CREATE TABLE director
(
dirnumb DECIMAL(3) NOT NULL,
Dirname VARCHAR(20),
Dirborn DECIMAL(4),
dirdied DECIMAL(4),
PRIMARY KEY (dirnumb)
);
3.1 PopulatetheDirectortable
Now that the new table is created and required integrity constraints are enforced, use Director.sql script to populate the table.
Were you able to insert all rows (8 rows in total)? If unsure, count. Now insert the following tuple to the movie relation.
Why didn’t the above insertion succeed?
School/Department/Area
Document: SQL Tute 3 V1.0.docx Author: Santha Sumanasekara Save Date: 16/09/2018 Page 6 of 11
27 Minority Report 2002 SCI FI 4 PG 4 0 8
Answer:
Here, we have dirnumb 8 for this new movie. However, in the previous step, the insertion of the 8th director failed due to the duplication of dirnumb. (both 7th and 8th directors had the same dirnumb). So, this (attempted) insertion violated referential integrity constraint.
How do you fix this problem?
Answer:
Insert the corresponding director record first with correct dirnumb. Thereafter, you can re-run the above insertion.
3.2 Altertheschemadefinition–addcolumns
Let’s suppose we were asked to store directors’ birthplace, as similar to Stars. In order to add this attribute, we have to alter the schema of the table.
Now, you may add some values to this new attribute, using UPDATE statement. E.g.
3.3 Alter the schema definition – add Constraint
SQLite doesn’t include all forms of table alterations. It limits alterations to table name changes, column name changes, and column addition. Other database systems, such as Oracle, allow more complex alterations, such as adding constraints.
For example, adding a foreign key in Movie table:
Between Movie and Director entities (in the underlying data model), there was a one-to-many relationship, resulting in a foreign key in Movie table.
Exercise:
The above ALTER TABLE command doesn’t work in SQLite.
Suggest a workaround to add a foreign key to the movie table.
Answer:
Recreate the database, this time include a reference to the foreign key (dirnumb) when creating the movie table.
Document: SQL Tute 3 V1.0.docx Author: Santha Sumanasekara Save Date: 16/09/2018 School/Department/Area Page 7 of 11
ALTER TABLE director
ADD COLUMN birthplace VARCHAR(20)
;
UPDATE director
SET birthplace = ‘New York, USA’
WHERE dirnumb = 1;
ALTER TABLE movie
ADD Constraint fk_const
FOREIGN KEY (dirnumb) REFERENCES director(dirnumb);
4 Creating and using Views 4.1 Creatingaview
In the lecture, we create a view to (virtually) store continent names, number of countries and total population. The required information was derived from Country table, using grouped aggregation. We started the definition of the view by writing up the required SQL script that gives you the desired result and then, use it as the basis of the view definition.
Exercise: create a view called dir_awards, which stores the director number, name, total number of awards won, and the total number of nominations received. The last two attributes do not exist directly in the base tables, therefore, they have to be generated by aggregating some attributes in base tables. In this example, you can add up awards won by movies, after grouping movies based on the director. You can do the same for the award nominations, too.
Answer:
CREATE VIEW dir_awards (dirnumb, dirname, awrd_total, noms_total) AS (SELECT director.dirnumb, dirname, SUM(awrd), SUM(noms)
FROM director LEFT OUTER JOIN movie ON director.dirnumb = movie.dirnumb GROUP BY director.dirnumb,dirname);
4.2 Usingaview
Run the following query:
Now, try to do an insertion via the view.
Were you successful?
Answer:
You guessed it — no, this won’t work. dir_awards is not an updatable view. It has grouped aggregate functions. You cannot use this view to update base tables.
5 Creating and using Triggers
Triggers are procedures that are stored in the database and are implicitly run, or fired, when something happens. Triggers are created using the CREATE TRIGGER statement. A trigger will have a defined trigger event (an INSERT, DELETE or UPDATE action) and a trigger action. A trigger action can be one or more SELECT, INSERT, DELETE or UPDATE statements or an PL/SQL procedure.
Document: SQL Tute 3 V1.0.docx Author: Santha Sumanasekara Save Date: 16/09/2018 School/Department/Area Page 8 of 11
CREATE VIEW continent(name, NumCountries, Population) AS
SELECT c.continent, count(*),sum(c.population)
FROM country c
GROUP BY c.continent;
SELECT *
FROM dir_awards;
INSERT INTO dir_awards (dirnumb, dirname)
VALUES (21, ‘David Lane’);
5.1 BEFOREandAFTERTriggers
We discussed in the lecture how we can use a BEFORE trigger to add log records when data on one or more tables are updated. We explored how we can record before and after population values stored in a log file (when population statistics are updated on Country table).
Exercise: Time to time, movies receive new critics’ reviews and as a result, their average critic ratings change. In the database, this is handled by updating crit attribute of the corresponding movie. For auditing purposes, we must store these changes in a log record. Each log record should include: movie number, time stamp, old critic rating and the new rating.
1. Create a table to store log records
2. Create a trigger to generate log records whenever crit attribute in the Movie table is
updated.
3. Test your trigger by changing the critic rating of the movie “2001” to 5.
Answer:
CREATE TABLE crit_log_record
(
mvnumb NUMBER(3),
change_timestamp timestamp,
old_crit NUMBER(3),
new_crit NUMBER(3)
);
CREATE TRIGGER update_crits_log
BEFORE UPDATE OF crit ON movie
FOR EACH ROW
BEGIN
INSERT INTO crit_log_record VALUES
(:NEW.mvnumb, sysdate, :OLD.crit, :NEW.crit);
END;
— For students to work on SQLite.
— There are some slight syntax differences (such as :NEW vs NEW) CREATE TRIGGER update_crits_log
BEFORE UPDATE OF crit ON movie
BEGIN
INSERT INTO crit_log_record VALUES
(NEW.mvnumb, datetime(‘now’), OLD.crit, NEW.crit);
END;
insertions or updates proceeding if such insertion or update violate the above rule.
5.2 INSTEADOFTriggers
INSTEAD OF trigger is a special kind of trigger, normally used when dealing with non-updatable views. For example, if a view is generated using a join between two base tables, such views cannot be used to insert into or update rows in the base table. In such circumstances, if you want to provide a transparent mechanism to insert and update base tables using the view, you can use an INSTEAD OF trigger.
The following activity involves with the creation of the view — dir_awards, which stores the director number, name, total number of awards won, and the total number of nominations received.
Document: SQL Tute 3 V1.0.docx Author: Santha Sumanasekara Save Date: 16/09/2018 School/Department/Area Page 9 of 11
Advanced Exercise: In Movies table, we store the number of Oscar nominations and the number
of Oscar awards that each movie won. To ensure data integrity, we must ensure the
number of awards cannot exceed the number of nominations. Write a trigger stop any
The last two attributes do not exist directly in the base tables, therefore, they have to be generated by aggregating some attributes in base tables. In this example, we can add up awards won by movies by grouping movies based on the director. We can do the same for the award nominations, too.
Start the exercise, by creating the view, as follows: (you must have done this in Section 3.1)
If you created the view correctly, you should be able to use it in SELECT statements:
Now, try to do an update via the view.
You will get an error message, indicating that’s not allowed.
Create a trigger to update dirname attribute in the dir_awards view.
After the activation, try to repeat the same update (which failed earlier). Can you do the update
now? Check the contents in the base tables.
Answer:
— INSTEAD OF trigger exercise
CREATE VIEW dir_awards (dirnumb, dirname, awrd_total, noms_total) AS (SELECT director.dirnumb, dirname, SUM(awrd), SUM(noms)
FROM director LEFT OUTER JOIN movie
ON director.dirnumb = movie.dirnumb
GROUP BY director.dirnumb,dirname);
CREATE OR REPLACE TRIGGER dir_awards_insert
INSTEAD OF UPDATE ON dir_awards
FOR EACH ROW
BEGIN
UPDATE director SET director.dirname = :NEW.dirname
WHERE director.dirnumb = :OLD.dirnumb;
END;
6 Advanced Activity: Facebook-Lite
Let’s assume that a decision has been made to develop a light version (called Facebook-Lite) of the ever-popular Facebook application. This light version has limited functionalities compared to
Document: SQL Tute 3 V1.0.docx Author: Santha Sumanasekara Save Date: 16/09/2018 School/Department/Area Page 10 of 11
CREATE VIEW dir_awards (dirnumb, dirname, awrd_total, noms_total) AS
(SELECT director.dirnumb, dirname, SUM(awrd), SUM(noms)
FROM director LEFT OUTER JOIN movie
ON director.dirnumb = movie.dirnumb
GROUP BY director.dirnumb,dirname);
SELECT * FROM dir_awards;
UPDATE dir_awards
SET dirname = ‘Woody Allen’
WHERE dirname = ‘Allen, Woody’;
[21:53:26] Error while executing SQL query on database ‘world’: cannot modify
dir_awards because it is a view
the full version. Only core functionalities are to be retained. You are tasked with the design of the database backend.
After a careful analysis, the following core functionalities are to be retained in the light version.
The system stores information on members; each member is uniquely identified by email and a full name, screen name, date of birth, gender, status and location are to be stored. Each member has a visibility level on Facebook-Lite (private, friends-only, or everyone).
As in the case of Facebook, members form networks of friends. A friendship is always between two members. A member can send a friend request to another member. Once that member accepts the request, they become friends on Facebook-Lite. Each friendship has a start date.
Members can make posts on Facebook-Lite. A post has a unique postID and a body and a timestamp. Facebook-Lite only allows textual posts.
Other members can respond to posts. They can also respond to previous responses. Responses are identical to posts in structure, so, they share the same structure as in original post. Each response will have a parent post or a parent response.
Members can “like” posts and responses. The system keeps track of likes, specifically the member who make the like and the corresponding post/ response. No other information is required on likes.
1. Draw an entity-relationship model to represent these requirements. Make sensible assumptions for cardinality and participation constraints where they are not clearly outlined in the description.
2. Map the above ER model into relations using 7-step mapping process.
3. Create a database using the schema you arrived at the (2) above. You must have
constraints (key, entity integrity and referential integrity) defined with the tables you create.
4. Populate your tables with some data to test the functionality of your Facebook Lite
application.
Document: SQL Tute 3 V1.0.docx Author: Santha Sumanasekara Save Date: 16/09/2018 School/Department/Area Page 11 of 11