Question

Case: Civil Defence and Emergency Management
The Ministry of Civil Defence and Emergency Management requires a system to manage emergencies when arise. These emergencies can be caused by one or combination of disasters such as tsunami, earthquake, hurricane, flood, drought etc. To cope with these disasters the ministry maintains records of both the organisations and their people who are capable to aid in these broad areas of disaster by having specific skills and certain levels of competence in those skills. The relationship between people and organisation may change over time and it is important for the ministry to keep track of them.
The country has been divided into a number of regions to help with the disaster management but it must be realised that a disaster can span regions. Naturally regions can be further subdivided into smaller regions so that relief may be more clearly targeted. The ministry is required to keep inventories of all resources (e.g. water, food, helicopter) which could be deployed when needed. Resources could be owned by the ministry; they could also be owned by people or organisation outside the government but deployable by the ministry.
Once an operation is in progress then all events associated with the disaster must be recorded with details, such as when the volcano blows rated at certain danger level under some weather condition. The effects of any action in respond to an event must also be stored so that the entire episode of the disaster can be viewed on a timeline.
1. Draw a logical ERD in crow’s foot notation for the dataset described above.
Please note: all important design decisions and assumptions made must be clearly listed. All entity sets (with specialisation / generalisation as needed), all attributes including primary and foreign keys, and all relationships including cardinalities must be shown as appropriate. Following database design principles, the ERD should not contain redundant entity sets, relationships or attributes.

Answer 1

ERD structure given herewith could be able to solve the listed problem, Detailed explanation is as followed :-

List of Tables :-

1) ResourceInventory

2) Region

3) SubRegionDetail

4) AffectedRegionDetail

5) MOD_Status (Ministory of Defence - Current Status, This could be used as Fact Table)

6) POC_Detail

7) POC_Escalation