introduction to database design methodology n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Introduction to Database Design Methodology PowerPoint Presentation
Download Presentation
Introduction to Database Design Methodology

Loading in 2 Seconds...

play fullscreen
1 / 86

Introduction to Database Design Methodology - PowerPoint PPT Presentation


  • 255 Views
  • Uploaded on

Introduction to Database Design Methodology. Chapter 5 Logical Database Design. Learning Goals. To describe database structure in terms of database schema To know the representation of relationships in relational database To transform ER diagram to database schema in relational database.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Introduction to Database Design Methodology' - brody


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
introduction to database design methodology

Introduction to Database Design Methodology

Chapter 5 Logical Database Design

learning goals
Learning Goals
  • To describe database structure in terms of database schema
  • To know the representation of relationships in relational database
  • To transform ER diagram to database schema in relational database
table of contents
Table of Contents
  • Logical design in relational database
  • Transforming ER diagram to relational database schema
    • Transforming entity set and attributes
    • Representing relationship between tables
    • Transforming 1-to-1 relationship
    • Transforming 1-to-M relationship
    • Transforming M-to-N relationship
slide4

Logical Design

in

Relational Database

logical database design
Logical Database Design
  • In relational database, data are stored in table(s).
  • Logical design in relational database is the design of table structures in database.
  • It is the step following the conceptual design of database.
  • Our goal is to transform the ER diagram in conceptual design into table structures in relational database.
logical database design1

All users’ requirements

Conceptual Database Design

Logical Database Design

Database

Logical Database Design
relational database schema

stud_id

name

dob

sex

STUDENT

Relational Database Schema
  • The structure of a relational database is also called the relational database schema.
  • The table structure / schema is usually represented in the following ways:
    • For example, for the table STUDENT:

The schema is represented as

STUDENT (stud_id, name, dob, sex)

OR

relational database schema1
Relational Database Schema

Note:

  • The primary key is underlined.
  • For simplicity, we have not listed out the data type of each field in representing the table structure.
  • A more detailed representation including the data type of each field is:

STUDENT ( stud_id:char(5), name:char(20), dob:date, sex:char(1) )

slide9

Transforming ER Diagram

to

Relational Database Schema

er diagram and relational database
ER Diagram and Relational Database
  • Corresponding items in entity-relationship diagram and relational database:
er diagram and relational database1
ER Diagram and Relational Database

Entity Set

Attribute Attribute value

stud_id = “s0012”

name = “Chan Tai Man”

dob = #1997/1/1#

sex = “M”

Entity

Attribute Attribute value

stud_id = “s0022”

name = “Wong May May”

dob = #1998/2/5#

sex = “F”

table

fields

record

slide12

Transforming

Entity Set and Attributes

transforming entity set and attributes

name

dob

stud_id

sex

STUDENT

stud_id

name

dob

sex

STUDENT

Transforming Entity Set and Attributes

Example

ER Diagram:

Schema: STUDENT (stud_id, name, dob, sex)

OR

task 1

book_title

author

book_id

publisher

BOOK

name

major

teach_id

sex

TEACHER

Task 1

Construct the table schemas for the following two entities

transforming relationships

Relationship

Entity

Entity

Transforming Relationships
  • How to transform relationships in ER diagram into relational database schema?
  • To answer this question, we should first understand how to represent relationship between tables in relational database.
  • The idea of representing relationship between tables is just similar to the case of designing “matching” exercises.

?

task 2
Suppose we want to design an exercise on “matching” the items on the left-hand-side to the corresponding items on the right-hand-side.

How to represent the matching/association between items?

Task 2
task 21
Task 2

Method 1: Joining items by lines. Please try.

task 22
Task 2

Method 2: Fill in the blanks on the left table. Please try.

task 23
Task 2

Method 3: Fill in the blanks on the right table. Please try.

task 24
Task 2

Method 4: Fill in the blanks on the “middle” table. Please try.

representing relationship between tables
Representing Relationship between Tables

In the above discussion,

  • Method 1 is just the graphical representation of relationship between tables.
representing relationship between tables1
Representing Relationship between Tables
  • In Method 2,the relationship is set up by adding a column of primary key of the right table to the left table. We call this new field the foreign key of the left table.
  • A foreign key is a field (or a composite of fields) in one table that matches a primary key (may be composite) in another table.
  • Relationship between tables is set up by relating the foreign key in one table to the primary key in the other table.

foreign key

representing relationship between tables2
Representing Relationship between Tables
  • Method 3 is just similar to Method 2. This time, the primary key of the left table is added to the right table as foreign key.

