-2

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.

philipxy
  • 14,867
  • 6
  • 39
  • 83
Luke Alanson
  • 19
  • 3
  • Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. – philipxy Sep 02 '20 at 03:38
  • 1
    Right now you are just asking for us to rewrite a textbook with a bespoke tutorial & you have shown no research effort. Please see [ask], hits googling 'stackexchange homework' & the voting arrow mouseover texts. Show the steps of your work following a textbook with justification & ask 1 specific researched non-duplicate question re the first place you are stuck. Quote the definitions, theorems & algorithms you are relying on. All the steps are also SO faqs. PS Start with definitions for '3NF DB', '3NF table' & '3NF decomposition algorithm'. Then definitions for the terms used by them. – philipxy Sep 02 '20 at 03:54

1 Answers1

-1

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.

Marcus Vinicius Pompeu
  • 1,219
  • 1
  • 11
  • 24
  • Hi, yes HD_DUEDATE, HD_DAYSLATE, and HD_TOTAL_COST are computations and HD_RETURNDATE is nullable as well. Are you saying that it is already in 3NF or do I need to remove/move them elsewhere? – Luke Alanson Sep 02 '20 at 03:11
  • @LukeAlanson, computed columns are denormalizations by nature, violations to the 3NF. For example, HD_DAYSLATE depends on the date, the moment, of the query. To achieve 3NF these three columns must be removed. – Marcus Vinicius Pompeu Sep 02 '20 at 03:32
  • @LukeAlanson, you model is nearly perfect, 3NF compliant or not. I would compute HD_DUEDATE in the insertion. Create a new column HD_TOTAL_DAYSLATE, and compute it and HD_TOTAL_COST upon HD_RETURNDATE. Also make HD_DAYSLATE computed by the query(ies) clause(s) or defined it as a DBMS computed expression. – Marcus Vinicius Pompeu Sep 02 '20 at 03:49
  • @LukeAlanson, for exemple, for MSSQL, HD_DAYSLATE may be defined as `ALTER TABLE ... ADD HD_DAYSLATE AS CASE WHEN getdate() >= HD_DUEDATE + 1 THEN datediff(d, HD_DUEDATE, getdate()) ELSE 0 END` – Marcus Vinicius Pompeu Sep 02 '20 at 03:55
  • @LukeAlanson It's true that when each value in a column of a table is a function of other columns in the same row there is a FD in that table where the set of argument columns determines the column, but that alone doesn't tell you anything in particular, for 3NF you need to know all the FDs that hold in each table, and moreover a column being a function of all rows in a table is in general not a case of that. In particular it is not the case that the mere fact that a column is computed tells you anything in particular, the FDs are necessary & sufficient for determining 3NF. – philipxy Sep 03 '20 at 18:56