COP4710, FSU: Schwartz Midterm
How do you use insert statements to modify data?
INSERT INTO product VALUES ('c', '1100', 'pc');
What are tuples?
the rows of each relation, not including the first row
What are the nontrivial FD's of: AB -> C, C -> D, D-> A?
A -> C, B -> C, C -> D, D-> A
What is a database schema?
A description of the data and the organization of the data into tables in a relational database Example: Accounts( type: string. acctNo: integer, balance: integer )
What are the keys of: AB -> C, C -> D, D-> A?
AB
What are the super keys of: AB -> C, C -> D, D-> A?
AB, AC, AD, because they cover up all the variables
How do you write an expression of relational algebra to add an attribute?
ALTER TABLE laptop ADD od;
How do you write an expression of relational algebra to delete an attribute?
ALTER TABLE printer DROP color;
What are the requirements to satisfy 3rd normal form?
All requirements for 2nd NF must be met. Eliminate fields that do not depend on the primary key; That is, any field that is dependent not only on the primary key but also on another field must be moved to another table
How do you write an expression of relational algebra to create a table?
CREATE TABLE product( maker CHAR(25), model CHAR(15) PRIMARY KEY, type CHAR(20) key, );
What is an equivalent way to represent relations?
Changing the order of the attributes
How do you use delete statements to modify data?
DELETE FROM laptop WHERE hd < 100;
What are non trivial function dependencies?
If there is at least one attribute on the right hand side that is not part of the left hand side example: AB -> C AB -> AC
What is a relation schema?
Relation title(attributes listed) Example: Accounts(type, acctNo, balance)
What is the difference between natural join and theta join?
Theta join allows for arbitrary comparison relationships & natural join is a theta join w/ an equality on common columns and removes duplicate columns
How do you use update statements to modify data?
UPDATE product SET maker = 'A' WHERE maker = 'B';
What is the difference between a bag and a set?
a bag displays duplicates and a set does not
What is a super key?
a set of attribute that contain a key, short for superset of a key - thus every key is a super key
What would appear following: Studio CROSS JOIN MovieExec?
all the attributes of studio and movieExec: R(name, address, cert#, networth, presc#)
What is a weak entity set?
an entity set's key is composed of attributes, where some or all belong to another entity set
What is a domain for an attribute?
describes the type of each attribute Example: Accounts(type: string, acctNo: integer, balance: integer)
How do you set a default when writing an expression of relational algebra?
od CHAR(10) DEFAULT 'none';
How do you write an expression of relational algebra for: "Find the model numbers of all color laser printers"?
r1 = σ color = true AND type = laser (printer) r2 = ∏ model(r1)
How do you write an expression of relational algebra for: "which manufacturers make laptops with a hard disk of at least 100 GB"?
r1 = σ hd >= 100(Laptop) r2 = product X (r1) r3 = ∏ maker(r2)
How do you write an expression of relational algebra for: "Find the model number and price of all products made by manufacturer B"?
r1 = σ maker = B(product X PC) r2 = σ maker = B(product X Laptop) r3 = σ maker = B(product X Printer) r4 = ∏ model, price(r1) r5 = ∏ model, price(r2) r6 = ∏ model, price (r3) r7 = r4 U r5 U r6
Part 1: How do you write an expression of relational algebra for: "What PC models have a speed of at least 3.00"?
r1 = σ speed >= 3.00 (PC) r2 = ∏ model(r1)
How do you write an expression of relational algebra for: "Find those manufacturers that sell laptops, but not PC's"?
r1 = σ type(Laptops) r2 = σ type(PC) r3 = ∏ maker(r1) r4 = ∏ maker(r2) r5 = r3-r4
How would you find the different speed and the average price of a pc?
select avg(distinct price) from pc group by speed;
How would you find the average speed of PC's?
select avg(speed) from PC
How do you make sure duplicates are eliminated in: select model from pc where speed >= 3.00?
select distinct model from pc where speed >= 3.0;
How would you write a query to find the maker with the highest price using ALL?
select p1.model from printer p1 where p1.price >= ALL (select p2.price from printer p2);
What would some functional dependencies be for: street address, city, state, zip code, name, social security number, area code, and phone number? What is the primary key?
street address city -> state street address state -> zip code SSN -> name SSN city -> state SSN state -> area code SSN name -> phone number *Primary key = SSN*
What are attributes?
the first row Example: type, acctNo, balance
When is a functional dependency considered trivial?
the right side is a subset of its left side example: AB -> A AB -> B AB -> AB
What would appear following: Starsin NATURAL FULL OUTER JOIN Moviestar?
the tuples that are both in starsin and moviestar the tuples that are only in starsin and not moviestar the tuples are are only in moviestar and not starsin
Consider a relation representing the present position of molecules in a closed container. The attributes are an ID for the molecule, the x, y,and z coordinates of the molecule, and its velocity in the x, y, and z dimensions. What FD's would you expect to hold? What are the keys?
x -> velocity x y -> velocity y z -> velocity z ID -> x, y, z ID -> velocity x, velocity, y, velocity z Keys: x, y, z, velocity x, velocity y, velocity z Super keys: ID, x, y, z
How would you write a constraint for "A PC processor speed less than 2.00 must not sell for more than $500"?
σ processor speed < 2.00 AND price > 500 (PC) = ∅
Draw an expression tree for part 2.
∏ maker -> X ->-> Product & σ hd >= 100 -> laptop
Draw an expression tree for part 1.
∏ model -> σ speed >= 3.00 -> PC