foreign key

representing relationship between tables3
Representing Relationship between Tables
  • In Method 4, a new table is created with the columns of primary keys from the other two tables as foreign keys.
transforming relationships1
Transforming Relationships
  • Depending on the different types of binary relationships in ER diagram, there are different methods to transform the relationships into the corresponding table structures.
  • The basic approach is to add appropriate foreign key(s) in appropriate table. The relationships between tables are set up by relating the foreign key in one table to the primary key in the other table.
  • The transformation methods can be classified into the following cases:
    • Transforming 1-to-1 binary relationship
    • Transforming 1-to-M binary relationship
    • Transforming M-to-N binary relationship
slide29

Transforming

1-to-1 Relationship

transforming 1 to 1 relationship

1

1

Relationship

A

B

Transforming 1-to-1 Relationship

Given a one-to-one binary relationship between two entity sets A and B.

Steps:

  • Construct table schemas of the two entity sets.
  • Choose one of entity set, say A.
    • If possible, choose the one in which each entity must be associated with one entity in the other entity set.
    • Add the primary key from the other schema (B) as a foreign key in schema of A.
  • Include the attribute(s) (if any) of the relationship as attribute(s) of schema of A.
transforming 1 to 1 relationship1

club_id

club_name

name

dob

Leads

1

1

stud_id

STUDENT

CLUB

sex

Transforming 1-to-1 Relationship

Example

  • Student leads club in a school

Original tables:

STUDENT

CLUB

transforming 1 to 1 relationship2

club_id

club_name

name

dob

Leads

1

1

STUDENT

CLUB

stud_id

sex

Transforming 1-to-1 Relationship

Example (con’t)

  • 1. Construct table schemas of the two entity sets.
    • Schema: STUDENT (stud_id, name, dob, sex)
      • CLUB (club_id, club_name)
transforming 1 to 1 relationship3

club_id

club_name

Leads

1

1

name

dob

STUDENT

CLUB

stud_id

sex

Transforming 1-to-1 Relationship

Example (con’t)

  • Choose entity set (CLUB) in which each entity should be associated with one entity in the other entity set. Add the primary key from the other schema as a foreign key in this schema (CLUB).
    • Schema: STUDENT (stud_id, name, dob, sex)
      • CLUB (club_id, club_name,stud_id)

foreign key

transforming 1 to 1 relationship4

club_id

club_name

name

dob

Leads

1

1

STUDENT

CLUB

stud_id

sex

stud_id

name

dob

sex

STUDENT

club_id

club_name

stud_id

CLUB

Transforming 1-to-1 Relationship

Example (con’t)

Schema (Graphically):

transforming 1 to 1 relationship5
Transforming 1-to-1 Relationship

Example (con’t)

Schema: STUDENT (stud_id, name, dob, sex)

CLUB (club_id, club_name,stud_id)

Resulting tables:

CLUB

STUDENT

primary key of CLUB

primary key of STUDENT

foreign key of CLUB

transforming 1 to 1 relationship6

Leads

1

1

STUDENT

CLUB

Transforming 1-to-1 Relationship

Discussion

Compare the following two choices of adding foreign key. Which one is better?Explain briefly.

Schema 1:

STUDENT (stud_id, name, dob, sex)

CLUB (club_id, club_name,stud_id)

foreign key

Schema 2:

STUDENT (stud_id, name, dob, sex, club_id)

CLUB (club_id, club_name)

transforming 1 to 1 relationship7
Transforming 1-to-1 Relationship

Discussion - Answer:

Schema 1:

STUDENT (stud_id, name, dob, sex)

CLUB (club_id, club_name,stud_id)

STUDENT

CLUB

  • No null item in the foreign key field.
  • Each club must be led by one student
transforming 1 to 1 relationship8
Transforming 1-to-1 Relationship

Discussion - Answer (con’t):

Schema 2:

STUDENT (stud_id, name, dob, sex, club_id)

CLUB (club_id, club_name)

STUDENT

CLUB

  • Some records have null values in the foreign key field.
  • Some students may not lead any club.

Therefore, schema 1 is better (why?).

task 3
A teacher may lead a class (as a form master).

Transform the above ER diagram into database schema.

sex

class_id

major

1

1

Leads

teach_id

CLASS

TEACHER

cls_name

name

Task 3
slide41

Transforming

1-to-M Relationship

transforming 1 to m relationship

Relationship

1

M

A

B

Transforming 1-to-M Relationship

Given a one-to-many binary relationship between two entity sets A and B.

