Lec 5 E-R model

Entity Sets

1. Entity with several attributes is considered to be the basic block of database system.
   • Entity can be either concrete or abstract.
     • Example: specific student, company, event, plant.
   • Attribute types
     • Simple(sex : man or woman) and Composite (name : first-name, middle-name, last-name)
     • Singled-valued(id) and multi-valued(phone number)
     • Derived attributes(in another word,optional attributes, like hobbies, pets and something that's not so necessary)
2. Entities are connected by "relationships"(will be mentioned in next part).
   • Go to next point for details

Relationship Sets

1. relationship set is an association among several entities(usually binary)

   • Example:

   RelationSet  CustomerEntity   LoanEntity
   borrower1   Jones                  L-17
   borrower2   Mike                   L-11
       ```
   
   

2. Binary relationship set types

   • one to one
   • one to many
   • many to one
   • many to many

3. Keys

   • Super key : a set of some special attributes which uniquely determined each entity

   • Candidate key : minal member of super key

   • Example :
     Candidate key:{cus-num}, {cus-name},{cus-id}
     Super key:{cus-num, cus-name,cus-id}

   • The combination of primary keys participating entity sets forms a super key of relation set [e.g. (customer-id, loan-number) is the super key of borrower]

   • The difference between candidate key and primary key

     • Considering the semantics, we will select one from candidate keys to be the primary key. (?)
     • To be continued

4. E-R Diagram

   • Basic information

     • Rectangles（长方形） represent entity sets.
     • Diamonds（菱形） represent relationship sets.
     • Lines link attributes to entity sets and entity sets to relationship sets.
     • Ellipses（椭圆） represent attributes.
       • Double ellipses represent multivalued attributes.
       • Dashed ellipses denote derived attributes.

     • Underline indicates primary key attributes
       

       Screen Shot 2015-05-02 at 7.24.55 PM.png

   • Sometimes there will be a descriptive attribute of the relationship such as "access-date".

     
     
     Screen Shot 2015-05-02 at 7.26.25 PM.png

   • Recursive relationship set
     

     Screen Shot 2015-05-02 at 7.27.10 PM.png

   • Binary relation types:

     -> : to one, -- : to many

     • Example:

       1. Customer <- borrower -> loan

          A loan is associated with at most one customer via borrower

       2. Customer <- borrower -> loan

          A customer is associated with several (including 0) loans via borrower.

       3. Customer -- borrower -> loan

          A customer is associated with at most one loan via borrower.

       4. Customer -- borrower -- loan

          A loan is associated with several (possibly 0) customers via borrower.

     • Total participation(double line) & Patial participation(single line)

     • Binary vs. Non-binary

       • Convert

         parents(he, she, child) => father(he, child), mother(she, child)

Weak Entity Sets

1. An entity set that does not have a primary key is referred to as a weak entity set.
2. Instead of primary key, it will have one or more discriminator or partial key.
3. Weak entity should depend on a strong entity which has a primary key.

Extended E-R Features

1. Specialization & Generalization

   • Similar to the "herited" concept in oop programming.
   • This should satisfy "disjoint" and "overlaping"

2. Total and Parital

   • Total: an entity must belong to one of the lower-level entity sets.
   • Partial: an entity need not belong to one of the lower-level entity sets.

3. Aggregation

   • Similar to the "Encapsulation" in oop programming.

   • Treat relationship as an abstract entity.

     
     
     Screen Shot 2015-05-02 at 7.44.37 PM.png
     
     
     Screen Shot 2015-05-02 at 7.44.41 PM.png

Design of an E-R Database Schema

Pass

Reduction of an E-R Schema to Tables

1. A strong entity set => to a table with the same attributes.

2. Composite attributes are flattened out by creating a separate attribute for each component attribute.

   • customer(customer-id,name, cust-street, cust-city)
   • name(first-name, last-name)

   ==>

   • customer(customer-id, first-name, last-name, cust-street, cust-city)

3. A multivalued attribute M of an entity E is represented by a separate table.

   • employee(emp-id, ename, sex, age, dependent-names(Derived))

   ==>

   • employee(emp-id, ename, sex, age)
   • employee-dependent-names(emp-id, dependent-name)

4. Many-to-one and one-to-many relationship sets that are total on the many-side can be represented by adding an extra attribute to the “many” side, containing the primary key of the one side. (If there is parital participation, there may be some NULL values which we do not care)

   • account(account-number, balance)
   • branch(branch-name, branch-city, assets)
   • account-branch(account-number,_ branch-number_)

   ==>

   • account(account-number, branch-name, balance)
   • branch(branch-name, branch-city, assets)

5. one-to-one relationship sets, either side can be chosen to act as the “many” side.

6. The table corresponding to a relationship set linking a weak entity set to its identifying strong entity set is redundant. (Similar to the method mention in 4.)

Representing Specialization