Download exercise in Data Base and more Essays (university) Database Management Systems (DBMS) in PDF only on Docsity!
Exercise 2.2.1a
For relation Accounts, the attributes are:
acctNo, type, balance
For relation Customers, the attributes are:
firstName, lastName, idNo, account
Exercise 2.2.1b
For relation Accounts, the tuples are:
(12345, savings, 12000), (23456, checking, 1000), (34567, savings, 25)
For relation Customers, the tuples are:
(Robbie, Banks, 901-222, 12345), (Lena, Hand, 805-333, 12345), (Lena, Hand, 805-333, 23456)
Exercise 2.2.1c
For relation Accounts and the first tuple, the components are:
123456 F 0E 0 acctNo savings F 0E 0 type 12000 F 0E 0 balance
For relation Customers and the first tuple, the components are:
Robbie F 0E 0 firstName Banks F 0E 0 lastName 901-222 F 0E 0 idNo 12345 F 0E 0 account
Exercise 2.2.1d
For relation Accounts, a relation schema is:
Accounts(acctNo, type, balance)
For relation Customers, a relation schema is:
Customers(firstName, lastName, idNo, account)
Exercise 2.2.1e
An example database schema is:
Accounts ( acctNo, type, balance ) Customers ( firstName, lastName, idNo, account )
Exercise 2.2.1f
A suitable domain for each attribute:
acctNo F 0E 0 Integer type F 0E 0 String balance F 0E 0 Integer firstName F 0E 0 String lastName F 0E 0 String idNo F 0E 0 String (because there is a hyphen we cannot use Integer) account F 0E 0 Integer
Exercise 2.2.1g
Another equivalent way to present the Account relation:
acctNo balance type 34567 25 savings 23456 1000 checking 12345 12000 savings
Another equivalent way to present the Customers relation:
idNo firstName lastName account
Exercise 2.3.1c
CREATE TABLE Laptop ( model CHAR(30), speed DECIMAL(4,2), ram INTEGER, hd INTEGER, screen DECIMAL(3,1), price DECIMAL(7,2) );
Exercise 2.3.1d
CREATE TABLE Printer ( model CHAR(30), color BOOLEAN, type CHAR (10), price DECIMAL(7,2) );
Exercise 2.3.1e
ALTER TABLE Printer DROP color;
Exercise 2.3.1f
ALTER TABLE Laptop ADD od CHAR (10) DEFAULT ‘none’;
Exercise 2.3.2a
CREATE TABLE Classes ( class CHAR(20), type CHAR(5), country CHAR(20), numGuns INTEGER, bore DECIMAL(3,1), displacement INTEGER );
Exercise 2.3.2b
CREATE TABLE Ships ( name CHAR(30), class CHAR(20), launched INTEGER );
Exercise 2.3.2c
CREATE TABLE Battles ( name CHAR(30), date DATE );
Exercise 2.3.2d
CREATE TABLE Outcomes ( ship CHAR(30), battle CHAR(30), result CHAR(10) );
Exercise 2.3.2e
ALTER TABLE Classes DROP bore;
Exercise 2.3.2f
ALTER TABLE Ships ADD yard CHAR(30);
Exercise 2.4.1a
R1 := σspeed ≥ 3.00 (PC) R2 := πmodel (R1) model 1005 1006 1013
Exercise 2.4.1b
R1 := σhd ≥ 100 (Laptop) R2 := Product (R1) R3 := πmaker (R2) maker E A
R5 := R3 – R
maker F G
Exercise 2.4.1f
R1 := ρ (^) PC1 (PC) R2 := ρ (^) PC2 (PC) R3 := R1 (^) (PC1.hd = PC2.hd AND PC1.model <> PC2.model) R R4 := πhd(R3) hd 250 80 160
Exercise 2.4.1g
R1 := ρ (^) PC1 (PC) R2 := ρ (^) PC2 (PC) R3 := R1 (^) (PC1.speed = PC2.speed AND PC1.ram = PC2.ram AND PC1.model < PC2.model) R R4 := πPC1.model,PC2.model(R3) PC1.model PC2.model 1004 1012
Exercise 2.4.1h
R1 := πmodel (σspeed ≥ 2.80 (PC)) πmodel (σ (^) speed ≥ 2.80(Laptop)) R2 := πmaker,model(R1 Product) R3 := ρ (^) R3(maker2,model2)(R2) R4 := R2 (^) (maker = maker2 AND model <> model2) R R5 := πmaker (R4) maker
B
E
Exercise 2.4.1i
R1 := πmodel,speed(PC) R2 := πmodel,speed(Laptop) R3 := R1 R R4 := ρ R4(model2,speed2) (R3) R5 := πmodel,speed (R3^ (speed < speed2 ) R4) R6 := R3 – R R7 := πmaker (R6 Product) maker B
Exercise 2.4.1j
R1 := πmaker,speed (Product PC) R2 := ρ (^) R2(maker2,speed2) (R1) R3 := ρ (^) R3(maker3,speed3) (R1) R4 := R1 (^) (maker = maker2 AND speed <> speed2) R R5 := R4 (^) (maker3 = maker AND speed3 <> speed2 AND speed3 <> speed) R R6 := πmaker (R5) maker A D E
Exercise 2.4.1k
R1 := πmaker,model(Product PC) R2 := ρ (^) R2(maker2,model2)(R1) R3 := ρ (^) R3(maker3,model3)(R1) R4 := ρ (^) R4(maker4,model4)(R1) R5 := R1 (^) (maker = maker2 AND model <> model2) R R6 := R3 (^) (maker3 = maker AND model3 <> model2 AND model3 <> model) R
Exercise 2.4.2j
Exercise 2.4.2k
Exercise 2.4.3a
R1 := σbore ≥ 16 (Classes) R2 := πclass,country (R1)
class country Iowa USA North Carolina USA Yamato Japan
Exercise 2.4.3b
R1 := σlaunched < 1921 (Ships) R2 := πname (R1)
name Haruna Hiei Kirishima Kongo Ramillies Renown Repulse Resolution Revenge Royal Oak Royal Sovereign Tennessee
Exercise 2.4.3c
R1 := σbattle=Denmark Strait AND result=sunk (Outcomes) R2 := πship (R1)
ship Bismarck
Hood
Exercise 2.4.3d
R1 := Classes Ships R2 := σlaunched > 1921 AND displacement > 35000 (R1) R3 := πname (R2)
name Iowa Missouri Musashi New Jersey North Carolina Washington Wisconsin Yamato
Exercise 2.4.3e
R1 := σbattle=Guadalcanal(Outcomes) R2 := Ships (^) (ship=name) R R3 := Classes R R4 := πname,displacement,numGuns(R3)
name displacement numGuns Kirishima 32000 8 Washington 37000 9
Exercise 2.4.3f
R1 := πname(Ships) R2 := πship(Outcomes) R3 := ρ (^) R3(name) (R2) R4 := R1 R name California Haruna Hiei Iowa Kirishima Kongo Missouri
Exercise 2.4.3h
R1 := πcountry(σ^ type=bb(Classes)) R2 := πcountry(σ^ type=bc(Classes)) R3 := R1 ∩ R country Japan Gt. Britain
Exercise 2.4.3i
R1 := πship,result,date(Battles (^) (battle=name) Outcomes) R2 := ρ (^) R2(ship2,result2,date2) (R1) R3 := R1 (^) (ship=ship2 AND result=damaged AND date < date2) R R4 := πship(R3)
No results from sample data.
Exercise 2.4.4a
Exercise 2.4.4b
Exercise 2.4.4c
Exercise 2.4.4d
Exercise 2.4.4e
Exercise 2.4.4f
Exercise 2.4.4g
Exercise 2.4.4h
Exercise 2.4.4i
Exercise 2.4.
The result of the natural join has only one attribute from each pair of equated attributes. On the other hand, the result of the theta-join has both columns of the attributes and their values are identical.
Exercise 2.4.
Union If we add a tuple to the arguments of the union operator, we will get all of the tuples of the original result and maybe the added tuple. If the added tuple is a duplicate tuple, then the set behavior will eliminate that tuple. Thus the union operator is monotone.
Intersection If we add a tuple to the arguments of the intersection operator, we will get all of the tuples of the original result and maybe the added tuple. If the added tuple does not exist in the relation that it is added but does exist in the other relation, then the result set will include the added tuple. Thus the intersection operator is monotone.
Difference If we add a tuple to the arguments of the difference operator, we may not get all of the tuples of the original result. Suppose we have relations R and S and we are computing R
- S. Suppose also that tuple t is in R but not in S. The result of R – S would include tuple t. However, if we add tuple t to S, then the new result will not have tuple t. Thus the difference operator is not monotone.
Projection If we add a tuple to the arguments of the projection operator, we will get all of the tuples of the original result and the projection of the added tuple. The projection operator only selects columns from the relation and does not affect the rows that are selected. Thus the projection operator is monotone.
Selection If we add a tuple to the arguments of the selection operator, we will get all of the tuples of the original result and maybe the added tuple. If the added tuple satisfies the select condition, then it will be added to the new result. The original tuples are included in the
Exercise 2.4.7c
If the condition C brings back all the tuples of R, then the cross product will contain n * m tuples. This number would be the maximum possible.
The minimum number of tuples that can appear in the result occurs if the condition C brings back none of the tuples of R. Then the cross product has zero tuples.
Exercise 2.4.7d
Assuming that the list of attributes L makes the resulting relation π (^) L (R) and relation S schema compatible, then the maximum possible tuples is n. This happens when all of the tuples of π (^) L (R) are not in S.
The minimum number of tuples that can appear in the result occurs when all of the tuples in πL (R) appear in S. Then the difference has max( n^ –^ m^ , 0) tuples.
Exercise 2.4.
Defining r as the schema of R and s as the schema of S:
- πr(R S)
- R δ(πr∩s (S)) where δ is the duplicate-elimination operator in Section 5.2 pg. 213
- R – (R – πr(R S))
Exercise 2.4.
Defining r as the schema of R
- R - πr(R S)
Exercise 2.4.
πA1,A2…An(R S)
Exercise 2.5.1a
σspeed < 2.00 AND price > 500(PC) = ø
Model 1011 violates this constraint.
Exercise 2.5.1b
σscreen < 15.4 AND hd < 100 AND price ≥ 1000 (Laptop) = ø
Model 2004 violates the constraint.
Exercise 2.5.1c
πmaker (σtype = laptop(Product))^ ∩^ πmaker (σ^ type = pc (Product)) = ø
Manufacturers A,B,E violate the constraint.
Exercise 2.5.1d
This complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R4 that stand for nodes of expression trees. Here is the sequence:
R1(maker, model, speed) := π (^) maker,model,speed(Product PC) R2(maker, speed) := π (^) maker,speed (Product Laptop) R3(model) := π (^) model (R1 (^) R1.maker = R2.maker AND R1.speed ≤ R2.speed R2) R4(model) := π (^) model (PC) 2 2 The constraint is R4 (^) 8 6R Manufacturers B,C,D violate the constraint.
Exercise 2.5.1e
πmodel (σLaptop.ram > PC.ram AND Laptop.price ≤ PC.price (PC × Laptop)) = ø
Models 2002,2006,2008 violate the constraint.
Exercise 2.5.2a
πclass(σ (^) bore > 16(Classes)) = ø
The Yamato class violates the constraint.
Exercise 2.5.2b
πclass(σ^ numGuns > 9 AND bore > 14(Classes)) = ø
No violations to the constraint.
Exercise 2.5.2c
This complex expression is best seen as a sequence of steps in which we define temporary relations R1 through R5 that stand for nodes of expression trees. Here is the sequence:
R1(class,name) := π (^) class,name (Classes Ships) R2(class2,name2) := ρ (^) R2(class2,name2)(R1) R3(class3,name3) := ρ (^) R3(class3,name3)(R1) R4(class,name,class2,name2) := R1 (^) (class = class2 AND name <> name2) R R5(class,name,class2,name2,class3,name3) := R4 (^) (class=class3 AND name <> name3 AND name2 <> name3) R
