Solutions for Chapter 7

[7.1] (b)

create table customers (cid char(4) not null, cname varchar(13), city
varchar(20) references discounts, discnt real, primary key (cid));
create table discounts (discnt real not null, unique, primary key discnt);

Then load table discounts with the desired numbers.

[7.5] (a) (i) not legal, because there is more than one table in the Subquery from clause.
(ii) not legal, because there is more than one table in the Subquery from clause.
(iii) not legal, because agentsales has a group by.

(c) ORACLE:
alter table agents modify (percent integer check (percent>=0 and percent<=10));
or
alter table agents add (constraint ckpercent check (percent >= 0 and percent <= 10));

DB2 UDB:
alter table agents
add constraint ckpercent check (percent >= 0 and percent <= 10);

INFORMIX:
alter table agents add constraint constraint ckpercent check (percent >= 0 and percent <= 10);

[7.6] (b) True by Definition 7.1.6.

(d) False. The "unique" column constraint and the "unique (columnname {, columnname})" table constraint can be used to define any number candidate keys. See Definitions 7.1.2 and 7.1.3.

[7.7] (b) No action.

(c) The delete would fail.

[7.8] (b)

create table customers1 (cid char(4) not null, cname varchar(13),
city varchar(20), discnt real
constraint discnt_max check (discnt <= 15.0), primary key(cid));

insert into customers1 select * from customers;

[7.9] The last column is not a simple column name (requirement 5 of Figure 7.15). Consider

update emps set totsal = totsal + 10000.0;

It is not clear how salary 1 and salary2 should be individually changed so that the total increases by 10000.