School of Science/ Computer Science and Information Technology
ISYS3412 Practical Database Concepts Week 8 – 9 : Tute/Lab – SQL Programming
Semester 1 2020
1 Objective
The objectives of this tute/lab session are:
• 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.
1.1 PreparationTasks
You must already have completed Week 6 – 7 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 6 – 7 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.
Director
1..1 Directs
0..N 0..N
MvNumb {PK} MvTitle YrMade MvType
Crit MPA A Noms Awrd
Movie
0..N MovStar
1..1
By 0..N
1..1
Includes 0..N
Member
MmbNumb {PK} MmbName MmbAddr MmbCity MmbSt NumRent Bonus
JoinDate
DirNumb {PK} DirName DirBorn DirDied
Star
StarNumb {PK} StarName BrthPlce StarBorn StarDied
Borrow
TxnNumb {PK} BorDte
2.1 Createablankdatabase
Open SQLite Studio and create a new (empty) database, as follows.
School/Department/Area
Document: Week 8 – SQL Tute 3 V1.0.docx Author: Santha Sumanasekara Save Date: 24/04/2020 Page 1 of 12
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.
2.2 Buildingtheschemausingascript.
In the Canvas, under Sample Databases and Tools 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.
Document: Week 8 – SQL Tute 3 V1.0.docx Author: Santha Sumanasekara Save Date: 24/04/2020 School/Department/Area Page 2 of 12
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.
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.
SELECT *
FROM movie;
SELECT *
FROM member;
…
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)
Write down the complete SQL statement you used in this step.
School/Department/Area
Document: Week 8 – SQL Tute 3 V1.0.docx Author: Santha Sumanasekara Save Date: 24/04/2020 Page 3 of 12
Was your insertion successful?
Now try it again with the following data.
Was your insertion successful?
What is the problem you encountered?
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.
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?
What is the problem you encountered?
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:
Document: Week 8 – SQL Tute 3 V1.0.docx Author: Santha Sumanasekara Save Date: 24/04/2020 School/Department/Area Page 4 of 12
25 Beautiful Lies 2010 DRAMA 4 M 6 4 5
26 Christopher Robin 2018 DRAMA – – – – –
14 2001 1968SCIFI4G 205
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);
If the above SQL displays the desired list for the deletion, now you can re-run the above SQL, by replacing SELECT * with DELETE.
2.7 Createanewtablefromscratch–Directortable.
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:
2.8 PopulatetheDirectortable
Now that the new table is created and required integrity constraints are enforced, use Director.sql script to populate the table.
SELECT * DELETE FROM star
WHERE starnumb NOT IN
(SELECT starnumb
FROM movstar);
CREATE TABLE director
(
dirnumb DECIMAL(3) NOT NULL,
Dirname VARCHAR(20),
Dirborn DECIMAL(4),
dirdied DECIMAL(4),
PRIMARY KEY (dirnumb)
);
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: Week 8 – SQL Tute 3 V1.0.docx Author: Santha Sumanasekara Save Date: 24/04/2020 Page 5 of 12
27 Minority Report 2002 SCI FI 4 PG 4 0 8
How do you fix this problem?
2.9 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.
2.10 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.
3 Creating and using Views 3.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
Document: Week 8 – SQL Tute 3 V1.0.docx Author: Santha Sumanasekara Save Date: 24/04/2020 School/Department/Area Page 6 of 12
ALTER TABLE director
ADD COLUMN brthplce VARCHAR(20)
;
UPDATE director
SET birthplce = ‘New York, USA’
WHERE dirnumb = 1;
ALTER TABLE movie
ADD Constraint fk_const
FOREIGN KEY (dirnumb) REFERENCES director(dirnumb);
CREATE VIEW continent(name, NumCountries, Population) AS SELECT c.continent, count(*),sum(c.population)
FROM country c
GROUP BY c.continent;
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.
3.2 Usingaview
Run the following query:
Now, try to do an insertion via the view.
Were you successful?
4 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.
4.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.
4.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. 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
Document: Week 8 – SQL Tute 3 V1.0.docx Author: Santha Sumanasekara Save Date: 24/04/2020 School/Department/Area Page 7 of 12
SELECT *
FROM dir_awards;
INSERT INTO dir_awards (dirnumb, dirname)
VALUES (21, ‘David Lane’);
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 insertions or updates
proceeding if such insertion or update violate the above rule.
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.
5 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 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.
Document: Week 8 – SQL Tute 3 V1.0.docx Author: Santha Sumanasekara Save Date: 24/04/2020 School/Department/Area Page 8 of 12
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
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.
6
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.
Extension Question (requires additional Software or
MyDesktop access)
In the Section 2 you have manually created the tables using CREATE TABLE commands. However, you can use Database Design Tools such as Oracle SQL Developer Data Modeller to generate the required DDL commands to generate these tables.
Oracle SQL Developer is available on MyDesktop (mydesktop.rmit.edu.au)
Use one of the read-only database accounts provided to log into the Oracle. Click on “New Connection …” Icon (Green +) on the top left-hand corner.
Document: Week 8 – SQL Tute 3 V1.0.docx Author: Santha Sumanasekara Save Date: 24/04/2020 School/Department/Area Page 9 of 12
On the New Connection Dialog Box, enter the following details.
Connection Name:
Password: movies
Connection Type: Basic
Hostname: emu.cs.rmit.edu.au
Port: 1521
Service Name: CSAMPR1.ITS.RMIT.EDU.AU
Note that Oracle Data Modeller uses Barker Notation, slightly different from the the UML notation used in this course. You may refer to http://www.vertabelo.com/blog/technical-articles/barkers-erd- notation or any other website to quickly find the differences between UML and Barker notation and rebuild the ER model on Oracle Data Modeller.
1. Open SQL Developer Data Modeller, by following: ViewàData ModeleràBrowser
School/Department/Area
Document: Week 8 – SQL Tute 3 V1.0.docx Author: Santha Sumanasekara Save Date: 24/04/2020 Page 10 of 12
2. A new Browser pane will appear in the left of the SQL Developer window.
3. Right click on Designs (see above diagram) and on the dialog box, click “New Design”.
This will open up two new tabs on your main work area. One tab is named “Logical” and the
other named as “Relational”.
4. Draw your ER model (replication of the ER model at the beginning of this tutorial) on the
Logical Design pane.
5. Once all the entities, relationships and their attributes are entered, save the diagram.
On the Browser right click on the unsaved design (in my example, it is named “Untitled_2”, and it may differ in your work area).
On the dialog box, click on “Save Design” and save it on a folder that you can access.
6. Click on the “Engineer to Relational Model” button on the top of the window.
This will open up a new dialog box.
Click on “Engineer” button at the bottom of the dialog box, and it will generate the relational model on the “Relational Design” tab.
7. On the “relational Design” tab, at the top of the window, click on “Generate DDL” button.
A dialog box will appear.
On the top of the dialog box, click on “Generate” button. Another dialog box will appear, and click “OK”. This will Generate the required DDL script to build the database schema.
Document: Week 8 – SQL Tute 3 V1.0.docx Author: Santha Sumanasekara Save Date: 24/04/2020
School/Department/Area Page 11 of 12
Document: Week 8 – SQL Tute 3 V1.0.docx Author: Santha Sumanasekara Save Date: 24/04/2020 School/Department/Area Page 12 of 12