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?
Database Design – How Can a Weak Entity Be Related to Another Weak Entity?
database-designerd
Related Solutions
The key to understand this case is at the ninth page from Doc-A:
"product" and "customer" are entity types, and "shipped to" is a relationship... the verb "ship" is then converted to a gerund... in other words, the relationship... has been converted into the entity type "shipping"
Hence, the final third form will have "shipped to" converted to an entity by itself, "shipping".
This is necessary because "Shipped to" is a MANY-TO-MANY relationship (although to myself Chen's text is not clear on this).
But, your example does not relate - the relationship "Identified by" is an ONE-TO-MANY (a card belongs to ONE customer / a customer have ONE OR MORE cards issued).
Hence, the model would have the relationship between "Bank" and "Bank card" identified by TWO verbs.
Or, making the model stronger, to register several authentication events, one may turn "Authenticated by" into a entity by itself, "Authenticating" (to register event date, data shared, etc.).
Depending on how much you briefed your problem statement, it can be stated that:
Trainer is a strong entity (it has an ID)
Neither Client nor Membership Plan seem to be weak or strong entities.
They both could be considered strong entities provided that a primary key is defined on them*; e.g.
Client.(Name, Phone)andMembership Plan.("Beginning Date", Duration)
In your Chen diagram, Meets entity should have both ends total participation, but there's an arrow between Trainer and Meets?
Other than that, it seems to be alright.
* A strong entity is defined as having a key that does not depend on any other key; a weak entity as as having a key that depends on a foreign key. E.g. Order has a primary key order_id which is independent, and that makes it a strong entity, whilst Order_Detail has a primary key order_detail_id and unique key order_id, line_no, which makes it a weak entity since order_detail_id does not provide a meaningful independent ID, and the unique key depends on Order table. See weak entity entry in Wikipedia.
They're independent from other entities, so they are candidates for being considered strong entities provided that they have a primary key, which is not known if they have and is not clear if they could in the definition.
Best Answer
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.