Week 5

Post date: Sep 30, 2019 4:0:11 AM

Chapter 8: Relational Database Design by ER-EER-to-Relational Mapping

Relational database design using ER-to-Relational mapping

Converting from an ER diagram goes through these steps:

• 1. Every Entity becomes a Relation (table).

  2. Every attribute of an entity becomes an attribute of the relation.

  3. When there are many potential key attributes, we will chose one.

  4. Apply the rules with the cardinality ratios.

Rules with Cardinality Ratios:

• • Many to many: A new relation (table) is added with the key attribute (primary key) being the combination of all foreign keys from each relation/table.

  • 1 to many: A new attribute (column) is going to be added in the relation/table "to the side of the many". It will be the foreign key from the relation/table from the "side of the 1".

  • 1 to 1: More analysis is required (see the slides and/or https://db.grussell.org/section006.html#_Toc67114425).

For Example:

Converting the following ER diagram of the exercise 4 of week 4:

Results in the following relations:

• • Artwork(title, year, type, price, #artist_name)

  • Group(name)

  • Customer(name, address, amount)

  • Artist(name, birthplace, age, style)

  • Like_Artist(#artist_name, #customer_name)

  • Classify(#artwork_title, #group_name)

  • Like_Group(#group_name, #customer_name)

Chapter 6: The Relational Algebra and Relational Calculus

Unary relational operations: SELECT and PROJECT;

Relational algebra operations from set theory

In-Class Exercises:

PART 1:

Ex 1:

Write the resulting relations based on the ER diagram of Week4-Ex1.