care to give this a crack?
-
quick excercise...
3 tables and 2 junction tables (junction1 & junction2)
some code to get you started. this will create the
first 3 tables, then the excercise follows.
basically needs some relations, and some inserts are the real questions.
have a crack at it if interested.
i find the inserts to the junctions kinda tough.
cheers.
----------------------------- Creating DRIVERS table, and populating.
create table drivers
(driverid int identity(1,1),
lastname char (20),
firstname char (20)
)
insert into drivers
(firstname, lastname)
values
('mike', 'bliss')
go
insert into drivers
(firstname, lastname)
values
('mark', 'martin')
go
insert into drivers
(firstname, lastname)
values
('scott', 'riggs')
go
insert into drivers
(firstname, lastname)
values
('tony', 'stewart')
go
insert into drivers
(firstname, lastname)
values
('jeff', 'fuller')
go
----------------------------- Creating CARS table, and populating.
create table cars
(carid int identity (1,1),
carname char (20),
cardesc char (20)
)
insert into cars
(carname, cardesc)
values
('mustang', 'musclecar')
go
insert into cars
(carname, cardesc)
values
('stingray', 'musclecar')
go
insert into cars
(carname, cardesc)
values
('gto', 'musclecar')
go
insert into cars
(carname, cardesc)
values
('camaro', 'musclecar')
go
insert into cars
(carname, cardesc)
values
('malibu', 'musclecar')
go
----------------------------- Creating LOGO table, and populating.
create table logo
(logoid int identity (1,1),
logoname char (20)
)
insert into logo
(logoname)
values
('cnc racing')
go
insert into logo
(logoname)
values
('roush racing')
go
insert into logo
(logoname)
values
('mbv motorsports')
go
insert into logo
(logoname)
values
('mach one')
go
insert into logo
(logoname)
values
('gibbs racing')
go
-----------------------------
-----------------------------
--there will be 2 additional tables created in this excercise.
--those 2 tables will be junction tables, and data will
--be inserted using the first 3 tables we created.
-----------------------------
-----------------------------
--first junction table will have data from the 'drivers & cars' tables.
--as defined below. you can call this: junction1
--1. mike bliss drives the mustang and stingray.
--2. mark martin drives the camaro.
--3. scott riggs drives all five cars.
--4. tony stewart drives the camaro.
--5. jeff fuller drives the stingray, gto and malibu.
-----------------------------
-----------------------------
--second junction table will have data from the 'cars & logo' tables.
--as defined below. you can call this: junction2
--1. the mustang and stingray have the 'cnc racing' logo.
--2. the gto has the 'mbv motorsports' logo.
--3. the camaro has the 'gibbs racing' logo.
--4. the malibu has the 'mach one' logo.
----------------------------
-- thats it.
_________________________
-
Sounds like homework, U should do it yourself.
Junciton tables should be created with foreign key constraints to "Referencing tables" as Drivers and Cars
Drivers_Cars
ID,
DriverID References DriverID in Driver
CarID References CarID in Car
Select driverid
From Drivers
Where FirstName = 'Bliss'
etc...
-
the junctions no problem. relations.. no problem. inserts tough.
just trying to get a better grasp of it.
thoughts/examples?
_________________________
-
Insert the drivers that make sense. Then insert the cars that make sense. Do a query against both tables and find out what the values are for the driverid and carid. Make the appropriate inserts into the junction table, which has two columns, one for each id. Repeat the same process for the logos.
K. Brian Kelley
@kbriankelley -
technically this would be a join statement though right?
_________________________
-
the selects would be easy if it were a single driver, and a single car.
the problem for me is there are multiple cars per
driver. how does one insert those values into a
table? i mean... for junction1 table... do you set
it up with the following columns?
firstname, lastname, car1, car2, car3 etc?
and... if this is the case how would the select, insert work?
it's a join right? then... i have to select particular
cars per driver, then insert those into junction1.
i've checked out many articles which display the usual
examples, but this excercise feels like it's advanced
cause most join examples are based on selects, and
not really performing any inserts to a junction table. this is
a real pain for me but i've got to figure this out.
thoughts?
_________________________
-
No. Consider them separate input statements. For instance, if driverid 5 drives cars with carid 7 and 11, you've got two rows in your join table.
INSERT INTO DriverCar (DriverID, CarID) VALUES (5, 7)
INSERT INTO DriverCar (DriverID, CarID) VALUES (5, 11)
Join statements are used typically in SELECT statements. Yes, you may end up using them in UPDATE or DELETE statements, but they aren't in INSERT statements unless that INSERT statement is using a SELECT statement to get information out of other existing tables/views.
K. Brian Kelley
@kbriankelley -
bkelley... many many thanks for your help!
how would you create the junction table in this case?
what columns exactly?
i'm guessing some thing like this:
lastname, firstname, car1, car2, car3, car4, car5
if i'm on the right track, then there would be an
insert statement per car with consideration to who
the driver is of course.
create table cardriver
(carid int references car (carid),
driverid int references drivers (driverid),
lastname char (20),
firstname char (20),
car1 char (20),
car2 char (20),
car3 char (20),
car4 char (20),
car5 char (20)
)
then run a separate insert statement per car so
each different car goes to their own column.
eventually displaying each drivers name, and cars
that are associated with them.
does this sound correct to you or have i completely
over complicated this?
hammer me on this point if you must... no hard feelings.
i've got to understand this stuff, and sqlservercentral
is one of my best resources.
thanks again for your help!
_________________________
-
No, That way is bad, you have repeating groups, What if all of a sudden a driver has a 6th car, you have to add another colum to the table, that is no good.
Your in violation of 1st normal form.
First normal form (1NF) sets the very basic rules for an organized database:
- Eliminate duplicative columns from the same table.
- Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).
Second normal form (2NF) further addresses the concept of removing duplicative data:
- Meet all the requirements of the first normal form.
- Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
- Create relationships between these new tables and their predecessors through the use of foreign keys.
Third normal form (3NF) goes one large step further:
- Meet all the requirements of the second normal form.
- Remove columns that are not dependent upon the primary key.
A correct table would be
CarDriver (ID int,
DriverID int,
CarID int)
So for first driver insert statement would be.
-- mike bliss drives the mustang and stingray.
<A href="mailto
eclare@DriverID">Declare @DriverID int,@CarID int
Select @DriverID = ID
From Driver
Where Driver_First_Name = 'Mike'
And Driver_Last_Name = 'Bliss'
Select @CarID = ID
From Car
Where Car_Name = 'Mustang'
Insert into CarDriver(DriverID, CarID)
Values(@DriverID,@CarID)
Select @CarID = ID
From Car
Where Car_Name = 'StingRay'
Insert into CarDriver(DriverID, CarID)
Values(@DriverID,@CarID)
And So on.
Then to query what driver uses what cars,
Select Driver_First_name, Driver_last_Name, Car_Name
From Driver D
Join CarDriver on DriverID = D.ID
join Car C on CarID = c.ID
the name of the driver stays in only one table, you don't want to spam that out across many tables. Same with the cars, So for Relational databases you simply create a table that shows the relationship. Thats why the cardriver table only has the carid, and driver id.
-
Actually, you can create the join table like this:
CREATE TABLE CarDriver( CarID int NOT NULL, DriverID int NOT NULL, CONSTRAINT PK_CarDriver PRIMARY KEY (DriverID, CarID) )
I don't typically use a surrogate key, ID, in these cases because the composite key we get is still sufficiently small and integer based. However, you could do so if you so desire. The key though, (pun intended) is to ensure that you don't map the join between Car and Driver and that you ensure, for data integrity sake, you constrain the table to ensure no duplicates (hence the addition of the primary key constraint). Now, whether CarID or DriverID should be first in the constraint depends on the data itself and how you're going to use it. But that's a discussion for another day.
K. Brian Kelley
@kbriankelley -
thanks for the response ray & bkelley!
just to be sure on this point:
"the name of the driver stays in only one table, you don't want to spam that out across many tables. Same with the cars..."
so 'inserting' those values into the junction table is bogus. the only thing that should
be done is the relations. the junction does not contain data in this case, it only is
there to represent the relationship.
so seeing the values of drivers, and their cars is really a result from a query, and not
from the junction table it's self... correct?
it really doesn't matter if the query results display multiple car columns for each driver,
cause it's just a query after all.
perhaps i misunderstood the excercise.
i think i'll have to review this thread more closely and make sure what i'm thinking
is in-line with what you guys have explained.
thanks for your input!
_________________________
-
another question... do you find that the excercise in general is
easily understood? -or- do you feel it was poory written all together?
i mean... are the concepts clear?
_________________________
-
Sheesh. Where was the damn Internet when I was doing homework 30 years ago?
-
the net is great isn't it/
what an excellent resource.
_________________________
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply