IBM DB2® 9.7

Relational database concepts Lab

I

Information Management Cloud Computing Center of Competence IBM Canada Lab

2

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

3

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:

4

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)

5

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

B1

Getting started with

DB2 Express-C

3 2009 24.99 978-0-

9866283-5-1

280 DB-A01

Teaches you the essentials of DB2 using DB2 Express-C, the free

version of DB2

6

B2 Database Fundamentals 1 2010 24.99

978-0-9866283-

1-1 300 DB-

A02

Teaches you the fundamentals of

databases

B3 Getting

started with DB2 App Dev

1 2010 24.99 978-0-

9866283-1-2

298 DB-A01

Teaches you the essentials of DB2

application development

B4 Getting

started with WAS CE

1 2010 24.99 978-0-

9866283-1-3

278 DB-A01

Teaches you the essentials of WebSphere

Application Server 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

7

Author

Author_Id

City

Lastname

Firstname

Email

Country Author

Author_Id

City

Lastname

Firstname

Email

Country

4.2 ERD for the entity BORROWER

BORROWER

Borrower_ID

Phone

Lastname

Firstname

EmailAddress

City

Country

Types of relationships 5.1 There could be a single copy of a BOOK or there might be several copies of

a book

8

BOOK COPYNo. ofCopies

5.2 Many Books may have many Authors

Book Author_ListAuthored By

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

9

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.

10

© 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 of International Business Machines Corporation in the United States, other countries, or both. Linux is a trademark of Linus Torvalds in the United States, other countries, or both UNIX is a registered trademark of The Open Group in the United States, other countries, or both Windows is a trademark of Microsoft Corporation in the United States, other countries, or both. VMware is a trademark or VMware Inc. in the United States, other countries, or both. 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.

Information concerning non-IBM products was obtained from the suppliers of those products, their published announcements or other publicly available sources. IBM has not tested those products and cannot confirm the accuracy of performance, compatibility or any other claims related to non-IBM products. Questions on the capabilities of non-IBM products should be addressed to the suppliers of those products. The information in this publication is provided AS IS without warranty. Such information was obtained from publicly available sources, is current as of July 2009, and is subject to change. Any performance data included in the paper was obtained in the specific operating environment and is provided as an illustration. Performance in other operating environments may vary. More specific information about the capabilities of products described should be obtained from the suppliers of those products.