Assignments for Week 6

Greetings, team. I am going to start looking over the assignment tomorrow and am hoping that we can break up the assignment and figure out what each of us is responsible for. I have to go to NYC for business at 5 am Monday morning and will not be back until late Thursday. I will have limited time to work on things outside of work--so I wanted to make sure that I have my bases covered until I get back.

I'll post again tomorrow with my ideas. Thanks!

Peggy

Team,
To distribute the work, I suggest that we all brainstorm and post a list of what nouns or entities we think our project will need. We can then tally up how many times each noun was given and use the top 5 (or less, if we can) as our noun/entities. Obviously, we can re-work, change, etc. to get the best set if nouns for this, instead of just going off numbers, as the do have to work together to produce all the necessary information for our project.

Once we have the 5 (or less) entities each of us can take one noun/entity and create the 5 (or less) attributes that describes our noun/entity?

Then we will have to work collaboratively to connect the entities together by adding in keys and what attributes are shared to draw our ERD.

Sound fair enough? I'm also open to other suggestion, just throwing the idea out there.

I have family coming into town this weekend to help out, so I will be on and off over the weekend.

What does everyone think?,
Kami

I am good with coming with our own set of nouns/entities. When do we want to get our ideas turned in?

I have a few suggestions for entities (Customer, Food, Sales, Health_Charts, Recipes)

Thanks
Deana

Team,
Here are my ideas for the nouns or entities for our tables (I also posted these in the instructions discussion as well):
Foods
Activities
Users
User_Counter

In the schedule, I have by 10pm Wed, everyone should post at least once to the assignment. So, hopefully everyone still agrees and can post their nouns by Tomorrow evening then we can finalize the nouns and give each person a noun Thurs. and then re-group on Friday or Saturday with our break down of each noun. Then Saturday/Sunday we can construct the final ERD??

Any volunteers to turn in the final for this week?

Kami

Deana,
For sales what were you thinking to go in this table? I'm assuming that your health_charts table is for exercises and the burnt calories?

Team,
Since the professor wants us to keep the database simple and not get too out of control by limiting us to 5 max tables with 5 attributes max, I think that ads, recipes, and any extra stuff beyond the calories and exercises should be left out. What do you think? What does the rest of the team think?

I think even doing the foods, exercises, user registration, and user intake/output could be a tremendous under taking if we had to fully build it, but I'm hoping and assuming we will only have to do a few entries into each.

So these are the tables we need to have for sure (according to outline of our project):
Users (name, personal info)
Foods (calories, servings, etc.)
Exercises (expended calories, time, etc.)

Then we need a place where the user stores their intake and output (exercises) do you think these could both go into one table or should it be one table for input and one table for output? If we use 2 then we have our 5, if only one, what other table do you all think is most important?


Kami

I definitely think we should definitely have a Users (Username, date_registered, food_entries, exercises_entered), Foods (food_name, food_category), Nutrients (food_name, food_type, calories, carbohydrate, protein, fat). Then other ideas would be exercises (exercise, muscles_trained, calories_expended), recipes (recipe_name, ingredients, servings, healthiness_rating). If we could a "rating system" to the recipes section that would be cool. The users could rate their own recipes based on how healthy they think they are. It's on the honor system, but there could be a complaint center if a full website were to be designed.

Greetings, group. So, it looks like these are the entities that everyone seems to agree on:

Users
Foods (it is my opinion that this table should include both the category and all of the nutrient information--for simplicity)
Exercises
Recipes

Do we want to add a 5th or stick with those 4? Is there something that I am leaving out?

The Users table would include registration and demographic information (username, password, real name, height, weight, gender, etc.).
The Foods table would include the food group, calories, protein, fiber, carbohydrates, fat, serving size, etc.
The Exercises table would include the activity, calories burned, time spent on activity, intensity, etc.
The Recipes table would include a catalog of recipes with the nutrient breakdown for each recipe.

Does it sound like I am getting this correct? How do we want to divide this within the group?

Thanks (and I apologize for my tardiness in posting this week).

