d426 4

entity rrelationship model

a high-level representation of data requirements, ignoring implementation details.

model includes three kinds of objects:

• An entity is a person, place, product, concept, or activity.

• A relationship is a statement about two entities.

• An attribute is a descriptive property of an entity.

attribute

is used in both entity-relationship and relational models. In the relational model, _____ is a formal term for column.

Since entity-relationship attributes typically become relational columns, the meaning of ______is similar in both models.

er diagram

depicts entities as rectangles with rounded corners, relationships as lines connecting rectangles, and optional attributes within entity rectangles.

glossary

also known as a data dictionary or repository, documents additional detail in text format.

includes names, synonyms, and descriptions of entities, relationships, and attributes.

type

is a set in relationship modeling

• An entity type is a set of things. Ex: All employees in a company.

• A relationship type is a set of related things. Ex: Employee-Manages-Department is a set of (employee, department) pairs, where the employee manages the department.

• An attribute type is a set of values. Ex: All employee salaries.

instance

is an element of a set:

• An entity instance is an individual thing. Ex: The employee Sam Snead.

• A relationship instance is a statement about entity instances. Ex: "Maria Rodriguez manages Sales."

• An attribute instance is an individual value. Ex: The salary $35,000.

conceptual design steps

Step	Name
1	Discover entities, relationships, and attributes
2	Determine cardinality
3	Distinguish strong and weak entities
4	Create supertype and subtype entities

logical design steps

Step	Name
5	Implement entities
6	Implement relationships
7	Implement attributes
8	Apply normal form

discovery entity

• are usually singular nouns, but not all nouns are entities. Designers should ignore nouns that denote specific data or are not relevant to the database.

discovery relationship

are usually verbs. Designers should ignore statements that are not about entities, not relevant to the database, or redundant to other relationships. Designers should look for relationships that are not explicitly stated, since users may overlook important information.

Entity-Verb-Entity

verb should be active not passive

discovery attributes

are usually nouns that denote specific data, such as names, dates, quantities, and monetary values. must have suffix

EntityQualifierType

synonnym

non-official name for an entity saved in glossary.

should reflect common usage, not naming conventions.

cardinality

maxima of relationships and attributes

OR

as maxima and minima of relationships and attributes

modality

is the minima of relationships and attributes

binary relationship

is a relationship between two entity types

• 1:1

• 1:Many

• Many:Many

one to one binary

one instance of the first entity is mapped with only one instance of the second entity. The primary key of one entity is available as a foreign key in the other entity. This type of relationship is less common in database design.

symbol: |—— |

one to many binary

one instance of the first entity is related to multiple instances of the second entity, but each instance of the second entity is related to only one instance of the first entity.

ex: Example − Consider two entities Department and Employee. One department can have many employees working in it, but each employee belongs to only one department

symbol: | ——<

many to many binary

one instance of the first entity is related to multiple instances of the second entity, and one instance of the second entity is related to multiple instances of the first entity. This type of relationship always requires a junction table (also called bridge table or associative table) to store the associations between the two entities.

Example − Consider two entities Student and Course. Many students can enroll in a course, and many courses can be taken by a student. A junction table Enrollment tracks which students are enrolled in which courses ?

symbol: >——<

intersected data

is information/ attribute that describes the relationship between two entities, not either entity by itself.

ex:If a salesperson sells a product, the quantity sold is ________ because it only makes sense when you know both the salesperson and the product.

associative entity

links two entities in a many-to-many relationship and holds any attributes that describe their relationship and intersected data

transitive dependency

occurs in a database when one attribute depends on another, and that second attribute, in turn, determines a third attribute.

when an indirect relationship exists between two attributes through a third attribute.

To avoid, break down the relationship into separate tables and use junction tables to connect them.

unary relationship

A relationship where an entity is related to itself (the same entity type).

• Example: An Employee manages another Employee.

Types:

• 1:1

• 1:M

• M:M

one to one unary

A specific type of unary relationship where each instance can be related to only one other instance, and vice versa.

• Example: A Person is married to one other Person, and each person has only one spouse.

one to many unary

