conceptual model translation n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Conceptual Model Translation PowerPoint Presentation
Download Presentation
Conceptual Model Translation

Loading in 2 Seconds...

play fullscreen
1 / 41

Conceptual Model Translation - PowerPoint PPT Presentation


  • 251 Views
  • Uploaded on

Conceptual Model Translation. Translating an ERD into a relational database schema. Conceptual model translation. Logical level models often require greater detail. Logical model may not support some conceptual constructs: e.g., no direct equivalent of many-to-many in RM

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 'Conceptual Model Translation' - teddy


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
conceptual model translation

Conceptual ModelTranslation

Translating an ERD into

a relational database schema

SFDV3002

conceptual model translation1
Conceptual model translation
  • Logical level models often require greater detail.
  • Logical model may not support some conceptual constructs:
    • e.g., no direct equivalent of many-to-many in RM
    • must map conceptual ⇒ logical
  • Typically ERD ⇒ SQL.
  • Oracle Designer’s Database Design Transformer.

SFDV3002

case study cd dvd library see also example 2 1

**substitute your own case study if you wish**

Case study: CD/DVD library(see also Example 2–1)

MEMBER

# MEMBER_NUM

* FIRSTNAMES

* SURNAME

* STREET

* SUBURB

* CITY

o PHONE

o EMAIL

HIRE

# HIRE_NUM

* HIRE_DATE

* RETURNED

to

makes

consists of

is item of

RECORDING

# RECORDING_ID

* TITLE

* CHARGE

* NUM_COPIES

CD

* ARTIST

* GENRE

DVD

* DIRECTOR

* ZONE

SFDV3002

general approach to translation
General approach to translation
  • Generate candidate relations via:
    • entity translation
    • subtypes (see SFDV3003)
    • attribute translation
    • primary key definition
    • relationship translation
  • Normalise candidate relations if necessary, to at least 3NF (see SFDV3003)

SFDV3002

translation rules
Translation rules

Entity translation

  • each ERD entity ⇒ relation (usually)
  • different ways to handle subtypes (see slides **20–25**)

Attribute translation

  • each ERD attribute ⇒ attribute in corresponding relation

Primary key definition

  • entity unique ID ⇒ PK of corresponding relation

Relationship translation

  • implement relationships through FK placements

SFDV3002

entity and attribute translation
Entity and attribute translation
  • Each entity (usually) ⇒ relation.
    • entity name ⇒ relation name
  • Each attribute of entity ⇒ attribute of corresponding relation.
    • entity attribute name ⇒ relation attribute name

SFDV3002

entity and attribute translation1
Entity and attribute translation

MEMBER

# MEMBER_NUM

* FIRSTNAMES

* SURNAME

* STREET

* SUBURB

* CITY

o PHONE

o EMAIL

HIRE

# HIRE_NUM

* HIRE_DATE

* RETURNED

SFDV3002

dealing with nulls
Dealing with nulls

MEMBER

# MEMBER_NUM

* FIRSTNAMES

* SURNAME

* STREET

* SUBURB

* CITY

o PHONE

o EMAIL

indicates nulls not allowed

indicates nulls are allowed

Member(Member_num, Firstnames, Surname,Street, Suburb, City, Phone, Email)

SFDV3002

translating subtypes approach 1 d orazio happel pp 143 144
Translating subtypes: Approach 1(D’Orazio & Happel, pp. 143–144)
  • One relation per subtype entity plus one for supertype entity.
  • Most flexible.
  • No wasted space.
  • Most complex implementation.

SFDV3002

translating subtypes approach 1 d orazio happel pp 143 1441
Translating subtypes: Approach 1(D’Orazio & Happel, pp. 143–144)

RECORDING

# RECORDING_ID

* TITLE

* CHARGE

* NUM_COPIES

CD

* ARTIST

* GENRE

DVD

* DIRECTOR

* ZONE

Recording(Recording_ID, Title, Charge, Num_copies)

CD(Recording_ID, Artist, Genre)

DVD(Recording_ID, Director, Zone)

SFDV3002

translating subtypes approach 2 d orazio happel pp 143 144
Translating subtypes: Approach 2(D’Orazio & Happel, pp. 143–144)
  • One relation per subtype entity, each includes supertype attributes.
  • No separate supertype relation.
  • Less flexible.
  • Potential for redundant data.
  • Less complex implementation.
  • Possible referential integrity and redundancy issues.

