IBM DB2® 9.7
Relational database
concepts
Lab
I
Information Management Cloud Computing Center of Competence
IBM Canada Lab
Contents
1. INTRODUCTION ...........................................................................................3
2. OBJECTIVES ................................................................................................3
3. SUGGESTED READING...............................................................................3
4. WORKING WITH ER DIAGRAMS ................................................................3
5. TYPES OF RELATIONSHIPS .......................................................................4
6. MAPPING ENTITIES TO TABLES................................................................5
7. RELATIONAL MODEL CONCEPTS.............................................................5
8. SOLUTIONS..................................................................................................6
WORKING WITH ER DIAGRAMS...............................................................................6
TYPES OF RELATIONSHIPS.....................................................................................7
MAPPING ENTITIES TO TABLES ...............................................................................8
RELATIONAL MODEL CONCEPTS .............................................................................8
2
1. Introduction
In this lab you will practice with relational database concepts. The lab consists of four
parts:
1. Working with ER diagrams
2. Types of relationships
3. Mapping entities to tables
4. Relational model concepts
2. Objectives
By the end of this lab, you will be able to:
Work with ER diagrams
Understand the different types of relationships
Map entities to tables
Identify attributes, degree and cardinality of relations,
3. Suggested reading
Database Fundamentals eBook (Chapters 1  4)
https://www.ibm.com/developerworks/wikis/display/db2oncampus/FREE+ebook+-
+Database+fundamentals
A free eBook to get you started with database concepts and the SQL language.
4. Working with ER diagrams
Given the diagram below:
3
4.1 Draw an ERD for the entity AUTHOR
4.2 Draw an ERD for the entity BORROWER
5. Types of relationships
Given the diagram below:
Write the relationship diagrams for the following 2 scenarios:
5.1 There can be a single copy of a BOOK or there might be several copies
of a book (Hint: Relation to be used - No. of Copies)
4
5.2 Many Books may have many Authors (Hint: Relation to be used 
Authored by)
6. Mapping entities to tables
Given the diagram below:
6.1 Map the entity BORROWER to the table BORROWER
6.2 Map the entity AUTHOR_LIST to the table AUTHOR_LIST
7. Relational model concepts
Review the BOOK relation below and answer the questions:
Relation: BOOK
BOOK_ID TITLE EDITION YEAR PRICE ISBN PAGES AISLE DESCRIPTION
Getting Teaches you the
978-0-
started with DB- essentials of DB2 using
B1 3 2009 24.99 9866283- 280
DB2 Express- A01 DB2 Express-C, the free
5-1
C version of DB2
5
978-0- Teaches you the
Database DB-
B2 1 2010 24.99 9866283- 300 fundamentals of
Fundamentals A02
1-1 databases
Getting 978-0- Teaches you the
DB-
B3 started with 1 2010 24.99 9866283- 298 essentials of DB2
A01
DB2 App Dev 1-2 application development
Teaches you the
Getting 978-0-
DB- essentials of WebSphere
B4 started with 1 2010 24.99 9866283- 278
A01 Application Server
WAS CE 1-3
Community Edition
7.1 Identify the attributes in the relation BOOK.
7.2 What is the degree of this relation?
7.3 What is the cardinality of this relation?
7.4 How many tuples does this relation have?
7.5 Identify the columns in this table BOOK
7.6 How many fields does this table have?
7.7 How many records does this table have?
7.8 Can different rows have different number of columns? How?
7.9 What would be an appropriate data type for the column  Pages ?
7.10 Can you store data of more than one data type in a column?
7.11 Which column(s) would be good candidates for primary keys?
7.12 Would choosing the columns YEAR and PRICE as the composite
primary key be a good idea? Why?
8. Solutions
Working with ER diagrams
4.1 ERD for the entity AUTHOR
6
Lastname
Lastname
Author_Id
Author_Id
Country Author Firstname
Country Author Firstname
City Email
City Email
4.2 ERD for the entity BORROWER
Borrower_ID
Country
Lastname
City BORROWER Firstname
Address
Email
Phone
Types of relationships
5.1 There could be a single copy of a BOOK or there might be several copies of
a book
7
No. of
BOOK
COPY
Copies
5.2 Many Books may have many Authors
Authored By
Book
Author_List
Mapping entities to tables
6.1. TABLE: BORROWER
BORROWER_ID LASTNAME FIRSTNAME EMAIL PHONE ADDRESS CITY COUNTRY
6.2. TABLE: AUTHOR_LIST
AUTHOR_ID BOOK_ID ROLE
Relational model concepts
7.1 Attributes in the relation BOOK are:
a. BOOK_ID
b. TITLE
c. EDITION
d. YEAR
e. PRICE
f. ISBN
g. PAGES
h. AISLE
i. DESCRIPTION
7.2 Degree of the relation BOOK is 9
8
7.3 Cardinality of the relation BOOK is 4
7.4 Four tuples (which is the cardinality)
7.5 Same answer as in 7.1
7.6 Nine fields. A field, column or attribute refer to the same
7.7 Four records. A tuple, row or record refer to the same
7.8 No. If you see a table, you can see all rows must have the same number
of columns.
7.9 The column  Pages stores the number of pages of a book; so INTEGER
is probably a good data type.
7.10 No. A column can only store data of one type.
7.11 BOOK_ID is a good choice as it uniquely identifies a row. ISBN could
also be chosen as a primary key because it also uniquely identifies a row.
7.12 YEAR and PRICE together as a composite primary key (a key consisting
-- in this case -- of two columns) would not be a good choice because
there can be records which can have the same YEAR-PRICE combination
and therefore would not uniquely identify a record.
9
© Copyright IBM Corporation 2011
All Rights Reserved.
IBM Canada
8200 Warden Avenue
Markham, ON
L6G 1C7
Canada
IBM, IBM (logo), and DB2 are trademarks or registered trademarks Information concerning non-IBM products was obtained from the
of International Business Machines Corporation in the United suppliers of those products, their published announcements or
States, other countries, or both. other publicly available sources. IBM has not tested those products
and cannot confirm the accuracy of performance, compatibility or
Linux is a trademark of Linus Torvalds in the United States, other any other claims related to non-IBM products. Questions on the
countries, or both capabilities of non-IBM products should be addressed to the
suppliers of those products.
UNIX is a registered trademark of The Open Group in the United
States, other countries, or both The information in this publication is provided AS IS without
warranty. Such information was obtained from publicly available
Windows is a trademark of Microsoft Corporation in the United sources, is current as of July 2009, and is subject to change. Any
States, other countries, or both. performance data included in the paper was obtained in the specific
operating environment and is provided as an illustration.
VMware is a trademark or VMware Inc. in the United States, other Performance in other operating environments may vary. More
countries, or both. specific information about the capabilities of products described
should be obtained from the suppliers of those products.
Other company, product, or service names may be trademarks or
service marks of others.
References in this publication to IBM products or services do not
imply that IBM intends to make them available in all countries in
which IBM operates. The following paragraph does not apply to the
United Kingdom or any other country where such provisions are
inconsistent with local law:
INTERNATIONAL BUSINESS MACHINES CORPORATION
PROVIDES THIS PUBLICATION "AS IS" WITHOUT WARRANTY
OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING,
BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF NON-
INFRINGEMENT, MERCHANTABILITY OR FITNESS FOR A
PARTICULAR PURPOSE.
Some states do not allow disclaimer of express or implied
warranties in certain transactions, therefore, this statement may not
apply to you.
This information could include technical inaccuracies or
typographical errors. Changes are periodically made to the
information herein; these changes will be incorporated in new
editions of the publication. IBM may make improvements and/or
changes in the product(s) and/or the program(s) described in this
publication at any time without notice.
Any performance data contained herein was determined in a
controlled environment. Therefore, the results obtained in other
operating environments may vary significantly. Some
measurements may have been made on development-level
systems and there is no guarantee that these measurements will be
the same on generally available systems. Furthermore, some
measurement may have been estimated through extrapolation.
Actual results may vary. Users of this document should verify the
applicable data for their specific environment.
10