rdbms and sql er model - vvtesh.co.invvtesh.co.in/teaching/dbms2019/lecture2-ermodel.pdf · er...
TRANSCRIPT
![Page 1: RDBMS and SQL ER MODEL - vvtesh.co.invvtesh.co.in/teaching/dbms2019/Lecture2-ERModel.pdf · ER Model •Models an enterprise as a collection of entities and relationships •Entity:](https://reader030.vdocuments.us/reader030/viewer/2022040606/5eae1ecf03c6c9712900e2b5/html5/thumbnails/1.jpg)
Venkatesh Vinayakarao (Vv)
RDBMS and SQL
ER MODEL
Venkatesh [email protected]
http://vvtesh.co.in
Chennai Mathematical Institute
Slide contents are borrowed from the official website of the course text. For the authors’ original version of slides, visit: https://www.db-book.com/db6/slide-dir/index.html.
![Page 2: RDBMS and SQL ER MODEL - vvtesh.co.invvtesh.co.in/teaching/dbms2019/Lecture2-ERModel.pdf · ER Model •Models an enterprise as a collection of entities and relationships •Entity:](https://reader030.vdocuments.us/reader030/viewer/2022040606/5eae1ecf03c6c9712900e2b5/html5/thumbnails/2.jpg)
Database Design Using The Entity-Relationship ModelA Data Model
67
![Page 3: RDBMS and SQL ER MODEL - vvtesh.co.invvtesh.co.in/teaching/dbms2019/Lecture2-ERModel.pdf · ER Model •Models an enterprise as a collection of entities and relationships •Entity:](https://reader030.vdocuments.us/reader030/viewer/2022040606/5eae1ecf03c6c9712900e2b5/html5/thumbnails/3.jpg)
ER Model
• Models an enterprise as a collection of entities and relationships• Entity: a “thing” or “object” in the enterprise that is
distinguishable from other objects• Described by a set of attributes
• Relationship: an association among several entities
• Represented diagrammatically by an entity-relationship diagram.
• The ER data model employs three basic concepts: • entity sets,• relationship sets, • attributes.
![Page 4: RDBMS and SQL ER MODEL - vvtesh.co.invvtesh.co.in/teaching/dbms2019/Lecture2-ERModel.pdf · ER Model •Models an enterprise as a collection of entities and relationships •Entity:](https://reader030.vdocuments.us/reader030/viewer/2022040606/5eae1ecf03c6c9712900e2b5/html5/thumbnails/4.jpg)
Entity Sets
• Entities can be represented graphically as follows:• Rectangles represent entity sets.
• Attributes listed inside entity rectangle
• Underline indicates primary key attributes
![Page 5: RDBMS and SQL ER MODEL - vvtesh.co.invvtesh.co.in/teaching/dbms2019/Lecture2-ERModel.pdf · ER Model •Models an enterprise as a collection of entities and relationships •Entity:](https://reader030.vdocuments.us/reader030/viewer/2022040606/5eae1ecf03c6c9712900e2b5/html5/thumbnails/5.jpg)
Relationship Sets
• Diamonds represent relationship sets.
70
![Page 6: RDBMS and SQL ER MODEL - vvtesh.co.invvtesh.co.in/teaching/dbms2019/Lecture2-ERModel.pdf · ER Model •Models an enterprise as a collection of entities and relationships •Entity:](https://reader030.vdocuments.us/reader030/viewer/2022040606/5eae1ecf03c6c9712900e2b5/html5/thumbnails/6.jpg)
Mapping Cardinalities
• Specifies constraints on entity-entity association.
71
1 : *
![Page 7: RDBMS and SQL ER MODEL - vvtesh.co.invvtesh.co.in/teaching/dbms2019/Lecture2-ERModel.pdf · ER Model •Models an enterprise as a collection of entities and relationships •Entity:](https://reader030.vdocuments.us/reader030/viewer/2022040606/5eae1ecf03c6c9712900e2b5/html5/thumbnails/7.jpg)
Relationship Sets with Attributes
72
![Page 8: RDBMS and SQL ER MODEL - vvtesh.co.invvtesh.co.in/teaching/dbms2019/Lecture2-ERModel.pdf · ER Model •Models an enterprise as a collection of entities and relationships •Entity:](https://reader030.vdocuments.us/reader030/viewer/2022040606/5eae1ecf03c6c9712900e2b5/html5/thumbnails/8.jpg)
Roles
• Entity sets of a relationship need not be distinct• Each occurrence of an entity set plays a “role” in the
relationship
• The labels “course_id” and “prereq_id” are called roles.
![Page 9: RDBMS and SQL ER MODEL - vvtesh.co.invvtesh.co.in/teaching/dbms2019/Lecture2-ERModel.pdf · ER Model •Models an enterprise as a collection of entities and relationships •Entity:](https://reader030.vdocuments.us/reader030/viewer/2022040606/5eae1ecf03c6c9712900e2b5/html5/thumbnails/9.jpg)
Attribute Types
74
Derived Attribute
Composite Attribute
Key
![Page 10: RDBMS and SQL ER MODEL - vvtesh.co.invvtesh.co.in/teaching/dbms2019/Lecture2-ERModel.pdf · ER Model •Models an enterprise as a collection of entities and relationships •Entity:](https://reader030.vdocuments.us/reader030/viewer/2022040606/5eae1ecf03c6c9712900e2b5/html5/thumbnails/10.jpg)
Total Vs. Partial Participation
75
Every customer need not borrow loan!
![Page 11: RDBMS and SQL ER MODEL - vvtesh.co.invvtesh.co.in/teaching/dbms2019/Lecture2-ERModel.pdf · ER Model •Models an enterprise as a collection of entities and relationships •Entity:](https://reader030.vdocuments.us/reader030/viewer/2022040606/5eae1ecf03c6c9712900e2b5/html5/thumbnails/11.jpg)
Weak Entities
76
Entities without key attributes.
Payment does not exist without a loan.
payment is existence dependent on loan.
![Page 12: RDBMS and SQL ER MODEL - vvtesh.co.invvtesh.co.in/teaching/dbms2019/Lecture2-ERModel.pdf · ER Model •Models an enterprise as a collection of entities and relationships •Entity:](https://reader030.vdocuments.us/reader030/viewer/2022040606/5eae1ecf03c6c9712900e2b5/html5/thumbnails/12.jpg)
Entities vs. Attributes
• Use of entity sets vs. attributes
• Use of phone as an entity allows extra information about phone numbers (plus multiple phone numbers)
![Page 13: RDBMS and SQL ER MODEL - vvtesh.co.invvtesh.co.in/teaching/dbms2019/Lecture2-ERModel.pdf · ER Model •Models an enterprise as a collection of entities and relationships •Entity:](https://reader030.vdocuments.us/reader030/viewer/2022040606/5eae1ecf03c6c9712900e2b5/html5/thumbnails/13.jpg)
Reduction to Relation Schemas
• Entity sets and relationship sets can be expressed uniformly as relation schemas.
• A database which conforms to an E-R diagram can be represented by a collection of schemas.
• For each entity set and relationship set there is a unique schema that is assigned the name of the corresponding entity set or relationship set.
![Page 14: RDBMS and SQL ER MODEL - vvtesh.co.invvtesh.co.in/teaching/dbms2019/Lecture2-ERModel.pdf · ER Model •Models an enterprise as a collection of entities and relationships •Entity:](https://reader030.vdocuments.us/reader030/viewer/2022040606/5eae1ecf03c6c9712900e2b5/html5/thumbnails/14.jpg)
Representing Entity Sets
• A strong entity set reduces to a schema with the same attributes
student(ID, name, tot_cred)
• A weak entity set becomes a table that includes a column for the primary key of the identifying strong entity set
section ( course_id, sec_id, sem, year )
![Page 15: RDBMS and SQL ER MODEL - vvtesh.co.invvtesh.co.in/teaching/dbms2019/Lecture2-ERModel.pdf · ER Model •Models an enterprise as a collection of entities and relationships •Entity:](https://reader030.vdocuments.us/reader030/viewer/2022040606/5eae1ecf03c6c9712900e2b5/html5/thumbnails/15.jpg)
Representing Relationship Sets
• A many-to-many relationship set is represented as a schema with attributes for the primary keys of the two participating entity sets, and any descriptive attributes of the relationship set.
• Example: schema for relationship set advisor
advisor = (s_id, i_id)
![Page 16: RDBMS and SQL ER MODEL - vvtesh.co.invvtesh.co.in/teaching/dbms2019/Lecture2-ERModel.pdf · ER Model •Models an enterprise as a collection of entities and relationships •Entity:](https://reader030.vdocuments.us/reader030/viewer/2022040606/5eae1ecf03c6c9712900e2b5/html5/thumbnails/16.jpg)
Representation of Entity Sets with Composite Attributes
• Composite attributes are flattened out by creating a separate attribute for each component attribute
•instructor(ID, first_name, middle_initial,
last_name, street_number, street_name, apt_number, city, state, zip_code)
![Page 17: RDBMS and SQL ER MODEL - vvtesh.co.invvtesh.co.in/teaching/dbms2019/Lecture2-ERModel.pdf · ER Model •Models an enterprise as a collection of entities and relationships •Entity:](https://reader030.vdocuments.us/reader030/viewer/2022040606/5eae1ecf03c6c9712900e2b5/html5/thumbnails/17.jpg)
Summary
• ER Model is made of • entity sets,
• relationship sets and
• attribute sets.
• We have a standard UML-kind notation to draw ER diagrams.
• Although it looks natural, designing a good ER model requires creativity.
82