Steps:

  • Construct table schemas of the two entity sets A and B.
  • Add the primary key of A (the “one” side) as a foreign key of B (the “many” side).
  • Include the attribute(s) (if any) of the relationship as attribute(s) of B.
transforming 1 to m relationship1

name

dob

stud_id

class_id

cls_name

sex

room

1

M

Has

STUDENT

CLASS

Transforming 1-to-M Relationship

Example

  • A class has many students

Original tables:

STUDENT

CLASS

transforming 1 to m relationship2

name

dob

stud_id

class_id

cls_name

sex

room

1

M

Has

STUDENT

CLASS

Transforming 1-to-M Relationship

Example (con’t)

  • 1. Construct table schemas of the two entity sets.
    • Schema:
    • CLASS (class_id, cls_name, room)
    • STUDENT (stud_id, name, dob, sex)
transforming 1 to m relationship3

name

dob

stud_id

class_id

cls_name

sex

room

1

M

Has

STUDENT

CLASS

Transforming 1-to-M Relationship

Example (con’t)

  • Add the primary key of CLASS as a foreign key in STUDENT.
    • Schema:
    • CLASS (class_id, cls_name, room)
    • STUDENT (stud_id, name, dob, sex, class_id)

foreign key

transforming 1 to m relationship4

name

dob

stud_id

class_id

cls_name

sex

room

1

M

Has

STUDENT

CLASS

stud_id

name

dob

sex

STUDENT

class_id

cls_name

room

CLASS

class_id

Transforming 1-to-M Relationship

Example (con’t)

Schema (Graphically):

transforming 1 to m relationship5
Transforming 1-to-M Relationship

Example (con’t)

Schema: CLASS (class_id, cls_name, room)

STUDENT (stud_id, name, dob, sex, class_id)

Resulting tables:

STUDENT

CLASS

primary key of STUDENT

primary key of CLASS

foreign key of STUDENT

transforming 1 to m relationship6

Has

M

1

CLASS

STUDENT

Transforming 1-to-M Relationship

Discussion

Compare the following two choices of adding foreign key. Which one is better?Explain briefly.

Schema 1:

CLASS (class_id, cls_name, room)

STUDENT (stud_id, name, dob, sex, class_id)

foreign key

Schema 2:

CLASS (class_id, cls_name, room, stud_id)

STUDENT (stud_id, name, dob, sex)

transforming 1 to m relationship7
Transforming 1-to-M Relationship

Discussion - Answer

Schema 1:

CLASS (class_id, cls_name, room)

STUDENT (stud_id, name, dob, sex, class_id)

CLASS

STUDENT

No duplication of data, except the foreign key values.

transforming 1 to m relationship8
Transforming 1-to-M Relationship

Dissussion - Answer (Con’t)

Schema 2:

CLASS (class_id, cls_name, room, stud_id)

STUDENT (stud_id, name, dob, sex)

STUDENT

CLASS

  • Some records with duplicate data in cls_name and room.
  • A class can contain many students

Therefore, schema 1 is better (why?).

task 4
EMPLOYEE works in DEPARTMENT

Each DEPARTMENT is managed by one EMPLOYEE

Transform the above ER diagram into database schema.

phone_no

emp_id

address

dep_id

dep_name

name

1

M

Works_in

EMPLOYEE

DEPARTMENT

M

1

1

Manages

Task 4
slide53

Transforming

M-to-N Relationship

transforming m to n relationship

Relationship

M

N

A

B

Transforming M-to-N Relationship

Given a many-to-many binary relationship between two entity sets A and B.

Method 1

Steps:

  • Construct table schemas of the two entity sets A and B.
  • Create a new table schema, say R, to represent the relationship between A and B.
  • Add the primary key of A and primary key of B as foreign keys in R. The combination of foreign keys may be the primary key of R.
  • Include the attribute(s) (if any) of the relationship as attribute(s) of table R.
transforming m to n relationship1

Relationship

Relationship

1

B

M

N

A

1

R

Relationship

M

N

A

B

Transforming M-to-N Relationship

Method 2

Steps:

  • Resolve the many-to-many relationship into multiple one-to-many relationships.
  • Transform all the one-to-many relationships into table schemas by using the method in previous section.
transforming m to n relationship2
Transforming M-to-N Relationship

Remark:

  • Both methods involve constructing new table schemas in addition to the original entity sets. You may wonder why it is necessary to do so for M-to-N relationship. We will discuss this at the end of this section.
  • The table schemas constructed by Method 1 and Method 2 should be the same.
transforming m to n relationship3

name

dob

