ISYS3412 Practical Database Concepts
2020 Semester 1
Week 3 Tute/lab – Part 1: Relational Database Model — Answers
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:
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. 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àWeek 2àResources area.
Step 4: Connect.
Click on DatabaseàAdd Database and on the dialog box, click “Browse for Existing Database Files” icon and “OK”.
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àWeek 3àResources area.
Activity 1: Creating Database Tables in SQLite Studio
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
• 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.
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 the your database, double click on the database name (Rocky) on the left-hand pane.
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.
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?
Yes – since no constraints were enforced in the schema, we were able to enter duplicate rows.
What constraint you have violated?
We have violated key constraint.
Discuss.
Whenever you create a table, you must explicitly specify what attribute(s) act as the primary key. The DBMS wouldn’t know unless you specify them. Use the “PRIMARY KEY (attribute list) clause.
This is a must.
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?
Yes – since no constraints were enforced in the schema, we were able to enter rows with null values in primary key attributes.
What constraint you have violated?
The above insertion violated Entity Integrity Constraint.
Discuss.
You must explicitly specify which attributes comprised in the primary key and include NOT NULL clause. This must be done for every single attribute in the case of a composite attribute, as in the case of PRODUCT_DETAILS table.
A word of warning: In SQLite when we include PRIMARY KEY clause, it does not implicitly assume that these attributes cannot be null. In other words to enforce both entity integrity constraint and key constraint you are required to use PRIMARY KEY () clause and NOT NULL clause. Some databases require this and some don’t. So, it is safe practice to add NOT NULL clause, as well.
7. Continue populating other tables with following data.
Were you able to enter these new rows?
Yes, no problem! As above we have violated referential integrity constraint, but it is not enforced in the schema.
What constraint you have violated?
Second and third insertions violated the referential integrity constraint.
Discuss.
In Orders table, Cust_No refers to Cust_no in Customers. So, within Orders table, Cust_no is acting as a foreign key. So, it can only have valid customer numbers (or nulls). 13144 is not a valid customer number.
In Order_Details table, we have two foreign keys. Order_no refers to Order_no attribute in Orders table, and Prod_Cod attribute refers to Prod_cod in Products table.
While ‘1’ is a valid order number, ‘STAND’ is not a valid product code.
You have noticed that the tables you created and populated in the Rocky Concrete 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.
Activity 2: Creating proper “Relations” in SQLite Studio
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-bsics-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.)
2. Let’s try another row. This time, try re-inserting the first row again.
Were you able to enter this new row? Discuss.
No, it throws an error message, stating that we violated the key constraint.
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.
No, it throws an error message, stating that we violated the entity integrity constraint.
7. Continue populating other tables with following data.
Were you able to enter these new rows? Discuss.
First insertion is OK, but second and third will fail, due to the referential integrity constraint.
Part 2: Relational Database Model: Review of Integrity Constraints
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.)
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).
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.
Violates Key Constraint because the primary key value in the Insert statement (987654321) already exists in the relation for Jennifer Wallace. The Key Constraint says this value (or set of values) cannot be duplicated.
2. Insert <'ProductA', 3, 'Bellaire', 2> into PROJECT.
• Violates Key Constraint because the key value in the Insert statement (3) already exists in the relation for “Product Z”. The Key Constraint says this value (or set of values) cannot be duplicated.
• Violates Referential Integrity Constraint because the attribute Dnum
is a foreign key of the Dnumber in the Department relation, however the value of Dnum in the insert statement (2) is not present in the Department relation.
3. Insert <'Production', 4, '943775543', '01-OCT-88' > into DEPARTMENT.
• Violate Key Constraint because the key value in the Insert statement (4) already exists in the relation for Dname “Administration”. The Key Constraint says this value (or set of values) cannot be duplicated. Change the Dnumber value in the insert statement to a value not already in the table.
• The insertion of a new department, ‘Production’ would also violate the referential integrity constraint. The Mgr_ssn of ‘943775543’ that references the Employee table, Ssn does not exist. In creating the ‘Department’ table we should add the integrity constraint:
Foreign key (Mgr_ssn) references Employee(Ssn)
4. Insert <'123454321', null, '40.0'> into WORKS_ON.
• Violates Entity Integrity Constraint because the value of attribute Pno in this Insert statement cannot be NULL as it is part of the primary key.
• Violates Referential Integrity Constraint because the attribute Essn is a foreign key of the Ssn in the Employee relation, however the value of Essn in the insert statement (123454321) is not present in the Employee relation.
5. Insert <'453345453', 'John', M, '12-DEC-60', 'SPOUSE'> into DEPENDENT.
Violates Referential Integrity Constraint . Dependent.Essn is a foreign key referring Employee.Ssn and thus any input of Dependent.Essn must be a valid value in Employee.SSn. ‘453345453’ cannot be found in Employee.Ssn (we only have 453453453 which is similar). Therefore this insertion will fail.
6. Delete the DEPENDENT tuples with dependent_name= ‘Joy’.
This does not violate any constraints but it is not correct logically. Such as delete will delete all dependents name Joy in the database. The other attribute of the key : Essn has be included in WHERE for an individual record to be selected.
7. Delete the EMPLOYEE tuple with SSN= ‘888665555’.
• Violates Referential Integrity Constraint . There is a foreign key dependency Department.mgr_ssn on Employee.ssn. The Department relation has a tuple that has mgr_ssn = 888665555, so the tuple in Employee relation with the same value for ssn cannot be removed.
• In addition to the above, The WorksOn relation has a tuple with ESSN=88866555, leading to a second referential integrity constraint violation.
8. Update the SUPER_SSN of the EMPLOYEE tuple with SSN=’123456789′ to ‘666884444’.
Super_SSN given here is not a new value. There is a tuple in the EMPLOYEE relation (parent table) with SSN=”666884444′. As such this insertion does not violate referential integrity constraint. In this particular situation, the parent relation is the EMPLOYEE relation itself.
So we can change Super_SSN to another Super_SSN that does exist in the parent table. Therefore, this update does not violate any constraint. So All good.