L04.pdf

IT5008 Database Design & Programming L04: ER Diagrams IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 1 / 39 Case Study IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. S...

IT5008 Database Design & Programming L04: ER Diagrams IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 1 / 39 Case Study IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 2 / 39 Case Study ❱ Requirement Requirement Design Game Store Our company, Apasaja Pte Ltd, has been commissioned to develop an application to manage the data of an online app store. We want to store several items of information about our customers such as their first name, last name, date of birth, e-mail, date and country of registration to our online sales service and the customer identifier that they have chosen. We also want to manage the list of our products, games, their name, their version, and their price. The price is fixed for each version of each game. Finally, our customers buy and download games. We record which version of which game each customer has downloaded. It is not essential to keep the download date for this application. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 3 / 39 Case Study Requirement Design ❱ Design Entity-Relationship Diagram IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 4 / 39 Case Study Requirement Design ❱ Design Entity-Relationship Diagram IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 5 / 39 Diagram IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 6 / 39 Diagram ❱ Entities Sets Entities Attributes Sets Relationships Aggregation Entities and Entity Sets Consideration Entities are identifiable things. The named box represents a set of entities or entity set. Concept Diagram IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 7 / 39 Diagram ❱ Entities Sets Entities Attributes Attributes Relationships Aggregation Attributes, Values, and Value Sets Consideration The ER model is value-oriented. Values can be integer, strings, or atoms. Attributes of Entities Diagram Entities can have attributes. All entities in one entity set have the same attributes. However, the attributes take different values for each entity. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 8 / 39 Diagram Entities Relationships ❱ Relationships Sets Sets Attributes Same Entity Set n-Ary Relationships and Relationship Sets Aggregation Relationship associates two entities (can be fewer or more). The named diamond represents a Consideration set of relationships or relationship set. Association Diagram A relationship set is a set of relationships associating entities from the same entity sets. In the example on the right, we exclude attributes for simplicity. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 9 / 39 Diagram Entities Relationships ❱ Relationships Sets Sets Attributes Same Entity Set n-Ary Relationships and Relationship Sets Aggregation Relationship associates two entities (can be fewer or more). The named diamond represents a Consideration set of relationships or relationship set. Idea Diagram IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 10 / 39 Diagram Entities Relationships ❱ Relationships Sets Attributes Attributes Same Entity Set n-Ary Distinguishing Relationships Aggregation Relationship are distinguished not by their attributes but by their participating entities. Consideration Attributes of Relationships Diagram Relationship can have attributes. All relationships in one relationship set have the same attributes. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 11 / 39 Diagram Entities Relationships ❱ Relationships Sets Same Entity Set Attributes Same Entity Set n-Ary Associating the Same Entity Set Aggregation Relationships can associate entities from the same entity set. In this case (and in general), Consideration participation (or role), in the relationship can be named. Idea Diagram IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 12 / 39 Diagram Entities Relationships ❱ Relationships Sets n-Ary Attributes Same Entity Set n-Ary More Than 2 Entity Sets Aggregation Relationship sets can associate more than 2 entity sets. We call the n-ary relationship sets. Consideration We call the relationship as n-ary relationships. Idea Diagram IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 13 / 39 Diagram Entities Aggregation Relationships ❱ Aggregation Relationship Sets as Entity Sets Consideration Associating with Relationship Set In some instances, we want to associate an entity set with a relationship set. We represent this by wrapping the relationship set in a box. Diagram IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 14 / 39 Diagram Entities Consideration Relationships Aggregation Entity or Relationship? ❱ Consideration Alternative #1 Alternative #2 IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 15 / 39 Identities and Cardinalities IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 16 / 39 Identities and Cardinalities ❱ Identities Key Attribute Identities Multi Key Partial Key Key Attribute Cardinality Entities' Identity One or more attributes can identify the entity. This is a property of all entities in an entity set✱. We use underline to differentiate the key attributes. Single Attribute Multiple Attributes ✱ At least all attributes identify the entity. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 17 / 39 Identities and Cardinalities ❱ Identities Key Attribute Identities Multi Key Partial Key Multi Key Cardinality Entities' Identity One or more attributes can identify the entity. This is a property of all entities in an entity set✱. We use underline to differentiate the key attributes. Several Keys Worst-Case ✱ At least all attributes identify the entity. Also, we prefer a minimal set of attributes. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 18 / 39 Identities and Cardinalities ❱ Identities Key Attribute Identities Multi Key Partial Key Partial Key Cardinality Partial Identification Some entities can only be identified within the scope of a relationship with another entity set. Note: The relationship must exist and be unique for each entity in the set. Weak Entities Diagram Matric numbers are given by the universities. The same number can be used by different universities. University is a dominant entity. Student is a weak entity (cannot be identified by its attributes alone). IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 19 / 39 Identities and Cardinalities Identities Cardinality ❱ Cardinality Participation Participation Classifications Examples Kinds of Participation The cardinality of the participation in a relationship can be constrained by a minimum and maximum value: (1, 1), (0, n), (2, 5), etc. Idea Diagram IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 20 / 39 Identities and Cardinalities Identities Cardinality ❱ Cardinality Participation Classifications Classifications Examples Common Names (1, x) mandatory participation (0, x) optional participation (x, 1) for all entities involved characterizes a one-to-one relationship (x, 1) for all entities involved and (x, N) or (x, y) for y > 1 for other characterizes a one-to-many relationship. (x, N) or (x, y) for y > 1 characterizes a many-to-many relationship. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 21 / 39 Identities and Cardinalities Identities Cardinality ❱ Cardinality Participation Examples Classifications Examples Example 1 Academic staff can supervise up to 5 research students. Some staff do not supervise students. Research students can have one or two supervisors. ✱ By default, if cardinality is omitted, we have many-to-many relationships. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 22 / 39 Identities and Cardinalities Identities Cardinality ❱ Cardinality Participation Examples Classifications Examples Example 2 Example of one-to-one relationship. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 23 / 39 Identities and Cardinalities Identities Cardinality ❱ Cardinality Participation Examples Classifications Examples Example 3 Weak entities can only be defined for a participation constrained by (1, 1) cardinalities (also called mandatory one-to-many relationships). IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 24 / 39 Schema Translation IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 25 / 39 Schema Translation ❱ Rules Value Sets Rules Entity Sets Relationship Sets Value Sets Exceptions Rule #1 Value sets are mapped to domains. In practice, this is a first step towards the physical design. ER attributes are mapped to attributes of relations. age INTEGER IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 26 / 39 Schema Translation ❱ Rules Value Sets Rules Entity Sets Relationship Sets Entity Sets Exceptions Rule #2 Entity sets are mapped to relations. The entity set attributes are mapped to attributes of the relation. The keys are mapped to primary key. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 27 / 39 Schema Translation ❱ Rules Value Sets Rules Entity Sets Relationship Sets Entity Sets Exceptions Rule #2 Entity sets are mapped to relations. The entity set attributes are mapped to attributes of the relation. The keys are mapped to primary key. CREATE TABLE company ( name VARCHAR(64) PRIMARY KEY, address VARCHAR(128) ); IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 28 / 39 Schema Translation ❱ Rules Value Sets Rules Entity Sets Relationship Sets Entity Sets Exceptions Rule #2 Entity sets are mapped to relations. The entity set attributes are mapped to attributes of the relation. The keys are mapped to primary key. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 29 / 39 Schema Translation ❱ Rules Value Sets Rules Entity Sets Relationship Sets Entity Sets Exceptions Rule #2 Entity sets are mapped to relations. The entity set attributes are mapped to attributes of the relation. The keys are mapped to primary key. CREATE TABLE person ( first_name VARCHAR(32), last_name VARCHAR(32), address VARCHAR(128) NOT NULL, PRIMARY KEY (first_name, last_name) ); IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 30 / 39 Schema Translation ❱ Rules Value Sets Rules Entity Sets Relationship Sets Relationship Sets Exceptions Rule #3 Relationship sets are mapped to relations. The attributes of the relation consist of the attributes of the relationship set. As well as of the keys of the participating entities. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 31 / 39 Schema Translation ❱ Rules Value Sets Rules Entity Sets Relationship Sets Relationship Sets Exceptions CREATE TABLE contract( start DATE NOT NULL, end DATE NOT NULL, first_name VARCHAR(32), last_name VARCHAR(32), name VARCHAR(64), PRIMARY KEY (first_name, last_name, name), FOREIGN KEY (first_name , last_name) REFERENCES person(first_name, last_name), FOREIGN KEY (name) REFERENCES company(name) ); IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 32 / 39 Schema Translation Rules Exceptions ❱ Exceptions One-to-Many One-to-Many (1, 1) Weak Entity Incorrect CREATE TABLE work_for ( start DATE NOT NULL, end DATE NOT NULL, enumber CHAR(8), cname VARCHAR(32), PRIMARY KEY (enumber, cname), FOREIGN KEY (enumber) REFERENCES employee(number), FOREIGN KEY (cname) REFERENCES company(name) ); IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 33 / 39 Schema Translation Rules Exceptions ❱ Exceptions One-to-Many One-to-Many (1, 1) Weak Entity Correct CREATE TABLE work_for ( start DATE NOT NULL, end DATE NOT NULL, enumber CHAR(8) PRIMARY KEY, cname VARCHAR(32), ​ FOREIGN KEY (enumber) REFERENCES employee(number), FOREIGN KEY (cname) REFERENCES company(name) ); IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 34 / 39 Schema Translation Rules Exceptions ❱ Exceptions One-to-Many (1, 1) (1, 1) Weak Entity Incorrect CREATE TABLE work_for ( start DATE NOT NULL, end DATE NOT NULL, enumber CHAR(8) PRIMARY KEY, cname VARCHAR(32), ​ FOREIGN KEY (enumber) REFERENCES employee(number), FOREIGN KEY (cname) REFERENCES company(name) ); IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 35 / 39 Schema Translation Rules Exceptions ❱ Exceptions One-to-Many (1, 1) (1, 1) Weak Entity Correct CREATE TABLE employee_work_for ( start DATE NOT NULL, end DATE NOT NULL, enumber CHAR(8) PRIMARY KEY, ename CHAR(32) NOT NULL, cname VARCHAR(32) NOT NULL, FOREIGN KEY (cname) REFERENCES company(name) ); IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 36 / 39 Schema Translation Rules Exceptions ❱ Exceptions One-to-Many Weak Entity (1, 1) Weak Entity Incorrect CREATE TABLE employee_work_for ( start DATE NOT NULL, end DATE NOT NULL, enumber CHAR(8) PRIMARY KEY, ename CHAR(32) NOT NULL, cname VARCHAR(32) NOT NULL, ​ FOREIGN KEY (cname) REFERENCES company(name) ); IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 37 / 39 Schema Translation Rules Exceptions ❱ Exceptions One-to-Many Weak Entity (1, 1) Weak Entity Correct CREATE TABLE employee_work_for ( start DATE NOT NULL, end DATE NOT NULL, enumber CHAR(8), ename CHAR(32) NOT NULL, cname VARCHAR(32) NOT NULL, PRIMARY KEY (enumber, cname), FOREIGN KEY (cname) REFERENCES company(name) ); IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 38 / 39 postgres=# exit Press any key to continue... IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan)