CSIT115 Database Management Systems: Object Modeling and ER Diagrams

Databases and Conceptual Modeling Frameworks

The selection of modeling techniques for database design involves several choices at different stages: - Conceptual Model Choices: Entity-Relationship Diagram (ERD), Object-Oriented, Hierarchical, or Network model. - Relational/Implementation Model Choices: Relational, Object-Oriented, Document-Based, Network model, or Key-Value.
Graphical Notations Timeline: - Entity-Relationship diagrams (ER): Established in $1976$ . - Object Modeling Technique (OMT): Established in $1991$ . - Unified Modeling Language (UML): Established in $1994$ , providing graphical notations for conceptual modeling.

Object Modeling Basic Concepts

Quantization: A database is quantized into discrete objects.
Object Components: Objects are described by: - Attributes (Properties): The data characterizing the object. - Operations (Methods): The actions or behaviors (noted as being ignored for this specific database context).
Object Class: A group of homogeneous objects with common properties, common semantics, and common identifiers.
Identifiers: Values of selected attributes (the identifier) are used to identify unique objects.
Concrete Examples of Objects and Classes: - A student is an object; a group of students forms a class called $STUDENT$ . - A lecturer is an object; a group of lecturers forms a class called $LECTURER$ . - A lecture hall is an object; it is identified by a building number and a room number. - A shipment is an object; it is identified by a supplier name, date, and time. - An accident is an object.
Links and Associations: - Link: A conceptual connection between two or more objects (e.g., "James talks to Janusz," "Lecture 1 is-in building 3 room 2"). - Association (or Relationship): Represents a group of homogeneous links with a common structure, common attributes, common semantics, and common identifiers (e.g., $STUDENT$ "Talks-to" $LECTURER$ ).

Core Definitions in Database Design

Entity: An object of importance about which information needs to be kept in the database.
Attribute: A piece of data that needs to be collected for an entity.
Multiplicity: The cardinality of an association or relationship, defining how many instances of one class can be associated with instances of another class.

Class Diagram Notations and Attributes

Class Representation: - Class Name: Located at the top. - Attributes: Listed below the name with specific notations for constraints: - Identifier (Primary Key): Marked with $\text{ID}$ or $\text{ID1}$ , $\text{ID2}$ for multiple/candidate keys. In UML, indicated by $\text{{PK}}$ . - Derived Attribute: Indicated by a forward slash (e.g., $\text{/age}$ , $\text{/totalStaff}$ ). These are values derivable from other attributes (Example: $\text{Sales Tax} = (\text{Sale Amount} \times 0.08)$ ). - Multivalued Attribute: Indicated by square brackets with a range or asterisk (e.g., $\text{phone } [\text{*}]$ , $\text{telNo } [1..3]$ ). Represents attributes that hold multiple values for a single entity (Example: $\text{Week days} = {\text{Sun, Mon, Tue, …}}$ ). - Optional Attribute: Indicated with a range starting at $0$ (e.g., $\text{country } [0..1]$ ).
Attribute Composition: - Simple Attribute: Composed of a single component with an independent existence (e.g., student name, product name). - Composite Attribute: Composed of multiple components with independent existences, such as an Address consisting of Home number, Street Name, and City Name.
Attribute Domain: The set of allowable values for one or more attributes.

Multiplicity and Cardinality Constraints

Multiplicity Standards: - Exactly one: $1$ or $1..1$ - Many (zero or more): $\text{<em>}$ or $0..</em>$ - Zero or one: $0..1$ - One or more: $1..*$ - Numerical specification: Specific ranges like $2..4$ or $1..10$ .
Participation Constraints: - Mandatory Participation: Indicated by a minimum multiplicity of $1$ (e.g., $1..1$ ). This means all instances must participate (e.g., "All branches are managed"). - Optional Participation: Indicated by a minimum multiplicity of $0$ (e.g., $0..1$ ). This means not all instances participate (e.g., "Not all staff manage branches").

Types of Relationships

One-to-One ( $1:1$ ): Example: One Department is managed by one Manager.
One-to-Many ( $1:M$ ): Example: One Company has many Employees (multiplicity $0..1$ on Company side, $\text{*}$ on Employee side).
Many-to-Many ( $M:M$ ): Example: Suppliers supply Parts (multiplicity $\text{*}$ on both sides).
Complex Degrees of Relationships: - Ternary Relationship: Involves three entities. Example: "Staff registers a Client at a Branch." - Quaternary Relationship: Involves four entities. Example: "A Solicitor arranges a Bid on behalf of a Buyer supported by a Financial Institution." - Recursive Relationship: A relationship where the same entity participates more than once in different roles. Example: "Supervises" where one Staff member is the "Supervisor" and another is the "Supervisee."
Role Names: Used to disambiguate the role an entity plays in a relationship (e.g., "Manager" vs. "Member of Staff" in a relationship between Staff and Branch Office).

Advanced Association Concepts