Peggy

Sounds good, I'd say the only table I see missing is the table that will store and calculate the daily exercises and food each user takes. The rest of the categories sound great to me.

The recipes table seems a distant and not well connected as the rest for me but may that how I was taught about databases. I think it's a great addition, especially later on but to keep this first round simple.

Kami

I totally agree about the recipes table. So, why don't we eliminate that one for the time being and then add it later when we need to. We will add in the calculation tables for food intake and exertion. What do we want to call those?

Intake_calc
Exercise_calc

Thoughts?

Peggy

I like those! I'm good with that. So I think that gives us 5 tables:
Food
Exercises
User
Intake_calc
Exercise_calc


Do these cover the basics and all sound good? If so..... Then can we choose one and each member comes up with the attributes for that table (noun)? Then we combine in the final ER model.


Kami

Team,
I will take the foods noun and come up what I think are the necessary attributes and then post. Everyone else want to pick their noun and do the same? Then we can review, make any suggestions if necessary then create the ER diagram?

So the nouns left are (1 for each of us):
Exercises
User
Intake_calc
Exercise_calc

Kami

Good Morning Everyone,

It is my contention that 5 tables ought to suffice as well (at least for the time being!). As noted in our original assignment, the use of unique IDs will allow us to cleanly and consistently correlate data from one table to another. As an example, our Intake_Calc table may contain columns such as:

uID | dateOccured | dateModified | calTotal

I realize that we tend to use different naming conventions. I prefer camel case. The use of an underscore denotes (in my experience) a parent/child relationship which is inappropriate in this context. As we divide our assignment this weekend, I think that we need to decide on a naming convention so that everything is consistent. Thoughts?

Tracking these sorts of data will enable us to achieve our stated goals. While it may be premature… I thought that I would offer to actually create our project. For our final, I thought it would be nice to have screen shots (at the very least) and the potential for interaction. I know that the professor assured us that we didn’t have to program for the class but I thought that it could really add some substance to our final. Would any of you object to that?

Kind Regards,
Abi

I'm good with that and I also use carmel case, so that is no issue with me. I have no issue with creating it and doing extra, if time permits, I could help as well with that so it's not all on your shoulders.

Kami

I saw another group ask about if 5 attributes was the max per noun/entity and he said he would like it to be but if we had to have a couple extra that's fine as long as it doesn't get too big or out of control. Just thought I'd throw that reminder out there.

Kami

Team,
Here is my list of attributes for the foods table:

foodID integer (10) Primary Key
foodName varchar (255)
foodCategoryID integer (10) Foreign Key
servingSize varchar (255)
caloriesPerServing integer (10)


This would require another table: foodCategories
Which would contain:
foodCategoryID integer (10) Primary Key
foodCategory varchar (255)

This extra table would only be so that meat wouldn't have to be typed into each meat item, or 1,000+ times and would be used to reduce human errors and make the database easier to search/operate and enter data.

Do we want any other nutrient information? I left it to calories only as we were to have 5 or less attributes and I thought a foodCategory was important so users could more easily find what they were looking for by narrowing down the foods by: vegetable, fruit, meat, etc.

Open to suggestions, just let me know! Its hard to limit it to 5 but I can add any other nutrient items you think are necessary.

Kami

The extra table is a great idea. Fantastic work.

intakeCalc
iID NUMBER Primary Key
uID NUMBER Foreign Key
fID NUMBER Foreign Key
itemUnits NUMBER
itemUnitCal NUMBER
calTotal NUMBER GENERATED ALWAYS AS (itemUnits*itemUnitCal) VIRTUAL
dateEntered DATE
dateModified DATE

Abi,
Looks good, I have no suggestions.

My only questions is the itemUnitCal that would be the same as itemCalPerUnit or (caloriesPerServing in my chart {foodItems} ) correct? If so, could we make that a foreign key or have it pull that information directly from the foodItems table, so it doesn't have to be manually entered?

