Introduction to database design methodology
This presentation is the property of its rightful owner.
Sponsored Links
1 / 54

Introduction to Database Design Methodology PowerPoint PPT Presentation


  • 42 Views
  • Uploaded on
  • Presentation posted in: General

Introduction to Database Design Methodology. Chapter 3 Basic Concepts of Entity-Relationship Model. Learning Goals. To describe the basic stages in database design To understand the basic concepts of entity-relationship model in conceptual database design

Download Presentation

Introduction to Database Design Methodology

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 3 Basic Concepts of Entity-Relationship Model


Learning goals

Learning Goals

  • To describe the basic stages in database design

  • To understand the basic concepts of entity-relationship model in conceptual database design

  • To identify the entities, attributes and relationships in simple scenarios


Table of contents

Table of Contents

  • Overview of Database Design

  • Entity-Relationship Model

    • Entity

    • Entity Set

    • Attribute

    • Relationship


Introduction to database design methodology

Overview of

Database Design


Scenario of an online bookstore

Scenario of an Online Bookstore

Consider the following scenario

  • The owner of an online bookstore wants to create an information system for online sale of books. People will be allowed to register as customers of the online website. Customer information like customers’ correspondence addresses, e-mail addresses, and credit card numbers are to be maintained. In a single order, customers will be allowed to purchase any quantity of books. Items in a single order will be shipped to the customer.


Scenario of an online bookstore1

Scenario of an Online Bookstore

Problem:

  • How to design a database system that can satisfy the needs of the above online bookstore?


Scenario of an online bookstore2

Scenario of an Online Bookstore

  • The above description may be considered as part of the user requirements of an online bookstore.

  • User requirements are usually collected by some fact-finding techniques, such as, examiningdocumentation, interviewing, observations, questionnaires, research, etc. We will not study such techniques in this part of the course.

  • Our main objective is to design a well-structured database based on the user requirements collected.


Database design

Database

Database Design

Logical database structure

Design the Database

User_1’s requirements

How?

All users’ requirements

User_2’s requirements

User_n’s requirements


Database design1

Database Design

  • A systematic approach to database design consists of three main stages:

    • Conceptual database design

    • Logical database design

    • Physical database design


Database design2

All users’ requirements

Conceptual Database Design

Logical Database Design

Database

Physical Database Design / Implementation

Database Design


Database design3

Database Design

  • Conceptual database design

    • To build the conceptual model of the database. This includes identification of the main data objects and relationships among the objects in the system.

    • We will study the conceptual design in this and the next chapter.

  • Logical database design

    • To translate the conceptual model into the logical structure of the database. This includes designing the table structures in relational database.

    • We will study the logical design in Chapter 5.

  • Physical database design

    • To design how the logical structure is to be physically implemented in the target Database Management System (DBMS).


Database design4

Database Design

  • Referring to the three levels of data abstraction in database (Chapter 2)

    • Conceptual and logical designs correspond to the design of logical structure of the database in the conceptual level.

    • Physical design corresponds to the design of physical implementation of the database in the physical level.


Database design5

Database Design

View Level

Conceptual Design

Conceptual Level

Logical Design

Physical Level

Physical Design


Database design6

Database Design

  • We will only study the conceptual and logical database designs in this part of the course.

  • You will learn

    • Conceptual database design in the rest of this chapter and in Chapter 4

    • Logical database design in Chapter 5


Introduction to database design methodology

Entity-Relationship

Model


Conceptual database design

Conceptual Database Design

  • The main objective of conceptual database design is to design the conceptual model of a database.

  • A conceptual model is a representation of the main data objects and relationships among the objects in a system (e.g. a school library system).

  • The most widely used conceptual model in database design is the entity-relationship (ER) model.

  • ER model is usually represented graphically in the form of an entity-relationship (ER) diagram, which will be introduced in Chapter 4.


Entity relationship model

Entity-Relationship Model

  • Entity-Relationship (ER) model views the real world as a collection of entities and relationships among entities.

  • Before we start to design the ER model of a database system, let us study some basic terminologies in ER model.

  • Remark: You may have learnt the concepts of ER model in the other topic of the Databases option. As these concepts are very important in conceptual database design, let us review these concepts before we study the design methodology.


Entity relationship model1

Entity-Relationship Model

  • The basis terminologies used in ER model are:

    • Entity

    • Entity Set

    • Attribute

    • Relationship


Entity relationship model2

