Entity-relationship diagram. Total 2 Problems ERDs should be ✓ Solved

Draw an ERD representing information requirements for two problems: a bookstore and a hair salon, as described in the assignment.

Paper For Above Instructions

Entity-relationship diagrams (ERDs) are fundamental tools in database design, providing a visual representation of entities, their attributes, and the relationships between them. This paper presents ERDs for two distinct scenarios: a bookstore and a hair salon, focusing on the information requirements as specified.

Problem 1: Book Store

The bookstore database captures essential details regarding the books and transactions involved in sales and returns. The necessary information for each book includes the following attributes: ISBN, cover type, current price, number of pages, publication year, category, and the number of copies in store. The ISBN serves as a unique identifier, ensuring that no two books in the database share the same ISBN.

The relationship between customers and books is critical; each customer can purchase multiple books, which may include multiple copies of the same title or different titles altogether. The database must also record relevant purchase details such as the date and time of purchase, total dollar amount, payment method, and amount paid.

Returns are another significant aspect of the bookstore scenario. When a customer returns a book, each physical book returned generates a distinct record in the database, capturing the date and time of the return. Notably, a customer can only return a book previously purchased from the store, which introduces a necessary validation check in the process.

The following is a conceptual overview of the entities for the bookstore ERD:

  • Entities:

    • Book: ISBN (PK), CoverType, CurrentPrice, Pages, PublicationYear, Category, CopiesInStore

    • Customer: CustomerID (PK), PurchaseDateTime, TotalAmount, PaymentMethod, AmountPaid

    • Return: ReturnID (PK), BookID (FK), ReturnDateTime

  • Relationships:

    • Customer purchases Book (one-to-many)

    • Customer returns Book (one-to-many)

Problem 2: Hair Salon

The hair salon database similarly organizes information concerning customers, services, and products. Each customer’s details are important, including name, address, email, phone number, and loyalty card number for those in the loyalty program. While multiple customers may share the same phone number, a unique loyalty number must be established for each participant.

The salon offers various hair services; each service includes its current price, which every customer pays upon receipt. Importantly, a customer may receive multiple services during a single session, with the database capturing all services rendered.

In addition to services, customers can purchase hair products, which are recorded alongside session details, including the total dollar amount for the session and payment method. Details regarding each product include its unique product number, description, type, and price.

If a customer participates in the loyalty program, total points earned across sessions will be calculated and added to each customer's point balance, incentivizing continued patronage.

The conceptual overview of entities for the hair salon ERD is as follows:

  • Entities:

    • Customer: CustomerID (PK), Name, Address, Email, Phone, LoyaltyCardNumber

    • Service: ServiceID (PK), ServiceName, CurrentPrice

    • Product: ProductID (PK), Description, ProductType, Price

    • Session: SessionID (PK), CustomerID (FK), SessionTotal, PaymentMethod, PointsEarned

  • Relationships:

    • Customer receives Service (many-to-many)

    • Customer purchases Product (one-to-many)

Conclusion

Creating ERDs for both the bookstore and hair salon allows for efficient organization and management of the data associated with their operations. By clearly defining entities, their attributes, and their relationships, these diagrams not only enhance the database design process but also facilitate a better understanding of the underlying data structure required to support business operations. Both diagrams must ensure adherence to the necessary rules, particularly the uniqueness of primary keys and the correctness of relationships, which are essential for maintaining data integrity.

References

  • Hoffer, J. A., Prescott, M. B., & McFadden, F. (2016). Modern Database Management. Pearson.

  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems. Pearson.

  • Codd, E. F. (1970). A Relational Model of Data for Large Shared Data Banks. Communications of the ACM.

  • Ambler, S. W. (2012). Agile Database Techniques: Effective Strategies for the 21st Century. Wiley.

  • Rob, P., & Coronel, C. (2016). Database Systems: Design, Implementation, & Management. Cengage Learning.

  • Gulledge, T. R. (2014). Introduction to database management systems. Jones & Bartlett Publishers.

  • Becker, J., & Knackstedt, R. (2014). Business Model Management: Design – Instrument – Process. Springer.

  • Finalis, O. (2015). Principles of Data Management: Facilitating Information Sharing. London's Academic Publishing.

  • Stair, R., & Reynolds, G. (2017). Principles of Information Systems. Cengage Learning.

  • Date, C. J. (2004). An Introduction to Database Systems. Pearson/Addison-Wesley.