DBAS Week 2

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:

1. One-to-One (1:1)

   • Each instance of Entity A is related to at most one instance of Entity B, and vice versa.

2. One-to-One (Mandatory One) (1:1)

   • Each instance of Entity A must be related to exactly one instance of Entity B.

3. One-to-One (Optional One) (0:1)

   • Each instance of Entity A may be related to zero or one instance of Entity B.

4. 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.

5. One-to-Many (Mandatory Many) (1:M)

   • Each instance of Entity A must be related to at least one instance of Entity B.

6. 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)?

1. Employee – Represents individual employees in a company, storing attributes like EmployeeID, Name, and Department.

2. Invoice Number – A unique number assigned to each invoice.

3. Car Model and Year – A combination of attributes describing a car but not a distinct entity.

4. Product – Represents goods or services sold by a business, with attributes like ProductID, Name, and Price.

5. 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