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?
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
Foreign Keys: none
Primary key: Name
Columns: CollegeName, StartDate, EndDate
ForeignKeys: CollegeName references College(Name)
PrimaryKey: CollegeName, StartDate
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.