Exam

Question 1

RELATIONSHIP TABLES

in a 1 to many relationship what do we do with the entities and the relationship?

Answer 1

combine the the 1 with the relationship (i.e. the one with the arrow)

Question 2

RELATIONSHIP TABLES

how are participation constraints manifested as in tables?

Answer 2

by not nulls

Question 3

RELATIONSHIP TABLES

how are key/cardinality constraints manifested?

Answer 3

by foreign keys

Question 4

RELATIONSHIP TABLES

how are binary relationships manifested?

Answer 4

their own table with a composite primary key that reflects the two different types of relationships they describe

EX. reports_to has supervisor/subordinate so the primary key is supervisor_ssn,subordinate_ssn which are foreign keys of the parent entity

Question 5

RELATIONSHIP TABLES

how are weak entities displayed?

Answer 5

a weak entity combines the entity with the relationship it is defined by and then uses a partial key composed of its local key and the primary key of the connected entity(s) (also ON DELETE CASCADE/UPDATE)

Question 6

RELATIONSHIP TABLES

if an entity has a participation constraint does it always require its own table?

Answer 6

not always

Question 7

ER DIAGRAMS

“must participate”

Answer 7

this means a participation constraint or a thick line connecting the two elements

Question 8

ER DIAGRAMS

“X is related to NO MORE than ONE Y”

Answer 8

key constraint - draw an arrow pointing to Y from X

Question 9

ER DIAGRAMS

what is a cardinality constraint?

Answer 9

look for arrows and their can only be one of those elements in a relationship between them

Question 10

ER DIAGRAMS

what is a participation constraint?

Answer 10

look for thick lines. entities connected must be paired as tuples at all times

Question 11

ER DIAGRAMS

thick line

Answer 11

> 1

Question 12

ER DIAGRAMS

arrow

Answer 12

< 1

Question 13

FDs/NORMAL FORMS

define 3NF

Answer 13

where all FDs have either the superkey on the LHS OR their RHS is part of the superkey

Question 14

FDs/NORMAL FORMS

How do we synthesize 3NF?

Answer 14

1) reduce to single attribute RHS
2) check LHS to see if every attribute is actually necessary for the FD
3) remove redundant rules (check closures to see/remove repeats)
4) take completed FDs and write out as R1(xy) R2(yz)…

Question 15

FDs/NORMAL FORMS

define BCNF

Answer 15

where all FDs are strictly have the superkey on the LHS

Question 16

FDs/NORMAL FORMS

how do we decompose to BCNF?

Answer 16

1) check which FDs are not in BCNF and decompose on the ones which are not
2) Decompose R into two subschemas, one with the offending FD attributes, the other with the original minus the offending
3) now check those subschemas as if they were their own relations, and iterate over and over until both satisfy BCNF

Question 17

RA/DATALOG

What is division used for?

Answer 17

“find all of that have RESERVED ALL BOATS” for example

Question 18

SQL

what does WHERE IN do?

Answer 18

It looks for the given attribute in the results of the subquery.

ex. “Find the who did X”

Question 19

SQL

WHERE EXISTS does what?

Answer 19

Checks whether a given set is empty or not

“Find the blank of blanks who have done x”

Question 20

SQL

ANY does what?

Answer 20

finds a random example of the subquery that follows it

Question 21

SQL

ALL does what?

Answer 21

finds every example of the subquery that follows it

Question 22

SQL

WHERE NOT EXISTS does what?

Answer 22

returns true if the subquery doesn’t satisfy the result

Question 23

SQL

COUNT ((DISTINCT) A) does what?

Answer 23

the number of values in column A

Question 24

SQL

GROUP BY

Answer 24

Collects the query results by the given attribute

Question 25

SQL

HAVING does what?

Answer 25

condition to satisfy for given query group

Question 26

DATALOG

How do you do joins?

Answer 26

atoms with specific named attributes

Question 27

Find the names of bookstores that have sold EVERY book which is old by the bookstore named chapters

Answer 27

use WHERE NOT EXISTS looking for all ids from the sold table where the name is chapters. EXCEPT those where the sold name is the same as the bookstore name.

Question 28

Find the names of the bookstores that have sold at least 100 different books published after year 2000

Answer 28

do a join of Sold and Book and check the year in the WHERE clause. use GROUP BY name and HAVING count (distinct id) >= 100

Question 29

if you see a not atom in datalog how is it written in SQL

Answer 29

WHERE NOT EXISTS clause

Question 30

Find the names of sailors with a higher rating that ALL sailors with age < 21

Answer 30

use WHERE NOT EXISTS subquery where the subquery’s age is less than 21 and S.rating <= S2. rating

Question 31

Find the title and year of those albums that are sold in every store in Toronto

Answer 31

use WHERE NOT EXISTS where you select the stores where city is toronto EXCEPT the the sold albums that share ids with the original query

the first inner query should be WHERE NOT EXISTS and get the store ids of all stores in toroonto. the bottom inner query (the EXCEPT) should get the store ids where the album was sold and this is connected ot the initial query by S.aid=A.aid. if this set difference is empty, then every store in toronto had the album.

Question 32

Find the names of all juniors who are enrolled in a class taught by I. Teach

Answer 32

just a WHERE clause with the appropriate joins and selections

Question 33

Find the age of the oldest student who is either a History major or enrolled in a course taught by I. Teach.

Answer 33

SELECT MAX(S.age) and a normal WHERE clause for history and then and OR statement with a subquery that is close to the previous question

Question 34

Find the names of all classes that either meet in room R128 or have 5+ students enrolled

Answer 34

simple WHERE ORd with IN subquery. subquery GROUPSBY name with a HAVING clause of 5 or more)

Question 35

Find the names of faculty members who teach in every room in which some class is taught

Answer 35

You know to use WHERE NOT EXISTS to find all classes and then an except clause to find the classes in whcih the faculty members teach