Appendix E. Modeling and Designing Relational Databases
Designing a database is the essential first step to developing a web database application. In this appendix, we introduce database modeling and the techniques to convert a model into the SQL statements needed to create a database. We assume you’ve worked your way through Chapter 5, and are familiar with the basic database terminology and SQL statements.
This appendix isn’t intended to replace a course or book on
relational databases. Modeling requirements with an entity-relationship
model requires both patience and experience. Instead, we detail our
thought processes in a case study that models the winestore requirements
and converts them to SQL
TABLE statements. Pointers to resources
on modeling and database design are included in Appendix G.
The Relational Model
Relational database management systems maintain, enforce, and use relationships between data to answer complex queries. To illustrate the principles of relational databases, we use the winestore system requirements and descriptions from Chapter 16 as the basis for our examples.
Case Study: Relationships in the Winestore
There are three essential types of data or entities that form the basis of the winestore. First, there is the wine itself: each wine has characteristics or attributes such as a name, a type, and a variety. Second, there is the customer, who has attributes such as a name and an address. Last (and importantly in selling wine!) is a customer purchase ...