Ch4. Logical Database Design and the Relational Model(Part II)

General Rule

- *Each entity* in an E-R diagram is mapped to a *single table* in a relational database. - The table is named after(~의 이름을 따서 이름짓다) the entity

Rule 8: Supertype/Subtype Relationships

- Create a *separate relation* for the *supertype and each of the subtypes* - Assign *common attributes* to the *supertype* - Assign to the *subtypes* the *primary key of the supertype* and *those attributes unique to that subtype* - Assign attribute(s) to the *supertype* to function as *subtype discriminator(s)*

Rule 5: Associative Entities(Identifier Not Assigned)

- Mapping the associative entity involves essentially the same steps as *mapping an M:N relationship*. 1) *Identifier Not Assigned* : Default primary key for the associative relation (also called the *intersection table*) is a *composite key composed of the primary keys of the two entities* - the foreign key is also part of composite primary key, we only need solid underline

Rule 7: Ternary (and n-ary) relationships

- One relation for *each entity* and one for the *associative entity*. The default primary key of this associative entity consists of the *primary keys for the participating entity types* (In some cases, additional attributes are required to form a unique primary key). - date/time bold line 인 이유? : On a same day, same patient can have the same type of treatment from the same physician. So we need to add more attributes as primary key to uniquely identify each row.

Example of mapping a 1:M relationship

- 여기서 customer-order: 1 to many relationship이긴 하지만 *order 쪽 0 인게 특이함* - 여기서 order 쪽에 customer id dash line 없는 이유? 화살표가 이미 나타내서

Rule 5: Associative Entities(Identifier Assigned)

2) *Identifier Assigned (Surrogate(대리의) identifier or key)* - Two reasons: 👉The associative entity type has a *natural identifier* that is familiar to end users 👉The *default identifier* (consisting of the identifiers for each of the participating entity types) may *not uniquely identify instances of the associative entity* - the foreign keys are *not* part of primary key, so we need to as dashed underline.

What is Associative Entity?

An entity type that associates the instances of one or more entity types and contains attributes that are peculiar to the relationship between those entity instances - we don't need associative entity in other relationships *(1:M or 1:1)*

Mapping a *Multivalued Attribute*

Two Resulting Relations

When do we need multivalued attribute?

When we don't know the *exact number of options* e.g. if we don't know how many hobbies are

Rule 4: Binary Relationships(*M:N*)

b)*Many-to-Many* - Create a *new relation *(associative entity or composite entity)* with the primary keys of the two entities* as its primary key - SSN, Loan# = *foreign keys* to Customer_Loan table

Rule 4: Binary Relationships*(1:1)*

c)*One-to-One* - *Primary key on the mandatory side* becomes a *foreign key on the optional side* - This approach will *avoid the need to store null values* in the foreign key attribute

Should We Convert Other Relationships?

payment에 이미 정보가 있어서 pay table 만들 필요 X - we don't need associative entity in other relationship (1:M or 1:1)

Rule 6: Unary (Recursive/순환) Relationships(M:N)

<Many-to-Many> e.g. bill-of-materials(원료구입서) 1) *Two relations* - One for the *entity type* - One for an *associative relation* in which the *primary key has two attributes*, *both taken from the primary key of the entity*

Rule 6: Unary (Recursive/순환) Relationships(1:M)

<One-to-Many> : Recursive foreign key in the *same relation*(*1 relation*) - *Recursive foreign key*: A foreign key in a relation that *references the primary key values of that same relation*

Rule 3: Weak Entity

One *column* per *attribute* + *columns* for *primary key of its strong entity* - The *discriminator* (or *partial key*) of a *weak entity* is the set of attributes that distinguishes among all the entities of a week entity set - The *primary key* of a *weak entity set* is formed by the *primary key of the strong entity* plus the *weak entity's discriminator* *Dash line under word(Dep_Num)*: *partial key* of the weak entity (not foreign key - table 아니기 때문에) *Only in tables (data structures), we use dashed underline to mark the foreign key. * - If a foreign key is also part of *composite* primary key, we only need *solid* underline. This rule is *not limited to strong/weak entity relationship*. And yes, blue arrows indicate foreign key. - And *EID (in weak entity) is foreign key and part part of primary key*.

Rule 1: Regular (strong) entity

a) one *column* per *attribute* b) *Composite Attributes*: Use only their *simple*, *component* attributes

Rule 4: Binary Relationships(*1:M*)

a)*One-to-Many* - *Primary key on the one side* becomes *a foreign key on the many side* - on Customer table side, you could put *loan* there, but there are gonna be lots of redundancy! 😞

Rule 2: Multi-valued attributes

a)If an entity has a *multi-valued attribute*, this multi-valued attribute in an E-R diagram is *mapped to a relation of its own* b) the relation is named after *the plural multi-valued attribute* c)the relation should *have one column which is the entity's primary key* - if there are *5 hobbies*,there should be *5 rows*: Employee1 - hobby1 Employee2 - hobby2 Employee3 - hobby3 Employee4 - hobby4 Employee5 - hobby5