Database Design Chapter 3



Why do Data Modeling?


Model: Simplified representation of a complex object or process

Data Modeling: A businesses way to represent the entities that comprise the business and way these entities interrelate.

Entity Set: The entire population of a single entity type is called an entity set.



Benefits of using a model:
-Creating a model is cheaper than creating the real thing
-A model is a concise means of communication
-A model can be changed quickly without incurring a large cost.
-A model only emphasizes selected, important aspects of the whole


Business Rule: A brief, precise, and unambiguous description of a policy, procedure, or principle within a business or organization.
-A business rule will indicate a fact about some aspect of the business that places a limit (or constraint) on the business.
-These rules provide a structure to the business and help describe what the business does and doesn’t do at a detail level.


Example of business rules:
-Students register for classes.
-Professors teach classes.
-A grade is assigned to a student who takes a class.
-A professor has an office.
-A class has a maximum enrollment.
-Student grade point average is calculated as quality points divided by credit hours attempted.


Other business rules that may be more complex and not necessarily obvious like examples below:
-Stuends must be a high school graduate to be admitted.
-A student may register for one class without transcripts on file.
-A professor must teach at least 15 credit hours each term.
-A student must have passed any prerequisite course to register for a subsequent course.


Common sources used to identify business rules:
-User interviews
-Exisiting forms and reports
-Local, state, and federal regulations
-business policies


How business rules can be implemented:
-Using a primary key (Student can only be admitted once)
-Using a foreign key (Student can’t register unless transcript is on file)
-Using a check constraint (gender of student can only be M or F)
-Using a database trigger (increase student count by one each time a new row is added to the student registration table)
-Using a stored procedure (Calculate grade point average when requested by application)


Business rules are:
-Communication between a user and a business analyst or database designer.
-Way to document how the business or organization works
-Method to determine entities, attributes, relationships, and some constraints.
-Boundry for the data



Entity Relationship Diagram (ERD): Enables us to create a model of each entity in our data model and the relationships between them.

Formats of ERD: Crows foot, (more commonly used) Other formats: Chen model, Kronke, and Unified Modeling Language (UML).
-Each goal is to provide a logical design of a relational database system.

Crows Foot ER model:
-Entities
-Attributes for each entity
-relationships between the entities including the cardinalities
-identifiers that uniquely identify each entity instance


Symbols/ Definitions
-Rectangle used to represent an entity
-Horizontal Line: used to connect related entities. This represents a relationship
-Pentagon: Used to represent the intersection of data (attribute)
-Short vertical line: Represents one.
-Circle: Represents zero.
-“Crows foot”: represents many.


Cardinalities: The way the Crows foot model implements constraints on business rules.

Two types of cardinality: Maximum and Minimum
Maximum cardinality can be written one of three ways:
- one to one (Also written as 1:1)
-one to many (Also written as 1:M)
- many to many (Also written as M:M or M:N)

Maximum Cardinality Constraints use 3 symbols: (Zero, one, many)
-Used to represent how many of each entity (the maximum) are participating in the data relationship we are modeling.


Minimum Cardinality: a pair in the symbol that represents the minimum number of entities participating.


Cardinality combinations:
Symbol
(See page 11 on CH3 powerpoint)



Entity Relationships
1. Degree of the relationship: How many entities are involved in the relationship.
2. Maximum Cardinality: What is the maximum possible number of entity occurrences in the relationship?
3. Minimum Cardinality: What is the minimum required number of entity occurences in the relationship?


Binary Relationship: A relationship between two entities is the most common occurrence.
-Example of binary relationship: “Student registers for a course”  Relationship between two entities “Student and course”.
REGISTERS: The relationship has a name also  -STUDENT-COURSE.


Ternary relationship: A relationship involving three entities.
Example, “PROFESSOR-COURSE-CLASSROOM”


