Relational Model - Database Design - Lecture Slides, Slides of Database Management Systems (DBMS)

Download Relational Model - Database Design - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

How to translate ER Model to

Relational Model

Review - Concepts

Relational Model is made up of tables

• A row of table = a relational instance/tuple

• A column of table = an attribute

• A table = a schema/relation

• Cardinality = number of rows

• Degree = number of columns

From ER Model to Relational Model

So… how do we convert an ER diagram into a table??

Simple!!

Basic Ideas:

 Build a table for each entity set

 Build a table for each relationship set if necessary (more on

this later)

 Make a column in the table for each attribute in the entity set

 Indivisibility Rule and Ordering Rule

 Primary Key

Example – Strong Entity Set

SID Name Major GPA 1234 John CS 2. 5678 Mary EE 3. SSN Name Dept 9999 Smith Math 8888 Lee CS Student SID Name Major GPA Advisor Professor SSN Name Dept

Example – Weak Entity Set

Age Name Parent_SID 10 Bart 1234 8 Lisa 5678 Student SID Name Major GPA Name Age owns Children

• Primary key of Children is Parent_SID + Name

Representation of Relationship Set

--This is a little more complicated--

 Unary/Binary Relationship set

 Depends on the cardinality and participation of the relationship  Two possible approaches

 N-ary (multiple) Relationship set

 Primary Key Issue

 Identifying Relationship

 No relational model representation necessary

Example – One-to-One Relationship Set SID Maj_ID Co S_Degree 9999 07 1234 8888 05 5678 Student SID Name Major GPA ID Code study Major

• Primary key can be either SID or Maj_ ID_Co Degree

Example – One-to-One Relationship Set SID Name Major GPA LP_S/N Hav_Cond 9999 Bart Economy -4.0 123-456 Own 8888 Lisa Physics 4.0 567-890 Loan Student SID Name Major GPA S/N # Have Laptop

• Primary key can be either SID or LP_S/N Condition Brand 1: Relationship

Example – Many-to-One Relationship Set SID Name Major GPA Pro_SSN Ad_Sem 9999 Bart Economy -4.0 123-456 Fall 2006 8888 Lisa Physics 4.0 567-890 Fall 2005 Student SID Name Major GPA SSN Professor

• Primary key of this table is SID Semester Name N: Relationship Dept Advisor

Representing Relationship Set Unary/Binary Relationship

• For many-to-many relationship
  • Same thing as one-to-one relationship without

total participation.

• Primary key of this new schema is the union of the

foreign keys of both entity sets.

• No augmentation approach possible…

Example – N-ary Relationship Set P-Key1 P-Key2 P-Key3 A-Key D-Attribute 9999 8888 7777 6666 Yes 1234 5678 9012 3456 No E-Set 1 P-Key Another Set

• Primary key of this table is P-Key1 + P-Key2 + P-Key D-Attribute A relationship A-Key E-Set 2 P-Key E-Set 3 P-Key

Representing Relationship Set Identifying Relationship

• This is what you have to know
  • You DON’T have to build a table/schema for the identifying

relationship set once you have built a table/schema for the

corresponding weak entity set

• Reason:
  • A special case of one-to-many with total participation
  • Reduce Redundancy

Representing Multivalue Attribute

• For each multivalue attribute in an entity

set/relationship set

• Build a new relation schema with two columns
• One column for the primary keys of the entity

set/relationship set that has the multivalue attribute

• Another column for the multivalue attributes. Each cell of

this column holds only one value. So each value is

represented as an unique tuple

• Primary key for this schema is the union of all attributes

Example – Multivalue attribute

SID Name Major GPA 1234 John CS 2. 5678 Homer EE 3. Stud_SID Children 1234 Johnson 1234 Mary 5678 Bart 5678 Lisa 5678 Maggie Student SID Name Major GPA Children The primary key for this table is Student_SID + Children, the union of all attributes