Compiled Contents of:Week 3 The Relational Database Model
Content File: Topic 3 Objectives
Topic 3 Objectives
After completing this weeks study students should be able to:
Content File: FIT1004SG3.pdf
Content File: samplerels.pdf
Content File: fit1004l3.pdf
Content File: Week 3 Tutorial - Relational Database Model
Tutorial 3 – The Relational Database Model
Topic 3 Lecture notes.
Rob, P. & Coronel, C. Database Systems: Design, Implementation & Management, 6th Edition, 2004, Chapter 3, Review Questions 1 – 5, 9 & 11.
Rob, P. & Coronel, C. Database Systems: Design, Implementation & Management, 7th Edition, 2006, Chapter 3 Review Questions 1 – 5, 9 & 11
1. What is the difference between a database and a table?
2. What is entity integrity and referential integrity?
3. Why are entity integrity and referential integrity important in a database?
4. A database user manual notes that, “The file contains two hundred records, each one of which contains nine fields.” Use appropriate relational database terminology to “translate” the preceding statement.
5. Use the small database shown in Figure Q3.5 to illustrate the difference between a natural JOIN, an equiJOlN, and an outer JOIN.
9. What are homonyms and synonyms, and why should they be avoided in database design?
11. Identify and describe the components of the table shown in Figure Q3.11, using correct terminology. Use your knowledge of naming conventions to identify the table’s probable foreign key(s).
1. Describe (in your own words) the five integrity constraints specified in the Relational Model Version 2.
2. List the Primary and Foreign key attribute(s) for each table in the following database:
3. Which attributes in the database above enforce Entity integrity?
4. Which attributes in the database above enforce Referential integrity?
5. Which attribute(s) in the database above could enforce column integrity?
6. Why is there not an integrity violation in the TRUCK table for truck 1004?
7. In the above database what would happen if I deleted base 501 and the delete rule between the BASE and TRUCK tables was:
8. In the above database what would happen if I updated base 501 and the update rule between the BASE and TRUCK tables was:
9. When a new row is inserted in the TRUCK table what must be checked?
10. When the TYPE_CODE attribute is updated for truck 1001 what must be checked?
11. When truck 1005 is deleted from the TRUCK table what must be checked?
12. Which integrity constraints should never permit violations and which integrity constraints should provide a violation response.
13. Give examples of possible violation responses.
Practical Issues - Instructional Relational Algebra:
Instructional Relational Algebra (IRA) is an educational tool which allows students to investigate RELATIONAL ALGEBRA commands, the base language of the relational database model. To make clear the differentiation between IRA and a relational database, the environment that IRA operates in is called a DataBase Environment (DBE). Within a DBE which is described (and created) by a schema, relational algebra commands can be tested against the stored data.
Instructional Relational Algebra is available as: ira.zip (please note this is a Windows application)
To install this file:
a. copy this file to a temporary directory and extract the contents to an IRA folder. In the University labs, please extract the files into a folder IRA under the C:\DATA folder. At home I suggest extracting the files to C:\IRA
b. if you are installing at home - do a find on your hard disk (F3) and look for a file called CTL3DV2.DLL. There is a copy in your ira directory and possibly one in your windows or windows\system directory. If you only have a single copy in your IRA directory this file needs to be copied to your c:\windows directory. If there is already a file of this name somewhere in your windows directory DO NOT overwrite it with the IRA copy.
c. delete the file CTL3DV2.DLL in your IRA directory (make sure you do not delete the copy under Windows)
You should then be able to run IRA by selecting the file irawin in your IRA directory.
The initial screen of IRA looks like:
Select Help and then Contents. This will present you with the help system in two screens - a Table of Contents and the Main Help screen. If the two screens are obscuring one another use your mouse to move the top Table of Contents to one side.
The table of contents presents the help topics as chapters of a book:
As you select (and open a chapter) the main help screen will alter to reflect where you are in the help table of contents. You should thoroughly read through the first three chapters - Getting Started, Menu Options and Command Syntax.
If you find that the IRA application help is not sufficient to get you started, a movie is available which shows the basic operation of IRA. To play this AVI which was created by the TechSmith Camtasia product, you will need to install TechSmith's special screen TSCC codec (190K). After installing the TSCC codec, if you have problems playing the movie directly from your browser, I would suggest you right click the appropriate file below (depending on your connection speed), download the file to a local directory and then use your movie player to view the movie.
IRA Tutorial Movie
1.1 IRAWIN Tutorial Task
The schema file for the sample DBE is:
Explanation of this schema:
Using the sample DBE and IRA answer the following questions (record the IRA commands used to achieve the desired result) -
1. display all the customers with a customer number > 000002
2. display the name and city of all customers
3. display the name and city of all customers with a customer number > 000002
4. do a natural join of customer and ordhead
5. show the customer name, order# and orderdate for all orders
6. show the order#, product#, product description and qty ordered for all orders of customer 000001
7. do a natural join of whouse and stock WITHOUT using join. Remember that although many RDBMS vendors implement a join operation, a natural join can be created from a combination of several native operations (see your lecture slides) .To accomplish this you will need to make use of the IRA command
which can be used to rename the attributes of a temporary relation - eg. redefine answer1 [name, address, city] would redefine the attributes in relation answer1 as name, address and city
1.2 IRAWIN Tutorial Task - Task 2 (Enrichment task)
If time permits you should attempt this task - create an IRA Database Environment (DBE) called RENTAL consisting of three relations
building number char(4)
tenant number char(4)
building number char(4)
Add some sample data to the DBE.
To complete this exercise you will need to read the IRA online help: Help - Contents - Getting Started - Creating a new DBE
Using your newDBE, write IRA statements to solve the following (record the IRA commands used to achieve the desired result):
1. List the name and contact address of all tenants
2. List the building number, and lease period for all rented buildings
3. List the tenant name, building address and bond amount for all tenants
Content File: wk03-tut.pdf
Content File: fit1004t3-soln.pdf
Content File: Week 3 IRA Tutorial Solution
Week 3 IRA Tutorial Solution
For the solutions which follow the symbolic form has also been supplied so that you can see how the relational algebra is represented in a pictorial manner.
1.1 IRAWIN Tutorial Task
1. Display all the customers with a customer number > 000002
ANSWER1 = custno > 000002 (CUSTOMER)
a1 = select customer [custno > 000002
2. Display the name and city of all customers
ANSWER2 = cname, city (CUSTOMER)
a2 = project customer [ cname, city
3. Display the name and city of all customers with a customer number > 000002
ANSWER3 = cname, city (custno > 000002 (CUSTOMER))
a3 = project a1 [ cname, city ]
4. Do a natural join of customer and ordhead
ANSWER4 = CUSTOMER ORDHEAD
a4 = join customer [custno] ordhead
5. Show the customer name, order# and orderdate for all orders
ANSWER5 = cname, ordno, odate (CUSTOMER ORDHEAD)
a5 = project a4 [cname, ordno, odate]
6. Show the order#, product#, product description and qty ordered for all orders of customer 000001
ANSWER6 = PRODUCT (ORDLINE ( ordno (custno = 000001 (ORDHEAD))))
a61 = select ordhead [ custno = 000001]
7. do a natural join of whouse and stock WITHOUT using join. Remember that although many RDBMS vendors implement a join operation, a natural join can be created from a combination of several native operations
ANSWER7 (wno, city, prodno, qtyonhand) = whouse.wno, city, prodno, qtyonhand (whouse.wno=stock.wno (WHOUSE STOCK) )
a71=whouse cross stock