slide1 l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
MIS 3053 Database Design & Applications The University of Tulsa Professor: Akhilesh Bajaj ER Model Lecture 4 Mapping PowerPoint Presentation
Download Presentation
MIS 3053 Database Design & Applications The University of Tulsa Professor: Akhilesh Bajaj ER Model Lecture 4 Mapping

Loading in 2 Seconds...

play fullscreen
1 / 18

MIS 3053 Database Design & Applications The University of Tulsa Professor: Akhilesh Bajaj ER Model Lecture 4 Mapping - PowerPoint PPT Presentation


  • 230 Views
  • Uploaded on

MIS 3053 Database Design & Applications The University of Tulsa Professor: Akhilesh Bajaj ER Model Lecture 4 Mapping an ER model to tables. © Akhilesh Bajaj, 2000, 2002, 2003, 2006. All Rights Reserved. . Review of the ER Model So Far. Entities and entity sets

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 'MIS 3053 Database Design & Applications The University of Tulsa Professor: Akhilesh Bajaj ER Model Lecture 4 Mapping' - pahana


Download Now 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
slide1

MIS 3053

Database Design & Applications

The University of Tulsa

Professor: Akhilesh Bajaj

ER Model Lecture 4

Mapping an ER model to tables

© Akhilesh Bajaj, 2000, 2002, 2003, 2006. All Rights Reserved.

slide2

Review of the ER Model So Far

  • Entities and entity sets
  • Relationships and relationship sets
  • Attributes of entity sets and relationship sets
  • Degree of relationship sets
  • Cardinality of relationship sets
  • Keys of entity sets
  • Existence dependencies (weak entity sets)
  • Subclass / Superclass hierarchy amongst entity sets
slide3

End-user

descriptions

One ER

diagram

Set of tables

Goals Today

  • Learn how to map an ER diagram to a set of tables
  • In-class assignment on mapping ER diagrams to tables.
  • Goal of ER module:
slide4

What is a Table

  • A table has a name
  • A table has columns
  • - the columns are also called attributes.
  • The data in the table is stored as rows.
  • A table has a primary key (determines each row uniquely).
  • A database consists of many such tables, and is
  • called a relational schema.
slide5

Mapping Strong Entity Sets

  • Create a separate table for the strong entity set.
  • The name of the table is the name of the entity set.
  • The columns of the table are the attributes of the
  • strong entity set.
  • The primary key of the table is the primary key of the entity set.
slide6

Mapping Weak Entity Sets

  • Create a separate table for the weak entity set.
  • The name of the table is the name of the entity set.
  • The columns of the table are the attributes of the
  • weak entity set + the primary key of the corresponding strong
  • entity set.
  • The primary key of the table is the primary key of the
  • corresponding strong entity set + the unique identifier of the
  • weak entity set.
slide7

Mapping Superclass / Subclass Hierarchies

  • Create a separate table for the superclass first, using the
  • rules for mapping entity sets we have seen earlier.
  • For each subclass entity set, create a separate table. The
  • columns of each table = the additional attributes of the
  • corresponding subclass entity set + the primary key of the
  • superclass entity set.
  • The primary key of the subclass table is the primary key
  • taken from the superclass table.
  • Recall that the hierarchy triangle is NOT a relationship set
  • and is not mapped into a table.
slide8

Mapping Multivalued Attributes

  • Create a separate table for the multivalued attribute.
  • The name of the table is the name of the attribute.
  • The columns of the table are the primary key of the
  • entity set to which the attribute belongs + a separate column for
  • values of the attribute.
  • The primary key of the table is all the columns of the table.
  • If there are separate multivalued attributes, then create a
  • separate table for EACH attribute.
slide9

Mapping Composite Attributes

  • Only attributes that are the leaves of a composite hierarchy
  • are mapped as columns of a table.
  • If the composite attribute is single-valued, treat the leaf attributes
  • as ordinary attributes, when mapping them.
  • If the composite attribute is multivalued, treat the leaf attributes
  • as multivalued-attributes, when mapping them, except create only
  • ONE new table with all the leaf attributes in it. The Primary Key
  • of this table will be all the attributes of the table.
  • Note: There can never be a case where only some leaf attributes
  • are multivalued and others are not. Either the entire composite
  • Attribute (such as address) is multivalued, or it is not.
slide10

Mapping Relationship Sets

  • 1. Mapping Existence Relationship sets (Weak entity set
  • relationship set with its strong entity set: DOUBLE Diamond) :
  • Existence relationship sets (between weak and corresponding
  • strong entity sets) are NOT mapped into tables.These are
  • the ones that are represented by double diamonds.
  • We should note that a weak entity set can have other, normal
  • relationship sets with other entity sets also. These are treated as
  • normal (single diamond) relationship sets, when mapping.
  • Let’s see how to map normal relationship sets to tables next.
