Logical Database Design

* Construction of a *relational* model of the data based on the [conceptual database design](Conceptual%20Database%20Design.md) stage.
* Enforces stuff like keys, not-null, etc.

• STILL independent of a specific database and other physical considerations

Relational Data Model §

See Relational Data Model

From Conceptual Database Design §

To convert from a conceptual database design, we need to:

1. Flatten all composite attributes and multi-valued attributes in any entity set
2. If participation is total the key cannot be NULL.

For One-to-Many Binary Relationships (i.e. with key constraints) §

1. Delete the relationship attribute, and move all relationship attributes into the ‘one’ entity, i.e. the entity with the key constraint.
2. Add the primary key of the ‘many’ entity as a foreign key into the ‘one’ entity

The primary key of the ‘many’ entity becomes the foreign key of the ‘one’ entity

Customer & Accounts

Take the business rule: “An account belongs to one customer”. Create a logical schema from it. Let’s define:

$$\text{Customer(CustomerID, Name)}$$

$$\text{Account(AccountID, DateCreated, Amount)}$$

We know that for each account, it can only relate to one customer, so the key constraint is on the account entity. Therefore, we store the primary key of the ‘many’ entity (which is Customer) and use it as a foreign key:

$$\text{Account(AccountID,emphCustomerID, DateCreated, Amount)}$$

Trying to store an account with multiple customers will be an illegal instance, since the primary key $\text{AccountID}$ will be repeated. However, we can have the same customer having multiple accounts, since the foreign key $\text{CustomerID}$ can be repeated as long as the primary key is not

For Many-to-Many Binary Relationships §

1. Create a relationship entity to store the relationship attributes.
2. Take primary keys from both entities in the relationship and store them in the relationship entity as foreign keys

The primary keys of the participating entities become foreign keys of the relationship entity

For One-to-One Binary Relationships §

1. Put the primary key of one side as the foreign key of the other side.
2. The relationship attributes go to that same entity

The choice of entity to have the foreign key and relationship attributes should factor in:

• Which entity would have the least NULL values?
• The optional entity gets the foreign key

For Unary Relationships §

For both unary one-to-one and one-to-many relationships, simply add a foreign key.

For a unary many-to-many relationship, do a similar process as for many-to-many binary relationships and create an associate entity, and store two foreign keys.