from er diagrams to the relational model rose-hulman institute of technology curt clifton
Post on 20-Dec-2015
214 views
TRANSCRIPT
![Page 1: From ER Diagrams to the Relational Model Rose-Hulman Institute of Technology Curt Clifton](https://reader030.vdocuments.us/reader030/viewer/2022032800/56649d485503460f94a23294/html5/thumbnails/1.jpg)
From ER Diagrams to the Relational Model
Rose-Hulman Institute of Technology
Curt Clifton
![Page 2: From ER Diagrams to the Relational Model Rose-Hulman Institute of Technology Curt Clifton](https://reader030.vdocuments.us/reader030/viewer/2022032800/56649d485503460f94a23294/html5/thumbnails/2.jpg)
Review – Entity Sets and Attributes Entity set: collection of “things” in the DB Attribute: property of an entity
Soda
name
calories
![Page 3: From ER Diagrams to the Relational Model Rose-Hulman Institute of Technology Curt Clifton](https://reader030.vdocuments.us/reader030/viewer/2022032800/56649d485503460f94a23294/html5/thumbnails/3.jpg)
Kinds of Attributes Simple – single atomic value
Soda name, calories Composite – several sub-attributes
PersonName(First,Middle,Last) Multi-valued – set of values for one attribute
Car color, Degrees earned (Somewhat rare, makes some searches harder)
![Page 4: From ER Diagrams to the Relational Model Rose-Hulman Institute of Technology Curt Clifton](https://reader030.vdocuments.us/reader030/viewer/2022032800/56649d485503460f94a23294/html5/thumbnails/4.jpg)
Review – Keys Let us tell entities apart The key for an entity set is a subset of the
attributes for that entity set, such that no two entities agree on all the attributes
Soda
name
calories
![Page 5: From ER Diagrams to the Relational Model Rose-Hulman Institute of Technology Curt Clifton](https://reader030.vdocuments.us/reader030/viewer/2022032800/56649d485503460f94a23294/html5/thumbnails/5.jpg)
Review – Relationships Associate 2 or more entity sets Constraints
Maximums shown with numbers Read like: a subject-verb-number-object
Participation shown with double line Read like: a subject-has to-verb…
Person SodaMost Likes
N 1
![Page 6: From ER Diagrams to the Relational Model Rose-Hulman Institute of Technology Curt Clifton](https://reader030.vdocuments.us/reader030/viewer/2022032800/56649d485503460f94a23294/html5/thumbnails/6.jpg)
ER Design Techniques Avoid redundancy and don’t duplicate data Don’t use entity set when attribute will do Limit use of weak entity sets
![Page 7: From ER Diagrams to the Relational Model Rose-Hulman Institute of Technology Curt Clifton](https://reader030.vdocuments.us/reader030/viewer/2022032800/56649d485503460f94a23294/html5/thumbnails/7.jpg)
N 1
Redundancy Wastes space Leads to inconsistency For example:
Soda
name
manf
Manf
name
addr
Madeby
![Page 8: From ER Diagrams to the Relational Model Rose-Hulman Institute of Technology Curt Clifton](https://reader030.vdocuments.us/reader030/viewer/2022032800/56649d485503460f94a23294/html5/thumbnails/8.jpg)
Failed Attempt At Fix
Soda
name
manf Manf addr
Delete Manf entity set Add address to Soda
![Page 9: From ER Diagrams to the Relational Model Rose-Hulman Institute of Technology Curt Clifton](https://reader030.vdocuments.us/reader030/viewer/2022032800/56649d485503460f94a23294/html5/thumbnails/9.jpg)
Successful Fix Eliminate manf attribute from Soda Use relationship to find manufacturer info.
N 1
Soda
name
Manf
name
addr
Madeby
![Page 10: From ER Diagrams to the Relational Model Rose-Hulman Institute of Technology Curt Clifton](https://reader030.vdocuments.us/reader030/viewer/2022032800/56649d485503460f94a23294/html5/thumbnails/10.jpg)
Don’t Use Unnecessary Entity Sets Entity set should…
Have at least one non-key attribute OR Be the “many” in a many-one or many-many
relationship
N 1
Soda
name
Manf
name
addr
Madeby
![Page 11: From ER Diagrams to the Relational Model Rose-Hulman Institute of Technology Curt Clifton](https://reader030.vdocuments.us/reader030/viewer/2022032800/56649d485503460f94a23294/html5/thumbnails/11.jpg)
Bad Entity Set Suppose we didn’t have manufacturer address
N 1
Soda
name
Manf
name
Madeby
![Page 12: From ER Diagrams to the Relational Model Rose-Hulman Institute of Technology Curt Clifton](https://reader030.vdocuments.us/reader030/viewer/2022032800/56649d485503460f94a23294/html5/thumbnails/12.jpg)
Avoid Weak Entity Sets Don’t try to be too clever Can usually just add a unique ID Government has done this for their databases:
Social Security Numbers Vehicle Identification Numbers
But… Don’t trust uniqueness of IDs assigned by others
![Page 13: From ER Diagrams to the Relational Model Rose-Hulman Institute of Technology Curt Clifton](https://reader030.vdocuments.us/reader030/viewer/2022032800/56649d485503460f94a23294/html5/thumbnails/13.jpg)
Why Use Weak Entity Sets At All? Federated Databases, for example…
All students in Indiana receiving state aid All players on FIFA soccer teams
One query sent to multiple DB Still want a Conceptual DB Schema But no global authority to assign unique IDs
![Page 14: From ER Diagrams to the Relational Model Rose-Hulman Institute of Technology Curt Clifton](https://reader030.vdocuments.us/reader030/viewer/2022032800/56649d485503460f94a23294/html5/thumbnails/14.jpg)
The Relational Model Originated as theoretical idea
“A Relational Model of Data for Large Shared Data Banks”, E. F. Codd, Comm. of the ACM, 13(6), June 1970
http://www.acm.org/classics/nov95/s1p3.html Revolutionized databases Led to 1981 ACM Turing Award
The “Nobel Prize of computing”
![Page 15: From ER Diagrams to the Relational Model Rose-Hulman Institute of Technology Curt Clifton](https://reader030.vdocuments.us/reader030/viewer/2022032800/56649d485503460f94a23294/html5/thumbnails/15.jpg)
Relations (Semi-) Formally
Tuple: an ordered list n-tuple: an ordered list of length n Relation: a set of n-tuples
Informally: Relation: a table with unique rows Rows = tuples; Columns = attributes; Values in column = domain
Database: a collection of relations
![Page 16: From ER Diagrams to the Relational Model Rose-Hulman Institute of Technology Curt Clifton](https://reader030.vdocuments.us/reader030/viewer/2022032800/56649d485503460f94a23294/html5/thumbnails/16.jpg)
Some Other Terms Relation schema
Describes a relation RelationName (AttrName1, AttrName2,…) Or RelationName (AttrName1:type, …)
Database schema Set of all the relation schema for the DB’s
relations
![Page 17: From ER Diagrams to the Relational Model Rose-Hulman Institute of Technology Curt Clifton](https://reader030.vdocuments.us/reader030/viewer/2022032800/56649d485503460f94a23294/html5/thumbnails/17.jpg)
Why is the Relational Model Dominant?
Very simple – just one data structure Matches a “list the items” mentality Easy to manipulate tables with UI Forms basic foundation for SQL
Relational model based on sets SQL based on bags (a.k.a., multi-sets)
![Page 18: From ER Diagrams to the Relational Model Rose-Hulman Institute of Technology Curt Clifton](https://reader030.vdocuments.us/reader030/viewer/2022032800/56649d485503460f94a23294/html5/thumbnails/18.jpg)
From ER Diagrams to Relations Entity sets become relations
Columns are attributes of entity set Relationships also become relations
Columns are keys of participating entity sets
![Page 19: From ER Diagrams to the Relational Model Rose-Hulman Institute of Technology Curt Clifton](https://reader030.vdocuments.us/reader030/viewer/2022032800/56649d485503460f94a23294/html5/thumbnails/19.jpg)
Example: Basic Entity Sets
Soda
name
calories
Customer
name
addr
![Page 20: From ER Diagrams to the Relational Model Rose-Hulman Institute of Technology Curt Clifton](https://reader030.vdocuments.us/reader030/viewer/2022032800/56649d485503460f94a23294/html5/thumbnails/20.jpg)
Example: Basic Relationship
Soda
name calories
Likes
MostLikes
Customer
nameaddr
BuddyWith
1
Naming buddy
Namedbuddy
since
![Page 21: From ER Diagrams to the Relational Model Rose-Hulman Institute of Technology Curt Clifton](https://reader030.vdocuments.us/reader030/viewer/2022032800/56649d485503460f94a23294/html5/thumbnails/21.jpg)
Simplifying! Can avoid relations for
many-one relationships
Just add key of the one to the relation of the many
Soda
MostLikes
Customer
1
nameaddr
name calories
![Page 22: From ER Diagrams to the Relational Model Rose-Hulman Institute of Technology Curt Clifton](https://reader030.vdocuments.us/reader030/viewer/2022032800/56649d485503460f94a23294/html5/thumbnails/22.jpg)
Over Simplifying! What happens if we try to eliminate relation
for a many-many relationship? Consider treating Likes as we did Most Likes
Redundancy Data loss
![Page 23: From ER Diagrams to the Relational Model Rose-Hulman Institute of Technology Curt Clifton](https://reader030.vdocuments.us/reader030/viewer/2022032800/56649d485503460f94a23294/html5/thumbnails/23.jpg)
Weak Entity Sets Need enough columns to make rows unique! So…
All attributes of weak entity set + Key from supporting relationship
Player TeamPlayson
name namenumber
1
city
![Page 24: From ER Diagrams to the Relational Model Rose-Hulman Institute of Technology Curt Clifton](https://reader030.vdocuments.us/reader030/viewer/2022032800/56649d485503460f94a23294/html5/thumbnails/24.jpg)
Entity Sets with Subclasses Use nulls, or Use multiple relations
“ER Style” How should we choose
which to use?
Soda
name calories
DietSoda
sweetenerisa