Convert Data Model To A Database Design Specifying Tables

Convert data model to a database design specifying tables primary keys

San Juan Sailboat Charters (SJSBC) leases sailboats on behalf of owners who want to earn income from their boats when not in use. SJSBC specializes in multiday or weekly charters for sailboats ranging from 28 to 51 feet in length. The company's operations involve detailed record-keeping for equipment, maintenance, customer logs, and contractual relationships with owners and customers. The task requires converting the conceptual data model into a physical database design, including tables, primary keys, foreign keys, and column properties. Additionally, the assignment involves describing how weak, supertype, and subtype entities are represented, creating a visual Crow’s Foot ER diagram, and documenting referential integrity constraints.

Paper For Above instruction

The transformation of a conceptual data model into a physical database design is a fundamental step in developing an effective information system, especially for complex operations such as those of San Juan Sailboat Charters (SJSBC). This process involves translating entities, relationships, and constraints into tables, primary keys, foreign keys, and attribute properties that are implementable within a relational database management system (RDBMS).

Database Tables and Keys

The primary entities identified in the SJSBC data model include OWNER, BOAT, EQUIPMENT, CHARTER, CUSTOMER, and possibly SJSBC itself as an equipment owner. The implementation begins by establishing tables that directly correspond to these entities.

- The OWNER table would contain attributes such as OwnerID (Primary Key), OwnerName, OwnerType (individual or company), ContactDetails, etc. Since owners can be individuals or companies, the OwnerType attribute may serve as a supertype discriminator if more detailed subtype-specific data is necessary.

- The BOAT table would include BoatID (Primary Key), Length, YearBuilt, Status, and other relevant details such as physical specifications. Desktop keys such as BoatID uniquely identify each vessel.

- The EQUIPMENT table should encompass EquipmentID (Primary Key), EquipmentType, Description, Cost, and Status. Ownership of equipment, whether by owners or SJSBC, is managed via an OwnershipID foreign key that links back to the OWNER or SJSBC owner entity.

- CHARTER functions as a rental transaction record, with CharterID as the primary key, including Foreign Keys such as CustomerID, BoatID, and EquipmentID (if equipment is tracked per charter). Date fields for start and end dates are also included.

- The CUSTOMER table includes CustomerID (Primary Key), Name, ContactInformation, and possibly a CustomerLogID if logs are stored separately.

Foreign keys enforce relationships, such as BoatID in CHARTER referencing BOAT, OwnerID referencing OWNER, and EquipmentID referencing EQUIPMENT. These relationships ensure referential integrity, maintaining consistency between related records.

Column Properties

Each table’s columns are assigned appropriate data types—integers for IDs, strings for names and descriptions, dates for charter periods, and numerical types for measurements like boat length or equipment cost. Constraints such as NOT NULL are applied to key fields and other essential attributes, while DEFAULT values may be set for optional data.

Representation of Weak Entities

Weak entities are those that do not possess sufficient attributes to form a primary key independently, usually relying on a related strong entity. In this context, if any equipment items depend on a specific ownership (e.g., equipment that cannot exist without an owner), they are modeled as weak entities. These are represented with composite primary keys combining the EquipmentID and the OwnerID (or other parent entity key), and their existence is dependent on the parent entity. Referential integrity constraints enforce that weak entity records cannot exist without corresponding parent records.

Representation of Supertype and Subtype Entities

Owners are modeled as supertype entities, with subtype entities distinguishing between individual owners and companies. The Owner table manages common attributes, while subtype tables—such as IndividualOwner and CorporateOwner—contain specific fields relevant to each subtype, linked via one-to-one relationships. Discriminator attributes, like OwnerType, facilitate identifying the subtype.

Creating the ER Diagram

A Crow’s Foot ER diagram visualizes the database schema, illustrating entity tables, primary keys, foreign keys, and relationships. For example, the OWNER entity connects to EQUIPMENT with a one-to-many relationship, indicating that an owner can have multiple pieces of equipment. The BOAT-to-CHARTER relationship is one-to-many, as each boat can be chartered multiple times. Relationships between CUSTOMER and CHARTER are similarly one-to-many. The diagram uses standard ER symbols: rectangles for entities, diamonds for relationships, and Crow’s Foot symbols for cardinalities, ensuring clarity and adherence to modeling conventions.

Referential Integrity Enforcement

Integrity constraints are crucial in maintaining data consistency. For required parent entities, foreign keys are set with ON DELETE CASCADE or ON UPDATE CASCADE actions, ensuring dependent records are automatically maintained or deleted appropriately. For required children, such as a CHARTER record needing an existing CUSTOMER and BOAT, foreign key constraints enforce these dependencies. Figure 6-28(b) illustrates that such constraints prevent nulls in foreign key columns and restrict deletions of parent records if child records exist, unless cascade operations are specified.

Conclusion

Successfully converting a data model into a relational database design involves carefully defining tables, keys, and relationships, and implementing constraints to uphold integrity. Representing weak entities, supertypes, and subtypes ensures accurate modeling of complex structures and inheritance. The ER diagram provides a visual blueprint for implementation, and enforcing referential integrity guarantees reliable and consistent data storage, facilitating effective management of SJSBC’s leasing operations.

References

  • Database Systems: A Practical Approach to Design, Implementation, and Management (6th ed.). Pearson.
  • Fundamentals of Database Systems (7th ed.). Addison-Wesley.
  • Database System Concepts (7th ed.). McGraw-Hill Education.
  • Database Management Systems (3rd ed.). McGraw-Hill.
  • Entity-Relationship Modeling. In R. Elmasri & S. B. Navathe, Fundamentals of Database Systems. Addison-Wesley.
  • Modern Database Management (12th ed.). Pearson.
  • An Introduction to Database Systems (8th ed.). Pearson.
  • Conceptual Database Design: An Entity-Relationship Approach. Benjamin/Cummings.
  • Modern Database Technology. Apple Academic Press.
  • International Journal of Data Management, 4(2), 45-52.

Related Assignments