The database scheme consists of four relations:

Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)

The relation "Product" shows the maker, model number, and type (pc,
laptop, or printer). It is assumed that model numbers are unique for
all the makers and product types. Each model number specifying pc in
the relation "PC" is characterized by speed (of the processor in MHz),
total amount of RAM (in Mb), hard disk drive capacity (in Gb), CD ROM
speed (for example, '4x'), and the price. The relation "Laptop" is
similar to that one of PCs except for the CD ROM speed, which is
replaced by the screen size (in inches). For each printer model in the
relation "Printer" it is told whether the printer is color or not
(color attribute is 'y' for color printers; otherwise it is 'n'),
printer type (laser, jet, or matrix), and the price.

Exercise: 2
Find printer makers.
Result set: maker.


Typical mistake when solving exercise #2 is the query:

select distinct maker
from product, printer
where product.model=printer.model

The Product and Printer tables are in one-to-many relationship. In
particular, it means that Printer table may not involve all the
printer models available in Product table. As a result, the above
query will return only those models, which are available (!) in
Printer table.


----------------------
I don't understand why it is a one-to-many relationship and why
Printer table may not involve all the printer models available in
Product table.

Thanks.

Yue
> I don't understand why it is a one-to-many relationship and why
> Printer table may not involve all the printer models available in
> Product table.

Do you prepare to exam?

--To get all types from Product (NULL will be return where columns do not
match)
select maker
from product left join printer on
product.model=printer.model


Printer table may or may not contain all types it depends on your data .


create table Printer (id int , name char(1))
go
insert into Printer (1,'A')
insert into Printer (2,'B')
go

create table Product (id int , printerid int reference Printer(id), name
varchar(100))
go
insert into Product (1,1,'bbbbb')
insert into Product (2,2,'aaaaaaa')

You can add an unique containt on id and printerid not not allow more than
two types to Printer





"Yue" <dariahuangster@gmail.com> wrote in message
news:c452dff9-e5e6-4011-8527-07b17b048f4b@r66g2000hsg.googlegroups.com...
> The database scheme consists of four relations:
>
> Product(maker, model, type)
> PC(code, model, speed, ram, hd, cd, price)
> Laptop(code, model, speed, ram, hd, screen, price)
> Printer(code, model, color, type, price)
>
> The relation "Product" shows the maker, model number, and type (pc,
> laptop, or printer). It is assumed that model numbers are unique for
> all the makers and product types. Each model number specifying pc in
> the relation "PC" is characterized by speed (of the processor in MHz),
> total amount of RAM (in Mb), hard disk drive capacity (in Gb), CD ROM
> speed (for example, '4x'), and the price. The relation "Laptop" is
> similar to that one of PCs except for the CD ROM speed, which is
> replaced by the screen size (in inches). For each printer model in the
> relation "Printer" it is told whether the printer is color or not
> (color attribute is 'y' for color printers; otherwise it is 'n'),
> printer type (laser, jet, or matrix), and the price.
>
> Exercise: 2
> Find printer makers.
> Result set: maker.
>
>
> Typical mistake when solving exercise #2 is the query:
>
> select distinct maker
> from product, printer
> where product.model=printer.model
>
> The Product and Printer tables are in one-to-many relationship. In
> particular, it means that Printer table may not involve all the
> printer models available in Product table. As a result, the above
> query will return only those models, which are available (!) in
> Printer table.
>
>
> ----------------------
> I don't understand why it is a one-to-many relationship and why
> Printer table may not involve all the printer models available in
> Product table.
>
> Thanks.
>

I'll think about it more.
Thank you, Uri.