slide11

Mapping Relationship Sets

  • 2. MAPPING A RELATIONSHIP SET (SINGLE DIAMOND)
  • 2 a) Binary relationshipsthat are 0/1:1 ..…◊ ..... Any cardinality:
  • Add a column(s) in the table that corresponds to the entity
  • set that is on the “Any Cardinality” side.
  • Example:

0/1:1 here Side

“Any Cardinality here” Side

0:1

0:n

R

A

B

  • The column(s) we add to B here is the primary key of the entity set
  • that is on the 0/1:1 side (A in this case) and any other attributes of the
  • relationship set. So, we borrow the primary key of A into B, but it
  • does NOT become part of the primary key of B. It’s still a foreign
  • key referring A though. We can also preface the columns added to B with the label
  • of R, to show that the columns are borrowed because of R.
  • [Nota Bene: If the cardinality is 0:1, null values have to be allowed,
  • if it is 1:1, then null values should not be allowed,
  • for the primary key of A when put in B.]
slide12

Mapping Relationship Sets

  • 2 b). Binary relationship sets with cardinality NOT as in 2a)
  • AND all ternary & higher degree relationship sets:
  • Create a separate table with the same name as the name of
  • the relationship set
  • The columns of the table are the attributes of the relationship
  • set (if any) + primary keys of all the entity sets that participate
  • in the relationship set.
  • The primary key of the table is = the primary keys of all the
  • entity sets that participate in the relationship set.
  • If any of the entity sets in the relationship set are weak, recall that the
  • primary key of a weak entity set is the unique identifier + the primary key
  • of the corresponding strong entity set.

Note: Rules 1 & 2 are mutually exclusive: Rule 1 is for double diamonds, rule 2 is for single diamonds

slide13

Sequence Of Steps

  • Given an ER diagram, what sequence of steps should we follow?
  • Map the strong entity sets
  • Map the weak entity sets
  • Map the subclass entity sets
  • Map multivalued attributes, if any
  • Map Composite attributes, if any
  • Map relationship sets
slide14

Example 1

The examples are deliberately abstract, to emphasize the steps,

independent of any particular situation.

Attrib B1

Attrib A2

Attrib R11

Attrib B2

Attrib A1

0:1

1:m

A

B

R1

0:1

Attrib R21

1:n

R2

Attrib C1

C

Attrib C2

1:n

Attrib D1

D

Attrib C3

Attrib D2

slide15

Attrib A2

Attrib A1

1:1

A

0:m

Example 2

The examples are deliberately abstract, to emphasize the steps,

independent of any particular situation.

Attrib B1

Attrib B2

0:m

B

Attrib R11

R1

1:n

Attrib D1

D

Attrib D2

slide16

In Class Assignment 1

  • Al’s motor shop (AMS) is an automobile repair facility owned
  • by the Capone family. AMS has 5 repair bays. Each repair bay
  • (place where car is repaired) has a bay_id and bay_location.
  • AMS employs 14 employees. Each employee has an
  • employee_id, address, phone and salary. Of these 14, 2 are
  • office staff. They are further described by typing_speed and
  • degree_held. The 12 mechanics are further described by
  • tech_level. Each mechanic is assigned to work on one bay.
  • AMS customers have a cust_id, name, address, phone. In
  • addition, Mr. Capone also wants to capture information
  • for each customer that lists the mechanic who last did a job
  • for the customer, the date on which the job was done, and
  • the amount the customer paid to AMS.
  • The above requirements have been captured in an ER diagram.
  • Please map the ER diagram to tables.
slide17

In Class Assignment

address

Salary

phone

address

Bay_id

phone

name

Cust_id

Bay_loc

Typing_speed

Degree_held

Emp_id

Tech_level

Repair_bays

Employees

date

payment

IS A

User-defined,

disjoint, total

0:n

1:1

Mechanics

Office_staff

Works

0:1

Last_job

0:n

Customers

tables mapped from the er diagram
Tables Mapped from the ER Diagram

Employees (emp_id, phone, address, salary)

Mechanics (emp_id, tech_level, bay_id)

emp_id FK REF employees, bay_id FK REF bays

Office_staff (emp_id, typing_speed, degree_held)

emp_id FK REF employees

Repair_bays (bay_id, bay_loc)

Customers (cust_id, name, address, phone, last_job_emp_id, last_job_date, last_job_payment)l

last_job_emp_id FK refs mechanics