ER Diagrams and the Relational Model

Question 1

Entities

Answer 1

• “Things”
• Represented using rectangles

Question 2

Attributes

Answer 2

• Describe entities
• Represented as ovals connected by a line to the entity
• Double ovals for attributes with multiple possible values

Question 3

Key

Answer 3

• A collection of attributes that can uniquely identify each entity in an entity set

Question 4

Candidate Key

Answer 4

• A minimal collection of attributes that is a key

Question 5

Primary Key

Answer 5

• Chosen from the set of possible candidate keys
• Underlined in the ER diagram

Question 6

Relationships between Entities

Answer 6

• Represented using diamonds that are connected to the relevant entity sets
• The Key is the union of the primary keys of the related entities
• The resulting key may not be minimal

Question 7

Role Indicators

Answer 7

• Labels on the lines to distinguish the roles of the entities in the relationship

Question 8

Cardinality Constraint

Answer 8

• Limits the number of times a given entity can appear in a relationship set
• An entity in A having “at most one” entity in B is denoted by an arrow going into entity in B

Question 9

Many-to-One Relationship

Answer 9

• In a many-to-one relationship from A to B, an entity in A can be related to at most one entity in B, and an entity in B can be related to 0+ entities in A

Question 10

One-to-One Relationship

Answer 10

• An entity in A can be related to at most one entity in B, and an entity in B can be related to at most one entity in A

Question 11

Many-to-Many Relationship

Answer 11

• An entity in A can be related to 0+ entities in B, and an entity in B can be related to 0+ entities in A

Question 12

Participation Constraint

Answer 12

• An entity in A having “at least one” entity in B is denoted by a thick line going from the entity in A

Question 13

Relational Terminology

Answer 13

• Table = relation
• Row = tuple
• Column = attribute

Question 14

Relational Schema

Answer 14

• Consists of the name of the relation, the name of the attributes, and the domains of the attributes
• The relational database schema is made up of the schemas of all of the relations in the database

Question 15

Special Case: Many-to-One Relationship Sets

Answer 15

• Eliminate the relation for the relationship set
• Combine the entity and attributes on the “one” end into the “many” end

Question 16

Uniqueness Constraint

Answer 16

• No two rows will have the same primary key

Question 17

Special Case: One-to-One Relationship Sets

Answer 17

• The relationship can be captured in the relation used for either of the entity sets

Question 18

Primary Keys in Relationship Sets

Answer 18

• Many-to-many: the union of the primary keys
• One-to-one: the primary key of either set
• Many-to-one: the primary key of the entity set at the “many” end

Question 19

Foreign Key

Answer 19

• An attribute in a relation that takes on values from the primary key attribute(s) of another relation

Question 20

First Normal Form (1NF)

Answer 20

• All data is in the form of tables (relations)

Question 21

Second Normal Form (2NF)

Answer 21

• An attribute that is not part of a key that depends on a subset of a key should be separated into a new table

Question 22

Third Normal Form (3NF)

Answer 22

• Separate foreign keys dependent on foreign keys