ER-to-Relational Data Model

[next] [prev] [prev-tail] [tail] [up] [toc]

ER-to-Relational Data Model

9.1 An Entity-Relationship (ER) Diagram

-- -------- ----- ---- |||||| N||-WORKS -FOR---- -|Lname||- || ----- ----- |||||||||||||| |--|||||||-||- -||||||||||||| |Fname|-|-| ||-|Name|-- ||M |-Locations||- |-|||||-- || ---------------| |Name||| || -DEPARTMENT-----|||||||||||||| --|||- | |||--|||||||-|- || ||||NumOfEmployees |||- -Ssn-- | ||| -|Number|||----1- ||||||||-||||||||| -------||||--| ---- ---- |EMPLOYEE -----1--MANAGING --- ----||--||----- ---- -||-- || ||| ------ -----||||||||||||- |||-------||| ------ ||StartDate||-- employee|||-- ---|employer --1-- N||SUPERVISION -|1 ----- ----- ---- ----- ----- ----- -------- ---- ----- --DEPENDENTS -OF---- ----- ----- ||||||| -------- |Name- -- |N ||| |||||--------------| -||Relationship||--|--------------| |||||||-|||||||| --DEPENDENT-----

9.2 A Relational Data Schema

EMPLOYEE

Fname

Lname

SSN

SupervisorSSN

DEPARTMENT

Name

NUMBER

MANAGER-SSN

StartDate

DEPENDENT

Relationship

EMPL-SSN

Name

DEP-LOCATION

Location

DEP-NUMBER

WORKS-FOR

EmployeeSSN

DeptNumber

Comments:

EMPLOYEE (SupervisorSSN)	→	EMPLOYEE (SSN)
DEPARTMENT (MANAGER-SSN)	→	EMPLOYEE (SSN)
DEPENDENT (EMPL-SSN)	→	EMPLOYEE (SSN)
DEP-LOCATION (DEP-NUMBER)	→	DEPARTMENT (NUMBER)
WORKS-FOR (EmployeeSSN)	→	EMPLOYEE (SSN)
WORKS-FOR (DeptNumber)	→	DEPARTMENT (NUMBER)
unique: DEPARTMENT.NAME

create table EMPLOYEE{
  Fname...
  Lname...
  SSN... primary key
  SupervisorSSN... reference EMPLOYEE(SSN)
}
create table DEPARTMENT{
  NAME...
  NUMBER... primary key
  MANAGER-SSN... references EMPLOYEE(SSN)
  StartDate
}
create table DEPENDENT{
  Relationship...
  EMPL-SSN... references EMPLOYEE(SSN)
  Name ...
  primary key(EMPL-SSN,Name)
}
create table DEP-LOCATION{
  Location... primary key
  DEPNUMBER... references DEPARTMENT(Number)
}
create table WORKS-FOR{
  EmployeeSSN... references EMPLOYEE(SSN)
DeptNumber... references DEPARTMENT(Number)
}

9.3 The Mapping

(Strong) Entity Type into Relation

Include the simple attributes
Include the simple components of the composite attributes
Identify the primary keys
Don’t include: non-simple components of composite attributes, foreign keys, derived attributes, relational attributes

-||||||||| ||||||| |||||||||||| ||Lname||-||Name-|- -||Locations|||- -||||||||-| |||||-------------|||||||||| -|Fname---||||| | DEPARTMENT | -|Name||- ----|||-------------|||||-||||||||| ||||| | --|||||||||- |||NumOfEmployees ||||- |Ssn-- | -|Number|||- ||||||||||||||| ||||-- | -------------| -EMPLOYEE----|

EMPLOYEE

Fname

Lname

SSN

DEPARTMENT

Name

Number

Binary 1:1 Relationship Types into Foreign Keys

Include as foreign keys, in the relation of one entity type, the primary keys of the other entity type
Include also the simple attributes of the relationship type
If possible, the first entity type should have total participation in the relationship (to save memory!)

-||||||||| -|Lname||- ----------------| ||||||||| -||||||||-| |DEPARTMENT ---|Number-|-- ||Fname|-|-|||| -------||-------|||||||||||| -Ssn - |Name||-- ||1 |Name||-- |||--- ||| -------- |-------|----| 1 ---- ---- |EMPLOYEE------------MANAGING ---- ----- --|||||||||| --||StartDate||- ||||||||

EMPLOYEE|--------|-----| -Fname--|Lname---|SSN--| MANAGING -- -- -- -- DEPARTMENT-------- | | | |-- | | | | | | | --------|--------|-123-| ----MSSN-- |StartDate-|Number | -Number---Name----| | | | 321 |--- --123 | 1 ---- |1 | | --------|--------|------ -------- |--- -- -|---- --|- ------------------| | | | 231 | |321-- |-- ----2-- -- | |2-------|--------| ------------------------

EMPLOYEE|--------|-----| Fname---|Lname---|SSN--| -- -- -- -- DEPARTMENT-------- | | | --- | | | | | --------|--------|123--| ----MSSN- -StartDate|Number---Name----- | | |321 ---- - 123 | | 1 | | --------|--------|------ ---|---- -- -- --|------------------ | | |231 | |321-- |-- ----2-----|--------- ------------------------

DEPARTMENT

MANAGER-SSN

StartDate

Binary 1:N Relationship Types into Foreign Keys

Add as foreign keys, to the relation of the entity type at the N side, the primary keys of the entity type at the 1 side (don’t duplicate records!)
Include also the simple attributes of the relationship type

-||||||||| -|Lname||- -||||||||-| -|||||Fname|-|-||||| |Ssn-- |Name||-- -------------| -EMPLOYEE----| ||| ||| || ---- || ||---- ----|| employee||-- --|employer N |-SUPERVISION ---|1 ---- ---- ----

EMPLOYEE|--------|-----| SUPERVISION--| EMPLOYER-------|-------| Fname---|Lname---|SSN--| SSN-|MGRSSN--| SSN---|Fname---|Lname--| | | | | -- | ------| | | | --------|--------|123--| ----321-| 123 ----| ----123---|--------|-------| | | |321 |-- --231 | 123 --- 321 | | | --------|--------|---------- -- -- -- ---- ------|--------|-------- | | |231 |- 231 | | | ------------------------ ------------------------

EMPLOYEE|--------|------|-- -- --| EMPLOYER--------|-------| -Fname--|-Lname--|SSN-- MGRSSN --| -SSN---|Fname---|Lname- | | | | | | -| | | | --------|--------|-123- |-- -- --| --|||123---|--------|------ | | | | 321 |123 |--|| |321 | | | --------|--------|------|-- -- --|||| -------|--------|-------| | | | 231 |123 || |231 | | | ------------------------ -- -- -- ------------------------

EMPLOYEE|--------|-----|-- -- --| Fname---|Lname---|SSN--|MGRSSN --| | | | | | --------|--------|123--|-- -- --| | | |321 | 123 | --------|--------|-------- -- --| | | |231 | 123 | -------------------------- -- --

EMPLOYEE

SupervisorSSN

Binary M:N Relationship Type into Relation

We don’t want to duplicate records!
Set as foreign keys the primary keys of the participating entity types
Include the simple attributes of the relationship type

------ |||||||||- ---- ---- |||||Lname||| N |-WORKS -FOR --- |Fname||--| ||---- ----- |||||--||||||-||| ---||--- |Name||--|| |M -||-|| | ||| ---------------| -Ssn-- | ||| |DEPARTMENT | --- |||| ----------|||--- --------||---|--||||-|- -|||||||||- -EMPLOYEE----|-|Name||- ||Number||-

EMPLOYEE|--------|-----| WORKS-|-FOR------| DEPARTMENT|-------| -Fname--|Lname---|SSN--| SSN-----Number---- -Number---Name----| | | | |-----| | -------- | | --------|--------|-123--||-----123-------1------- -------1------------| | | | 321 |--- --123-------2-------------| 2 | | --------|--------|------ --- | --- ---|----------------- | | | 231 |--- -321-------1------- --- ------------------------ --|231 | 2 -- |-----------------

WORKS-FOR

EmployeeSSN

DeptNumber

N-Ary Relationship Type

Similar to binary M:N relationship type

Multivalued Attribute into Relation

Include the given attribute
Include as foreign keys the primary attributes of the entity/relationship type owning the multivalued attribute
Keys not designated within primary keys are to be mentioned as such in side comments

-||||||| |||||||||||||| -|Name|-- |||Locations||- -||-------------|||-||||| -DEPARTMENT-----| -|||||||--| |||||-||||||||||||||||- ||Number||- |||NumOfEmployees|||||

DEP-LOCATION

Location

DEP-NUMBER

Resembles the treatment of a relationship type.

Weak Entity+Relationship Types into Relation

Include simple attributes
Add the owner’s primary key attributes, as foreign key attributes
Declare into a primary key the partial keys of the weak entity type combined with those imported from the owner

--|||||||| -|||||||||Lname||- ---- ||Fname|---|||||| ----- ----- ||Name||- ----- ----- --|||- ||| ----- ---- -Ssn-- | ---- --DEPENDENTS -OF--- -------|-----| ----- ----- |EMPLOYEE |-|||||||-------- ------------- -|Name--- | |||| ----------------| ||Relationship||-----------------|| |||||||||||||||| --DEPENDENT-----

DEPENDENT

Relationship

EMPL-SSN

NAME

Reference: Ch. 7.1 in textbook.

9.4 Assignment #5

Due: Mo, Feb 16

Problem 7.4 from textbook (page 204 4th ed; 237 5th ed).

Notes

If you submit your homework electronically, use the departmental submit utility: submit XXX lab5 filename. XXX represents ‘c670aa’ for students of the 3:30 section, and ‘c670ab’ for students of the 5:30 section.
- Files are restricted to 1,048,576 bytes
- The submit program issues error messages to the student’s CSE email account

[next] [prev] [prev-tail] [front] [up] [toc]