Database Management/E-R Diagrams

From Wikiversity
Jump to navigation Jump to search

This lesson allows users to learn how to represent entities, attributes, and relationships on an entity-relationship diagram.

Objectives and Skills[edit | edit source]

Objectives and skills for this lesson include:

Readings[edit | edit source]

  1. McFadyen: Relational Databases and Microsoft Access - Chapter 7 (Entity Relationship Modeling)
  2. Wikibooks: Databases: Entity Relationships
  3. ER Diagram Tutorial: SmartDraw: Entity Relationship Diagrams
  4. Supportive Information: Watt: Database Design (2nd edition) - Chapter 8 (The Entity Relationship Data Model)
  5. Supportive Information: Microsoft: Designing Diagrams
  6. What is Entity Relationship Diagram?: [1]

Multimedia[edit | edit source]

Learn How to create an Entity Relationship Diagram:

  1. Youtube: Entity Relationship Diagram (ERD) Training Video Learn How to conceptually create your own diagram)
  2. Youtube: Database Design DBDL and ERD Video Part 1: How to understand and create Database Design Language (DBDL)
  3. Youtube: Database Design DBDL and ERD Video Part 2: How to Exploring Entity-Relationship Diagrams
  4. Youtube: Entity Relationship Diagram - Part 1
  5. Youtube: Entity Relationship Diagram (ERD) Tutorial - Part 2
  6. Youtube: How to Create a Database Diagram Using SQL Server Management Studio
  7. Youtube: Entity-Relationship Diagrams

Activities[edit | edit source]

  1. Read Watt: Database Design - 2nd Edition - Chapter 8 (Entity Relationship Data Model). Complete the required exercises.
  2. Read McFadyen: Relational Databases and Microsoft Access - Chapter 7 (Entity Relationship Modeling).

Lesson Summary[edit | edit source]

  • A entity-relationship (E-R) model was developed by Peter Chen.[1]
  • The semantic model is a methodology example that provides structural meaning between data and relationships.[2]
  • Common Access data types include integers, short text, long text, date/time, autonumber, etc.[3]
  • ER modeling is based on two concepts: 1. Entities, defined as tables that hold specific information (data). 2. Relationships, defined as the associations or interactions between entities.[4]
  • The key is an attribute or a group of attributes whose values can be used to uniquely identify an individual entity in an entity set.[5]
  • There are different types of keys including: candidate, composite, primary, secondary, alternate and foreign.
  • Relationships are used to connect related information between tables.[6]
  • There are a variety of relationships including: one-to-one relationship, one-to-many relationship, many-to-many relationship.[7]

Key Terms[edit | edit source]

alternate key
Alternate keys are all candidate keys not chosen as the primary key.[8]
A column in a database table can be also be called an attribute.[9]
candidate keys
A candidate key is a simple or composite key that is unique and minimal. It is unique because no two rows in a table may have the same value at any time. [10]
composite keys
A composite key is composed of two or more attributes, but it must be minimal.[11]
database design language (DBDL)
A standard database notation used to display tables and keys during the database design process. A table is represented by identifying the primary key along with listing all the fields/columns.
derived attributes
A derived attribute contains values calculated from other attributes.[12]
foreign key
A foreign key (FK) is an attribute in a table that references the primary key in another table OR it can be null. Both foreign and primary keys must be of the same data type.[13]
An entity may be defined as a thing capable of an independent existence that can be uniquely identified and can have a relationship with other entities.[14]
A key identifies unique rows in a table that also establish a relationship between those tables.
many-to-many relationship
Implemented by means of an associative table (also known as junction table or cross-reference table), say, AB with two one-to-many relationships A -> AB and B -> AB. In this case the logical primary key for AB is formed from the two foreign keys (i.e. copies of the primary keys of A and B).[15]
A special symbol that means the value is either unknown or inapplicable.
one-to-one relationship
A one-to-one relationship exists when one row in a table may be linked with only one row in another table and vice versa.[16]
one-to-many relationship
a one-to-many relationship exists when one row in table A may be linked with many rows in table B, but one row in table B is linked to only one row in table A.[17]
primary key
The primary key is a candidate key that is selected by the database designer to be used as an identifying mechanism for the whole entity set. It must uniquely identify tuples in a table and not be null.[18];
a relationship between entities is implemented by storing the primary key of one entity as a pointer or "foreign key" in the table of another entity.[19]
subtype discriminator
an attribute of the supertype that indicates an entity's subtype [20]
secondary key
An example of an alternate key.

Review Questions[edit | edit source]

Enable JavaScript to hide answers.
Click on a question to see the answer.
  1. The primary key is a _______ able to identify every _____ of the table.
    The primary key is a column able to identify every row of the table.[21]
  2. What type of key specified for an entity is meaningless to the entity and to end-users.
    A surrogate key,doesn’t describe any characteristic of an entity
  3. A______to________relationship exists when one row in table is linked with many rows in another table.
    one to many

See Also[edit | edit source]

References[edit | edit source]