Hi Team, I have already completed most of the 2nd part of the project, however I have a major issue with it. 1. Since we are basing our SQL on the ERD, would it not work since the model we have been given is not in 3rd Normal Form which creates lots of redundancies in the code. I understand that we technically didn't learn normalisation yet during this project, however the actual SQL itself would require it so do i just assume it's existence for my SQL. 2. In the HiredVehicle Entity, it is linked to various other entities obviously requiring the Foreign keys from those that require it. However problem arises here, HiredVehicle has a Vehicle FK that references a Vehicle. Vehicle has a DepotID FK that references Depot. But HiredVehicle also has DepotID as an FK referencing Depot. This creates a redundancy issue whilst also having the potential to create major errors in the design. Example: HiredVehicle has DepotID FK 1 and Vehicle FK 1. However Vehicle PK 1 references DepotID FK 2 instead of 1. Now we have related data that inconsistently references each other wrongly because DepotID is referenced twice both indirectly and directly. Realistically Depot should only be connected to Vehicle and we can find the related DepotID by searching into the VehicleID from HiredVehicle, which then allows us to find the DepotID from Vehicle to finally reference Depot. 3. In the text it clearly states each vehicle has a unique fleet membership number meaning that is the PK, so i do not understand why regnum is the PK. While yes I believe regnum should be the PK as it is also unique, the text is clearly stating fleetnum as a PK not regnum. If I am not understanding this please advise me what I am understanding wrong. But I have done ERDs and made databases for 3/4 years now and I am slightly confused with this database design. Thank you all,

Hi, I'm a student in the unit. It seems that there should be two foreign keys in HiredVehicle both referencing depotID - one for the depot the vehicle was picked up from and one for the depot it will be returned to. There would be no point to recording both pick-up and return depots if vehicles were always returned to the same depot. Hence the depotID foreign key in Vehicle would change over time to reflect its current depot - but this means we lose the information about which depot a vehicle was hired from at a particular time. So I don't think we can just use a join between HiredVehicle and Vehicle although having the HiredVehicle - Depot connection does leave open the possibility for human error to write the incorrect pick-up depot in HiredVehicle (one that does not correspond to the Vehicle's current depotID FK value). However, I do think that DailyTariff should not be connected to HiredVehicle as the TariffIDs for a vehicle should be derivable (though with a lot of work) from the Vehicle - HiredVehicle connection. Not only is there a possibility that someone will write an incorrect tariff type for a vehicle in HiredVehicle, but multiple tariff types are associated with one vehicle if we follow the connections between the tables so we would not be able to store all of these types in one FK column in HiredVehicle even if we wanted to, unless we change the relationship to many to many between DailyTariff and HiredVehicle (which is not the case currently).

"Mia Scherini" <24*1*9*5@s*u*e*t*u*a*e*u*a*> wrote: Thanks, I understand you're explanation and while I agree with the way you have explained it I still find it odd that the depotid is done the way it's done. Because there was no assumption made that the vehicles might be picked up and dropped off to a different location. And the fact depotid is linked to a vehicle makes us assume that the vehicle always belongs there and will be picked up and dropped off in that same location. I will implement what you have explained because it seems most logical to the model we have been given, despite disagreeing with the model itself.

Hi, If the vehicles were picked and dropped at the same location, there was no need for two relationships. In reality, one can pick and drop the vehicles to separate depots. Also, this sample ERD is one way of solving the project. Students can offer their own solutions and no doubt they could be better. Cheers