ER Example with MySQL Workbench

Question 1

What is an identifier or key?

Answer 1

Fully identifies an instance

Question 2

What is a Partial Identifier?

Answer 2

Identifies an instance in conjunction with one or more partial identifiers

Question 3

What are the attributes types?

Answer 3

• Mandatory - NOT NULL (blue diamond)
• Optional - NULL (empty diamond)
• [Derived] - e.g [YearsEmployed]
• {Multivalued} - e.g {Skill}
• Composite - e.g Name (First, Middle, Last)

Question 4

What is the definition of derived attributes?

Answer 4

Derived attributes imply that their values can be derived from some other attributes in the database.

Example: anything calculable - [Years employed]

Question 5

What are the types of relationship cardinality?

Answer 5

• One to One: Each entity will have exactly {0 or 1} related entity
• One to Many: One of the entities will have {0, 1, or more} related entities, the other will have {0 or 1}
• Many to Many: Each of the entities will have {0, 1, or more} related entities

Question 6

What is auto_increment?

Answer 6

tells mySQL to add one

Question 7

What is ON Delete Restrict?

Question 8

What is ON UPDATE CASCADE?

Question 9

How to deal with Many-to-Many?

Answer 9

Based on business rules
- Depends if the business wants one email or multiple like amazon

Question 10

What is the rule for dealing with One-to-One (Binary)

Answer 10

Rule: move the key from the one side to the other side

Question 11

How to design unary relationships One to One?

Answer 11

Put a Foreign key in the relation

Question 12

How to design Unary relationships One-to-Many

Answer 12

Put a Foreign key in the relation

Question 13

How to design Unary relationships Many-to-Many?

Answer 13

• Generate an Associative Entity
• Put two Foreign keys in the Associative Entity
• Need 2 different names for the Foreign keys
• Both Foreign keys become the combined key of the Associative Entity

Question 14

What is the definition of ternary relationships?

Answer 14

Relationships between three entities

- Generate an Associative Entity

- Three One-to-Many relationships

- Same rules then apply as One-to-Many