Link Attribute: An attribute that describes an association rather than a single entity. - Example: "$Purchase-price$" on an "Owns" link between Person and Car. - Example: "$Enrolment-date$" on the "Enrols" link between Student and Subject.
Association Class: A class that encapsulates an association to allow it to have its own attributes and potentially links to other classes. Used when a relationship itself behaves like an entity.
Strong Entity Type: An entity type that is not existence-dependent on another entity type.
Weak Entity Type: An entity type that is existence-dependent on another entity type.
Qualified Association: An association where the objects on the "many" side are disambiguated by an attribute called a qualifier. - Example: A Building has many Rooms. The \text{room#} is a qualifier. Within a Building (\text{building#}), the \text{room#} identifies at most one room. The pair \text{(building#, room#)} identifies the Weak Entity "Room."

Generalization and Specialization

Generalization: An "Is-a-subset" or hierarchy relationship between classes (e.g., $\text{STUDENT}$ is a generalization of $\text{UNDERGRADUATE STUDENT}$ and $\text{POSTGRADUATE STUDENT}$ ).
Participation Constraints in Specialization: - Mandatory: Every member of the superclass must be a member of a subclass. - Optional: A member of the superclass does not have to belong to any subclass.
Disjoint Constraints: - Disjoint ( $\text{Or}$ ): A member of a superclass can be an instance of at most one subclass. - Non-disjoint: A member of a superclass can be a member of more than one subclass.
Categories of Constraints: - Mandatory and Disjoint. - Optional and Disjoint. - Mandatory and Non-disjoint. - Optional and Non-disjoint.
Worked Examples: - Staff Hierarchy: Staff is the superclass; Supervisor and Manager are subclasses ( $\text{{Optional, Or}}$ ). - Owner Hierarchy: Owner is the superclass; PrivateOwner and BusinessOwner are subclasses ( $\text{{Mandatory, Or}}$ ).

The Database Design Lifecycle

Requirement Analysis: Determine what data is needed, by whom, and frequency of use.
Conceptual Database Design: Create a high-level description of data (ER model).
Logical Database Design: Convert the ER model into logical schemas (tables).
Schema Refinement: Normalize and simplify the model.
Physical Database Design: Performance tuning based on workload.
Application and Security Design: Create applications and define access controls.

Design Verification and Connection Traps

Design Verification: A process where the team splits; one group creates scenarios/situations, and the other proves the design can handle them. Refinements occur during this phase.
Connection Traps: Problems in ER models due to misinterpreting relationship meanings: - Fan Trap: Occurs when a model represents a relationship between entities, but the pathway between certain entity occurrences is ambiguous. - Example: Division has multiple Branches AND multiple Staff. To find which branch a staff member works in, the model must be restructured so Branch is between Division and Staff. - Chasm Trap: Occurs when a model suggests a relationship exists, but a pathway does not exist between certain entity occurrences. - Example: Branch has Staff and Branch has Properties. If Staff oversee Properties, but not all Properties are assigned to Staff, you cannot see which Branch a Property belongs to unless a direct "Offers" link exists between Branch and Property.

Worked Case Study: Car Rental Co. (CRC)

Requirements: - Cars: make, model, year, engine size, fuel type, passengers, registration number ( $\text{ID1}$ ), purchase price, purchase date, rent price, and insurance details. - Subcontractors (Repair shops): name ( $\text{ID1}$ ), address (street, city), range of services, phone numbers, and services offered (name, price, duration). - Policy: Cars are not kept for more than $1$ year. - Repairs: Done by subcontractors with long-term agreements. Keep amount, date, and credit card number ( $\text{ID}$ ).
Key Attributes Identified: - Car: \text{/Num_repairs} (derived attribute). - Insurance Policy: $\text{Policy number (ID1)}$ , $\text{Date}$ , $\text{Price}$ , $\text{Terms}$ . - Repair Shop: $\text{Name (ID1)}$ , $\text{Address}$ , $\text{Phone [1..*]}$ .
Identified Relationships: - One Insurance Policy covers many Cars ( $1:M$ ). - Many Cars are repaired by many Repair Shops ( $M:M$ ). - The relationship between Car and Repair Shop includes link attributes: $\text{Creditcard}$ , $\text{date}$ , $\text{Amount}$ , and \text{/num_repairs}.

Worked Case Study: University Database

Professor: $\text{SSN (ID)}$ , $\text{name}$ , $\text{age}$ , $\text{rank}$ , $\text{research specialty}$ .
Department: $\text{DepNum (ID)}$ , $\text{Name}$ , $\text{Main office}$ .
Relationships: - "Runs": One Department is run by one Professor (Chairman). - "Work-in": Professors work in one or more Departments; include a $\text{Percentage}$ of time as a link attribute/association class.

Case Study: B&N (Barnes & Noble) Bookstore &

Requirements: - Customers ( $\text{CID}$ ) browse a catalog of Books ( $\text{ISBN}$ ). - Corporate customers usually order by ISBN and quantity ( $\text{QTTY}$ ), paying by credit card. - Orders are shipped together when all items are in stock. - Catalog Details: $\text{ISBN}$ , $\text{title}$ , $\text{author}$ , $\text{purchase price}$ , $\text{sales price}$ , $\text{year published}$ , \text{Qtty_in_stock}.
Design Review Dialogue: - Dude 2: "What if a customer places two orders for the same book in one day?" - Dude 1: "The first order is handled by creating a new Orders relationship and the second order is handled by updating the value of the quantity attribute in this relationship." - Dude 2: "What if a customer places two orders for different books in one day?" - Dude 1: "No problem. Each instance of the Orders relationship set relates the customer to a different book." - Dude 2: "Ah, but what if a customer places two orders for the same book on different days?" - Dude 1: "We can use the attribute order date of the orders relationship to distinguish the two orders." - Dude 2: "Oh no you can't. The attributes of Customers and Books must jointly contain a key for Orders. So this design does not allow a customer to place orders for the same book on different days."
The Solution: Introduce an Order ID ( $\text{OID}$ ) as part of the identification for the relationship, creating an entity or association class for "Orders." This allows identifying triplets like $\text{}$ , $\text{}$ , and $\text{}$ (the same customer and book but a different order ID).