Modelo Relacional

IBM DB2® 9.7
Relational database concepts Lab

I

Information Management Cloud Computing Center of Competence IBM Canada Lab

Contents
1. 2. 3. 4. 5. 6. 7. 8. INTRODUCTION ...........................................................................................3 OBJECTIVES ................................................................................................3 SUGGESTEDREADING...............................................................................3 WORKING WITH ER DIAGRAMS ................................................................3 TYPES OF RELATIONSHIPS .......................................................................4 MAPPING ENTITIES TO TABLES................................................................5 RELATIONAL MODELCONCEPTS.............................................................5 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 copyof 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 6.2

Map the entity BORROWER to the table BORROWER Map the entity AUTHOR_LIST to the table AUTHOR_LIST

7.

Relational model conceptsReview the BOOK relation below and answer the questions: Relation: BOOK
BOOK_ID B1 TITLE Getting started with DB2 ExpressC EDITION 3 YEAR 2009 PRICE 24.99 ISBN 978-098662835-1 PAGES 280 AISLE DBA01 DESCRIPTION Teaches you the essentials of DB2 using DB2 Express-C, the free version of DB2

5

B2

Database Fundamentals Getting started with DB2 App Dev Getting started with WAS CE

12010

24.99

B3

1

2010

24.99

978-098662831-1 978-098662831-2 978-098662831-3

300

DBA02 DBA01 DBA01

298

B4

1

2010

24.99

278

Teaches you the fundamentals of databases Teaches you the essentials of DB2 application development Teaches you the essentials of WebSphere Application Server Community Edition

7.1 7.2 7.3 7.4 7.5 7.6 7.7 7.8 7.9 7.10 7.11 7.12Identify the attributes in the relation BOOK. What is the degree of this relation? What is the cardinality of this relation? How many tuples does this relation have? Identify the columns in this table BOOK How many fields does this table have? How many records does this table have? Can different rows have different number of columns? How? What would be an appropriate data type for the column...