is when one instance of an entity is related to many other instances of the same entity, but each of those instances is related to only one original instance.

Example:

• An employee manages many employees, but each employee has only one manager.

many to many unary

A many-to-many unary relationship is when many instances of an entity can be related to many other instances of the same entity.

Example:

• A student studies with many students, and each student studies with many others.

n-ary relationships

Feature	Unary Relationship	Binary Relationship	Ternary Relationship
Number of entity types involved	One	Two	Three
Relationship connects	An entity to itself	Two different entities	Three different entities at the same time
Possible cardinalities	1:1, 1:M, M:N	1:1, 1:M, M:N	Usually represents complex relationships involving three entities
Associative entity needed?	Yes, if M:N has relationship attributes	Yes, if M:N has relationship attributes	Often converted into an associative entity to store relationship data
Purpose	Shows relationships among instances of the same entity	Shows relationships between two different entities	Shows a relationship involving three entities where all three are needed to describe the event
Example	Employee manages Employee	Customer places Order	Supplier provides Product to Warehouse

ternary relationship

three different entities participate in a single relationship simultaneously.

When determining cardinality, we consider it in the context of two entities relative to the third.

all 3 converge to an associative entity

relationship maximum

is the greatest number of instances of one entity that can relate to a single instance of another entity

• singular: maximum is one

• plural maximum is many

crow foot notation

relationship minimum

is the least number of instances of one entity that can relate to a single instance of another entity. A relationship has two minima, one for each of the related entities.

• optional: min is 0

• required when min is 1

attribute maximum

is the greatest number of attribute values that can describe each entity instance.

specified as one (singular) or many (plural).

attribute minimum

is the least number of attribute values that can describe each entity instance.

specified as zero (optional) or one (required).

unique attribute

describes at most one entity instance.

identifying attribute

is unique, singular, and required. Identifying attribute values correspond one-to-one to, entity instances.

strong entity

has one or more identifying attributes.

only becomes an identifying entity when it is used to identify a weak entity.

weak entity

does not have an identifying attribute.

Instead, usually has a relationship, called an identifying relationship (includes, gives offers), to another entity, called an identifying entity. The identifying entity must be singular and required in an identifying relationship.

identifying entity

is a strong entity that provides the primary key needed to identify a weak entity.

A weak entity can be an ________ entity if it is used to identify another weak entity.

entities

Feature	Strong Entity	Weak Entity	Identifying Entity
Has its own primary key	✅	❌ (partial key only)	✅
Exists independently	✅	❌	✅
Depends on another entity	❌	✅	❌
Identifies another entity	❌	❌	✅
E-R notation	Single rectangle	Double rectangle	Single rectangle
Example	Customer	Order Item	Order

subtype entity

is a subset of another entity type called the supertype entity

highlight subsets of data with different attributes and relationships than the supertype. This clarifies database semantics and behavior

attributes only apply to ______

all are weak entities

supertype entity

entity usually has several subtypes

attributes apply to all subtypes. An attribute that is repeated in several subtypes becomes a single______ attribute.

similar entities

are entities that have many common attributes and relationships.

_____ entities become subtypes of a new supertype entity,

are often created when different groups of an entity have different optional attributes.

partition

divides a supertype into subtypes where only one subtype can be chosen within that group.

In diagrams:

• Vertical alignment = Subtypes belong to the same (mutually exclusive/ cant be true at same time).

• Horizontal alignment = Subtypes belong to different (can overlap).

partition attribute

optional. indicates which subtype entity is associated with each supertype instance.

isa relationship

A supertype entity identifies its subtype entities. The identifying relationship is called_______

the relationship is assumed and may be omitted from the ER diagram.

subtype and supertype design

Step	Activity
4A	Identify supertype and subtype entities.
4B	Replace similar entities and optional attributes with supertype and subtype entities.
4C	Identify partitions and partition attributes.
4D	Document supertypes, subtypes, and partitions in glossary and ER diagram.

cardinality notation

< + o = OR

| + | = ONLY

| = one

o = zero

> and < = many

uml (universal modeling language)

is commonly used for software development. Software data structures are similar to database structures, so ___ includes ER conventions.

