Cmpt 354 - The Entity Relationship Model

Front 1

What is the main goal of database design?

Back 1

Produce a set of tables that can store the data required by an enterprise.

Front 2

What can database tables represent?

Back 2

Entities and relationships between entities.

Front 3

What are the five main steps in database design?

Back 3

1) Requirements analysis.

2) Conceptual Database Design.

3) Logical Database Design (schema refinement).

4) Physical Database Design.

5) Security Design.

Front 4

What questions are involved in requirements analysis?

Back 4

1) What data are to be stored?

2) What applications are required to work with the database?

3) Which are the most frequent, and the most important operations?

Front 5

Describe five steps taken during conceptual database design.

Back 5

1) User the ER model to develop a high level description of the data.

2) Identify the entities and relationships in the enterprise.

3) Identify what information about these entities and relationships is to be stored in the database.

4) Identify the integrity constraints that apply to the entities and relationships.

5) Check with the client that the ER model that has been developed is correct.

Front 6

What are three steps taken during logical database design?

Back 6

1) Determine which data model should be used to implement the database.

2) Determine which DBMS to use.

3) Map, or translate, the conceptual schema to a database schema of the chosen model.

Front 7

What are two major problems to avoid in database design?

Back 7

1) Redundancy.

2) Incompleteness.

Front 8

What are the four main components of the ER model?

Back 8

1) Entities.

2) Attributes.

3) Relationships.

4) Constraints.

Front 9

What is an entity?

Back 9

A distinguishable object.

Front 10

What is an entity set?

Back 10

A collection of entities of the same type.

Front 11

What is an attribute?

Back 11

An entity is described by a set of attributes.

Front 12

How do you represent an entity and its attributes in an ER diagram?

Back 12

Front 13

What are four types of attributes?

Back 13

1) Single valued.

2) Composite.

3) Multi valued.

4) Derived.

Front 14

What is a composite attribute?

Back 14

They group related attributes together. They should be replaced with their sub-parts on the ERD.

eg. Address = City, Street, Number

Front 15

What is a multi valued attribute?

Back 15

Multiple attributes of the same type. They should be replaced by an entity set and a relationship between the two sets.

eg. Phone numbers or cars.

Front 16

What is a derived attribute? How do you display it on an ERD?

Back 16

It an an attribute derived from other values.

It is displayed as an oval with a dotted line as the border.

Note that derived attributes do not need to be stored in the database; they are presumed to be calculated when needed.

Front 17

What is a key?

Back 17

A set of attributes whose values uniquely identify an entity in an entity set.

Front 18

What are the three types of keys? Briefly describe each.

Back 18

1) Super key: any set of attributes whose values uniquely identify an entity in an entity set.

2) Candidate key: a minimal super key, that is a super key with no extraneous attributes. (a relation can have more than one candidate key)

3) Primary key: the candidate key designated by the database designer to refer to rows in the table. (they are underlined in an ERD)

Front 19

What is a relationship?

Back 19

An association between two or more entities.

Front 20

What is a relationship set?

Back 20

A set of relationships of the same type.

Front 21

What can relationship sets contain? What is the limitation?

Back 21

Descriptive attributes that cannot be part of a relationship set's primary key.

Front 22

How do you represent a relationship set on an ERD?

Back 22

Front 23

What are mapping cardinalities?

Back 23

They specify how many entities may be related to entities they are related to.

ie. one-to-one, one-to-many, many-to-one, many-to-many.

Front 24

How are mapping cardinalities represented on an ERD?

Back 24

Directed and undirected lines.

Arrows points from an entity set to the relationship set.

ie. An employee can work in only one branch. A branch can have many employees working in it.

Front 25

What are the attributes of a relationship set?

Back 25

1) The attributes that comprise the primary keys of the participating entity sets. (and)

2) Any descriptive attributes of the relationship set.

Front 26

What does the primary key of a relationship depend on?

Back 26

The key constraints in the relationship.

eg.

many-to-many: all the non descriptive attributes of the relationship set.

one-to-many: the primary key for the many entity.

one-to-one: the primary key of either entity.

Front 27

What are the attributes of works_in?

What is the primary key of works_in?

Why?

Back 27

1) sin and branchName.

2) sin.

Each employee can only work in one branch, so can appear in works_in only once. Therefore we can uniquely identify the relationship with just sin.

Front 28

What is a participation constraint?

Back 28

Each entity in an entity set must be involved in at least one relationship.

Front 29

What are two types of participation constraints? Define them.

Back 29

1) Total: there is a constraint.

2) Partial: no constraint.

Front 30

How do you indicate a total participation constraint on an ERD?

Back 30

A double line from the relationship to the entity (or a thick line).

Front 31

What is a ternary relationship?

Back 31

A relationship between a set of three entities.

Front 32

What is a weak entity set?

Back 32

A weak entity cannot be identified by its own attributes alone. They are identified by combining its partial key with the primary key of another entity set (owner entity set).

Front 33

When are weak entity sets permitted?

Back 33

1) The owner and weak entity set participate in a one-to-many identifying relationship set and

2) The weak entity set has total participation in the identifying relationship.

Front 34

Draw an example of a weak entity set between a House and a Room.

Back 34

Front 35

What is a class hierarchy? What can be said about the attributes?

Back 35

Sub-classing of entities where each entity in a subclass is also an entity in the superclass.

The attributes of the superclass are inherited by the subclass entities.

Front 36

The subclass relationship is sometimes referred to as an ________ relationship.

Back 36

is-a

Front 37

What is specialization?

Back 37

Superclass --> Subclass through new attributes.

Front 38

What is generalization?

Back 38

Subclass --> Superclass through common attributes.

Front 39

What do subclass entities also inherit?

Back 39

Participation in superclass relationship sets.

Front 40

Draw an ERD for the subclasses of RentalProperty and HeritageBuilding with the superclass of Building.

Back 40

Front 41

What is the difference between condition-defined and user-defined participation of entities in a subclass?

Back 41

1) Must meet some condition (eg. type = savings only for SavingsAccount).

2) Entity assigned to subclass by database user (employees assigned to Manager subclass).

Front 42

What is aggregation?

Back 42

Indicates that a relationship set participates in another relationship set. This is basically an abstraction of a relationship set.

Front 43

When should aggregation be used?

Back 43

1) When there is a relationship between an entity set and another relationship.

2) Aggregation is often used with non-binary relationships.

Front 44

Draw an aggregation of the Branch buys Part from Supplier ERD.

Back 44

Front 45

Name four ER design principles.

Back 45

1) Faithfulness.

2) Avoid redundancy.

3) Simplicity.

4) Specify as many constraints as possible.