Compiled Contents of:Week 3 The Relational Database Model

 

Content File: Topic 3 Objectives


Topic 3 Objectives

Topic 3 Objectives

After completing this weeks study students should be able to:

  1. Explain the features of the Relational Model
    • Components of a relation
    • Properties of a relation
    • Null values, and
    • Relational Operators
  2. Make use of a subset of the relational operators to solve data queries using symbolic notation:
    • select
    • project, and
    • join
  3. Appreciate that the relational database model takes a logical view of data and how data redundancy is dealt with
  4. Describe the basic relational database components
    • Entities
    • Attributes
    • Relationships amongst entities
    • Integrity constraints, and
    • Data Dictionary
  5. Describe the relational table's components and characteristics and contrast the table with a relation
  6. Explain how keys are used in the relational database environment
    • candidate keys
    • primary keys
    • alternate keys
    • foreign keys, and
    • secondary keys
  7. Describe the role of an index in the relational database model

Topic 3 - Where are We

 

 

Content File: FIT1004SG3.pdf


FIT1004SG3.pdf
 

Content File: samplerels.pdf


samplerels.pdf
 

Content File: fit1004l3.pdf


fit1004l3.pdf
 

Content File: Week 3 Tutorial - Relational Database Model


Week 3 Tutorial - Relational Database Model

Tutorial 3 – The Relational Database Model

References:

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

Review Questions:

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).



Additional Questions

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:

i) DELETE RESTRICT

ii) DELETE CASCADE

iii) DELETE SET NULL

iv) DELETE SET DEFAULT (default value is 99)

8. In the above database what would happen if I updated base 501 and the update rule between the BASE and TRUCK tables was:

i) UPDATE RESTRICT

ii) UPDATE CASCADE

iii) UPDATE SET NULL

iv) UPDATE SET DEFAULT (default value is 99)

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.

Installing IRA

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.

Using IRA

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:

custno 6
person_name 18
town_name 12
prodno 6
prod_desc 11
ordno 6
date 8
quantity 4
wno 4
%%
customer 3
custno custno P
cname person_name N
city town_name N
ordhead 3
ordno ordno P
custno custno N
odate date N
product 2
prodno prodno P
description prod_desc N
whouse 2
wno wno P
city town_name N
ordline 3
ordno ordno P
prodno prodno P
qtyord quantity N
stock 3
wno wno P
prodno prodno P
qtyonhand quantity N

Explanation of this schema:

  • The first section of this IRA Schema file declares the domains which will be used and the size of each domain (all domains are text data type only). For example the first line declares a domain called custno which is 6 characters long. After all domains have been declared the end of this section of the schema is signalled by the separator %%
  • The second section of the schema declares the actual relations - each declaration begins with a relation name and then has listed the attributes of the relation, their selected domain and if the attribute is a primary key or not

For example:

customer 3
custno custno P
cname person_name N
city town_name N

declares a relation called customer which is made up of three attributes

  • custno, drawn from the custno domain and the relations primary key,
  • cname drawn form the person_name domain and not part of the relations primary key, and
  • city drawn from the town_name domain and not part of the relations primary key.

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

REDEFINE <relname> [ <attribute> ... ]

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

building number char(4)
building address char(20)
building value max value 999999

TENANT

tenant number char(4)
tenant name char(20)
tenant contact address char(20)

RENT

building number char(4)
tenant number char(4)
lease period months 99
bond amount max value 999999

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

Week 3 tutorial as a PDF file

 

Content File: wk03-tut.pdf


wk03-tut.pdf
 

Content File: fit1004t3-soln.pdf


fit1004t3-soln.pdf
 

Content File: Week 3 IRA Tutorial Solution


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

Symbolic:

ANSWER1 = custno > 000002 (CUSTOMER)

IRA:

a1 = select customer [custno > 000002 ]
display a1

2. Display the name and city of all customers

Symbolic:

ANSWER2 = cname, city (CUSTOMER)

IRA:

a2 = project customer [ cname, city ]
display a2

3. Display the name and city of all customers with a customer number > 000002

Symbolic:

ANSWER3 = cname, city (custno > 000002 (CUSTOMER))

IRA:

a3 = project a1 [ cname, city ]
display a3

4. Do a natural join of customer and ordhead

Symbolic:

ANSWER4 = CUSTOMER ORDHEAD

IRA:

a4 = join customer [custno] ordhead
display a4

5. Show the customer name, order# and orderdate for all orders

Symbolic:

ANSWER5 = cname, ordno, odate (CUSTOMER ORDHEAD)

IRA:

a5 = project a4 [cname, ordno, odate]
display a5

6. Show the order#, product#, product description and qty ordered for all orders of customer 000001

Symbolic:

ANSWER6 = PRODUCT (ORDLINE ( ordno (custno = 000001 (ORDHEAD))))

IRA:

a61 = select ordhead [ custno = 000001]
a62 = project a61 [ordno]
a63 = join a62 [ordno] ordline
a64 = join a63 [prodno] product
display a64

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

Symbolic:

ANSWER7 (wno, city, prodno, qtyonhand) = whouse.wno, city, prodno, qtyonhand (whouse.wno=stock.wno (WHOUSE STOCK) )

IRA:

a71=whouse cross stock
redefine a71 [wno, city, wnum, prodno, qtyonhand]
a72 = select a71 [wno = wnum]
a73 = project a72 [wno, city, prodno, qtyonhand]
display a72