Introduction to conceptual design, ERD and EERD Flashcards
Why do we use conceptual models to communicate designs? (i.e ERD)
Designers, end-users as well as programmers tend to view systems differently. We therefore need to make sure everyone can communicate on an equal level, and for that we use conceptual models to communicate
designs.
How is an ERD created? (Entity relationship diagram)
An ERD is created using the following steps:
• (1) Identify the entities and their relationships
• (2) Identify, and associate, entity attributes with their entities
• (3) Identify, and associate, relationships between entities
• (4) Determine Attribute Domains
• (5) Determine candidate, primary and foreign key attributes.
• (6) Review the data model with stakeholders
What is an entity?
An entity represents a set of objects that share the same attributes • If you’re familiar with OOP, this would be a class • E.g., we might have several students (set of objects) registered to a course, but all students share some attributes (represented by an entity/class)
What is attributes?
§ Properties or characteristics of entities are called attributes. These
represent what we want to know about entities, i.e., their properties
What different types of attributes are there?
Simple: Attributes which cannot be further subdivided. E.g., a student ID
Composite: Attributes which can be further divided. E.g., studentName, which can be
divided into first- and last name.
Derived: Attributes are derivable from other values in the database, but they do are not
stored themselves. E.g., we could derive a students age from their date of birth.
Single-value: Attributes which only contain a single value. E.g., the age of a student
Multi-value: Attributes which may contain more than one value. E.g., a student may have
multiple phone numbers or email addresses
What is the difference between a strong entity and a weak entity?
A strong entity does not depend on any other entity and can therefore
be uniquely identified by its own attributes. These entities will always
have a primary key
A weak entity is partially or wholly dependent on a strong entity. An
entity is not weak because it cannot exist independently from a strong
entity, but because it cannot be identified independently
• For example, we can consider HB_Bank_Account as one entity and
Handelsbanken as another
• HB_Bank_Account would be considered a weak entity as it depends on one
or more attributes from the strong entity, Handelsbanken
• A bank account at Handelsbanken cannot be identified without the actual
bank existing
What types of entity keys are there?
§ A Super Key is an attribute or set of attributes that uniquely identifies each
entity occurrence in a table
§ A Candidate Key is a minimal set of attributes necessary to identify an entity.
I.e., it’s a minimal super key
§ A Primary Key is selected from the Candidate Key set to uniquely identify
each entity occurrence. It is often depicted by being underlined, tagged as PK
or by an equal measure
§ A Foreign key acts as a reference for the primary key of another entity. E.g., if
a pet is allowed to have a single owner, the pet entity would have a reference
to said owner
What do we to do in the case we dont have any candidate keys for an entity?
In the case where we don’t have any candidate keys for an entity, we
instead need to create our own by defining a new attribute
• The go-to here is often ENTITY_NAME + ID, e.g., CustomerID
What should you think about when choosing a primary key from the candidate keys?
When choosing a primary key from our candidate keys, you may want to choose
one:
• with the minimal set of attributes,
• that is unlikely to have its values changed,
• that is unlikely to lose uniqueness in the future,
• with the fewest characters or smallest maximum value
Which is the best primary key, StudentID or StudentEmail for an StudentEntity?
StudentID is a better choice
• A student may change their email address at some point, they may forget the user
information to log into their email etc.
How do you identify relationships between entities?
Typically, relationships are identified by verbs or verbal expressions.
For example:
• “An order consists of at least one order line”
• “IM students belong to the IM institution”
What indicates Cardinality and Modality in an Entity Relationship Diagram enviroment?
Cardinality indicates the maximum number of times an instance in one entity can
be associated with instances in the related entity
• For example, a student may be registered to 0 or multiple university courses and a
university course may have 0 or many registered students
Modality indicates the minimum number of times an instance in one entity can
be associated with an instance in the related entity, i.e., whether the association
is optional or not
• E.g., a student may be registered to a course, but it doesn’t have to be
• However, a student must have one and only one address, i.e., the association is mandatory
What are the different types of relationships between entities?
These are the different types of relationships between entities:
• One-to-One (1..1): A department has one manager, and a manager can only be the
manager for one department
• One-to-Many (1..∗): A customer can make zero or several orders, but an order will be
associated with one and only one customer
• Many-to-Many (∗..∗): A student may be registered to several courses and a course may
have several registered students
What is an EERD?(Enhanced entity relationship diagram)
EERDs are basically models that expands upon ERDs, making them helpful for designing databases with high-level models
§ With EERDs, we retain everything from an ERD while adding:
- Specialization and generalization
- Subclasses and superclasses
§ A superclass will act as a parent entity to its subclasses, which in turn acts as child entities
• E.g., we could have an Employee superclass with Manager and Secretary acting as subclasses
What is the difference between a super class(i.e Employee) and a subclass(i.e Manager)?
An entity occurrence in a subclass represents the same object as in the superclass
• Much like a child class in OOP would inherit its base/super class’s properties and members
§ A subclass may have additional attributes, in addition to the ones inherited
• E.g., while all employees have a salary in common, a secretary might have something to indicate their typing speed
§ In other words, a subclass is its own entity. This means that it could also act as a superclass for other subclasses
• A secretary might act as a superclass for the subclasses Administrative Secretary and Managerial Secretary
