Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
33 Cards in this Set
- Front
- Back
Define SQL |
Structured Query Language |
|
Define Nested Query |
A query within another SQL query. This is embedded through the WHERE clause. |
|
Define Redundancy |
When data is stored in two or more places, causing the data to become redundant.
|
|
Define Functional Dependency |
A constraint between two sets of attributes in a relation from a database.
|
|
Define Second Normal Form (2NF) |
If a relation is in 2NF if it is in 1NF and no non-prime attribute is dependent on any proper subset of a candidate key of the relation. |
|
Create a table PERSON with the Primary Key name, and attributes address, dname where dname is foreign key back to DEPT.
|
create table PERSON ( name VARCHAR(25) PRIMARY KEY NOT NULL, address VARCHAR(255), dname VARCHAR(10), foreign key (dname) references DEPT(dname) on delete set null on update cascade ); |
|
Insert a person into PERSON
|
insert into PERSON(name, address, dname) values ('Corwin', '38 Carleton Road', 'CompSci');
|
|
delete a person from PERSON |
delete from PERSON where name='Corwin';
|
|
update a person in PERSON
|
update PERSON set dept='Math' where name='Corwin';
|
|
Given relation R(A,C,E,G, I,K,M,O,R, T, V ) and the set of functional dependenciesFD = {CMO -> EIKTV, E -> A, C -> K, V -> RG, CM -> IV } Find a canonical cover for R. |
CMO->ET E->A C->K V->RG CM->IV |
|
Given relation R(A,C,E,G, I,K,M,O,R, T, V ) and the set of functional dependenciesFD = {CMO -> EIKTV, E -> A, C -> K, V -> RG, CM -> IV } Is CMO -> R a full functional dependency? Explain. |
No. CMO->R is a partial dependency, by definition, since O can be removed from CMO and the dependency still holds. |
|
Given relation R(A,C,E,G, I,K,M,O,R, T, V ) and the set of functional dependenciesFD = {CMO -> EIKTV, E -> A, C -> K, V -> RG, CM -> IV } Find a primary key for R. |
{CMO}+->{CMOEIKTVARG} Check minimal: {CM}+->{CMIVRGK} {MO}+->{MO} {CO}+->{COK} |
|
Given relation R(A,C,E,G, I,K,M,O,R, T, V ) and the set of functional dependenciesFD = {CMO -> EIKTV, E -> A, C -> K, V -> RG, CM -> IV } Decompose R into 3NF using your canonical cover above. |
|
|
Define First Normal Form (1NF) |
A relation is in first normal for if and only if the domain of each attribute contains only atomic values. |
|
Define Third Normal Form (3NF) |
A relation is in 3NF if it is 2NF and every non-prime attribute of R is non-transitively dependent on every key of R. |
|
Define Canonical Cover |
A set of functional dependencies that is minimal with each left hand side being unique and has no redundant attributes. |
|
Movie ( mID, title, year, director ) Reviewer ( rID, name ) Rating ( rID, mID, stars, ratingDate ) Find the titles of all movies directed by Steven Spielberg. |
select title from Movie where director="Steven Spielberg"; |
|
Movie ( mID, title, year, director ) Reviewer ( rID, name ) Rating ( rID, mID, stars, ratingDate ) Find all years that have a movie that received a rating of 4 or 5, and sort them in increasing order. |
select distinct year from Movie, Rating where Movie.mID = Rating.mID and Rating.stars >= '4'; |
|
Movie ( mID, title, year, director ) Reviewer ( rID, name ) Rating ( rID, mID, stars, ratingDate ) Find the titles of all movies that have no ratings. |
select title from Movie where not exists ( select mID from Rating where Movie.mID = mID); |
|
Movie ( mID, title, year, director ) Reviewer ( rID, name ) Rating ( rID, mID, stars, ratingDate ) Some reviewers didn't provide a date with their rating. Find the names of all reviewers who have ratings with a NULL value for the date. |
select name from Reviwer, Rating where Reviewer.rID=Rating.rID and ratingDate IS NULL; |
|
Movie (mID, title, year, director) Reviewer (rID, name) Rating(rID, mID, stars, ratingDate) Write a query to return the ratings data in a more readable format: reviewer name, movie title, stars, and ratingDate. Also, sort the data, first by reviewer name, then by movie title, and lastly by number of stars. |
select name, title, stars, ratingDate from Reviewer, Rating, Movie where Reviewer.rID=Rating.rID and Rating.mID=Movie.mID order by name, title, stars; |
|
Movie ( mID, title, year, director ) Reviewer ( rID, name ) Rating( rID, mID, stars, ratingDate ) For all cases where the same reviewer rated the same movie twice and gave it a higher rating the second time, return the reviewer's name and the title of the movie. |
select name, title from Movie M, Rating R, Reviewer REV where REV.rID=R.rID and M.mID=R.mID and exists ( select mID from Rating where mID=R.mID and rID=REV.rID and stars>R.stars and ratingDate>R.ratingDate); |
|
Movie (mID, title, year, director) Reviewer (rID, name) Rating(rID, mID, stars, ratingDate) For each movie that has at least one rating, find the highest number of stars that movie received. Return the movie title and number of stars. Sort by movie title. |
select distinct title, stars from Movie M, Rating R, Reviewer REV where R.mID=M.mID and stars in ( select MAX(stars) from Rating where mID=M.mID ); |
|
Movie (mID, title, year, director) Reviewer (rID, name) Rating(rID, mID, stars, ratingDate) List movie titles and average ratings, from highest-rated to lowest-rated. If two or more movies have the same average rating, list them in alphabetical order. |
select distinct title, AVG(stars) as S from Movie, Rating where Movie.mID=Rating.mID group by title order by S desc; |
|
Movie (mID, title, year, director) Reviewer (rID, name) Rating(rID, mID, stars, ratingDate) Find the names of all reviewers who have contributed three or more ratings. (As an extra challenge, try writing the query without HAVING or without COUNT.) |
select name from Reviewer, Rating where Reviewer.rID=Rating.rID group by name having COUNT(name)>2; |
|
Movie (mID, title, year, director) Reviewer (rID, name) Rating(rID, mID, stars, ratingDate) Find the names of all reviewers who rated Gone with the Wind. |
select distinct name from Reviewer, Rating, Movie where Reviewer.rID=Rating.rID and Rating.mID=Movie.mID and Movie.title="Gone with the Wind"; |
|
Movie (mID, title, year, director) Reviewer (rID, name) Rating(rID, mID, stars, ratingDate) For any rating where the reviewer is the same as the director of the movie, return the reviewer name, movie title, and number of stars. |
select distinct name, title, stars from Movie, Reviewer, Rating where Reviewer.rID=Rating.rID and Movie.mID=Rating.mID and Reviewer.name=Movie.director; |
|
Movie (mID, title, year, director) Reviewer (rID, name) Rating(rID, mID, stars, ratingDate) Return all reviewer names and movie names together in a single list, alphabetized. (Sorting by the first name of the reviewer and first word in the title is fine; no need for special processing on last names or removing "The".) |
select name from Reviewer union select title from Movie; |
|
Movie (mID, title, year, director) Reviewer (rID, name) Rating(rID, mID, stars, ratingDate) Find the titles of all movies not reviewed by Chris Jackson. |
select title from Movie where not exists ( select name from Reviewer, Rating where Reviewer.rID=Rating.rID and Rating.mID=Movie.mID and Reviewer.name="Chris Jackson" ); |
|
Movie (mID, title, year, director) Reviewer (rID, name) Rating(rID, mID, stars, ratingDate) For all pairs of reviewers such that both reviewers gave a rating to the same movie, return the names of both reviewers. Eliminate duplicates, don't pair reviewers with themselves, and include each pair only once. For each pair, return the names in the pair in alphabetical order. |
select R.name, S.name from Reviewer R, Reviewer S, Rating R1, Rating R2 join Reviewer S on R.name<S.name where R.name!=S.name and R1.rID=R.rID and R2.rID=S.rID and R1.mID=R2.mID group by R.name, S.name; |
|
Movie (mID, title, year, director) Reviewer (rID, name) Rating(rID, mID, stars, ratingDate) For each rating that is the lowest (fewest stars) currently in the database, return the reviewer name, movie title, and number of stars. |
select name, title, stars from Reviewer, Movie, Rating where Reviewer.rID=Rating.rID and Rating.mID=Movie.mID and stars=(select min(stars) from Rating); |
|
How do you find the canonical cover for a set of functional dependencies? |
Apply decomposition rule to each functional dependency (1attribute on RHS) Remove redundant LHS attributes Remove redundant functional dependencies Apply union to make functional dependencies with unique LHS |
|
How to find 3NF from Canonical Cover |
For each functional dependency X->Y in the cover, create a relation R(X,Y). If Y is in the LHS of another functional dependency, it is likely a foreign key in R. If no R contains the primary key of R, create a relation with these attributes. |