1. Limited time only! Sign up for a free 30min personal tutor trial with Chegg Tutors
    Dismiss Notice
Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Homework Help: Data Modelling Homework please help!

  1. Sep 3, 2009 #1
    1. The problem statement, all variables and given/known data

    Prepare the Relational Tables to represent the following situation, which is
    described by means of text and an Entity Relationship Diagram.
    Note: Do not attempt to modify the ERD. Your task is to state the relational tables
    which fit the given ERD.

    The NSW government has agreed to set up an organisation to monitor the fishing
    industry in this state. The concern is that over-fishing might lead to the eventual
    extinction of some species and great problems for those people who depend on
    seafood for their survival. The organisation wants a computer based information
    system to store the data necessary to monitor the situation.
    All commercial fisherpersons within the state must have a licence to fish which is
    granted each year by the organization. Records of all licences issued over the last
    5 year's must be maintained by the system.

    A register of all major species, identified by the government species code, is to be
    kept and is to be regularly updated with the population surveys of each species
    which will be carried out by the organisation's scientists. The scientists will work
    out a "minimum population" figure and a "total catch" figure, for each calendar
    year, for each species.

    Each licenced commercial fisherperson must apply to the organisation annually
    for quotas which specifies the amount of each species of seafood that the
    fisherperson can catch in a particular year. A catch report must be made by each
    commercial fisherperson listing the tonnage of each species caught on each
    fishing expedition. The fisherperson also provides a brief weather description for
    each expedition and the boat name. The organisation has inspectors who do
    random audits to ensure that the catch reports are accurate.

    These reports are to be stored so that the quotas can be checked to ensure that
    fisherpersons are not over-fishing.
    The system will produce regular reports of trends in species populations and
    exception reports where the population seems to be falling rapidly, or it is
    approaching the "minimum population" levels, or where the population surveys do
    not match the catch reports.
    One type of report is shown below for Mr John Snapper. (Note that there are
    several other types of reports.)

    2. Relevant equations

    3. The attempt at a solution


    Organization (OrgId, supervisor_id) (1:1)

    Supervisors( Supervisor_id, name, experience) (1:1) [[FK references Organization]]

    Licensee_Details (Licensee_Key, Licensee_Id, Name, Licensee_Addr_Key,License_Issue_Date, License_Expiry_Date)(1:1)

    Licensee_Address (Licensee_adress_key, licensee_key, addr1, addr2, state) (1:1)

    Licenses_Issues_Record (License_Issued_Key, Licensee_key, License_Issue_Date) [[REPORT]]

    Species_Records (Species_Key, Species_Id, Min_Population, Total_Catch, Year) (1:1)

    Govt_Quote_List (Govt_Quote_List_key, Licensee_Key, year, species_key,quantity) (1:1)

    fisherman_report_master (fisherman_report_key, licensee_key, expedition_date, weather, boat_name, year) (1:n)

    fisherman_report_child(fisherman_report_child_key,fisherman_report_master_key,species_key, tonnage) (n:1)
    1. The problem statement, all variables and given/known data

    2. Relevant equations

    3. The attempt at a solution

    Attached Files:

  2. jcsd
Share this great discussion with others via Reddit, Google+, Twitter, or Facebook

Can you offer guidance or do you also need help?
Draft saved Draft deleted