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:
New Entity and Binary Relationships
Diagram
Equivalent to a multiway relationship, but the relationship becomes an entity with relationships to the other entities.
