dbms - fields and keys - maryville college · keys crow’s foot diagrams how to identify keys...

IntroductionAttributes

KeysCrow’s Foot Diagrams

DBMS - Fields and Keys

Robert Lowe

Division of Mathematics and Computer ScienceMaryville College

February 4, 2016

Robert Lowe DBMS - Fields and Keys



Outline

1 Introduction

2 Attributes

3 Keys

4 Crow’s Foot Diagrams




Review: Entities

An entity is the person, place, or thing you are storinginformation about.

Bank Account Transaction




Review: Relationships

Relationship establishlinks between entities.Cardinality is the numberof entities involved in arelationship.Degree is the number ofentity types involved in therelationship.

Relationship




Review: Attributes

Attributes are the individual pieces of information which makeup an entity.




Identifying Attributes

Look at each entity.Determine what parts of information areneeded in an entity.Give nice singular names to all of theattributes that you are going to store.Verify that each attribute represents onlyone value for each entity.

Attribute




When Attributes Become Entities

Sometimes, an attribute orgroup of attributes wouldnaturally take on manyvalues for an entity.Attributes should onlydescribe one value.In these situations, we turnattributes into entities andestablish relationships.

Name

Customer

Street Address City State

Zip

Phone




When Attributes Become Entities

Name

Customer

Street Address

City

State

Zip

1 NHas Address

1

N

Has

PhoneNumber

Phone Number




When Entities Become Attributes

Sometimes an entity is notnecessary!One-to-One relationshipstypically mean that anentity should really beeither an attribute, or thetwo entities should becombined.

Enrollment

1

1

Results In

Grade




When Entities Become Attributes

Enrollment

1

1

Results In

Grade

Enrollment

Grade




Introduction to Keys

Keys are used to identify entities.There are two types of keys:

Primary KeysForeign Keys

Effective key design is absolutely essential to effectivedatabase design.Keys can be either single attributes, or groups of attributes.




Primary Keys

A primary key is an attribute, or set of attributes, whichuniquely identify an entity.No two entities contain the same primary key value.A primary key should be selected in such a way that nopart of the primary key can uniquely identify any part of anentity. (More on this in future lectures!)Many times, the primary key is an arbitrarily created IDnumber.




Foreign Keys

A foreign key relates a child entity to its parent.A foreign key stores the primary key of the parent in achild.A properly constrained foreign key attribute cannot take onany value except that of the primary key of an existingparent entity.




Diagramming Keys - Primary Keys

Bank Account Transaction1 N

Has

AccountNumber

Balance

Owner

Amount

Description

Date




Diagramming Keys - Foreign Keys

Bank Account Transaction1 N

Has

AccountNumber

Balance

Owner

Amount

Description

Date

BankAccountAccountNumber




How to Identify Keys

Every entity in a well formed ER model must have aprimary key.Identify/create attributes which are unique to each entity.Foreign keys follow directly from primary keys.As a convention, primary keys should be named<entityName><attributeName>




The Complete ER Modeling Procedure

1 Identify entities.2 Identify relationships.3 Create intermediate entities for any many-to-many

relationships.4 Create attributes.5 Adjust for situations where attributes should become

entities and entities should become attributes.6 Identify/Create primary keys.7 Create foreign keys where needed.

Just remember the acronym: ERIAAK (Entities, Relationships,Intermediate, Attributes, Adjust, Keys).




Crow’s Foot Diagram

Developed by JamesMartin in 1990More formally called the“InformationEngineering/IE Model”.Crows are cooler. We’ll callit a crow’s foot diagram!A more compact ERdiagram.Lots of strangehieroglyphics, but it isn’ttoo bad.

image source: Wikicommons




Drawing Entities

Entity NameAttribute 1Attribute2Attribute3




Crow’s Foot Notation

Exactly One

Zero or One

One or Many

Zero or Many




The Bank Crow’s Foot Diagram

BankAccountAccountNumberOwnerBalance

TransactionIDDateDescriptionAmountBankAccountAccountNumber


dbms - fields and keys - maryville college · keys crow’s foot diagrams how to identify keys...

Documents