Sqlxxxxx

Only available on StudyMode
  • Download(s) : 47
  • Published : May 13, 2013
Open Document
Text Preview
2.3.1
a)
CREATE TABLE Product (
maker Char(100),
model INT,
type CHAR(10),
PRIMARY KEY model
);

b)
create table Laptop(
modelINT,
speedDOUBLE,
ramINT,
hdINT,
screenDOUBLE,
priceINT,
PRIMARY KEY model
);

c)
CREATE TABLE Printer(
modelINT,
colorSTRING,
typeINT,
price INT
PRIMARY KEY model
);

d)
CREATE TABLE PC (
modelINT,
speedDOUBLE,
ramINT,
hdINT,
priceINT,
PRIMARY KEY model
);

e)
ALTER TABLE Printer DROP color;

f)
ALTER TABLE Laptop ADD od CHAR(10) DEFAULT ‘none’;

2.3.2
a)
CREATE TABLE Ships(
nameCHAR(30),
classCHAR(30),
launchedINT,
PRIMARY KEYname
);

b)
CREATE TABLE Outcomes(
shipCHAR(30),
battleCHAR(30),
resultCHAR(4),
PRIMARY KEYship
);

c)
CREATE TABLE Classes(
classCHAR(30),
typeCHAR(2),
countryCHAR(30),
numGunsINT,
boreDOUBLE,
displacementDOUBLE,
PRIMARY KEYclass
);

d)
CREATE TABLE Battles(
nameCHAR(30),
dateDATE,
PRIMARY KEYname
);

e)
ALTER TABLE Classes DROP bore;

f)
ALTER TABLE Ships ADD yard CHAR(30);
2.5.1
a)
σspeed < 3 ^ price > 800(PC) = ∅

b)
σscreen > 15.4 ^ (hd < 120 ^ price < 1000)(Laptop) =∅

c)
σmaker(PC) ∩ σmaker(Laptop) = ∅

d)
σ(Laptop.ram < PC.ram) ^ (Laptop.price > PC.price) (Laptop x PC) = ∅

e)
σ(PC.maker = Laptop.maker) ^ (PC.speed > Laptop.speed) (Laptop x PC) = ∅

2.5.2
a)
σbore < 18(Classes)= ∅

b)
σnumGuns > 10 ^ bore > 15 = ∅

c)
π(name, class, launched)(σ(S1.name <> S2.name) ^ (S1.name <> S3.name) ^ (S2.name <> S3.name) ^ (S1.class = S2.class) ^ (S1.class = S3.class) ^ (S2.class = S3.class)(ρ(S1)(Ships) x ρ(S1)(Ships) x ρ(S1)(Ships))) = ∅...
tracking img