chapter 4 High-Level Database Models

example 4.19 page 150 for referential integrity

""Movies" and "Studios" has "Owns" relationship with rounded arrow point to "Studios" entity: mean every movie own by one studio, and this 'one studio' is present in "Studios" entity.

example of degree constraints

"Stars" entity (<=10) then relationship "Stars-in" then "Movies" entity = a movie can not be connected by relationship "Stars-in" to more than 10 star entities.

4.3.3 Referential Integrity

....

shape of node to indicated element kinds

1. Entity sets - rectangles 2. Attributes - ovals 3. Relationships - diamonds

4.5 From E/R Diagram to Relation Designs

1. Turn each entity set into a relation with the same set of attributes 2. Replace a relationship by a relation whose attributes are the keys for the connected entity sets.

Keys in E/R model

1. Underline the key attribute(s). 3. In an Isa hierarchy, only the root entity set has a key, and it must serve as the key for all entities in the hierarchy.

special situation in converting E/R to Relation

1. Weak entity sets cannot be translated straightforwardly to relations. 2. "Isa" relationships and subclasses require careful treatment. 3.

one to one

1. an entity of either entity set can be connected to at most one entity of the other set. 2. many to one from E to F and many to one from F to E.

many to one

1. each member of E can be connected by R to at most one member of F. 2. each member of F can be connected to many members of E (E and F are entity sets, R is a relationship)

Entity sets

1. entity is an abstract object of some sort. 2. entity set is a collection of similar entities forms.

important points about key

1. every entity set must have 1 key. 2. in isa-hierarchy & weak entity sets, the key belong to other entity set. 3. in an isa-hierarchy, the key must be in the root.

weak entity set Notation

1. for entity set, rectangle with a double border 2. for many to one relationships, diamond with double border.

Example of weak entity sets

1. hierarchy base. Example: entity "Crews" of a studio has "number" attribute as key, also another studio also has entity "Crews" with "number" attribute as key ==> "Crews" is weak entity. 2. Common source of weak entity sets. Example (figure 4.22): Weak entity "Contracts" has "salary" attribute, but "salary" is not a key. the key for a contract are the name of the studio and the star involved, plus the title and year of the move involved.

2 principle causes of Weak Entity Sets

1. hierarchy base. 2. Common source of weak entity sets.

isa

1. is a relationship connecting between an entity set to its subclasses. 2. we are using triangle to define the relationship. 3. is one to one relationship

arrow notation of referential integrity

1. is a rounded arrow head. 2. many to one from E to F with requirement of given entity of E which relates to F

4.3 constraints in ER

1. keys 2. Referential Integrity 3. Degree constraints

Multiplicity of binary E/R relationships

1. many to one 2. one to one 3. many to many

special case

1. many to one is special case of a many to many 2. one to one is special case of many to one.

how to underline key

1. underline only the primary key Ex: "stars" entity has "name, address" attributes, but just underline "name." 2. can underline more than 1 attributes in an entity because they are each members of the key. Ex: entity "Movies" has "title, year, length, genre" "title and year" is underline.

converting multiway relationships to binary

1.it is useful to observe that any relationship connecting more than two entity sets can be converted to a collection of binary, many to one relationships.

Entity Sets Versus Attributes

An entity set should satisfy at least one of the following conditions: 1. It is more than the name of something; it has at least one nonkey attribute. or 2. It is the "many" in a many-one or many-many relationship.

the conversion of idea to a high-level design

Idea --> High-Level Design ---> Relational Database Schema ---> Relational DBMS

many to many

R is neither many to one from E to F or from F to E, then we say R is many to many.

referential-integrity constraints

Referential integrity is a database concept that ensures that relationships between tables remain consistent. (When one table has a foreign key to another table, the concept of referential integrity states that you may not add a record to the table that contains the foreign key unless there is a corresponding record in the linked table. It also includes the techniques known as cascading update and cascading delete, which ensure that changes made to the linked table are reflected in the primary table. DBMS then enforces these rules.)

an arrow pointing in multiway relationships

an arrow pointing to an entity set E means that if we select one entity from each of the other entity sets in the relationship, those entities are related to at most one entity in E.

subclasses

an entity set contains certain entities (subclasses) that have special properties not associated with all members of the set.

Relationships

are connections among two or more entity sets.

Attributes

are properties of the entities in that set.

simplicity counts

avoid introducing more elements into your design than is absolutely necessary.

Multiway Relationships

define relationships involving more than two entity sets. (rare)

relationship label (roles)

each line to the entity set present a different role, we label the edges between the entity set and relationship by name, which we call "roles."

faithfulness

entity sets and their attributes should reflect reality.

4.3.4 Degree Constraints

is a bounding number to the edges that connect a relationship to an entity set, indicating limits on the number of entities that can be connected to any one entity of the related entity set.

Entity-Relationship Diagrams

is a graph representing entity sets, attributes, and relationships.

Key

is a set of attributes for one entity set such that no two entities in this set agree on all the attributes of the key.

4.4 Weak entity sets

is an entity set's key belong to another entity set.

drawing lines from the relationship

it is possible that one entity set appears two or more times in a single relationship. If so, we draw as many lines from the relationship to the entity set as the entity set appears in the relationship.

Instances of an E/R diagram

particular data to describe a database.

avoiding redundancy

should be careful to say everything once only.

choosing the Right Relationships

to avoid redundancy, update anomalies, and deletion anomalies, where the connected pairs or sets of entities for one relationship can be deduced from one or more other relationships.

entity-relationship (E/R) model

using 3 principal element types: 1. Entity sets, 2. Attributes, and 3. Relationships.