L4: Entity-Relation Design Flashcards
Database Design Process:
Specific Design Areas(3)
Logic Design
Physical Design
Security Design
Database Design Process:
Requirements Analysis
Issues to Address
-
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?
Database Design Process:
Conceptual Design:
- Definition
- Requirements
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
E/R Model:
Defining a Subclass
of an
Entity Set
- 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
E/R Design:
Relation
vs
Relationship
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)
Database Design Process:
3 Main Steps
- Requirements Analysis
- Conceptual Design
- Specific Design Areas
E/R Model:
How are Attributes represented?
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.
E/R Model:
Relationship:
- Uniqueness
- Keys
- Attributes
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
E/R Model:
Relationships:
- Definition(formal)
- Representation
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
E/R Model:
Weak Entities:
- Definition
- Identification
- Identifying Owner
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
E/R Model:
Weak Entities:
Rules
- 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
E/R Model:
Multiway Relationships:
- Diagram
- Arrows
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
E/R Model:
Two Primitive Units
Entites
Entity Sets
E/R Model:
Entities
and
Entity Sets
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
E/R Model:
Participation Constraints
- 2 Types
- Representation
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
E/R Model:
Two Ways to view:
“isA”
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
E/R Model:
Constraints of
“isA”
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
E/R Model:
New Entity and Binary Relationships
Diagram
Equivalent to a multiway relationship, but the relationship becomes an entity with relationships to the other entities.
ER Diagrams:
Relationship Degrees of Connection
- 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
E/R Model:
Subclasses
- 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
Entity-Relation Model:
Main Components
-
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
E/R Model:
Types of Constraints
- 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)