Entity-Relationship Model

  • Entity

    • An entity is a real-world object that is distinguishable from other objects.

    • Entities may be physical (e.g. a student) or non-physical (e.g. an event in sport day, an exam).

    • Some examples of entities are: a student, a teacher, a book, an employee, an examination, etc.

    • In designing database, we should consider those entities that are of interest to us, i.e. those related to the user requirements.


Entity relationship model3

Entity-Relationship Model

I’m also an entity

I’m an entity

Exam

I’m an entity

I’m an entity

I’m an entity

I’m an entity

I’m an entity


Entity relationship model4

Entity-Relationship Model

  • Attribute

    • An attribute describes a particular characteristic of an entity.

    • Each entity has a set of attributes.

    • The attributes represent the data that we want to keep about each entity.

    • For example,

      • stud_id, name, dob, sex are possible attributes of a student;

      • ISBN, title, author, publisher are possible attributes of a book.


Entity relationship model5

stud_id

name

dob

sex

Entity-Relationship Model

Attributes of a student

Entity: a student


Entity relationship model6

Attributes of a book

title

author

ISBN

publisher

Entity: a book

Entity-Relationship Model


Entity relationship model7

Entity-Relationship Model

  • Domain of Attribute

    • An attribute can only take certain values. The set of all possible values that can be taken by an attribute is known as the domain of an attribute.

    • Remark: domain of an attribute can be considered as the data type of an attribute. Some examples of data type are: character (string), integer, real number, date, etc.


Entity relationship model8

Entity-Relationship Model

  • Domain of Attribute

    • For example, the entity STUDENT has the following attributes with the corresponding domains / data types.


Entity relationship model9

Entity-Relationship Model

Attribute Attribute value

stud_id = “s0012”

name = “Chan Tai Man”

dob = #1997/1/1#

sex = “M”

Attribute Attribute value

stud_id = “s0022”

name = “Wong May May”

dob = #1998/2/5#

sex = “F”

Same type of entity


Entity relationship model10

Entity-Relationship Model

AttributeAttribute value

ISBN = “0-021-74387-6”

title = “Database System”

author = “Chan TM”

publisher = “EA Education Co.”

Attribute Attribute value

ISBN = “0-07-111180-8”

title = “Network System”

author = “Peter Chan”

publisher = “AB Publication Co.”

Same type of entity


Entity relationship model11

Entity-Relationship Model

  • Entity Set

    • An entity set is a collection of similar entities, i.e. entities sharing the same types of attributes.

    • For example, a set of all students in a school is an entity set STUDENT. Here STUDENT is a name given to the entity set of all students in the school.

    • Remark: In database design, sometimes an “entity” and an “entity set” may be used interchangeably. For example, “an entity STUDENT” may sometimes refer to “an entity set STUDENT”.


Entity relationship model12

Entity-Relationship Model

Entity set: STUDENT

  • Entity Set

Entity set: BOOK

Entity set: TEACHER


Entity relationship model13

Entity-Relationship Model

Entity Set: STUDENT

stud_id

name

Student 1

Student 2

Student 3

Student 4

Student 5

Student 6

dob

sex

Student 7

Entities in the same entity set share the same types of attributes.


Entity relationship model14

Entity-Relationship Model

  • Primary Key

    • A primary key is a minimal set of attributes whose values uniquely identify an entity in an entity set.

    • The primary key is usually represented by underlining the corresponding attribute(s).

    • Examples:

      • For the entity set STUDENT with attributes stud_id, name, dob, sex, the primary key is stud_id

      • For the entity set STUDENT with attributes class, class_no, name, dob, sex, the primary key is the combination of class and class_no


Entity relationship model15

Entity-Relationship Model

AttributeAttribute value

stud_id = “s0012”

name = “Chan Tai Man”

dob = #1997/1/1#

sex = “M”

Primary key

AttributeAttribute value

stud_id = “s0022”

name = “Wong May May”

dob = #1998/2/5#

sex = “F”

Entity Set: STUDENT


Introduction to database design methodology

Activity 1


Entity relationship model16

Entity-Relationship Model

  • Try to do Activity 1 of Chapter 3


Entity relationship model17

Entity-Relationship Model

  • Relationship

    • A relationship is an association between two or more entities.

    • For example, in the description: “a teacher teaches a student”,

      • the entities are teacher and student.

      • the relationship between the entities teacher and student is “teach(es)”:

        a teacherteaches a student

relationship

entity

entity


Entity relationship model18

Entity-Relationship Model

Relationship: Teaches

Student 1

Student 2

Teacher 1

Student 3

Student 4

Teacher 2

Student 5

Student 6

Teacher 1

Entity Set: TEACHER

