3

As we can see in the entity-relationship diagram (ERD) below, the ATTENDANCE entity is related to the TRANSCRIPT entity. But how can entities in TRANSCRIPT be identified if there is no primary key in ATTENDANCE? Is the ERD wrong?

ER diagram

1

All tables, according to Codd's definition, must have a primary key. The key can be simple, consisting of a single column, or compound, consisting of multiple columns. It is perfectly acceptable for those columns to be foreign keys. When FKs are part of the PK it is sometimes called a weak entity. I guess because the entity type cannot stand on its own - it requires another in order to make sense.

For the given diagram a valid implementation would be

Table: College
Columns: Name
Foreign Keys: none
Primary key: Name

Table: Attendance
Columns: CollegeName, StartDate, EndDate
ForeignKeys: CollegeName references College(Name)
PrimaryKey: CollegeName, StartDate

Table: Transcript
Columns: CollegeName, StartDate, CourseName, Semester etc.
ForeignKeys: (CollegeName, StartDate) references Attendance(CollegeName, Startdate)
PrimaryKey: CollegeName, StartDate, CourseName

These primary keys consist of attributes that are placed in the entity types "naturally" through analysis and normalisation. Hence they are called "natural keys." As you can see natural keys can quickly become unwieldy. That is why they are often replaced by a unique integer. This integer is called a surrogate key, because it stands in for the natural key.

Inserting a surrogate key in the database design typically converts a weak entity to a strong one i.e. the surrogate replaces the whole of the natural key. This is certainly the easiest to code and is supported by the SQL IDENTITY feature. It need not be so, however. Say we added a surrogate to Attendance - let's call it AttendanceId. It would still be legitimate to model Transcripts as (AttendanceId, CourseName). It would still be a "weak" entity.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.