Database Design and Entity-Relationship Model

Flashcards covering database design phases and the Entity-Relationship Model.

What are the six phases of database design?

The six phases are: Characterize user’s data needs, Choose a data modeling technique, Develop a conceptual schema, Review the schema, Map conceptual schema to a logical schema, and Map logical schema to a physical schema.

How do you characterize user’s data needs in database design?

Interact with users and domain experts to understand data requirements.

What are some data modeling techniques?

Relational Model, Entity-Relationship (E-R) Model, Object-based Data Model, Semi-structured Data Model.

What is typically used to develop a conceptual schema?

E-R model.

What is involved in mapping a conceptual schema to a logical schema?

Convert the E-R diagram into tables and constraints.

What is involved in mapping a logical schema to a physical schema?

Files, indexes, and data dictionaries.

What is the purpose of the Entity-Relationship (E-R) Model?

Facilitates database design and maps real-world enterprises onto a conceptual schema.

What are the three basic concepts of an E-R Model?

Entity & Entity sets, Relationship & Relationship sets, and Attributes.

What is an entity?

An object that exists and is distinguishable from other objects.

What is an entity set?

A set of entities of the same type that share the same properties.

What is a relationship?

An association among several entities.

What is a relationship set?

A mathematical relation among n entities; a subset of the Cartesian product of entity sets.

What is a binary relationship set?

Involves two entity sets.

What are attributes?

Properties possessed by all members of an entity set.

What is a domain in the context of attributes?

Set of permitted values for each attribute.

What is a simple/atomic attribute?

Cannot be divided into other attributes.

What is a composite attribute?

Can be divided into other attributes.

What is a single-valued attribute?

Has a single value for a particular entity.

What is a multi-valued attribute?

Has a set of values for a specific entity.

What is a derived attribute?

Can be computed from other attributes.

What are mapping cardinalities?

Number of entities to which another entity can be associated via a relationship set.

What are the four types of mapping cardinality for a binary relationship?

One-to-one, One-to-many, Many-to-one, Many-to-many.

What is a one-to-one mapping cardinality?

An entity in A is associated with at most one entity in B and vice versa.

What is a one-to-many mapping cardinality?

An entity in A is associated with any number of entities in B, but an entity in B is associated with at most one entity in A.

What is a many-to-one mapping cardinality?

An entity in A is associated with at most one entity in B, but an entity in B can be associated with any number of entities in A.

What is a many-to-many mapping cardinality?

An entity in A is associated with any number of entities in B and vice versa.

Keys of an Entity Set

Similar concept from Relational Database model

Relationship Set

Mathematical relation among n  2 entities

E-R Model

Facilitates DB design

E-R Diagram

Expresses logical structure of a DB graphically

Conversion of Relationship Sets (R)

Two steps: Step 1 and Step 2

Many-to-one or one-to-many

Primary key of entity set on the “many” side

Many -to-many

Union of primary-key from each entity set

One-to-one

Primary key of either entity set