School of Science/ Computer Science and Information Technology
ISYS3412 Practical Database Concepts Week 3: Tute/Lab – Relational Database Model
Semester 1 2020
Objective
The objectives of this tute/lab session are:
• Learn about the relational database model;
• Learn how to create tables in SQLite Studio
• Learn how to do simple CRUD (create, read, update, and delete) data in tables
• Learn about integrity constraints;
• Learn how to apply integrity constraints in SQLite Studio to make tables to act like relations;
Preparation Tasks
Task 1: (skip this task you do not intend to use your laptop during the lab, and go directly to Preparation Task 2)
It is recommended that you have SQLite Studio installed in your laptop. Follow these steps to install it on your computer. (For more operating system-specific installation instructions, visit: https://github.com/pawelsalawa/sqlitestudio/wiki
Step 1. Visit https://sqlitestudio.pl to download the application install for your operating system. It is available for Windows (InstallSQLiteStudio-3.2.0.exe), Mac OS X (SQLiteStudio-3.2.0.dmg), Linux (sqlitestudio-3.2.0.tar.xz) or as source files.
Step 2: There is a (two-part) installation and usage guide for SQLite Studio available on Canvas. CanvasàModulesàResources: Sample Databases and Tools
If you haven’t reviewed it yet, you may wish to review it before you move onto next step.
Step 3: Test your database installation with a sample database.
Download Movies.db file from Canvas: Modules à Resources: Sample Databases and Tools And save it on your computer.
Step 4: Connect.
Click on Database à Add Database and on the dialog box, click “Browse for Existing Database Files” icon and “OK”.
Document: Week 3 Tute-Lab V1.0.docx Author: Santha Sumanasekara Save Date: 13/03/2020 School/Department/Area Page 1 of 9
This will build a new database and it should appear on the left-hand pane AND on the Database Selector drop-down list at the top, as follows:
(If it appears on left-hand pane, but not on the Database Selector list, that means, the database is available, but is not connected. It can be connected by double clicking database name on the left- hand pane.)
If you are successful, you should have presented a query editor where you can enter and run SQL commands and a Grid View underneath it to view query results.
Task 2:
Create a directory (say, Week-3-Lab) and download following three files from Canvas: Modulesà Resources: Sample Databases and Tools.
Rocky-basics-No-Constraints.sql
Rocky-basics-With-Constraints.sql
Rocky-Data.sql
1 Activities
1.1 CreatingDatabaseTablesinSQLiteStudio
In this activity, you familiarize yourself with running a SQL DDL script in SQLite Studio to create following four tables in a database for a fictitious Rocky Concrete Store.
• Customers • Products
Science of Science/ Computer Science and IT
Document: Week 3 Tute-Lab V1.0.docx Author: Santha Sumanasekara Save Date: 13/03/2020 Page 2 of 9
• Orders
• Order_Details
Step 1: Create an empty database on SQLite Studio.
Click on Database à Add Database and on the dialog box, click “Create a new Database File” icon and “OK”.
Call it “Rocky” and save it on Week-3-lab directory. At this point, you have a database, however, it is empty (no tables/ relations).
Step 2: Create tables.
You have been provided a SQL script to create four tables. (It first checks if these tables exist and if so delete old copies and then create new versions).
Click on “Load SQL from file” button as shown on the following screenshot and choose “rocky- basics-No-Constraints.sql” file.
Document: Week 3 Tute-Lab V1.0.docx Author: Santha Sumanasekara Save Date: 13/03/2020 Science of Science/ Computer Science and IT Page 3 of 9
Once the script appears on your SQL Editor, highlight the whole script and hit the “Execute Query” button on the top. It is important to highlight the whole script, otherwise it will only run the topmost SQL Query.
Step 3: Be familiarised with the database
1. To list all the tables in your database, double click on the database name (Rocky) on the left-hand pane.
Document: Week 3 Tute-Lab V1.0.docx Author: Santha Sumanasekara Save Date: 13/03/2020 Science of Science/ Computer Science and IT Page 4 of 9
2. To view full listing of Customers table in, double click on the table name and click on “Data” tab.
Since you haven’t populated the tables yet, you won’t see any rows.
3. To view Customers table schema, double click on the table name and click on “Structure” tab.
This will show the list of attributes in the table with data types and (if any) constraints.
Step 4: Populate the tables.
Document: Week 3 Tute-Lab V1.0.docx Author: Santha Sumanasekara Save Date: 13/03/2020 Science of Science/ Computer Science and IT Page 5 of 9
Open a SQL Editor window (if you haven’t got one already opened). ToolsàOpen SQL Editor
1. Start to populate the Customer Table using the following SQL statement. (If you wish to copy-and-paste the command, do not use the following (as it was generated using Word. Use the SQL statements in the “rocky-basics-Data.sql” you downloaded from the Canvas.)
Try Step 2 above (in step 3) again, this time using a SQL command.
5. Let’s try another row. This time, customer’s name is “Nev’s Nursery”. It contains an apostrophe (single quotation mark). A single quotation mark (‘) within the literal must be preceded by an escape character. To represent one single quotation mark within a literal, enter two single quotation marks.
Note that some text editors change two adjacent single quotation marks into one double quotation mark. If you do a copy-and-paste from a text editor, make sure that they stay as two single quotation marks.
7. Let’s try another row. This time, try re-inserting the first row again.
Were you able to enter this new row?
What constraint have you violated? Discuss.
Now, try to insert a new customer, who hasn’t got a customer number. When you enter only a few values of a row, you must specify which attribute values you are going to enter, as follows:
Were you able to enter this new row?
What constraint have you violated? Discuss.
7. Continue populating other tables with following data.
Science of Science/ Computer Science and IT
Document: Week 3 Tute-Lab V1.0.docx Author: Santha Sumanasekara Save Date: 13/03/2020 Page 6 of 9
Were you able to enter these new rows?
What constraint you have violated? Discuss.
You have noticed that the tables you created and populated in the Rocky Basics database do not
comply with additional requirements to be treated them as relations. They are just plain tables. In the next activity, we rebuild them with required additional constraints to make them into proper
relations.
1.2 Activity2:Creatingproper“Relations”inSQLiteStudio
The file you downloaded at the beginning – “rocky-basics-With-Constraints.sql” – has key, entity integrity and referential integrity constraints embedded in the corresponding CREATE TABLE SQL commands.
Compare these commands with the ones appear on “rocky-basics-No-Constraints.sql”. Identify the new clauses included in this file, which were absent in the first one. Discuss with the colleague sitting next to you.
Click on “Load SQL from file” button as shown on the following screenshot, and choose “rocky- basics-With-Constraints.sql” file.
Once the script appears on your SQL Editor, highlight the whole script and hit the “Execute Query” button on the top. It is important to highlight the whole script, otherwise it will only run the topmost SQL Query.
1. Start to populate the Customer Table using the following SQL statement. (If you wish to copy-and-paste the command, do not use the following (as it was generated using Word. Use the SQL statements in the “rocky-basics-data.sql” you downloaded from the Canvas.)
Document: Week 3 Tute-Lab V1.0.docx Author: Santha Sumanasekara Save Date: 13/03/2020 Science of Science/ Computer Science and IT Page 7 of 9
2. Let’s try another row. This time, try re-inserting the first row again.
Were you able to enter this new row? Discuss.
3. Now, try to insert a new customer, who hasn’t got a customer number. When you enter only a few values of a row, you must specify which attribute values you are going to enter, as follows:
Were you able to enter this new row? Discuss.
7. Continue populating other tables with following data.
Were you able to enter these new rows? Discuss.
1.3 RelationalDatabaseModel:ReviewofIntegrityConstraints
This question has been adopted from Fundamentals of Database Systems, Elmasri and navathe. (Question 5.11).
A relational database schema and an instance of this schema are given below. Each of the operations given in questions 1 to 8 are applied on the original database instance, disregarding the effects of the previous operations. Suppose that each of the update operations given below is applied directly to the database instance shown in the diagram. (That is, assume we rollback the database instance to its original state after each operation, wiping out any impact of any operation on the next operation.)
Most of the attribute names are self-explanatory. Super_SSN refers to corresponding employee’s superviosr’s SSN (Social Security Number). This example is based on US system, assume it is similar to Australian Tax File Number.
Arrows indicate foreign keys and the corresponding attributes in parent relation. In the case of Super_SSN, the parent relation is the Employee relation itself (self referencing).
Document: Week 3 Tute-Lab V1.0.docx Author: Santha Sumanasekara Save Date: 13/03/2020 Science of Science/ Computer Science and IT Page 8 of 9
List all integrity constraints violated by each operation. If the operation does not violate any constraints, indicate as ‘no violations’.
For each and every violation, explain why such violation occurred and actions you can take to make sure that the operation does not violate any integrity constraints.
1. Insert <'Robert', 'F', 'Scott', '987654321', '21-JUN-42', '2365 Newcastle Rd, Bellaire, TX', M, 58000, '888665555', 1 > into EMPLOYEE.
2. Insert <'ProductA', 3, 'Bellaire', 2> into PROJECT.
3. Insert <'Production', 4, '943775543', '01-OCT-88' > into DEPARTMENT.
4. Insert <'123454321', null, '40.0'> into WORKS_ON.
5. Insert <'453345453', 'John', M, '12-DEC-60', 'SPOUSE'> into DEPENDENT.
6. Delete the DEPENDENT tuples with dependent_name= ‘Joy’.
7. Delete the EMPLOYEE tuple with SSN= ‘888665555’.
8. Update the SUPER_SSN of the EMPLOYEE tuple with SSN=’123456789′ to ‘666884444’.
Document: Week 3 Tute-Lab V1.0.docx Author: Santha Sumanasekara Save Date: 13/03/2020
Science of Science/ Computer Science and IT Page 9 of 9