We will have to make sure that we either go with fID or foodID so they are same in both tables so they can be used as foreign keys. Then if the above is caloriesPerServering the same with that name as well. Also, how do we want to address the units, should we put the units in its own column since we will be using the caloriesPerServing (itemCalPerUnit) to multiply it can't be a varchar field or it wouldn't know how to calculate 120g x 4. So should I change it to a intger and then add a unitID with a unit table similar to the category table?

Kami

If we store (duplicate) those fields for our system items, it does seem redundant. However, it would provide us the flexibility of allowing users to enter items that were outside of our system and store the results of those items in their own personal record (rather than flooding our system with potentially erroneous data).

Since we are using the calories in another table to multiply against number of serving ate. I separated the units out into their own table so again we don't have to retype cup, g, pkg, etc over and over and also so that the servingSize can be used to do the virtual calculation in the input chart.

foodItems:
foodID - integer (10) Primary Key
foodName - varchar (255)
foodCategoryID - integer (10) Foreign Key
servingSize - integer (10)
servingUnitID - integer (10) Foreign Key
caloriesPerServing - integer (10)


foodCategories:
foodCategoryID integer (10) Primary Key
foodCategory varchar (255)

servingUnits:
servingUnitID - integer (10) Primary Key
servingUnit - varchar (255)

Ok, so the user when entering a food will have to manually enter the calories themselves?

Example, they would pull up apple, a medium apple is 200 calories and they ate two.
So they would put into the input table the medium apple id (by selecting it from the list) then they would have to remember the number of calories and type them in so they would type 2 in the itemUnits and then they would type 200 in the itemUnitCal, then it would auto output 400 into calTotal for them?

This is a hard one because if something is not in the system they should be able to enter it but if it is in the system I think it should auto pull that information from the foodItems table based on the foodID. I'm not sure how we could set it up to do that or if it is even possible. Could we set it up so that if the foodID is NULL then they could enter itemCaloriesPerUnit but if the foodID has a number then it auto pulls? We wouldn't want them to type in the wrong amount of calories because they remember it wrong after selecting it from the list.

I hope this makes sense...

Thoughts, or am I not understanding? lol my brain is a little fried at the moment! lol
Kami

Users will never HAVE to enter anything manually (other than picking what they ate and how much of the thing they ate). Data will be obscured from the user. The values populating the intake (and calories spent) table will be populated when users take action. To re-iterate, a manual entry is (ought to be) an OPTION for users who cannot find the food they want.

Ok great, so when they select a food it will auto populate the caloriesPerServing and multiply it by itemUnits, which they will have to enter, to get the totalCal.

Ok so then I fixed my table above so that the caloriesPerServing is integer only so it can be calculated and added a units column so that the serving size units will be shown on each food item.

Sounds great to me

I guess I'm left with exerciseCalc table... Just an idea: what's the point in having a forum if everyone just emails?? So confusing to be gone for 10 hours and have 21 emails and like 15 posts to read at the same time. Don't get me wrong, I appreciate the activity, but it's hectic trying to catch up

Also, the Exercises table seems to use almost everything I would put in the exerciseCalc table... Time, calories burned, exercise ID... I feel like the Exercises table should be more like muscleTrained, exerciseType (strength/cardio)...

Anyways

Table: exerciseCalc

1. exerciseNumber foreign Key (10) (from exercises table)
2. time time(7)
3. numberSets integer (10)
4. numberReps integer (10)
5. exerciseFrequency integer (10)

Not sure what else would go in here. It's really dependent on the exercises table, and also redundant.. so not sure.

This is what I have so far for the Users table:

userID - varchar (55) Primary Key
passWord - varchar (25)
First_Name - varchar (25)
Last_Name - varchar (25)
GenderID - varchar (1)
HeightID - integer (4)
WeightID - integer (3)

Since the users table will be the primary table in the database (their entry point)--I am not sure what the foreign keys would be. Maybe I'm not understanding everything correctly. Ideas?

Peggy

I also agree with Keilor--I think we should stick to one forum. Either use email or use the message board. I was having a hard time following the thread.