Student 7

Entity Set: STUDENT


Entity relationship model19

Entity-Relationship Model

  • After defining the basic terms, let us summarize the objectives of conceptual database design as follows:

    • Identifying the entities/entity sets of interest in a system.

    • Identifying the attributes of each entity to be recorded.

    • Identifying the relationships among the entities.

    • Constructing the entity-relationship (ER) model of the database.

  • Remark: The ER model is usually constructed in the form of an ER diagram, which will be introduced in Chapter 4.


Guidelines on identifying entities

Guidelines on Identifying Entities

  • Guidelines on Identifying Entities:

    • Entities are the objects that are of interest to our system.

    • To identify the entities, look for the objects in the organization and in the user requirements.

    • As an entity/entity set represents many objects which share common properties, we should give a general name to an entity instead of the name of a particular object. For example, use the entity name “STUDENT” instead of “ChanTaiMan”.


Guidelines on identifying entities1

Guidelines on Identifying Entities

  • Remarks:

    • For a given system, different database designers may not identify exactly the same set of entities. Therefore, they may get different database designs. Certainly, some designs would be better than the other.

    • It is important to identify the entity sets carefully because they are potentially the tables in relational database. (Refer to next slide)


Guidelines on identifying entities2

Guidelines on Identifying Entities

Entity set

AttributeAttribute value

stud_id = “s0012”

name = “Chan Tai Man”

dob = #1997/1/1#

sex = “M”

AttributeAttribute value

stud_id = “s0022”

name = “Wong May May”

dob = #1998/2/5#

sex = “F”

Table in database


Guidelines on identifying attributes

Guidelines on Identifying Attributes

  • Guidelines on Identifying Attributes:

    • To identify attributes, look for the characteristics of the entities that are of interest to us.

    • They represent the data that we want to keep about each entity. In fact, we store the values of the attributes in the database.


Guidelines on identifying relationships

Guidelines on Identifying Relationships

  • Guidelines on Identifying Relationships:

    • To identify relationships, look for the associations / links between entities.

    • The name of a relationship is usually an active or passive verb.


Examples of relationship involving two entities

Examples of Relationship Involving Two Entities


Example an online bookstore

Example - an Online Bookstore

Consider the following description of an online bookstore again.

  • The owner of an online bookstore wants to create an information system for online sale of books. People will be allowed to register as customers of the online website. Customer information like customers’ correspondence addresses, e-mail addresses, and credit card numbers are to be maintained. In a single order, customers will be allowed to purchase any quantity of books. Items in a single order will be shipped to the customer.


Example an online bookstore1

Example - an Online Bookstore

  • The above description may be the way that the user (owner of the bookstore) understands the system.

  • Sometimes, the description is filled with ambiguities and irrelevant information, together with fairly precise descriptions of the real situation.

  • We need to analyze the description and make an initial list of entity sets, attributes and relationships for the system


Example an online bookstore2

Example - an Online Bookstore

  • The owner of an online bookstore wants to create an information system for online sale of books. People will be allowed to register as customers of the online website. Customer information like customers’ correspondence addresses, e-mail addresses, and credit card numbers are to be maintained. In a single order, customers will be allowed to purchase any quantity of books. Items in a single order will be shipped to the customer.

Here, we identify the entities, attributes and relationships by using the following colours:

entity

relationship

attribute


Example an online bookstore3

Example - an Online Bookstore

  • Entity Sets: BOOK, CUSTOMER

  • Relationship: purchase

  • Description: Customer purchases books


Example an online bookstore4

Example - an Online Bookstore

  • Attributes of BOOK:

    • Book_Rec_No

    • ISBN

    • Title

    • Author

    • Publisher

  • Attributes of CUSTOMER:

    • Customer_ID

    • Correspondence_address

    • e-mail

    • Credit_card_no


Example an online bookstore5

Example - an Online Bookstore

  • Remark:

    • The attributes of BOOK are not mentioned in the given description. We may sometimes need to find out further information about the system when necessary.

    • To uniquely identify each entity [instance] that are of the same entity set, a primary key (the underlined attribute) is added for each entity.

    • We may also identify ORDER as an entity / entity set of the system. We will consider such case in Chapter 4.


Introduction to database design methodology

Activity 2


Entity relationship model20

Entity-Relationship Model

  • Try to do Activity 2 of Chapter 3


Introduction to database design methodology

Case Study


Case study

Case Study

  • Refer to Sections 1 to 4.1 of Case Study


References

References

  • http://en.wikipedia.org/wiki/Entity-relationship_model

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


  • Login