SFDV3002

translating subtypes approach 2 d orazio happel pp 143 1441
Translating subtypes: Approach 2(D’Orazio & Happel, pp. 143–144)

RECORDING

# RECORDING_ID

* TITLE

* CHARGE

* NUM_COPIES

CD

* ARTIST

* GENRE

DVD

* DIRECTOR

* ZONE

CD(Recording_ID, Title, Charge, Num_copies, Artist, Genre)

DVD(Recording_ID, Title, Charge, Num_copies, Director, Zone)

SFDV3002

translating subtypes approach 3 d orazio happel pp 143 144
Translating subtypes: Approach 3(D’Orazio & Happel, pp. 143–144)
  • Only one relation with attributes from all related super- and subtype entities.
  • No separate subtype relations.
  • May need a “type”attribute.
  • Least flexible.
  • Unused attribute values.
  • Simplest implementation.

SFDV3002

translating subtypes approach 3 d orazio happel pp 143 1441
Translating subtypes: Approach 3(D’Orazio & Happel, pp. 143–144)

RECORDING

# RECORDING_ID

* TITLE

* CHARGE

* NUM_COPIES

CD

* ARTIST

* GENRE

DVD

* DIRECTOR

* ZONE

Recording(Recording_ID, Title, Charge, Num_copies, Artist, Genre, Director, Zone)

SFDV3002

primary key definition
Primary key definition
  • Entity unique identifier ⇒ corresponding relation’s PK.

SFDV3002

primary key definition1
Primary key definition

MEMBER

# MEMBER_NUM

* FIRSTNAMES

* SURNAME

* STREET

* SUBURB

* CITY

o PHONE

o EMAIL

HIRE

# HIRE_NUM

* HIRE_DATE

* RETURNED

SFDV3002

relationship translation one to one
Relationship translation: One-to-one
  • Place PK of first relation into second relation as FK, or vice versa, or both.

SFDV3002

relationship translation one to one1
Relationship translation: One-to-one
  • If one entity optional and other mandatory, put FK in “optional” relation.

Ensures no

nulls in FK

(see slide **37**)

SFDV3002

relationship translation one to one2
Relationship translation: One-to-one
  • If one entity optional and other mandatory, put FK in “optional” relation.

FK

SFDV3002

relationship translation one to many
Relationship translation: One-to-many
  • Place PK of “one” relation into “many” relation as FK.

SFDV3002

relationship translation one to many1
Relationship translation: One-to-many

MEMBER

# MEMBER_NUM

* FIRSTNAMES

* SURNAME

* STREET

* SUBURB

* CITY

o PHONE

o EMAIL

HIRE

# HIRE_NUM

* HIRE_DATE

* RETURNED

to

makes

Hire(Hire_num, Hire_date, Returned, Member)

FK

Member(Member_num, Firstnames, Surname, Street, Suburb, City, Phone, Email)

SFDV3002

relationship translation many to many
Relationship translation: Many-to-many
  • Invent intermediate “all-key”entity & convert M:N relationship ⇒ two 1:M’s.

always many

& mandatory

SFDV3002

relationship translation many to many1
Relationship translation: Many-to-many

HIRE

# HIRE_NUM

* HIRE_DATE

* RETURNED

Hire(Hire_num, Hire_date, Returned, Member)

FK

consists of

Recording_Hire(Hire_num, Recording_ID)

FK

is item of

Recording(Recording_ID, Title, Charge, Num_copies, Artist, Genre, Director, Zone)

RECORDING

# RECORDING_ID

* TITLE

* CHARGE

* NUM_COPIES

CD

* ARTIST

* GENRE

DVD

* DIRECTOR

* ZONE

SFDV3002

relationship translation unary d o razio happel pp 164 1 72
Relationship translation: Unary(D’Orazio & Happel pp. 164–172)
  • Place FK in relation that refers to its own PK.

FK

SFDV3002

relationship participation and nulls
Relationship participation and nulls

Relationship participation determines whether FKs can be null:

FK in Employeecan be null

Position

Employee

FK in Employeecannot be null

Position

Employee

SFDV3002