idef1x

model convention. became popular, in part, due to early adoption by the United States Department of Defense.

intangible entity

is documented in the data model, but not tracked with data in the database.

in er diagram are distinguished with special notation, such as a dashed rectangle or distinct color.

model conventions

ER modeling concepts also vary. Ex: Some ER models may:

• Allow relationships between three or more entities.

• Decompose a complex model into a group of related entities, called a subject area.

• Refer to strong entities as independent and weak entities as dependent.

primary key selection

• Stable – The identifier should not change over time. If it changes, every related record that uses it must also be updated.

• Simple – should be easy to type and store. Small values are easy to specify in an SQL WHERE clause and speed up query processing. Ex: A 2-byte integer is easier to type and faster to process than a 15-byte character string.

• Meaningless – The identifier should not contain descriptive information (such as a person's name or department). Descriptive information can change, but the identifier should remain the same.

always required and unique

strong table

derived from strong entity.

The primary key must be unique and required, and should be stable, simple, and meaningless.

artificial key

is a simple primary key created by the database designer.

are integers, generated automatically by the database as new rows are inserted to the table.

are stable, simple, and meaningless.

weak table

derived from a weak entity.

has a foreign key that references the identifying table and implements the identifying relationship.

weak table primary key

The key depends on the identifying relationship:

• Plural weak entity: Primary key = foreign key + another column (composite key).

• Singular weak entity: Primary key = foreign key only.

supertype table

A table created from a supertype entity. It is implemented like:

• A strong entity if it has its own identifying attribute.

• A weak entity if it is identified through an identifying relationship.

subtype table

A table created from a subtype entity. Its primary key is the same as the supertype's primary key and also acts as a foreign key.

subtype primary key

• Is identical to the supertype primary key.

• Is also a foreign key that references the supertype table.

implement entities

converts identifying relationships into foreign keys.

implement relationships

converts all other relationships into foreign keys or tables.

many to one relationship

is implemented using a foreign key in the "many" table.

The foreign key references the primary key of the "one" table and is required (NOT NULL) if the relationship is mandatory.

It is usually named after the referenced primary key, sometimes with a prefix to make its purpose clearer.

one to one relationship

is implemented using a foreign key.

The foreign key can be placed in either table, but it is usually stored in the table with fewer rows to reduce NULL values.

It references the primary key of the other table, must be unique, and is required (NOT NULL) if the relationship is mandatory.

The foreign key is usually named after the referenced primary key, sometimes with a prefix to make its purpose clearer.

implementing relationships

Feature	One-to-One (1:1)	One-to-Many (1:M) / Many-to-One (M:1)	Many-to-Many (M:N)
Implementation	Becomes a foreign key.	Becomes a foreign key.	Becomes a new weak (junction) table.
Foreign key location	Can be in either table (usually the one with fewer rows).	Always in the "many" table.	New table contains two foreign keys, one to each related table.
References	Foreign key references the primary key of the other table.	Foreign key references the primary key of the "one" table.	Each foreign key references the primary key of one related table.
Primary key	Original primary key remains; foreign key is unique.	Original primary key remains; foreign key is not unique.	Primary key is a composite key made from both foreign keys.
Required?	Foreign key is NOT NULL if the relationship is mandatory.	Foreign key is NOT NULL if the relationship is mandatory.	Foreign keys are usually required, with primary key cascade and foreign key restrict rules.
Extra attributes	Not applicable.	Not applicable.	Relationship attributes are stored as columns in the new weak table.
Naming	Foreign key is usually named after the referenced primary key, with an optional prefix.	Foreign key is usually named after the referenced primary key, with an optional prefix.	Weak table is usually named using both related table names, with an optional qualifier to clarify its purpose.

many to many relationship

becomes a new weak table:

• The new table contains two foreign keys, referring to the primary keys of the related tables.

• The primary key of the new table is the composite of the two foreign keys.

• The new table is identified by the related tables, so primary key cascade and foreign key restrict rules are usually specified.

plural attributes

an attribute that can have multiple values for a single entity (e.g., a student can have multiple phone numbers).

It is implemented by creating a new weak table that stores the plural attribute and a foreign key referencing the original table.

The new table uses a composite primary key made up of the plural attribute and the foreign key, and is usually named using the original table name followed by the attribute name.

attribute type

During logical design, an SQL data type is defined for each attribute type. Attribute types and the corresponding data types are documented in the glossary.

Each attribute name includes a standard attribute type as a suffix. The attribute type determines the data type of the corresponding column.

attribute cardinality

determines how an attribute is implemented as a table column.

• Required attributes become NOT NULL columns,

• unique attributes become UNIQUE columns

• attributes that are both required and unique become PRIMARY KEY columns.

• If multiple columns form a primary key or unique constraint, they are defined as composite constraints in the CREATE TABLE statement.

functional dependence

Dependence of one column on another.

reflects business rules. Ex: "Each student receives one letter grade in a course" indicates the Grade column depends on the composite column (StudentID, CourseCode).

table symbol: →

B → A means A depends on B. Each value of B relates to at most one value of A.

redundancy

the repetition of related values in a table.

• It occurs when a column depends on a non-unique column.

• causes problems because updates must be made in multiple places, making queries slower and increasing the risk of inconsistent data.

normal forms

are rules for designing tables with less redundancy.each level reducing redundancy further.

• 1NF → 3NF: Commonly used to reduce basic data redundancy.

• BCNF: Improves 3NF by removing dependencies on non-unique columns.

• 4NF: Removes multivalued dependencies.

• 5NF: Removes join dependencies.

normal form tables

Normal Form	Column Rules	Row Rules	Cell Rules	Purpose
First Normal Form (1NF)	Every column represents a single attribute. No repeating columns or groups.	Each row must have a unique primary key.	Each cell contains exactly one value (no lists or multiple values).	Ensures data is atomic and rows are uniquely identified.
Second Normal Form (2NF)	All non-key columns must depend on the entire primary key. No column depends on only part of a composite key.	Table must already satisfy 1NF. Each row is uniquely identified by the primary key.	Each non-key cell must contain values related to the whole primary key, not just part of it.	Removes partial dependencies and reduces duplicate data.
Third Normal Form (3NF)	Non-key columns cannot depend on other non-key columns; they must depend only on the primary key.	Table must already satisfy 2NF. Rows are uniquely identified by the primary key.	Each non-key cell must contain values that describe only the row’s primary key.	Removes transitive dependencies and further reduces redundancy.
Boyce-Codd Normal Form (BCNF)	Every determinant (column that determines another column) must be a candidate key.	Table must satisfy 3NF. Each row must be uniquely identifiable by a candidate key.	No cell value should depend on a non-unique column.	Eliminates dependencies on non-unique columns.
Fourth Normal Form (4NF)	Columns should not contain multiple independent sets of values.	Table must satisfy BCNF. Each row represents one relationship between attributes.	Cells should not store combinations of unrelated multi-valued facts.	Removes multivalued dependencies.
Fifth Normal Form (5NF)	Columns should only represent attributes that are necessary for the relationship.	Table must satisfy 4NF. Rows should not require unnecessary joins to reconstruct information.	Cell values should depend only on the key and not on hidden join relationships.	Removes join dependencies and minimizes complex redundancy.

partial dependency

This occurs when you have a Composite Primary Key (a key made of 2+ columns), but a non-key column relates to only one part of that composite key.

keys

Concept	Meaning	Key Property
Super Key	Any attribute set that uniquely identifies rows	May include extra attributes
Candidate Key	Minimal Super Key	No redundant attributes
Primary Key	Selected Candidate Key	Official unique identifier

candidate key

is a simple or composite column that is unique and minimal. Minimal means all columns are necessary for uniqueness.

A table may have several

The database designer designates one as the primary key.

bcnf

A stricter version of 3NF where every determinant must be a Candidate Key.

The potential to violate may occur in a relation that:

• Contains two (or more) composite candidate keys.

• Has candidate keys that overlap (share at least one attribute).

denormalization

means intentionally introducing redundancy by merging tables.

eliminates join queries and therefore improves query performance.

results in first and second normal form tables and should be applied selectively and cautiously.