Formal Specification

1. What data they will have in their final application.

Tables:


create table animal
	(scientificName varchar(30) not null,
	 commonName varchar(30),
	 dietaryType varchar(30),
	 popSize numeric(9,0) check (popSize > 0),
	 primary key (scientificName)
	);

create table plant
	(scientificName varchar(30) not null,
	 commonName varchar(30),
	 isEdible varchar(3),
	 popSize numeric(9,0) check (popSize > 0),
	 primary key (scientificName)
	);

create table logs
	(animal.scientificName varchar(30),
	 plant.scientificName varchar(30),
	 employeeID varchar(6),
	 primary key (employeeID, animal.scientificName, plant.scientificName),
	 foreign key (animal.scientificName) references animal(scientificName)
		on delete set null,
	 foreign key (plant.scientificName) references plant(scientificName)
		on delete set null,
	 foreign key (employeeID) references parkWorker(employeeID)
		on delete set null
	);

create table parkWorker
	(employeeID varchar(6),
	 firstName varchar(15),
	 lastName varchar(15)
	 primary key (employeeID)
	);

create table projectManager
	(employeeID varchar(6),
	 firstName varchar(15),
	 lastName varchar(15)
	 primary key (employeeID)
	);

create table project
	(projectID varchar(6),
	 employeeID varchar(6),
	 name varchar(20),
	 cost numeric(9,0) check (cost > 0),
	 goals varchar(99),
	 primary key (projectID),
	 foreign key (employeeID) references projectManager(employeeID),
		on delete set null
	);

create table benefits
	(projectID varchar(6),
	 IDnumber varchar(6),
	 primary key (projectID, IDnumber),
	 foreign key (projectID) references project(projectID)
		on delete set null,
	 foreign key (IDnumber) references fundraiser(IDnumber)
		on delete set null
	);

create table fundraiser
	(IDnumber varchar(6),
	 name varchar(20),
	 w.employeeID varchar(6),
	 m.employeeID varchar(6),
	 primary key (IDnumber),
	 foreign key (w.employeeID) references parkWorker(employeeID)
		on delete set null,
	 foreign key (m.employeeID) references projectManager(employeeID)
		on delete set null
	);

create table donates
	(IDnumber varchar(6),
	 donorID varchar(6),
	 donationAmount numeric(11,2) check (donationAmount > 0),
	 date date check (date > 70-01-01 and date < 29-12-31), 
	 primary key (IDnumber, donorID),
	 foreign key (IDnumber) references fundraiser(IDnumber)
		on delete set null,
	 foreign key (donorID) references donor(donorID)
		on delete set null,
	);

create table donor
	(donorID varchar(6),
	 lifetimeContribution numeric(14,2),
	 firstName varchar(20),
	 lastName varchar(20),
	 primary key (donorID)
	);

create table guest
	(guestID varchar(9),
	 firstName varchar(20),
	 lastName varchar(20),
	 satisfactionRating numeric (2,1) check (satisfactionRating > 0.0 and satisfaction rating <5.0),
	 primary key (guestID)
	);

create table lookup
	(guestID varchar(9),
	 animal.scientificName varchar(30),
	 plant.scientificName varchar(30),
	 primary key (guestID, animal.scientificName, plant.scientificName),
	 foreign key (animal.scientificName) references animal(scientificName)
		on delete set null,
	 foreign key (plant.scientificName) references plant(scientificName)
		on delete set null,
	 foreign key (guestID) references guest(guestID)
		on delete set null
	);

2. What constraints exist and how they are modeled.

see tables above for not null, check, and primary/foreign key constraints.

3. What functionality the final application will have; each project should have at least one cool feature. (The tasks each user of the system (including the system administrators) will be able to perform with your application.)

Normal users

  • look up plants and animals
  • sign a virtual guest logbook/+1 the number of visits they've had
  • make a donation

    System Administrator

  • update attributes of animals and plants
  • Add a fundraiser
  • log plants/animals

    4. Updated division of labor

    We are roommates, we share everything including what work needs to be done.

    Written Database description

    This database models a nature conservancy and therefore contains information on the animals, plants, and employees of the park in addition to information on park projects, fundraisers, donors, and guests. Each entity listed above contains identifying information such as name, and id numbers. There are also multiple relationships contained within the database (and accompanying schema) that reflect the important interactions between entities (eg. Donors donating money to fundraisers).

    E-R Diagram

    E-R Diagram

    Relational Schema

    E-R Diagram

    Project proposal:

    Database of SanWild Wildlife Trust

    We will have several different types of entities with varying connections and interactions
  • Animal
  • Plant
  • Fundraisers
  • Projects
  • Guests
  • Employees/Administrators

    Our database's domain will be all alphanumeric strings as we will need to be able to list names; dates; id numbers; salaries; unique identifiers for humans, plants, animals; and geographic locations. Our database will contain all of our enitites, their attributes, and certain connections between them.

    Users will be able to look up animals and plants by name or description (though description might return multiple posible values), "admins" can also add any value to the database, guests can look up projects and fundraisers that are on going and those that have been closed, event organizers can modify existing events and fundraisers as needed and can also track guest numbers at the park.

    Nico will do coding and Ben will nag him whenever he misses a single character. Then nico will sush ben and then later ben will code while nico plays games. We live in the same room and spend a frightening amount of time together, so really we don't have a set breakdown of specialty. Ben likes to think big picture structure and Nico loves to make things pretty and enjoys the finer things in life.

    Additional Deadlines:

  • Feb 17th - Go over proposal with Tom and expand on any weak points / clear up areas of confusion
  • Feb 17th - Have researched the park and know what connections exist between entities as well as what major animals and plants exist
  • April 1st - have an essentially working draft of the thing due on april 14th so we can have time to address any particularly tricky errors
  • April 2nd - write 4/2 42 times

    Helpful Links

  • Link to their homepage
  • SanWild wikipedia page