normalization overview
Normalization overview
  • Data redundancy
    • What it is and why it’s a problem
    • Update, insert and delete anomalies
  • Dependencies
    • Used to identify underlying data dependencies so that we can remove redundancy
    • Normalisation
    • Functional and multivalued dependencies
data redundancy
Data redundancy
  • Values stored repetitively in relations (esp. poorly designed relations)
  • Potential for anomalous data to be stored

This relation associates employees with projects. Assume no nulls are allowed.

how redundancy arises
How redundancy arises
  • Ad hoc databases
    • “flat file” databases
    • spreadsheets
  • Poor database design
    • poor analysis
    • poorly designed ERDs
  • Modifications to existing systems
    • “bolting on” new attributes
update anomalies

37

35

37

35

Only one value

updated: ANOMALY

Update anomalies
  • Each person’s salary is repeated for each project they are involved with. What does this imply when we need to increase someone’s salary?

Both values

updated: OK

50

48

delete anomalies

What happens to

(Brown, 20)?

ANOMALY

Delete anomalies
  • If a project ends (i.e., is deleted), what happens to the data for employees on that project?

Delete project Alpha

insert anomalies

Where do we store

(Johnson, 36) until then? ANOMALY

Insert anomalies
  • What happens when we hire a new person? (remember, no nulls allowed)

Johnson hasn’t yet been assigned to a project, but no nulls allowed

the solution normalisation
The solution: Normalisation
  • Breaking up the relation eliminates the worst of the redundancy [see lecture 10 for more details]
but first functional dependencies fds
But first: Functional dependencies (FDs)
  • Theoretical basis for normalisation [D’Orazio & Happel pp. 206–209]
  • Attribute B functionally dependent on (or functionally determined by) attribute (A  B) if each value of A is associated with exactly one value of B
    • Student ID functionally determines student name, but not vice versa [D’Orazio & Happel fig. 10.2, p. 206]
    • Car registration functionally determines car owner
    • Every attribute functionally dependent on PK
some more examples
Some more examples
  • student ID  name, address, phone, …
  • car registration  owner name
    • what about car registration and VIN?
  • IRD number  tax payable
  • product ID + order no  quantity ordered
  • What about:
    • home address  student name?
    • name  birth date?
    • student ID + name  birth date?

rego  VIN

VIN  rego

using functional dependencies
Using functional dependencies
  • To determine functional dependencies:
    • need detailed knowledge of the business rules
    • examine existing data sets, although not always practical when these data sets are large or unknown
  • Can represent FDs as denoted as Functional Dependency Diagrams (FDDs) [D’Orazio and Happel, Table 10.1, p. 207]
  • “Bottom-up” approach
    • E-R conceptual modelling is “top-down”
    • best used as a design validation tool
types of functional dependency
Types of functional dependency
  • Dependencies on more than one attribute
    • always arise with composite PKs
  • Example:
    • year + course code  course coordinator (i.e., course coordinator determined by combination of both a particular year and a course code)

Year

Course coordinator

Course code

types of functional dependency1

Student

Name

Invoice

Date

Types of functional dependency
  • Partial functional dependency
  • Subset of left hand side determines right hand side
    • “extra” attributes on LHS are unnecessary

Invoice

Number

Student ID

Date of

Birth

Invoice

Total

types of functional dependency2
Types of functional dependency
  • Transitive dependency
    • part number determines supplier number
    • supplier number determines supplier name
    • therefore, part number alone also determines supplier name
  • Ideally should not exist within the same relation

Part

number

Part

number

Supplier

number

Supplier

name

Supplier

name

multivalued dependencies
Multivalued dependencies
  • Attribute B multiply dependent on (or multiply determined by) attribute A if for each value of A there can be a set of B values (A B)
    • year + course code multidetermines lecturer
      • 2003 + SFDV3003: {Long, Stanger}
      • 2004 + SFDV3003: {Woodford, Irwin, Stanger}
      • 2004 + SFDV3004: {Stanger, Edwards}
      • compare with year + course code  coordinator
    • home address  student name
    • but probably not name  birth date

summary
summary
  • Redundancy and anomalies
    • Causes and consequent problems
  • Functional Dependencies
    • Partial vs. full dependency
    • Transitive dependency
  • Multivalued dependencies