Database Systems Design and Development - (INFS428) - Lectures  

 

Primary Keys

     • Underlined in the ER diagram

     • Key attributes are also underlined in frequently used table structure shorthand

     • Ideally composed of only a single attribute

     • Possible to use a composite key:

          – Primary key composed of more than one attribute

 

 

Resolving Multivalued Attribute Problems

     • Although the conceptual model can handle multivalued attributes, you should not implement them in the relational DBMS

          – Within original entity, create several new attributes, one for each of the original multivalued attribute’s components

               • Can lead to major structural problems in the table

          – Create a new entity composed of original multivalued attribute’s components

 

 

 

 

 

 

Derived Attributes

     • Attribute whose value may be calculated (derived) from other attributes

     • Need not be physically stored within the database

     • Can be derived by using an algorithm

 

 

 

 

RELATIONSHIP Strength

     • Existence dependence

          – Entity’s existence depends on the existence of one or more other entities

     • Existence independence

          – Entity can exist apart from one or more related entities

     • Weak (non-identifying) relationships

          – One entity is not existence-independent on another entity

     • Strong (Identifying) Relationships

          – Related entities are existence-dependent

 

 

A Weak Relationship Between COURSE and CLASS

 

A Strong (Identifying) Relationship Between COURSE and CLASS

 

Relationship Strength and Weak Entities

     • Weak entity meets two conditions

          – Existence-dependent:

               • Cannot exist without entity with which it has a relationship

          – Has primary key that is partially or totally derived from the parent entity in the relationship

     • Database designer usually determines whether an entity can be described as weak based on the business rules

 

A Weak Entity in an ERD

 

A Weak Entity in a Strong Relationship

 

Relationship Degree

     • Indicates number of associated entities or participants

     • Unary relationship

          – Association is maintained within a single entity

     • Binary relationship

          – Two entities are associated

     • Ternary relationship

          – Three entities are associated

 

 

Three Types of Relationships

 

The Implementation of a Ternary Relationship

 

The 1:1 Recursive Relationship “EMPLOYEE is Married to EMPLOYEE”

 

Implementation of the M:N Recursive “PART Contains PART” Relationship

 

Implementation of the 1:M “EMPLOYEE Manages EMPLOYEE” Recursive Relationship

 

Converting the M:N Relationship into Two 1:M Relationships

 

The M:N Relationship Between STUDENT and CLASS

 

A Composite Entity in an ERD

 

The Chen Representation of the Invoicing Problem

 

The Crow’s Foot Representation of the Invoicing Problem

 

Developing an ER Diagram

     • Database design is an iterative rather than a linear or sequential process

     • Iterative process

          – Based on repetition of processes and procedures

 

A Supertype/Subtype Relationship

 

A Supertype/Subtype Relationship in an ERD

 

Components of the ER Model

 

The Completed Tiny College ERD

 

Various Implementations of a 1:1 Recursive Relationship

 

 

                Source: Rob and Coronel - Database Systems: Design, Implementation and Management – 6th Edition Course Technology