I have created this ER Diagram for a virtual car rental business. Each CAR can have many copies (CARCOPY) that can be hired many times over its lifetime. A CUSTOMER can hire more than one CARCOPY at once.
I've created an entity named HIREDETAIL which acts as a bridging entity to resolve many-to-many relationship. I want the overall scenario to be in the third normal form (3NF).
The problem that I see is in the HIREDETAIL entity. There's a column named HD_DAYS_RENT (number of days a car is to be rented). There's another column (HD_DUEDATE) which depends on HD_DATS_RENT as well as the HIRE_DATE which is inside of the HIRE table. It has nothing to do with the CARCOPY table. Is this considered as a partial dependency or transitive dependency? It is dependent on one prime and one non-prime attribute.
Also, similar thing is observed for HD_TOTAL_COST (cost calculated on the basis of daily rent of the CAR_DAILY_RENT and HD_DAYS_RENT). It depends on the CARCOPY_NUM (prime attribute) table, but also depends on the HD_DATS_RENT (non-prime attribute).
Another transitive dependency lies in the HD_DAYSLATE which is basically just the difference between the HD_DUEDATE and HD_RETURNDATE.
How do I resolve all these dependencies? I've only seen very simple partial and transitive dependencies, but I just cannot wrap my head around this. What should I change so that it will be in atleast 3NF.
In my understanding, HD_DUEDATE, HD_DAYSLATE and HD_TOTAL_COST are computations, hence, denormalizations by nature.
If you remove there three columns, your model became 3FN compliant.
I'm assumingHD_RETURNDATE is nullable, set upon car return.
