Database Structure Models

Structure Models

• They describe data and information structures in business processes
• They are tools for planning, documenting, discussing, and implementing databases
• Primary goal is to create a blueprint for relational database development
• Supports process information collection, aggression, and communication

Data Models

• They are graphic representations of database conceptual contents
• They facilitate communication between users and designers of the database
• A logical database structure model describes entities, relationships, cardinalities, attributes and characteristics

Class Diagrams

• They are structure models using UML notation
• They describe the logical structure of a database system

Classes

• They are separately identifiable collections of things (objects)
• Organizations collect and store information about these objects
• They represent organization resources like trucks, buildings, cash, and investments
• They also represent people (customers, employees) and events (sales, purchases) and conceptual structures like budgets

Class Implementation

• Classes are implemented as tables in relational databases
• Instances of objects are represented as rows in these tables
• Each class is represented by a rectangle composed of three compartments
  • Top Compartment: Displays the name of the class
  • Middle Compartment: Shows the attributes (data elements) shared by all instances
  • Bottom Compartment: Describes the operations each instance can perform
• Attribute and operation compartments are optional

Entities

• In UML notation, entities (people, things, events) are modeled as classes

Associations

• Associations depict relationships between two classes
• For example customers participate in sales
• Associations are drawn as lines connecting two classes
• Associations can be named to clarify the business purpose of the association

Association Names

• They are verbs or phrases that describe the relationship between instances of classes

Multiplicities

• They describe the minimum and maximum number of times instances in one class can be associated with another
• Represented as a pair of numbers on the opposite side of the association for a class
• Binary associations will have two sets of multiplicities
• Minimum values can be 0 (optional) or 1 (mandatory)
• Maximum values can be 1 or many (*)

Attributes

• They are data elements describing class instances
• Attribute specifications include data type, default value, constraints, and descriptive information

Primary Key (PK)

• It is an attribute or combination of attributes that uniquely identifies each instance in a class or table row
• It can be modeled within the attribute list on a UML diagram
• It serves as a unique identifier for each instance in the class

Primary Key Criteria

• It must uniquely identify each class instance, avoiding duplicable values like names
• It cannot be null (blank) under any circumstances
• Not using attributes that are potentially unavailable for any instance of the class is important
• It should not change over time
• Should be controlled by the organization assigning it to ensure uniqueness
• Sequential values make it easier to recognize gaps in data
• Shorter key values are preferred for ease of entry, indexing, and retrieval

Foreign Key (FK)

• It is an attribute or combination of attributes that link tables
• It links to the primary key of another table to support a defined association

Relationship

• It is the business purpose for the association between two classes or database tables

Generalization Relationship

• It is a UML symbol grouping things sharing characteristics
• It reduces redundancy by modeling shared characteristics only once

Aggregation Relationship

• It is a UML notation representing relationships between classes often considered together
• This is used when a sports league is made up of teams

Composition Relationship

• It is a UML notation representing relationships similar to aggregation
• In this relationship one class cannot exist without the other
• For example, a book and its chapters

Mapping UML to Relational Database Schema

• Map classes to tables
• Map class attributes to table fields
• Assign primary keys
• Map associations to foreign keys, placing them toward the "many" side
• The primary key of the "1" side becomes a foreign key on the "*" side
• Minimum multiplicities are determined by the timing of entries in associated tables
• If an entry in one table must match an entry in the other, the minimum is 1; otherwise, it is 0
• Create new tables to implement many-to-many relationships
• A many-to-many relationship occurs when maximum multiplicity is * on both ends
• Implement relationships among tables

Decision Model and Notation

• Involves four iterative steps:
  • Identifying decisions
  • Describing and documenting their impact on business objectives
  • Specifying decision requirements
  • Decomposing and refining requirements
  • Determining where business rules apply and where new rules are needed

Operational Decision Categories

• Eligibility/Approval: Determining eligibility for a product or service
• Validation: Assessing the validity of a claim for processing
• Calculation: Determining the correct discount for a customer
• Risk: Assessing the risk of relying on a supplier's delivery date
• Fraud: Assessing the likelihood of fraudulent claims
• Opportunity: Identifying the best available option
• Assignment: Assigning an issue to the appropriate party
• Targeting: Determining the appropriate response

Constraints

• They are optional or mandatory guidance about process performance

Business Rules

• They support decision-making, standardize processes, and constrain actions
• They are succinct statements of constraints, guiding behavior in specific situations
• They establish multiplicities in class models and criteria for branching in activity models
• Obligatory rules state what should occur
• Prohibited rules state what should not occur
• Allowed rules state what is allowed under certain conditions.

Semantics of Business Vocabulary

• It sets standards for stating business rules using natural language

Enforcement Levels

• Strict Enforcement: Violations are not authorized
• Pre-Override: Violations allowed if authorized in advance
• Post-Override: Violations allowed if authorized after the violation

Decision Tables

• Combine multiple business rules for different circumstances requiring a decision
• Consist of a name, inputs, outputs, and rules

Organizational Governance

• Involves business planning, execution, monitoring, and adapting
• During planning, leaders establish objectives, identify risks, create processes, and implement controls to mitigate risks