Unary or recursive relationship: Unary is derived from unit or one. This is when a single entity is involved in a relationship.
Example: Assume our college has professors that manage other professors. Here we clearly have a single entity- professor.
The relationship name is MANAGES.

Degrees of entitiy relationships you can have :
-Unary = 1
-Binary - 2
-Ternary - 3


Maximum Cardinality  (How it’s determined)
-Determined by examining one of the entities in the relationship and seeing how many of the other entities may be related.
Example, professors are related to sections. (A section is a scheduled course). We might have a rule that says one professor can teach 5 sections.
-This would be written as 1:5 (One professor can teach a maximum of 5 sections).
-1:M (One professor can teach an unlimited number of sections)

Relationships are bi-directional:
-Example: given two entities A and B, A has a relationship with B and B has a relationship with A. This means the relationship will have two maximum cardinalities - one for A and one for B.




Minimum Cardinality
-Determined by examine one of the entities in the relationship and seeing how few (instead of how many) entities must be related.
-Also called “Participation”
Example: What is the minimum amount of courses a professor can teach?” -zero, but the minimum must be met for this. So we would say “one”



Relationship Name: combination of the degree and the maximum cardinalities involved in that relationship.
-Example: 1:1 unary  1:1 binary (One to one unary) (one to one binary)
-Example: 1:M unary 1:M binary (one to many unary) , (One to many binary)
-Example: M:N Unary , M:N Binary (Many to Many unary), (Many to Many binary)

Most common relationship found in a business database - One to many binary relationship

Identifier- The attribute used to select an entity instance from all other entities. Each entity should indicate an identifier. Enables us to select an entity instance from an entity class.
Unique Identifier- If the identifier has exclusive, non-duplicated values.  Example: Combination of a professors first and last name. (This is shaded to identify it’s a unique identifier)
Entity Class- A group of entities that all share the same attributes.
Entity Instance- Just one member selected from the entity class


One to One Binary Relationship
-Involves two entities
Example, Professor and Office.  1:1 binary


PAGE 27


One to Many Binary Relationship
-Just means “More than 1” identified by a crows foot

Many to Many Binary Relationship
-Review page 33 video

One to Unary Relationship
REVIEW VIDEOS ON PAGES 27- 60



Page 60

Intersection Data: Data that exists due to the relationship (intersection) between STUDENT and SECTION.

Association Entity: Additional entity used in Intersection Data


Weak entity: An entity that depends on another entity for it’s existence.
Parent entity: The entity the weak one depends on.

Strong entity: Any entity that is not a weak entity.

Note: Strong entities can exist on their own. Weak ones cannot.


Type basic types of weak entities:
-ID-dependent
-Non-ID-dependent


ID-dependent - means the weak entity has the identifier (primary key) from it’s parent entity as part of it’s identifier (parent key).
Example, Employees and their dependents

Identifying Relationship: Means the weak entity contains the identifier (primary key) of it’s parent within it’s own identifier (primary key)

Important notes about Weak Entities:
-ID-dependent weak entities will always have a composite identifier (primary key)
-Non-ID-dependent weak entities may or may not have a composite identifier
-All ID-dependent entities are weak entities
-All weak entities will have a minimum cardinality of one
-All weak entities will have a maximum cardinality of one
-Since weak entities have a minimum and maximum cardinality of one, the parent is required to exist
-Remember the requirement is the row (or rows) representing the weak entity cannot exist without the corresponding parent row.

Why are weak entities important to identify?
-The parent row of a weak entity is added to the database before any row for a related weak entity row
-If the parent row of a weak entity is removed from the parent table, the row (or rows) representing the weak entity must also be removed (first)


When dealing with Weak Entities and Referential Integrity rules
-Always set to either Restrict option or Cascade



Modeling Current Versus Historical Data
Examples fo when we only need to track current
-A professor has at most one current computer
-A professor isn’t required to have a computer
-A computer is assigned to at most one professor
-A computer is assigned to at least one professor. In other words, the college doesn’t have unused computers sitting around.