State Birds and Flags Database

Synopsis

For this project, I implemented a database that compiles information about two things that interest me: state birds and state flags. This project contains a SQLite Database and is connected to a Java interface using JDBC. I set up an interactive map of the United States where you can select a state to view various information and facts about the state, images of their state flag and birds, along with fun facts about each and the population of any other state birds that happen to live in that state.

Users can also access the ‘more’ page where they can find some interesting facts about the bird populations such as: the total population across all species of birds in each state, which state each bird has its largest population in, and what birds live in more than 5 states.

Database

ER Diagram

Tables

State: The state table stores basic information about the state such as the year it was institutionalized, the population (of humans) in the state, and the total square mileage size of the state. Each state has a unique name.

Bird: The bird table stores basic information on each of the state birds such as the name of the bird, the total population of this bird in the world, its average weight, and a picture of the bird. Each bird will be assigned a unique birdID.

Flag: The flag table contains a picture of the flag, the year the flag was adopted, the unique name of the state the flag is for (which is a foreign key), and a unique flagID assigned to the flag.

Fun Fact: The Fun Fact table only contains unique factID and the text containing the fun fact.

Lives: The lives table is what stores information for each bird in each state. If a bird lives in more than one state, then it will have many entries in the lives table for each state it lives in. Some states have the same state bird, so there may be many 1’s in the isStateBird attribute for the same bird across different tuples.

Bird Facts: This table relates the Fun Facts pertaining to birds to the correct birdID it applies to.

Flag Facts: This table relates the Fun Facts pertaining to flags to the correct flagID it applies to.

Application Design

The front end was built using Eclipse WindowBuilder and was linked it to my SQLite database using JDBC. Everything was handled using JDBC to access and query my database, basic Java functions and classes to organize and make my data readable, and Eclipse WindowBuilder's tools to interact with the user and display the data using simple text and tables.