Answered step by step
Verified Expert Solution
Question
1 Approved Answer
The Magnificent Hydroinformatics Consultants Co. plans to implement an information system for a Flood Management Authority allowing for storage, processing and retrieval of information about
The Magnificent Hydroinformatics Consultants Co. plans to implement an information system for a Flood Management Authority allowing for storage, processing and retrieval of information about STAFF, PROJECTS, REPORTs and HORSEs. (Horses are used for transportation of staff e.g. when a report or food has to be brought to another staff member.) . . It is known that: one staff member can be involved in many projects and one project may have many staff; one staff member may be an author of many reports and one report may have many authors. one staff member can be registered to use many horses, but a horse can be registered for use by only one person (this person is responsible for feeding a horse timely and to make an entry in the database about the last time he/she fed the horse). . ("many" here means any number, from zero to infinity) Assignment: pt a. Please draw an Entity -- Relational (ER) Diagram linking these entities and indicating one-to- many and many-to-many relationships. 5 pt b. If this ER diagram has many-to-many relationships please break them into one-to-many relationships by introducing a bridge entity, and draw the new ER diagram as well. Indicate modality (mandatory or optional) and cardinality of relations. The new ER diagram should have all the entities (depicted as rectangular shapes) that become tables in the final design. 5 pt c. Specify some realistic attributes and primary keys (sce question D). Do not use more than 3-4 attributes per entity. 5 pt d. Design the tables representing these cntities. (It is not necessary to fill the tables with data, but if you want you can do it.) pt e. Write two SQL statements that would generate the two tables with the following attributes: Table D.I: StaffNo StaffName ProjNo ProjBegin Date ProjEndDate PositionInProj Table D.2: HorseNo HorseName StaffName TimeHorse WasFed Last Time
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started