First of all, I have read several similar questions with "technical" answers that look like C & P. What I need is a clear example. The normalization is 3NF.
In this project, in the administrative panel, you have to create cities and zones and each zone has to belong to a city. Also create hotels and assign them in the corresponding zone, and finally create aliases for each particular hotel, as people know the same hotel under different names. The tables hotels and hotels_alias are to fill an autocomplet input.
The price calculation is done according to the service (standard, private and VIP) depending on the zone and according to the number of passengers and the season, I still do not create the logic or tables to calculate the price per passenger and season. That is why they are not in the diagram below.
A good explanation I found is What's the difference between identifying and non-identifying relationships?
However I have some doubts.
hotels_alias can not exist without the table hotels that in turn can not exist without the zones table and this in turn does not exist without cities. Since a city is divided into many zones, hotels belong to these zones, zones that are part of a city, and hotel aliases belong to a hotel and can not exist if there is no hotel.
So far it is clear that cities are a strong or parent entity and zones, hotels and hotels_alias are child entities.
In the EER diagram you can see that it has an identifying relationship. The first question is: Is it correct that despite being child entities have their own ID? and that this ID is PK and NN and AI? In some examples, these child entities do not have their own ID, hence their PK is formed by two FKs from the related tables as in an N: N (zones_has_servicees) relationship.
If in fact child tables do not have to have their own ID because they must be able to identify themselves by their parent table, then how would you be able to update or delete an area, or a hotel or a hotel alias?
DELETE FROM zones WHERE name = 'name'
Is this correct? Should I create an index to the name column? What advantages, if any, would do with name colum instead of its own ID? Is it okay for a child table to have its own ID and create a composite PK with this ID and the ID of its parent table? Does this type of relationship have any function or is it only for engines like InnoDB ? to perform an ON DELETE CASCADE action?
What happens if I have two zones with the same name? for example: Hotel Zone, that both cities of Cancun and Tulum have that area. To make a DELETE would be ?:
DELETE FROM zones WHERE name = 'name' AND cities_id = ID
Understanding what a parent and a child entity is then why WordPress creates relationships like the one below where you can see that it uses a weak relationship with wp_postmeta and wp_posts. It is assumed that a wp_postmeta can not exist without a wp_posts, right? It does the same with comments and users.
First, your example 1 is not an EER diagram (rather call it a table diagram). To be called an ER or EER diagram, you have to use a notation (like Chen's notation) that represents entity-relationship model concepts and distinguishes entity sets from relationships. In the ER model, both entity relations and relationship relations are implemented using tables, and neither map to FK constraints, which are just an integrity mechanism. Many people confuse the ER model for the old network data model.
Second, identifying relationships are used in conjunction with weak entity sets, in which the regular (parent) entity set's primary key forms part of the weak (child) entity set's primary key. When an entity set is identified by its own attributes, it's a regular entity set.
To delete a row from a weak entity relation, you would usually identify it by its primary key. Weak entity sets generally have a composite primary key, consisting of its parent's key and an additional weak key. The weak key need only be unique in conjunction with the parent key. For example, if zones were identified by cities_id and name, you could delete a zone by specifying those attributes:
DELETE FROM zones WHERE cities_id = 1 AND name = 'name';
The composite primary key should automatically be indexed and uniquely constrained by your DBMS if you declared it as the PK. The advantage of weak entity sets is that, in some cases, this method of identification is more natural than introducing a meaningless surrogate key.
It's not a good idea to have a table with a composite primary key consisting of a unique surrogate ID together with another attribute like its parent's ID. Besides the risk of unintended duplicate values if uniqueness isn't correctly enforced, it unnecessarily over-complicates what would otherwise be a straightforward table with a simple surrogate PK.
Your WordPress diagram doesn't illustrate weak entity sets or identifying relationships (and it's not an EER diagram, as mentioned before). The tables you mentioned each have their own surrogate keys. Note that there's no such thing as a weak relationship in the ER model.