MySQL and mSQL

Physical Database Design

What was the point in creating the logical data model? You want to create a database to store data about CDs. The data model is only an intermediate step along the way. Ultimately, you would like to end up with a MySQL or mSQL database where you can store data. How do you get there? Physical database design translates your logical data model into a set of SQL statements that define your MySQL or mSQL database.

Since MySQL and mSQL are relational database systems, it is relatively easy to translate from a logical data model, such as the one we described earlier, into a physical MySQL or mSQL database. Here are the rules for translation:

1. Entities become tables in the physical database.

2. Attributes become columns in the physical database. You have to choose an appropriate datatype for each of the columns.

3. Unique identifiers become columns that are not allowed to have NULLs. These are called primary keys in the physical database. You may also choose to create a unique index on the identifiers to enforce uniqueness. For your purposes, mSQL does not have a concept of a primary key. It simply has unique indices. This issue does not apply to MySQL.

4. Relationships are modeled as foreign keys. We will cover this later.

If we apply these rules to our data model—minus the Record Label address information—we will end up with the physical database described in Table 2.2.

Table 2-2. Physical Table Definitions for the CD Database

Table	Column	Datatype	Notes
CD	CDId	INT	primary ...