Anyone familiar with SQL? Lookup/Intersection Tables

[BurningHeart]

I'm doing a project for my database class, we are working with MYSQL and I need a little help.

I typed up my database but the instructor told me I needed to add two lookup tables and an intersection table to my database.

My database deals with some exercises from this site, the bodyparts they go with, the equipment you use for the exercise, and the mechanics... if it's an isolation or compound movement.

I need to make the equipment and mechanics table a lookup table. How do I do this? I searched the net but it seems more complicated than what we're taught in class.

Take a look at it: (I know some data is left out, I'm not trying to make this database perfectly accurate)

This is what I have so far, can anyone tell me how to create and use a lookup table for EquipmentUsed and Mechanics please?

--------------------------------

CREATE TABLE Exercises
(
ExercisesID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

BodypartWorked VARCHAR(50),
Exercise VARCHAR(50),
EquipmentUsed VARCHAR(50),
Mechanics VARCHAR(50)
);

INSERT INTO Exercises (BodypartWorked, Exercise, EquipmentUsed, Mechanics)
VALUES ('Shoulders', 'Barbell Shoulder Press', 'Barbell', 'Compound');


INSERT INTO Exercises (BodypartWorked, Exercise, EquipmentUsed, Mechanics)
VALUES ('Shoulders', 'Dumbbell Shoulder Press', 'Dumbbell', 'Isolation');


INSERT INTO Exercises (BodypartWorked, Exercise, EquipmentUsed, Mechanics)
VALUES ('Chest', 'Barbell Bench Press', 'Barbell', 'Compound');


INSERT INTO Exercises (BodypartWorked, Exercise, EquipmentUsed, Mechanics)
VALUES ('Chest', 'Butterfly', 'Machine', 'Isolation');


INSERT INTO Exercises (BodypartWorked, Exercise, EquipmentUsed, Mechanics)
VALUES ('Triceps', 'Close-Grip Bench Press', 'Barbell', 'Compound');

INSERT INTO Exercises (BodypartWorked, Exercise, EquipmentUsed, Mechanics)
VALUES ('Triceps', 'Bench Dip', 'Body Weight', 'Isolation');


INSERT INTO Exercises (BodypartWorked, Exercise, EquipmentUsed, Mechanics)
VALUES ('Biceps', 'Chin-Up', 'Body Weight', 'Isolation');


INSERT INTO Exercises (BodypartWorked, Exercise, EquipmentUsed, Mechanics)
VALUES ('Biceps', 'Barbell Curls', 'Body Weight', 'Isolation');


INSERT INTO Exercises (BodypartWorked, Exercise, EquipmentUsed, Mechanics)
VALUES ('Quadriceps', 'Barbell Squats', 'Barbell', 'Compound');

INSERT INTO Exercises (BodypartWorked, Exercise, EquipmentUsed, Mechanics)
VALUES ('Quadriceps', 'Barbell Lunges', 'Barbell', 'Compound');

[johno_66]

This website helped me out ALOT doing this stuff:

http://www.mdbmakers.com/forums/

[BurningHeart]

Originally Posted by johno_66

This website helped me out ALOT doing this stuff:

http://www.mdbmakers.com/forums/

Thanks I registered for the site but still waiting for my confirmation email from them.

Anyone can help me make a lookup table for this database?

[WSsucks]

did u learn joins? i would make a query asking for the desired data and just join them example inner join, outer joiner etc.

[BurningHeart]

Originally Posted by WSsucks

did u learn joins? i would make a query asking for the desired data and just join them example inner join, outer joiner etc.

We learned joins but the instructor told me to make lookup tables. It would seem like lookup tables would be easier though right?

[phikappa]

Originally Posted by BurningHeart

We learned joins but the instructor told me to make lookup tables. It would seem like lookup tables would be easier though right?

You'll need to do joins regardless (unless everything is in one table).

Sounds like your instructor is asking you to modify your design to a more normalized structure. I'm guessing he wants you to have a Bodypart table and maybe an Exercise table. A many-to-many relationship would exist between Bodyparts and Exercises. Bodyparts can be worked using more than one exercise and Exercises typically hit more than one bodypart (although, you could assume that only one bodypart is hit by a particular exercise). The intersection table would act as a bridge between the Bodypart table and the Exercise table to handle the many-to-many join.

[WSsucks]

Originally Posted by BurningHeart

We learned joins but the instructor told me to make lookup tables. It would seem like lookup tables would be easier though right?

i dont know how he wants u to do it without joins. if i was doing it id join the tables for the stuff needed and be done with it.

[ChristianBBer]

You put the VCR in the TV and you put in a tape and it plays..

-hope this helps

[BurningHeart]

Originally Posted by phikappa

You'll need to do joins regardless (unless everything is in one table).

Sounds like your instructor is asking you to modify your design to a more normalized structure. I'm guessing he wants you to have a Bodypart table and maybe an Exercise table. A many-to-many relationship would exist between Bodyparts and Exercises. Bodyparts can be worked using more than one exercise and Exercises typically hit more than one bodypart (although, you could assume that only one bodypart is hit by a particular exercise). The intersection table would act as a bridge between the Bodypart table and the Exercise table to handle the many-to-many join.

Yes each bodypart will have many exercises related to it, and vice-versa... however I just need to make lookup tables for EquipmentUsed and Mechanics because the same answer will come up multiple times. I don't know why he didn't tell me to seperate Bodyparts and Exercises, but I'm not going to worry about it, would just make more work for me.

At the moment I only have 1 table in my database, and it includes everything in it. So I have multiple instances of "free weights" or "cables" or "isolation" or "compound". What he wants me to do is take these multiple instances out of the table and put them in a lookup table.

[BurningHeart]

Originally Posted by WSsucks

i dont know how he wants u to do it without joins. if i was doing it id join the tables for the stuff needed and be done with it.

How would I do this with a join? I know how to do queries with joins (I think) but I'm not sure how to setup the two tables to be interacted with each other.

Like if I make a seperate table for EquipmentUsed and included the fields "Cables, Bodyweight, Freeweights, Machines" how would I tell each exercise that are in a seperate table that it's associated with a certain field in the EquipmentUsed table?

[phikappa]

Originally Posted by BurningHeart

How would I do this with a join? I know how to do queries with joins (I think) but I'm not sure how to setup the two tables to be interacted with each other.

Like if I make a seperate table for EquipmentUsed and included the fields "Cables, Bodyweight, Freeweights, Machines" how would I tell each exercise that are in a seperate table that it's associated with a certain field in the EquipmentUsed table?

You're on the right track.

You'd have something like this. Two tables to hold equipment and bodyparts and one table to link the two together.

tEquipmentUsed
-----------
Equipment - e.g. Cables, Bodyweight, Freeweights, Machines, etc.


tBodypart
-----------
Bodypart - e.g. Chest, back, bicep, etc.


tEquipBody
-----------
Equipment
Bodypart


Then you'd do a select statement like this:

SELECT EQUIP.EQUIPMENT,
BODY.BODYPART
FROM tEqupiment EQUIP
INNER JOIN tEquipBody EB
ON EQUIP.Equipment = EB.Equipment
INNER JOIN tBodyPart BODY
ON EB.BodyPart = BODY.Bodypart

[BurningHeart]

Originally Posted by phikappa

You'll need to do joins regardless (unless everything is in one table).

Sounds like your instructor is asking you to modify your design to a more normalized structure. I'm guessing he wants you to have a Bodypart table and maybe an Exercise table. A many-to-many relationship would exist between Bodyparts and Exercises. Bodyparts can be worked using more than one exercise and Exercises typically hit more than one bodypart (although, you could assume that only one bodypart is hit by a particular exercise). The intersection table would act as a bridge between the Bodypart table and the Exercise table to handle the many-to-many join.

Actually yes there will be an intersection table between the Bodypart table and Exercise table. I had forgot I needed to include that one.

It seems for the intersection table I just need to do something like:

CREATE TABLE Exercises2BodypartWorked
(
ExercisesID INT NOT NULL,
BodypartWorkedID INT NOT NULL
);

Would that be all needed to be done to make the intersection table?

[phikappa]

Originally Posted by BurningHeart

Actually yes there will be an intersection table between the Bodypart table and Exercise table. I had forgot I needed to include that one.

It seems for the intersection table I just need to do something like:

CREATE TABLE Exercises2BodypartWorked
(
ExercisesID INT NOT NULL,
BodypartWorkedID INT NOT NULL
);

Would that be all needed to be done to make the intersection table?

Yup.

[DontTreadOnMe]

what program are you using?

access?

[BurningHeart]

Originally Posted by phikappa

You're on the right track.

You'd have something like this. Two tables to hold equipment and bodyparts and one table to link the two together.

tEquipmentUsed
-----------
Equipment - e.g. Cables, Bodyweight, Freeweights, Machines, etc.


tBodypart
-----------
Bodypart - e.g. Chest, back, bicep, etc.


tEquipBody
-----------
Equipment
Bodypart


Then you'd do a select statement like this:

SELECT EQUIP.EQUIPMENT,
BODY.BODYPART
FROM tEqupiment EQUIP
INNER JOIN tEquipBody EB
ON EQUIP.Equipment = EB.Equipment
INNER JOIN tBodyPart BODY
ON EB.BodyPart = BODY.Bodypart

To make the lookup tables, would I do something like this?

CREATE TABLE EquipmentUsed
(
EquipmentUsedID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
Cables,
Freeweight,
Bodyweight,
Machines,
Dumbbells
);

How would I assign each exercise an equipmentused?

Originally Posted by DontTreadOnMe

what program are you using?

access?

Using MYSQL, we just type in it notepad then upload it to the database through telnet.

[phikappa]

Originally Posted by BurningHeart

To make the lookup tables, would I do something like this?

CREATE TABLE EquipmentUsed
(
EquipmentUsedID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
Cables,
Freeweight,
Bodyweight,
Machines,
Dumbbells
);

More like this....

CREATE TABLE EquipmentUsed
(
EquipmentUsedID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
EquipmentDesc varchar(50) NOT NULL
);

Then do INSERT INTO for the values - Cables, Freeweight, Bodyweight, Machines, Dumbbells. So, your EquipmentUsed table would have 5 records in it.

[BurningHeart]

Originally Posted by phikappa

More like this....

CREATE TABLE EquipmentUsed
(
EquipmentUsedID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
EquipmentDesc varchar(50) NOT NULL
);

Then do INSERT INTO for the values - Cables, Freeweight, Bodyweight, Machines, Dumbbells. So, your EquipmentUsed table would have 5 records in it.

Ok I got the lookup table for EquipmentUsed as you typed it. I know I'm taking baby steps here, but the instructor just pretty much threw this assignment out there without explaining it to us. Sort of like this?

INSERT INTO EquipmentUsed (Cables, Freeweight, Bodyweight, Machine, Dumbbell)
VALUES ('1', '2', '3', '4', '5', '6');

or...

INSERT INTO EquipmentUsed
VALUES ('Cables')

INSERT INTO EquipmentUsed
VALUES ('Freeweight')

etc...

Once I have that done, how do I assign an EquipmentUsed to an exercise in the Exercises table?

[BurningHeart]

I've been working on the database some more. Can anyone look over it real quick and let me know if anything needs changing? Thanks

--------

CREATE TABLE ExerciseDesc
(
ExercisesID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

BodypartWorked VARCHAR(50),
Exercise VARCHAR(50),
EquipmentUsed VARCHAR(50),
Mechanics VARCHAR(50)
);


INSERT INTO ExerciseDesc
VALUES (13, 'Barbell Shoulder Press');

INSERT INTO ExerciseDesc
VALUES (14, 'Dumbbell Shoulder Press');

INSERT INTO ExerciseDesc
VALUES (15, 'Barbell Bench Press');

INSERT INTO ExerciseDesc
VALUES (16, 'Butterfly');

INSERT INTO ExerciseDesc
VALUES (17, 'Close-grip Bench Press');

INSERT INTO ExerciseDesc
VALUES (18, 'Bench Dip');

INSERT INTO ExerciseDesc
VALUES (19, 'Chin-up');

INSERT INTO ExerciseDesc
VALUES (20, 'Barbell Curls');

INSERT INTO ExerciseDesc
VALUES (21, 'Barbell Squats');

INSERT INTO ExerciseDesc
VALUES (22, 'Barbell Lunges');


CREATE TABLE Exercises2BodypartWorked
(
ExercisesID INT NOT NULL,
BodypartWorkedID INT NOT NULL
);

CREATE TABLE BodypartWorked
(
BodypartworkedID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
BodypartWorked VARCHAR(50) NOT NULL
);

INSERT INTO BodypartWorked
VALUES (1, 'Shoulders');

INSERT INTO BodypartWorked
VALUES (2, 'Triceps');

INSERT INTO BodypartWorked
VALUES (3, 'Biceps');

INSERT INTO BodypartWorked
VALUES (4, 'Quadriceps');

INSERT INTO BodypartWorked
VALUES (5, 'Chest');


CREATE TABLE EquipmentUsed
(
EquipmentUsedID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
EquipmentDesc VARCHAR(50) NOT NULL
);


INSERT INTO EquipmentUsed
VALUES (6, 'Cables');

INSERT INTO EquipmentUsed
VALUES (7, 'Barbell');

INSERT INTO EquipmentUsed
VALUES (8, 'Bodyweight');

INSERT INTO EquipmentUsed
VALUES (9, 'Dumbbell');

INSERT INTO EquipmentUsed
VALUES (10, 'Machine');


CREATE TABLE Mechanics
(
MechanicsID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
MechanicsDesc VARCHAR(50) NOT NULL
);


INSERT INTO Mechanics
VALUES (11, 'Compound');

INSERT INTO Mechanics
VALUES (12, 'Isolation');


INSERT INTO ExerciseDesc (BodypartWorked, Exercise)
VALUES ('1', '13');


INSERT INTO ExerciseDesc (BodypartWorked, Exercise)
VALUES ('1', '14');


INSERT INTO ExerciseDesc (BodypartWorked, Exercise)
VALUES ('2', '15');


INSERT INTO ExerciseDesc (BodypartWorked, Exercise)
VALUES ('2', '16');


INSERT INTO ExerciseDesc (BodypartWorked, Exercise)
VALUES ('3', '17');

INSERT INTO ExerciseDesc (BodypartWorked, Exercise)
VALUES ('3', '18');


INSERT INTO ExerciseDesc (BodypartWorked, Exercise)
VALUES ('4', '19');


INSERT INTO ExerciseDesc (BodypartWorked, Exercise)
VALUES ('4', '20');


INSERT INTO ExerciseDesc (BodypartWorked, Exercise)
VALUES ('5', '21');

INSERT INTO ExerciseDesc (BodypartWorked, Exercise)
VALUES ('5', '22');

[johno_66]

Originally Posted by BurningHeart

Thanks I registered for the site but still waiting for my confirmation email from them.

Anyone can help me make a lookup table for this database?

Check your junk folder it will be in there. Trust me these guys are amazing, they have helped me with LOADS of problems for my access database.

[BurningHeart]

Originally Posted by johno_66

Check your junk folder it will be in there. Trust me these guys are amazing, they have helped me with LOADS of problems for my access database.

Yeah I got registered. I posted on the site and they are trying to help (or so it seems) hehe

[BurningHeart]

bump, anyone?

I updated it a little...


--------------

CREATE TABLE ExerciseDesc
(
ExercisesID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

BodypartWorked VARCHAR(50),
Exercise VARCHAR(50),
EquipmentUsed VARCHAR(50),
Mechanics VARCHAR(50)
);


INSERT INTO ExerciseDesc
VALUES (13, 'Barbell Shoulder Press');

INSERT INTO ExerciseDesc
VALUES (14, 'Dumbbell Shoulder Press');

INSERT INTO ExerciseDesc
VALUES (15, 'Barbell Bench Press');

INSERT INTO ExerciseDesc
VALUES (16, 'Butterfly');

INSERT INTO ExerciseDesc
VALUES (17, 'Close-grip Bench Press');

INSERT INTO ExerciseDesc
VALUES (18, 'Bench Dip');

INSERT INTO ExerciseDesc
VALUES (19, 'Chin-up');

INSERT INTO ExerciseDesc
VALUES (20, 'Barbell Curls');

INSERT INTO ExerciseDesc
VALUES (21, 'Barbell Squats');

INSERT INTO ExerciseDesc
VALUES (22, 'Barbell Lunges');


CREATE TABLE Exercises2BodypartWorked
(
ExercisesID INT NOT NULL,
BodypartWorkedID INT NOT NULL
);

CREATE TABLE BodypartWorked
(
BodypartworkedID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
BodypartWorked VARCHAR(50) NOT NULL
);

INSERT INTO BodypartWorked
VALUES (1, 'Shoulders');

INSERT INTO BodypartWorked
VALUES (2, 'Triceps');

INSERT INTO BodypartWorked
VALUES (3, 'Biceps');

INSERT INTO BodypartWorked
VALUES (4, 'Quadriceps');

INSERT INTO BodypartWorked
VALUES (5, 'Chest');


CREATE TABLE EquipmentUsed
(
EquipmentUsedID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
EquipmentDesc VARCHAR(50) NOT NULL
);


INSERT INTO EquipmentUsed
VALUES (6, 'Cables');

INSERT INTO EquipmentUsed
VALUES (7, 'Barbell');

INSERT INTO EquipmentUsed
VALUES (8, 'Bodyweight');

INSERT INTO EquipmentUsed
VALUES (9, 'Dumbbell');

INSERT INTO EquipmentUsed
VALUES (10, 'Machine');


CREATE TABLE Mechanics
(
MechanicsID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
MechanicsDesc VARCHAR(50) NOT NULL
);


INSERT INTO Mechanics
VALUES (11, 'Compound');

INSERT INTO Mechanics
VALUES (12, 'Isolation');


INSERT INTO ExerciseDesc (BodypartWorked, Exercise, Mechanics)
VALUES (1, 13, 11);


INSERT INTO ExerciseDesc (BodypartWorked, Exercise, Mechanics)
VALUES (1, 14, 12);


INSERT INTO ExerciseDesc (BodypartWorked, Exercise, Mechanics)
VALUES (2, 15, 11);


INSERT INTO ExerciseDesc (BodypartWorked, Exercise, Mechanics)
VALUES (2, 16, 12);


INSERT INTO ExerciseDesc (BodypartWorked, Exercise, Mechanics)
VALUES (3, 17, 11);


INSERT INTO ExerciseDesc (BodypartWorked, Exercise, Mechanics)
VALUES (3, 18, 12);


INSERT INTO ExerciseDesc (BodypartWorked, Exercise, Mechanics)
VALUES (4, 19, 12);


INSERT INTO ExerciseDesc (BodypartWorked, Exercise, Mechanics)
VALUES (4, 20, 12);


INSERT INTO ExerciseDesc (BodypartWorked, Exercise, Mechanics)
VALUES (5, 21, 11);


INSERT INTO ExerciseDesc (BodypartWorked, Exercise, Mechanics)
VALUES (5, 22, 11);