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.) mr john snapper lic# 7373 18 mermaid st geelong (03)224556 species no. Species quota quota limit ytd_catch balance 234 tuna 548351842 15,000 11,125 3,875 236 ling 548354821 10,000 5,563 4,437 452 trout 548351452 20,000 12,458 7,542 last expedition 25/11/98

Do not expect others to solve your problem.

Kindly post what you have done so far.

The way the question has been posted , i don't think anyone will go through that fully.

This is very simple problem, i think you should solve yourself. You should know the
System Design Life Cycle ( SDLC) .

Mitesh, Here's how I solve this problem. Identify all the "entities" or table owners from this paragraph. The government organization is one because it is initiating the process. Then if you read fully, there are supervisors that belong to this organization. Maybe a separate table for them to follow RDBMS rules.
Then the fisherman details. There are things that are evident. One is to store fisherman's personal details(address/name) and the other is his license details. Many ways to skin the cat here. One way is to have a licensee_details table with all license information and then a table with this licensee's address. The lincess_address table will use the key from details table. For the bit about having to maintian record of licenses issued, we can model it as a view or table with trigger to store 5 years' data of licenses issued.
Now all the fishes come in the species_records table.
Moving along, we need to come up with quota for every fisherman for possible count of species. This calls for many-to-many tables. We'll have a govt_quota_list table with list of species that can be fished(primary key of species table), the quantity permitted for a year for the licensee(licensee_key). There are other ways to do this.
Finally, we need a fisherman_report parent and child table. The parent table will have details on the expedition-date, weather, boat_name and year. The child table will have FK reference to the parent table and for each record in parent it will have list of species and tonnage details.
Reports can be arrived by joining the above tables or making some views out of it.



ERD

============
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)




Hope this helps.

There are two cans, one of capacity 3 litres and another of capacity 5 litres. Using the two cans of 3 litre & 5 litre capacity , how will you pour exactly 4 litres from a bucket filled with water into another empty bucket?