Consider the following database with four relations, whose schemas are homework2
ID: 3907004 • Letter: C
Question
Consider the following database with four relations, whose schemas are
homework2 (1) - Word Hunter Davis X File Design Layout References Mailings Review View Nitro Pro99 Tell me what you want to do Home Cut Copy Fonmat Painter Insert Share OFind Times New Ro ? | 12 A. A, Aa- AaBbCcl AaBbC AaBbCcI AaBbCcI AaBbCcI AaBbCcI AaBbCcC AaBbC Paste ext Heading 1 List Paro....1No Spac... T Normal T Table Pa... Heading itle Select Editing Clipboard Font Paragraph Styles Considerthe following database with four relations, whose schemas are Product(maker,model, type)(Note: typecan be anyvalueofPC,Laptop.Printer) . PC (model, speed, ram, hd, price) Laptop(model, speed,ram, hd, screen, price) Printer (model, color, type, price) For eachof the following queries, i) write expressions ofrelational algebra to answer the query; ii) formulate SQL statements to answer the query 1. (6 Points) What PC models have a speed of at least 2.80GHz; 2. (6 Points) Which manufacturers (makers) make laptops with a hard disk of at least 1TB 3. (6 Points) Find the model and price of all products (PC, Laptop, Printer) made by manufacturer DELL; 4. (6 Points) Find the manufacturers that sell Laptops, but not PC's; 5. (6 Points) Find those pairs ofPC models that have both the same speed and RAM. A pair should be listed only once, e.g., list (i,j) but not (j, i) Page 2 of 2 567 words + 150% 9:57 PM 6/20/2018Explanation / Answer
If you have any doubts, please give me comment...
-- 1)
SELECT model
FROM PC
WHERE speed >= '2.80GHz';
-- 2)
SELECT P.maker
FROM Product P, Laptop L
WHERE P.model = L.model AND L.hd>=1;
-- 3)
SELECT PC.model, PC.price
FROM Product P, PC
WHERE P.maker='DELL' AND P.model = PC.model
UNION
SELECT L.model, L.price
FROM Product P, Laptop L
WHERE P.maker='DELL' AND P.model = L.model
UNION
SELECT PR.model, PR.price
FROM Product P, Printer PR
WHERE P.maker='DELL' AND P.model = PR.model
-- 4)
SELECT DISTINCT maker
FROM product P
WHERE P.type='Laptop'
EXCEPT
SELECT DISTINCT maker
FROM product P
WHERE P.type='PC';
-- 5)
SELECT P1.model, P2.model
FROM PC P1, PC P2
WHERE P1.model < P2.model AND P1.speed = P2.speed
AND P1.ram = P2.ram