You now have an ERD that you can use as a blueprint for constructing a database. The next step is to start thinking about how certain elements of the ERD should be implemented in SQL.
As discussed in lectures, relationships in an ERD become foreign keys in a relational database. This means that you’ll need to link associated pairs of relations by embedding the primary key of one as an attribute in the other. Here are some rough guidelines for doing this:
For a 1:M relationship, the foreign key is always embedded in the relation on the many side.
For a 1:1 relationship, the foreign key can be embedded in the relation on either side (or even on both sides). Participation can help here: if one side is optional (O) and the other mandatory (I), then it’s better to put the foreign key on the optional side.
M:N relationships need to be broken down into two 1:M relationships with an associative entity, as discussed in lectures. You can then apply the 1:M guideline to work out the foreign keys.
What foreign key attributes do you need to add to which entities?
Another important thing to consider is the type or domain of each attribute. For each attribute in your ERD, what general type of data will be stored in it (e.g., numeric, text, date/time, …)?
Are there any attributes that at first glance look like one type of data (e.g., numbers), but should probably be stored as something else (e.g., text)? Why?