Studied by 0 people
Looks like no one added any tags here yet for you.
Conceptual Data Modelling
Definition (What & Purpose)
What: Process of abstracting reality as a particular business sees it — into well defined & concrete entities
Purpose: Captures the nature & relations among data
Business Rules
What
Purpose/Significance
What
Statements that define or constrain some aspect of the business
Derived from the business’ policies, procedures, events, and/or functions
Automated through DBMS software
Purpose
Asserts business structure
Influences business behaviour
Business rules are ________ through DBMS software.
Business rules are automated through DBMS software.
T or F: Business rules are expressed in familiar terms to end users.
True
A good business rule is…
Hint: “Devin Pulls All Chicks Especially Dumb Blondes”
D = Declarative — What, not how
P = Precise — Clear & agreed-upon meaning
A = Atomic — One single statement
C = Consistent — Internally & externally
E = Expressible — Structured, in a natural language
D = Distinct — Non-redundant
B = Business-Oriented — Meaningful & understandable by business people
Define the following:
(a) Entity
(b) Entity Type
(c) Entity Instance
(a) Entity
Anything an organization wants to store data about
A person, place, object, event, or concept
(b) Entity Type
Collection of entities that share common properties or characteristics
Typically corresponds to a table
(c) Entity Instance
A single occurrence of an entity type
Typically corresponds to a single row in a table
Entity instances would be the actual ______________ in the final database table that implements an __________.
Entity instances would be the actual records (or rows) in the final database table that implements an entity type.
(a) Relationship Instance
(b) Relationship Type
(a) Relationship Instance
Link b/w entities
Corresponds to primary-key & foreign-key equivalencies in related tables
(b) Relationship Type
Category of relationship
Link b/w entity types
Define the following:
(a) Attribute
(b) Required Attribute
(c) Optional Attribute
(a) Attribute
Property or characteristic of an entity type or relationship type
Typically corresponds to a field in a table
(b) Required Attribute
Attribute must have a value for every entity (or relationship) instance it’s associated with.
(c) Optional Attribute
Attribute doesn’t have to have a value for every entity (or relationship) instance it’s associated with.
List all of the different types of classifications of attributes.
Required versus Optional
Simple versus Composite
Single-Valued versus Multivalued
Stored versus Derived
Identifier
Describe the Basic ER Diagram Notation
Entities
Strong entity —> Represented as a rectangle, with square/sharp edges
Weak entity —> Represented as a rectangle, with sharp edges BUT two lines as a border
Associative entity —> Represented as a rectangle, with rounded edges
Entity Name
Written in ALL CAPS
Positioned at top centre of entity rectangle
Attribute
Listed under entity name
Unique Identifier —> underlined once
Partial Identifier —> underlined twice
Optional attribute —> *no special notation
Derived attribute —> enclosed in square brackets
Multivalued attribute —> enclosed in curly brackets
Composite attribute —> attribute name followed by a comma separate listed enclosed in brackets
Relationship Degrees
Unary Relationship
Involves 1 entity type
Relationship line emerges from one place on entity, then circles back connects to a diff place on entity
Binary
Involves 2 entity types
Relationship line connects from one entity directly to another
Ternary
Involves 3 entity types
Relationship line emerging from each entity & joins together at a central point
(see attached image)
Relationship Cardinalities
One to one
One to one, and only one (mandatory one)
One to zero or one (optional one)
One to many
One to one or many (mandatory many)
One to zero or many (optional many)
“Business rules are automated through DBMS software.”
Explain what this means.
INCOMPLETE
An _______________ is a special entity that is also a relationship b/w two other entities.
An associative entity is a special entity that is also a relationship b/w two other entities.
Relationship Degree
What: The degree of a relationship specifies the number of entity types involved.
There are…
Unary —> 1 entity involved in the relationship
Binary —> 2 entities involved in the relationship
Ternary —> 3 entities involved in the relationship
(a) Define “Relationship Cardinality”
(b) Name & describe the types of relationship cardinalities.
What: Defines the number of instances of one entity that can be associated with instances of another entity.
Types of Relationship Cardinalities:
One-to-One (1:1)
Each instance of Entity A is related to at most one instance of Entity B, and vice versa.
One-to-One (Mandatory One) (1:1)
Each instance of Entity A must be related to exactly one instance of Entity B.
One-to-One (Optional One) (0:1)
Each instance of Entity A may be related to zero or one instance of Entity B.
One-to-Many (1:M)
Each instance of Entity A can be related to multiple instances of Entity B, but each instance of Entity B is related to only one instance of Entity A.
One-to-Many (Mandatory Many) (1:M)
Each instance of Entity A must be related to at least one instance of Entity B.
One-to-Many (Optional Many) (0:M)
Each instance of Entity A may be related to zero or many instances of Entity B.
Data Names
What
Purpose/Significance
What:
Name assigned to data objects in a database
Can be the name of an entity, attribute, relationship, etc.
Purpose/Significance
How data object is identified in database schema
Data objects must be named & defined before they can be used unambiguously in a database model
T or F: Data objects must be named & defined before they can be used unambiguously in a database model.
True
A good data name is…
(Hint: Rachel’s Man Understands Relationships Can Ruin Work)
R = Related to business, not technical
M = Meaningful & self-documenting
U = Unique
C = Composed of words from an approved list
R = Repeatable
W = Written in standard syntax
Categorize the following into two groups:
(a) Things an entity SHOULD be
(b) Things an entity SHOULD NOT be
An output of the database system
An object that will have many instances in the database
A user of the database system
An object that will be composed of multiple attributes
An object that database developer needs to model
(a) Things an entity SHOULD be
An object that will have many instances in the database
An object that will be composed of multiple attributes
An object that database developer needs to model
(b) Things an entity SHOULD NOT be
An output of the database system
A user of the database system
Which of the following are NOT appropiate entities (by definition)?
Employee – Represents individual employees in a company, storing attributes like EmployeeID, Name, and Department.
Invoice Number – A unique number assigned to each invoice.
Car Model and Year – A combination of attributes describing a car but not a distinct entity.
Product – Represents goods or services sold by a business, with attributes like ProductID, Name, and Price.
Phone Number – A contact detail for a person or business.
(2) Invoice Number —> This is an attribute, not an entity.
(3) Car Model & Year —> This should be part of a "Car" or "Vehicle" entity instead.
(5) Phone Number —> This should be an attribute of an entity like "Customer" or "Employee," not an entity itself
Define the following:
(a) Strong Entity
(b) Weak Entity
(c) Identifying Relationship
(a) Strong Entity
Exists independently of other entity types
Has its own unique identifier
(b) Weak Entity
Dependent on a strong entity (called its “identifying owner”)
Cannot exist on its own
Doesn’t have a unique identifier
Instead has a partial identifier
(c) Identifying Relationship
Links strong entities to weak entities
Describe the differences in how strong & weak entities are represented in an ER diagram.
Strong Entity
Single line as border for its rectangle
Has a unique identifier (PK) —> underlined w/ single line
Weak Entity
Double lines as border for its rectangle
Has a partial identifier —> underlined w/ double lines
A relationship that links strong entities to weak entities is called an ________________. And in this relationship, the weak entity is dependent on a strong entity, which is called its _______________.
A relationship that links strong entities to weak entities is called an identifying relationship. And in this relationship, the weak entity is dependent on a strong entity, which is called its identifying owner.
Observe the diagram of the entity-relationship below.
(a) Fill in the labels (a, b, c, d)
(b) Describe the relationship b/w EMPLOYEE & DEPENDENT
(a)
(b) INCOMPLETE
T or F: An entity must always contain an identifier attribute.
True
Simple v.s. Composite Attribute
Define
Give example
Simple — Attribute w/o sub attributes
Composite — Attribute with meaningful components (sub-attributes.
Example:
Single-Valued v.s. Multivalued Attribute
Define
Give example
Multivalued Attribute
Can have more than one value for a single entitiy
Example: An employee can have multiple skills
IMP NOTE! This is not the same as a composite attribute, which is an attribute made up of smaller parts (e.g., "Full Name" = First Name + Last Name).
Single-Valued Attribute
An attribute that has only one value for a given entity.
Example: An employee has one date of birth.
Stored v.s. Derived Attribute
Define
Give example
Derived Attribute
An attribute not stored in the database but calculated from other attributes.
Example: "Years Employed" can be calculated using (Current Date - Date Employed) instead of being stored as a separate value.
Stored Attribute
An attribute that is physically saved in the database.
It is not calculated from other attributes.
Example: Date of Birth of an employee is stored as it is, while Age can be calculated from it (making Age a derived attribute).
People often mistake or confuse the “__________” classification for an attribute with “__________” classification.
People often mistake or confuse the “multivalued” classification for an attribute with “composite” classification.
Identifiers are also called “____”.
Identifiers are also called “keys”.
Define “Identifier (key)”
Simple v.s. Composite Identifier
Candidate Identifier
Describe the criteria for identifiers.
Describe the rules for naming & defining attributes.
Attribute names should be…
Noun
Unique
Follow a standard format
Similar entities of different entity types should use the same qualifiers & classes
Attribute definitions should…
State what attribute is (& possibly its importance)
Clearly state what is & is NOT included in attribute’s value
Included aliases in documentation
State source of values
State if attribute value is changeable or fixed
Specify whether require or optional
State min & max number of occurrences allowed
Indicate relationships w/ other attributes
Modelling Relationships
(a) The type of relationship b/w specific entity instances is referred to as _________ and modelled as ________.
(b) T or F: Relationships can have attributes.
(c) ________ describe the features/characteristics of an association b/w entities.
(d) T or F: Two entities can have more than one type of relationship b/w them.
(e) ______________ is the combination of a relationship and entity.
INCOMPLETE
_______________ bridge many to many relationships.
Associative entities bridge many to many relationships.
Describe cardinality constraints.
Cardinality Constraints — the number of instances of one entity that can or must be associated with each instance of another entity
Minimum Cardinality
If zero, then optional
If one or more, then mandatory Maximum Cardinality
The maximum number
Demonstrate how to draw a unary relationship.
In the diagram below, the entities have what relationship type?
Ternary relationship
T or F: Multivalued attributes can be represented as relationships?
True
The following is an example of which type of cardinality constraint?
The following is an example of which type of cardinality constraint?
The following is an example of which type of cardinality constraint?
T or F: The following is an example of a multiple relationship? Justify your answer.
T or F: The following is an example of a multiple relationship? Justify your answer.
In the diagram below, '“prerequisite” in the entity “COURSE” is an example of what type of attribute?
Simple multivalued attribute.
In the diagram below, '“Skill” in the entity “EMPLOYEE” is an example of what type of attribute?
Composite multivalued attribute.
In which situations should a relationship with attributes be an associative entity instead?
– All relationships for the associative entity should be many
– The associative entity could have meaning independent of
the other entities
– The associative entity preferably has a unique identifier, and should also have other attributes
– The associative entity may participate in other relationships other than the entities of the associated relationship
– Convert ternary relationships to associative entities
Which of the following is true about associative entities:
(a) It has attributes.
(b) It is a relationship.
(c) It’s purpose is to link other entities together in a many-to-many relationship.
All statements are true.
(a) Demonstrate proper denotation for a relationship w/ an attribute on an ER diagram.
(b) Demonstrate proper denotation for an associative entity on an ER diagram.
Describe cardinality constraints in a ternary relationship.
Time Stamp
Define
When does this typically occur?
Demonstrate example.
Def: A time value that is associated with a data value.
Often indicates when some event occurred that affects the data value.
Example:
ER Diagram v.s. EER Diagram
ER Diagram
Stands for “Entity-Relationship” Diagram
Contains:
INCOMPLETE
EER Diagram
Stands for “Enhanced Entity-Relationship” Diagram
Extends upon original ER model with additional constructs:
Specialization (partial & total)
Subtypes & Supertypes
Attribute Inheritance
DC
Compare ER & EER Diagrams
ER Diagram vs. EER Diagram
Feature | ER Diagram (Entity-Relationship Diagram) | EER Diagram (Enhanced Entity-Relationship Diagram) |
|---|---|---|
Definition | A diagram that models entities, relationships, and attributes in a database. | An extension of ER diagrams that includes advanced concepts like subtypes, supertypes, and specialization/generalization. |
Complexity | Basic, suitable for simpler database designs. | More detailed, used for complex data structures. |
Includes Supertypes & Subtypes? | ❌ No | ✅ Yes |
Includes Specialization/Generalization? | ❌ No | ✅ Yes |
Use Case | Good for basic databases with clear entities and relationships. | Ideal for systems with inheritance, categories, or complex relationships. |
Example:
ER Diagram:
Entities: Employee, Department, Project
Relationships: Employee "works in" Department
EER Diagram:
Adds subtypes: Manager, Engineer (subtypes of Employee)
Uses specialization: Employee can be either a Manager or Engineer
INCOMPLETE/DC
Define the following:
(a) Subtype
(b) Supertype
(c) Attribute Inheritance
(a) Subtype
Sub-group of entities that are a more specific version of an entity (their supertype)
Inherits attributes from a general entity (supertype)
A sub-group has distinct attributes from other sub-groups belonging to the same supertype
(b) Supertype
Generic entity type that has a relationship w/ one or more subtypes
(c) Attribute Inheritance
Subtypes automatically inherit all attributes and relationships from their supertype.
Example: If "Employee" (supertype) has attributes like EmployeeID and Name, both "Manager" and "Intern" (subtypes) will also have these attributes without needing to redefine them.
Demonstrate the Basic Notation for Supertype-Subtype Relationships
Describe the Constraints in Subtype/Supertype Relationships.
Subtype Discriminator: An attribute of the supertype whose values determine the target subtype(s)
- Disjoint – a simple attribute with alternative values to indicate the possible subtypes
- Overlapping – a composite attribute whose subparts pertain to different subtypes. Each subpart contains a Boolean value to indicate whether or not the instance belongs to the associated subtype
The Disjoint Rule
What
Purpose/Significance
Example
What:
Purpose/Significance:
-
The Overlap Rule
What
Purpose/Significance
Example
What: Introduces a subtype discriminator
Purpose/Significance:
Example:
Entity Clusters
Definition
Purpose/Significance
Example
Definition: Set of one or more entity types and associated relationships grouped into a single abstract entity type
Purpose/Significance
EER (Enhanced ER) diagrams are difficult to read when there are too many entities and relationships.
Solution: Group entities and relationships into entity
Example
