Assignment: Database Design
The assessment items have been designed to allow you to acquire and use the concepts in the unit as a way to better understand your world with an emphasis on the quality of design.
The Task
Analyse the operations of SEAMM, the Case Study described overleaf.
1. Identify the entities and relationships that are relevant for a system to manage SEAMM
Develop a data dictionary (catalogue) and create an E-R diagram.
2. Using a DBMS (eg. Access), build a prototype database by creating the tables and populating them with at least 3 rows of dummy data.
Take screenshots and compile a description of your database.
3. Identify 3 data important retrieval requirements and show how your database would meet the requirements.
What to Submit
Submit a Word document containing:
A brief introduction to your assignment saying what's in it
An ER Diagram (screenshot from your DBMS relationship view)
Assumptions or business rules noted to explain your interpretation of SEAMM
Data dictionary (screenshots of design view of tables with description of each field)
Dummy data (data sheet view of tables)
Description of 3 queries - why they are needed by users
Query (perhaps in SQL) and the results of running the query,
that show how your database meets 3 queries
Case Study: South-East Asian Maritime Management (SEAMM)
The governments of South-East Asia and Australasia have agreed to set up an organization, SEAMM, to monitor the fishing industry in this region of the world. The concern is that over-fishing might lead to the eventual extinction of some species, great problems for the peoples who depend on seafood for their survival and ecological degradation. SEAMM is specifying an information system to support its management of the fishery - one that will collect, store, process and report on the data necessary to monitor the fish situation.
Each country in the region has been divided into marine areas. A register of all major species of seafood will be kept and is to be regularly updated with the population surveys of each species in each area (these surveys will be carried out by SEAMM scientists). The scientists work out a minimum population and total available catch figures for the next 12 months for each species in each area.
Each area has an area inspector. All commercial fishermen within an area must have a license to fish, which is granted by the inspector. Fishermen must apply to the inspector annually for a quota, which specifies the amount of each species of seafood that the fisherman can catch in 12 months period. A catch report must be made by each fishing expedition showing how much of each species was caught and where. These reports are checked against the market data.
The system produces trends in species populations and exception reports where the populations seems to be falling rapidly or is approaching the "minimum population" levels, or where the population surveys do not match the catch reports. Data mining to detect fish stock movement across the region, track long term trends and so on is important for the bureaucrats in SEAMM and the scientists.