Joel P. Schroeder
Course Description:
IS 344 - Database Management Systems
In-depth study of physical and logical design structure of relational databases; overview of SQL; hands-on experience with client/server database management system and application-development software.
In this course a team of students are to model and create a SQL database for the Brookfield Zoo located in Chicago, IL based upon the provided case study and other informational forms such as volunteer applications. This modeling will include the ERD for the database (both animals and people involved with the zoo) as well as the business rules that apply to this ERD. The database itself will be created, populated, and queried in a logical manner.
The following article illustrates the work completed by Group 6. The team consisted of Joel Schroeder, Troy Marx, Heidi Bartling, and Brandon Jorandby.
Brookfield Zoo Business Rules:
Animal ERD Business Rules:
· An inventory can be used for many feeding schedules/a feeding schedule is for exactly one food inventory
· An animal has one or many feeding schedules/a feeding schedule has exactly one animal
· An animal must live in at least one exhibit/an exhibit can hold many animals
· An animal must belong to one species/a species can have many animals
· A supplier can supply many inventories/ and inventory is of one supplier
· Each supplier supplies at least one species/a species can be supplied by many suppliers
· A supplier can supply more than one animal/an animal can be supplied by many supplier
· A species can live in many habitats/a habitat can be inhabited by one or more species
· A habitat is present in one or many exhibits/an exhibit must hold one or many habitats
· A maintenance task can be performed by many employees/an employee can handle many Maintenance tasks
· An employee can handle many exhibits/an exhibit can be handled by many employees
· An exhibit problem is problematic for one exhibit/an exhibit can have more than one problem
· An exhibit solution can fix many problems/a problem can have many exhibit solutions
· An animal problem is problematic for one animal/an animal can have more than one problem
· An animal solution can fix many problems/a problem can have many animal solutions
Person ERD Business Rules
· A person may have committed one or more crimes./ A crime may have been committed by one or more people.
· A person may have many emergency contacts./ An emergency contact may be held by many people.
· A person may earn one or more degrees./ A degree may be earned by many people.
· A person must work one or more jobs./ A job must be worked by one or more people.
· A person may possess one or more skills./ A skill may be held by many people.
· A person may have one or more degrees./ A degree is held by many people.
· A person may work for one or more employers./ An employer employs one or more people.
· A job requires one or more skills./ A skill may be required of many jobs.
· A person must have at least one contact number./ A contact number must be held by a person.
· A person must have at least one availability./ An availability must belong to one person.
· A person can donate one or more donations/ A donation can be made by one donator
· A person has one zipcode/a zipcode may have many people
· An employer has one zipcode/ a zipcode can have many employers (or businesses)
· A work schedule takes place at a location/ a location may have many work schedules
· A person may attend many programs./ A program may be attended by many people.
Animal ERD:
Course Description:
IS 344 - Database Management Systems
In-depth study of physical and logical design structure of relational databases; overview of SQL; hands-on experience with client/server database management system and application-development software.
In this course a team of students are to model and create a SQL database for the Brookfield Zoo located in Chicago, IL based upon the provided case study and other informational forms such as volunteer applications. This modeling will include the ERD for the database (both animals and people involved with the zoo) as well as the business rules that apply to this ERD. The database itself will be created, populated, and queried in a logical manner.
The following article illustrates the work completed by Group 6. The team consisted of Joel Schroeder, Troy Marx, Heidi Bartling, and Brandon Jorandby.
Brookfield Zoo Business Rules:
Animal ERD Business Rules:
· An inventory can be used for many feeding schedules/a feeding schedule is for exactly one food inventory
· An animal has one or many feeding schedules/a feeding schedule has exactly one animal
· An animal must live in at least one exhibit/an exhibit can hold many animals
· An animal must belong to one species/a species can have many animals
· A supplier can supply many inventories/ and inventory is of one supplier
· Each supplier supplies at least one species/a species can be supplied by many suppliers
· A supplier can supply more than one animal/an animal can be supplied by many supplier
· A species can live in many habitats/a habitat can be inhabited by one or more species
· A habitat is present in one or many exhibits/an exhibit must hold one or many habitats
· A maintenance task can be performed by many employees/an employee can handle many Maintenance tasks
· An employee can handle many exhibits/an exhibit can be handled by many employees
· An exhibit problem is problematic for one exhibit/an exhibit can have more than one problem
· An exhibit solution can fix many problems/a problem can have many exhibit solutions
· An animal problem is problematic for one animal/an animal can have more than one problem
· An animal solution can fix many problems/a problem can have many animal solutions
Person ERD Business Rules
· A person may have committed one or more crimes./ A crime may have been committed by one or more people.
· A person may have many emergency contacts./ An emergency contact may be held by many people.
· A person may earn one or more degrees./ A degree may be earned by many people.
· A person must work one or more jobs./ A job must be worked by one or more people.
· A person may possess one or more skills./ A skill may be held by many people.
· A person may have one or more degrees./ A degree is held by many people.
· A person may work for one or more employers./ An employer employs one or more people.
· A job requires one or more skills./ A skill may be required of many jobs.
· A person must have at least one contact number./ A contact number must be held by a person.
· A person must have at least one availability./ An availability must belong to one person.
· A person can donate one or more donations/ A donation can be made by one donator
· A person has one zipcode/a zipcode may have many people
· An employer has one zipcode/ a zipcode can have many employers (or businesses)
· A work schedule takes place at a location/ a location may have many work schedules
· A person may attend many programs./ A program may be attended by many people.
Animal ERD:
Person ERD:
The source code and queries for this database are much too large to fit on this page, but may be found in the attached file link.
| group_homework_creates_and_inserts.txt |
| joels_queries.txt |