club_name

club_id

sex

stud_id

Joins

N

M

CLUB

STUDENT

Transforming M-to-N Relationship

Example

  • Students may join many clubs.

Original tables:

STUDENT

CLUB

transforming m to n relationship4

name

dob

club_name

club_id

sex

stud_id

Joins

N

M

CLUB

STUDENT

Transforming M-to-N Relationship

Example - Method 1

  • 1. Construct table schemas of the two entity sets.
    • Schema:
    • STUDENT (stud_id, name, dob, sex)
      • CLUB (club_id, club_name)
transforming m to n relationship5
Transforming M-to-N Relationship

Example - Method 1 (con’t)

  • Create a new table schema, say MEMBERSHIP, to represent the relationship between STUDENT and CLUB.
  • Add the primary key of STUDENT and primary key of CLUB as foreign keys of MEMBERSHIP. The combination of foreign keys can be treated as the primary key of MEMBERSHIP.

Schema:

STUDENT (stud_id, name, dob, sex)

CLUB (club_id, club_name)

MEMBERSHIP ( stud_id, club_id)

Create a new table for the relationship

primary key

foreign key

foreign key

transforming m to n relationship6

name

dob

club_name

club_id

sex

stud_id

Joins

N

M

CLUB

STUDENT

stud_id

name

dob

sex

STUDENT

club_id

club_name

CLUB

stud_id

club_id

MEMBERSHIP

Transforming M-to-N Relationship

Example - Method 1 (con’t)

Graphically:

transforming m to n relationship7

name

dob

club_name

club_id

sex

stud_id

Joins

N

M

CLUB

STUDENT

name

dob

stud_id

club_name

sex

club_id

STUDENT

stud_id

club_id

CLUB

Belongs_to

M

N

Joins

1

1

MEMBERSHIP

Transforming M-to-N Relationship

Example - Method 2

  • Resolve the many-to-many relationship into multiple one-to-many relationships.

One-to-many relationship

One-to-many relationship

transforming m to n relationship8

name

dob

stud_id

club_name

sex

club_id

STUDENT

stud_id

club_id

CLUB

Belongs_to

M

N

Joins

1

1

MEMBERSHIP

Transforming M-to-N Relationship

Interpretation of the above ER diagram.

  • Each student can join the membership of many clubs, or none of the club.
  • Each membership should be associated with exactly one student.
  • Each membership should belong to exactly one club.
  • Each club may have many memberships (members), but should have at least one membership (member).
transforming m to n relationship9
Transforming M-to-N Relationship

Example - Method 2 (con’t)

  • Transform all the one-to-many relationships into table schemas.
    • It is not necessary to add the primary key of STUDENT or CLUB as the foreign key of MEMBERSHIP, as the MEMBERSHIP entity set already has the attributes stud_id and club_id.

Schema:

STUDENT (stud_id, name, dob, sex)

CLUB (club_id, club_name)

MEMBERSHIP ( stud_id, club_id)

primary key

foreign key

foreign key

transforming m to n relationship10

name

dob

stud_id

club_name

sex

club_id

stud_id

name

dob

sex

STUDENT

STUDENT

stud_id

club_id

CLUB

Belongs_to

M

N

Joins

1

1

MEMBERSHIP

club_id

club_name

CLUB

stud_id

club_id

MEMBERSHIP

Transforming M-to-N Relationship

Example - Method 2 (con’t)

Graphically:

transforming m to n relationship11
Transforming M-to-N Relationship
  • Schema: STUDENT (stud_id, name, dob, sex)
        • CLUB (club_id, club_name)
        • MEMBERSHIP (stud_id, club_id)

Resulting tables:

CLUB

STUDENT

MEMBERSHIP

transforming m to n relationship12
Transforming M-to-N Relationship

Discussion

Compare the following three cases of constructing database schema for the above many-to-many relationship. Which one is the best?Explain briefly.

Schema 1 : STUDENT (stud_id, name, dob, sex, club_id)

CLUB (club_id, club_name)

Schema 2 : STUDENT (stud_id, name, dob, sex)

CLUB (club_id, club_name, stud_id)

  • Schema 3 : STUDENT (stud_id, name, dob, sex)
        • CLUB (club_id, club_name)
        • MEMBERSHIP (stud_id, club_id)
transforming m to n relationship13
Transforming M-to-N Relationship

Discussion - Answer

Schema 1 (without creating a new table):

STUDENT (stud_id, name, dob, sex, club_id)

CLUB (club_id, club_name)

STUDENT

CLUB

Some records with duplicate data.

