Learn how to transform E-R data models into relational designs
Practice the normalization process from Chapter 2
Understand the need for denormalization
- Specify primary key (consider surrogate keys as appropriate)
- Specify properties for each attribute of the entity:
Data type
Null status
Default value (if any)
Data constraints. Common constraints are: primary key, Not NULL, foreign key, unique (all records in a relation must have a unique value for this attribute). Other constraints are possible, fieldA != fieldB. Not all constraints can be implemented by the database management system (DBMS), but it is useful to note them, because support for them can be built outside the DBMS.
- Decide if any denormalization is required see Zip Code discussion on p. 258.
See techniques below.
Even if an attribute should never have a null value (much of the time). Many times, must allow null values in the table schema, because you do not know the value at the time the row is created.
· One table holds a foreign key to the other table. You may choose which table holds the foreign key, but only one of the tables holds a foreign key.
· The column in the table holding the foreign key must be constrained to be unique, which keeps any two rows from having the same value for that column. (See p. 264 in the text.)
Put foreign key in table on the many side. These relationships are often called parent : child relationships.
Break the relationship into two 1:many relationships.
Create an intersection table (a weak ID dependent entity).
Put two foreign keys in the intersection table: one FK to one side of the relationship, one FK to the other side.
Put foreign key in tables representing Weak entities
Use on delete cascade