L4: Entity-Relation Design

Question 1

Database Design Process:

Specific Design Areas(3)

Answer 1

Logic Design

Physical Design

Security Design

Question 2

Database Design Process:

Requirements Analysis

Issues to Address

Answer 2

• Data:
  
  • What is going to be stored?
• Usage:
  
  • How is it going to be used?
  • What will be done with the data?
• User Access:
  
  • Who should access the data?

Question 3

Database Design Process:

Conceptual Design:

• Definition
• Requirements

Answer 3

Conceptual Design

A high level description of the database

Requirements:

• The design should be sufficiently precise, so that technical people can understand it
• But not so precise that non-technical people can’t participate

Question 4

E/R Model:

Defining a Subclass

of an

Entity Set

Answer 4

• Use an “isA” relationship
• Multiple subclasses can come off of the “isA” relationship
• Subclasses only show new attributes attached
• Child subclasses contain
  
  • Attibutes of parent class
  • New Attributes attached to the subclass

Question 5

E/R Design:

Relation

vs

Relationship

Answer 5

A Relation

is a set of tuples, each representing an entity

Such as Students, Classes, Buildings, Products

A Relationship

describes some relationship between entities

Such as the Classes a Student is enrolled in

Both Entity Sets and Relationship Sets can be represented in the E/R Diagram as Relations(tables)

Question 6

Database Design Process:

3 Main Steps

Answer 6

1. Requirements Analysis
2. Conceptual Design
3. Specific Design Areas

Question 7

E/R Model:

How are Attributes represented?

Answer 7

A Ovals connected to an Entity Set

One attribute is also selected as a Primary Key,

and the text in it’s oval is underlined.

Question 8

E/R Model:

Relationship:

• Uniqueness
• Keys
• Attributes

Answer 8

Uniqueness

For every unique combination of entities,

There can be only one relationship

Keys

Determined by the keys of its entities

The key of the Relationship is the Union of

the keys of its entities

Attributes

Relationships can also have attributes

Question 9

E/R Model:

Relationships:

• Definition(formal)
• Representation

Answer 9

Definition:

A Relationship between Entity Sets P and C

is a subset of all possible pairs of Entities in P and C,

with Tuples uniqely identified by the Keys of P and C.

It is a subset of the Cross Product of P and C.

Representation:

A Relationship is represented in the E/R diagram as a Diamond, connecting the two Entity Sets

Question 10

E/R Model:

Weak Entities:

• Definition
• Identification
• Identifying Owner

Answer 10

Definition:

Entities that do not have their own Key Attributes

Identification:

Identified with using the Primary Key of another Entity plus some attributes.

Identifying Owner

The other entity associated with the Weak Entity is its Identifying Owner.

Indicated on the diagram with a square around the entity

Question 11

E/R Model:

Weak Entities:

Rules

Answer 11

• Represented with a Bold Outline
• Partial Key Attribute has a Dotted Underline
• Has an Owner Entity
• One-to-Many Relationship Set with Owner
  
  • One Owner can have Many Weak Entities
• A Weak Entity Set must have Total Participation

Question 12

E/R Model:

Multiway Relationships:

• Diagram
• Arrows

Answer 12

An Arrow extending from a Multiway Relationship

means that

All the other Entity Sets together determine a unique instance of the Entity Set pointed to

Question 13

E/R Model:

Two Primitive Units

Answer 13

Entites

Entity Sets

Question 14

E/R Model:

Entities

and

Entity Sets

Answer 14

The primitive units of the E/R Model

Entities

Individual objects, which are members of an Entity Set

Entity Sets

• “Classes” or “Types” of objects in the model
• Represent the set of all possible entites
• Represented as Rectangles
• Has attributes, represented by Ovals

Question 15

E/R Model:

Participation Constraints

• 2 Types
• Representation

Answer 15

Total Participation

Means that an Entity must have at least one relation

Represented with a thick line to a Relation

Partial Participation

No real requirements

Represented with a plain line

The constraints are used in conjunction with Multiplicity Arrows

Question 16

E/R Model:

Two Ways to view:

“isA”

Answer 16

Specialization

• Add new subclasses to an existing class
• Identify subsets of an entity set that share common distinguishing features

Generalization

• Collect classes into a superclass
• Identify some common characteristics of a collection of entity sets
• Create a new entity set that contains entities posessing these common characteristics

Question 17

E/R Model:

Constraints of

“isA”

Answer 17

Overlap Constraint

Determines if two subclasses are allowed to contain the same entity

Covering Constraint

Determines if everything in the superclass is included in at least one subclass

Question 18

E/R Model:

New Entity and Binary Relationships

Diagram

Answer 18

Equivalent to a multiway relationship, but the relationship becomes an entity with relationships to the other entities.

Question 19

ER Diagrams:

Relationship Degrees of Connection

Answer 19

• One to One
  
  • An arrow goes from the relationship to each entity class
• Many to One
  
  • Arrow to one entity class, line to another
  • Each entity of the “many”(line) has the relationship with only one entity in the other entity class
• Many to Many
  
  • No arrows, only lines
  • Each entity can have the relationship with many of the other entity class

Question 20

E/R Model:

Subclasses

Answer 20

• Certain entity sets might have special cases
• Can inherit the entity set’s attributes and create a subclass with new, specialized attributes
• Useful to reduce nulls
• Represented in the E/R Diagram with:
  
  • “isA” triangle
• New tables created with key and new attributes

Question 21

Entity-Relation Model:

Main Components

Answer 21

• Entity Set
  
  • Represents a relation/table of actual entities
  • Analogous to a class
  • Represented with a Rectangle
• Attribute
  
  • Attached to Entity Sets or Relationships
  • Represented with an Oval
  • Key Attributes have text underlined
• Relationship
  
  • Relation/tables that define relationships between entities
  • Represented with a Diamond

Question 22

E/R Model:

Types of Constraints

Answer 22

• Single Value Constraints
  
  • Many to One (arrow-line pair)
  • Many to Many (line - line pair)
• Referential Constraints
  
  • At Most One( arrow, line)
  • Exactly One ( rounded arrow, line)
• Numerical Constraints
  
  • Maximum ( operand and number above line)