transforming m to n relationship14
Transforming M-to-N Relationship

Discussion – Answer (con’t)

Schema 2 (without creating a new table):

STUDENT (stud_id, name, dob, sex)

CLUB (club_id, club_name, stud_id)

STUDENT

CLUB

Some records with duplicate data.

transforming m to n relationship15
Transforming M-to-N Relationship

Discussion – Answer (con’t)

Schema 3 (creating a new table) :

STUDENT (stud_id, name, dob, sex)

CLUB (club_id, club_name)

MEMBERSHIP (stud_id, club_id)

CLUB

STUDENT

MEMBERSHIP

No duplicate data in records.

No duplicate data in records.

transforming m to n relationship16
Transforming M-to-N Relationship

Discussion – Conclusion

As observed from the above tables, it seems that schema 3 is the best. (Why?)

task 5
STUDENT borrows BOOK from school library

Resolve the above many-to-many relationship into multiple one-to-many relationships.

Transform the resulting ER diagram in into database schema.

dob

checkout_date

due_

date

sex

book_id

title

stud_id

Borrows

M

N

STUDENT

BOOK

name

author

description

Task 5
example

sex

club_id

name

Joins

N

M

stud_id

CLUB

STUDENT

Leads

1

1

club_name

dob

Example

Example

  • A student may join many clubs, a student may also lead club. A club must be led by one and only one student.

Schema:

STUDENT (stud_id, name, dob, sex)

CLUB (club_id, club_name,stud_id)

STUD_CLUB (stud_id, club_id)

example1

sex

club_id

name

Joins

N

M

stud_id

CLUB

STUDENT

Leads

1

1

stud_id

name

dob

sex

STUDENT

club_name

dob

club_id

club_name

stud_id

CLUB

stud_id

club_id

STUD_CLUB

Example

Example

Graphically:

example2

M

N

address

phone

date

quantity

prod_id

name

cust_id

Purchases

CUSTOMER

PRODUCT

name

description

Example
  • CUSTOMER purchases PRODUCT

Schema:

CUSTOMER (cust_id, name, address, phone)

PRODUCT (prod_id, name,description)

PURCHASE (cust_id, prod_id, quantity, date)

example3

address

phone

date

quantity

prod_id

name

cust_id

Purchases

M

N

CUSTOMER

PRODUCT

name

description

CUSTOMER

cust_id

name

address

phone

prod_id

name

description

PRODUCT

cust_id

prod_id

quantity

date

PURCHASE

Example
  • CUSTOMER purchases PRODUCT

Graphically:

example4

class_id

sex

cls_name

major

stud_id

name

N

M

Teaches

1

M

teach_id

Has

STUDENT

TEACHER

CLASS

1

1

Leads

dob

name

sex

Example
  • TEACHER, CLASS and STUDENT

Schema:

TEACHER (teach_id, name, major, sex)

CLASS (class_id, cls_name, teach_id)

STUDENT (stud_id, name, sex, dob, class_id)

TEACH_CLASS (teach_id, class_id)

example5

sex

name

Participates

M

N

event_id

stud_id

STUDENT

EVENT

M

N

Helps

description

dob

Example
  • STUDENT participates in sport day EVENT
  • STUDENT helps in sport dayEVENT

Schema:

STUDENT (stud_id, name, sex, dob)

EVENT (event_id, description)

PARTICIPATE (stud_id, event_id)

HELPER (stud_id, event_id)

database schema construction
Database Schema Construction
  • Try to doActivity 2 of Chapter 5
case study
Case Study
  • Read Section 4.3 of Case Study
online exercises
Online Exercises
  • Go to the following website:

http://adbc.kennesaw.edu/

  • Click Database Design > ER to Tables
  • There are some online exercises for practice in setting up associations between tables.
references
References
  • http://en.wikipedia.org/wiki/Entity-relationship_model
  • http://www.csc.lsu.edu/~chen/chen.html
  • http://cs-exhibitions.uni-klu.ac.at/index.php?id=431
  • http://en.wikipedia.org/wiki/Peter_Chen
  • http://adbc.kennesaw.edu/
  • Peter Rob, Carlos Coronel, Database Systems – Design, Implementation, & Management, 5th Ed., Thomson Learning, Chapter 3.
  • Abraham Silberschatz, Henry F. Korth, S. Sudarshan, Database System Concepts, 5th Ed., Mc Graw Hill, Chapter 6.
  • Ramez Elmasri, Shamkant B. Navathe, Fundamentals of Database Systems, 5th Ed., Pearson Addison Wesley