Cpsc3660 Final Cards - Group Set

Front 1

Define SQL

Back 1

Structured Query Language

Front 2

Define Nested Query

Back 2

A query within another SQL query. This is embedded through the WHERE clause.

Front 3

Define Redundancy

Back 3

When data is stored in two or more places, causing the data to become redundant.

Front 4

Define Functional Dependency

Back 4

A constraint between two sets of attributes in a relation from a database.

Front 5

Define Second Normal Form (2NF)

Back 5

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.

Front 6

Create a table PERSON with the Primary Key name, and attributes address, dname where dname is foreign key back to DEPT.

Back 6

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

);

Front 7

Insert a person into PERSON

Back 7

insert into PERSON(name, address, dname) values ('Corwin', '38 Carleton Road', 'CompSci');

Front 8

delete a person from PERSON

Back 8

delete from PERSON where name='Corwin';

Front 9

update a person in PERSON

Back 9

update PERSON set dept='Math' where name='Corwin';

Front 10

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.

Back 10

CMO->ET

E->A

C->K

V->RG

CM->IV

Front 11

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.

Back 11

No. CMO->R is a partial dependency, by definition, since O can be removed from CMO and the dependency still holds.

Front 12

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.

Back 12

{CMO}+->{CMOEIKTVARG}

Check minimal:

{CM}+->{CMIVRGK}

{MO}+->{MO}

{CO}+->{COK}

Front 13

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.

Front 14

Define First Normal Form (1NF)

Back 14

A relation is in first normal for if and only if the domain of each attribute contains only atomic values.

Front 15

Define Third Normal Form (3NF)

Back 15

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.

Front 16

Define Canonical Cover

Back 16

A set of functional dependencies that is minimal with each left hand side being unique and has no redundant attributes.

Front 17

Movie ( mID, title, year, director )

Reviewer ( rID, name )

Rating ( rID, mID, stars, ratingDate )

Find the titles of all movies directed by Steven Spielberg.

Back 17

select title

from Movie

where director="Steven Spielberg";

Front 18

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.

Back 18

select distinct year

from Movie, Rating

where Movie.mID = Rating.mID and Rating.stars >= '4';

Front 19

Movie ( mID, title, year, director )

Reviewer ( rID, name )

Rating ( rID, mID, stars, ratingDate )

Find the titles of all movies that have no ratings.

Back 19

select title

from Movie

where not exists (

select mID

from Rating

where Movie.mID = mID);

Front 20

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.

Back 20

select name

from Reviwer, Rating

where Reviewer.rID=Rating.rID and ratingDate IS NULL;

Front 21

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.

Back 21

select name, title, stars, ratingDate

from Reviewer, Rating, Movie

where Reviewer.rID=Rating.rID and

Rating.mID=Movie.mID

order by name, title, stars;

Front 22

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.

Back 22

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);

Front 23

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.

Back 23

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

);

Front 24

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.

Back 24

select distinct title, AVG(stars) as S

from Movie, Rating

where Movie.mID=Rating.mID

group by title

order by S desc;

Front 25

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.)

Back 25

select name

from Reviewer, Rating

where Reviewer.rID=Rating.rID

group by name

having COUNT(name)>2;

Front 26

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.

Back 26

select distinct name

from Reviewer, Rating, Movie

where Reviewer.rID=Rating.rID and Rating.mID=Movie.mID and Movie.title="Gone with the Wind";

Front 27

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.

Back 27

select distinct name, title, stars

from Movie, Reviewer, Rating

where Reviewer.rID=Rating.rID and Movie.mID=Rating.mID and Reviewer.name=Movie.director;

Front 28

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".)

Back 28

select name

from Reviewer

union

select title

from Movie;

Front 29

Movie (mID, title, year, director)

Reviewer (rID, name)

Rating(rID, mID, stars, ratingDate)

Find the titles of all movies not reviewed by Chris Jackson.

Back 29

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"

);

Front 30

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.

Back 30

select R.name, S.name

from Reviewer R, Reviewer S, Rating R1, Rating R2

join Reviewer S on R.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;

Front 31

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.

Back 31

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);

Front 32

How do you find the canonical cover for a set of functional dependencies?

Back 32

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

Front 33

How to find 3NF from Canonical Cover

Back 33

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.

Front 34

What are Armstrongs Rules of Inference?

Back 34

1 - Reflexivity

2 - Augmentation

3 - Union

4 - Decomposition

5 - Transitivity

6 - Pseudo Transitivity

Front 35

What does it mean to be Reflexive?

Back 35

If Y C X, then X -> Y

Front 36

What is augmentation?

Back 36

X->Y => XZ->YZ

Front 37

What is transitive?

Back 37

X->Y, Y->Z => X->Z

Front 38

What is decomposition?

Back 38

X->YZ => X->Y

Front 39

What is Union?

Back 39

X->Y, X->Z => X->YZ

Front 40

What is Pseudo-transitive?

Back 40

X->Y, WY->Z => WX -> Z