Building a Logical Data Model for Property Rental: A Step-by-Step Guide Using Clean Up Inc Case Study

Introduction to Logical Data Modeling in Database Design

In database courses, moving from a conceptual model to a logical data model is a critical step. This tutorial guides you through the process using the Clean Up Inc (CUI) property rental case study, which is common in data structure and algorithm assignments. By the end, you'll understand how to expand composite attributes, handle fixed types, and implement business rules—skills essential for any aspiring data architect. This approach mirrors real-world scenarios where companies like CUI manage waste collection across multiple local authorities.

Understanding the Case Study Context

Clean Up Inc is a waste management company that needs a database to manage properties, bins, contracts, drivers, and waste collections. The assignment requires you to refine your conceptual model from Assignment 1A into a logical model that meets additional requirements. For example, local authorities are classified into fixed types: Borough, City, District Council, Shire, and Town. Similarly, road surfaces are Asphalt, Concrete, or Unsealed. These fixed types must be implemented as lookup tables or enumerated values in your logical model.

Step 1: Expanding Composite Attributes

Composite attributes on your conceptual model must be broken into simple attributes. For instance, if you have an attribute like Address, split it into StreetNumber, StreetName, Suburb, Postcode. The assignment specifies that phone numbers should be simple attributes—so PhoneNumber is a single field, not split into area code and number. This aligns with typical database normalization practices.

Step 2: Handling Fixed Type Classifications

Fixed types like local authority types, road surfaces, bin replacement reasons, waste types, and collection intervals should be stored in reference tables. For example, create a LocalAuthorityType table with columns TypeID and TypeName containing the five values. This allows easy addition of new types (e.g., new waste types) without altering the schema—a key requirement from CUI.

Step 3: Modeling Bins and Contracts

Bins are identified by a 16-character hexadecimal RFID code. The bin charge is determined by the contract under which the bin was supplied, not necessarily the current contract. Therefore, you need a BinSupply table that links a bin to a contract at a specific time, storing the charge. This captures historical data accurately. For example, a bin supplied under a 2024 contract might still be in use under a 2025 contract, but the charge remains from the original supply.

Step 4: Truck Drivers and Approvals

Each driver has a unique driver number. Drivers are approved to drive specific trucks, and the approval date is recorded. A truck can have many approved drivers. This many-to-many relationship requires an associative table, e.g., DriverTruckApproval with columns DriverID, TruckVIN, ApprovalDate. Trucks are identified by VIN, and make/model are not unique—so they are attributes of the truck table.

Step 5: Waste Collection and Overweight Detection

When a truck collects waste, the bin's weight is checked. If overweight, collection is flagged as 'Y' and waste is not collected; otherwise 'N' and collected. A waste type can only be collected once per property per date, but different waste types can be collected on the same date. This requires a Collection table that includes PropertyID, WasteTypeID, CollectionDate, BinID, DriverID, TruckVIN, and OverweightFlag. A unique constraint on PropertyID, WasteTypeID, CollectionDate enforces the rule.

Step 6: Putting It All Together – Logical Data Model

Your final logical model should include tables for: Property, LocalAuthority, LocalAuthorityType, RoadSurface, Bin, BinReplacementReason, Contract, BinSupply, WasteType, CollectionInterval, Driver, Truck, DriverTruckApproval, and Collection. Each table should have primary keys, foreign keys, and appropriate data types. For instance, RFID code can be stored as CHAR(16) with a check constraint for hexadecimal characters.

Example: Implementing the Logical Model in Oracle

CREATE TABLE LocalAuthorityType ( TypeID NUMBER PRIMARY KEY, TypeName VARCHAR2(20) NOT NULL ); INSERT INTO LocalAuthorityType VALUES (1, 'Borough'); INSERT INTO LocalAuthorityType VALUES (2, 'City'); -- etc. CREATE TABLE Property ( PropertyID NUMBER PRIMARY KEY, StreetNumber VARCHAR2(10), StreetName VARCHAR2(50), Suburb VARCHAR2(30), Postcode VARCHAR2(10), PhoneNumber VARCHAR2(15), LocalAuthorityID NUMBER REFERENCES LocalAuthority(LocalAuthorityID), RoadSurfaceID NUMBER REFERENCES RoadSurface(RoadSurfaceID) ); CREATE TABLE Bin ( BinRFID CHAR(16) PRIMARY KEY, BinSize VARCHAR2(10), -- e.g., 120L, 240L CurrentContractID NUMBER REFERENCES Contract(ContractID) ); CREATE TABLE BinSupply ( BinRFID CHAR(16) REFERENCES Bin(BinRFID), ContractID NUMBER REFERENCES Contract(ContractID), SupplyDate DATE, BinCharge NUMBER(10,2), PRIMARY KEY (BinRFID, ContractID) );

Why This Matters: Real-World Application

Logical data modeling is not just an academic exercise; it's used in every industry. For example, a ride-sharing app like Uber uses similar models for drivers, vehicles, and trips. Even in gaming, databases track players, items, and transactions. Understanding how to transform business rules into a normalized schema is a key skill for data engineers and back-end developers.

Common Pitfalls to Avoid

• Forgetting to expand composite attributes: Always break down addresses, names, etc., into atomic components unless instructed otherwise.
• Ignoring fixed types: Do not hardcode values; use lookup tables to allow easy updates.
• Missing historical data: For bin charges, record the supply contract, not just the current contract.
• Overlooking unique constraints: Ensure that a property cannot have two collections of the same waste type on the same date.

Conclusion

By following these steps, you can build a robust logical data model that meets the Clean Up Inc requirements. This process mirrors real-world database design, where collaboration and iteration are key. Remember to keep your Assignment 1A confidential and only share with your group and teaching staff. Good luck with your modeling task!