Hello, if you have any need, please feel free to consult us, this is my wechat: wx91due
CS 105 Practice Problems
Question 1: Definitions (XX points)
(a) (XX points) What is an attribute of a Table?
(b) (XX points) What is a Relation in a Database?
(d) (XX points) What is a Database Management System (DMBS)?
Question 2: Keys (XX points)
(a) (XX points) What is a key?
(b) (XX points) Draw the relationship between keys, superkeys, and candidate keys.
(c) (XX points) Can a superkey contain attributes which, by themselves, do not guarantee uniqueness?
(d) (XX points) If we know a superkey contains three attribute {a1 , a2 , a3 }, how do we determine if this superkey is a candidate key?
(e) (XX points) What is a primary key?
Question 3: Schemas (XX points)
(a) (XX points) Why do we need to know the primary keys for each Relation?
(b) (XX points) Consider the following Schema:
Suppliers(sid: int , sname: str, address: str) Parts(pid: int , pname: str, color: str)
We want our Database to be able to relate Parts with Suppliers. We know that one type of part can be supplied by more than one supplier, and that one supplier can supply more than one part. Should we add a new Relation to our Schema or modify the existing Relations?
(c) (XX points) Consider the following Schema:
Video(video id , title, size (bytes), length (seconds), num upvotes, num downvotes, mp4 path)
Account(account id , account name, num followers) Published(account id, video id)
Subscription(subscriber account id, subscribee account id) Write out each of the foreign key contraints.
(d) (XX points) Design a Schema that should contain the following information: We want to design a library database, where we need to keep track of books, authors, and authorships. Each book has a title, a genre, an author, and a number of pages. Each author has a first name, a last name, a date of birth (can be NULL), and a date of death (can be NULL). A book is written (authored) by only one author in our library as well as the date it was authored. Be as efficient as you can!
(e) (XX points) Modify your Schema from part (d) to contain library accounts as well as accounts checking out books. Let us pretend that there is only one copy of each book in the library to keep things simple. Draw the complete schema.
Question 4: Relational Algebra (XX points)
Draw the result Table for each of the queries below if they were executed on the following Table T:
(a) (XX points) σage≤80∩age≥40(T)
(b) (XX points) πage(σfirst name = “James”∪last name = “James”
(T))
(c) (XX points) If Relation R1 has m rows and Relation R2 has n rows, how many rows does R1 × R2 have?
(d) (XX points) If Relation R1 has m rows and Relation R2 has n rows (and they are union compatible), what are the maximum and minimum number of rows for Relation R1 U R2?
(e) (XX points) If Relation R1 has m rows and Relation R2 has n rows (and they are union compatible), what is the number of rows for Relation R1 ∩ R2?
Please consider the following Relations for the next few questions:
(f) (XX points) Which Relations are union compatible?
(g) (XX points) Please draw the result of the following query: CurrentMovieCheckoutuMovieCheckoutHistory
(h) (XX points) Please draw the result of the following query: CurrentMovieCheckout Movie
(i) (XX points) Please draw the result of the following query: User CurrentMovieCheckout Movie
(j) (XX points) Please draw the result of the following query:
πtitle(σfname=“Andrew′′ (User CurrentMovieCheckout Movie))
Please consider the following Schema for the next few questions:
Video(video id , title, size (bytes), length (seconds), num upvotes, num downvotes, mp4 path) Account(account id , account name, num followers)
Published(account id, video id)
Subscription(subscriber account id, subscribee account id)
VideoComment(comment id , account id, video id, text) Conversation(comment id, next comment id)
(k) (XX points) Write the relational algebra expression to find the names of accounts who have not published a video called “Astartes” .
(l) (XX points) Write the relational algebra expression to find the text of all comments made under the video called “Astartes” .
(m) (XX points) Write the relational algebra expression to find the text of comments on videos longer than 100 seconds.
(n) (XX points) Write the relational algebra expression to find the names of accounts who subscribe to the account with name “Andrew Wood” .
(o) (XX points) Write the relational algebra expression to find the ids of accounts who every account subscribes to (please assume that you automatically subscribe to yourself).
(p) (XX points) Write the relational algebra expression to find the ids of accounts who subscribe to “Andrew Wood” or to “Barack Obama” .
(q) (XX points) Write the relational algebra expression to find the ids of accounts to subscribe to both “Andrew Wood” and to “Barack Obama” .
(r) (XX points) Write the relational algebra expression to find the ids of accounts who subscribe to “Andrew Wood” but not to “Barack Obama” .
(s) (XX points) Write the relational algebra expression to find the ids of accounts with more than 30 followers.
Question 5: SQL (XX points)
(a) (XX points) Please translate question 4(k) into SQL.
(b) (XX points) Please translate question 4(l) into SQL.
(c) (XX points) Please translate question 4(m) into SQL.
(d) (XX points) Please translate question 4(n) into SQL.
(e) (XX points) Please translate question 4(p) into SQL.
(f) (XX points) Please translate question 4(q) into SQL.
(g) (XX points) Please translate question 4(r) into SQL.
(h) (XX points) Please translate question 4(s) into SQL.
Please consider the following Schema for the next few questions: Student(student id , first name, last name, age)
Enrolled(student id, course id , lecture time, room id) Room(room id , name, capacity)
MajorsIn(student id, department name)
(i) (XX points) Write the SQL query to find the rooms with the largest capacity.
(j) (XX points) Write the SQL query to find the rooms with the smallest capacity.
(k) (XX points) Write the SQL query to find the number of students in each major. Your query should produce two pieces of information: the name of each major, and the number of students in that major.
Question 6: Programming in Python (XX points)
(a) (XX points) Please consider the following code:
1 # some code before the conditional
2 . . .
3
4 if x % 2 == 0:
5 print (”x · 
is · 
even ! ”)
6 else :
7 print (”x · 
is · 
odd”)
8
9 # some code after the conditional
10 . . .
Please draw this code as a flowchart.
(b) (XX points) What is the syntax for creating a variable with a specific type?
(c) (XX points) What is a variable?
(d) (XX points) Please consider the following code:
1 def foo (x : int ) −> bool :
2 # some code
3 . . .
What information do we know about function foo?
(e) (XX points) Please consider the following code:
1 def bar (x : int ) −> bool :
2 return x % 2 == 0
What does function bar do? Justify your answer by showing the output for executing bar(4) and bar(3).
(f) (XX points) What is a function?
(g) (XX points) What is the syntax for an import statement and what functionality do they provide?
(h) (XX points) Please consider the following code:
1 def baz (x : int ) −> None :
2 for i in range(x ) :
3 print ( i )
Show the output for executing baz(5).
(i) (XX points) Please consider the following code:
1 from typing import List
2
3 def copy (x : List [ object ] ) −> List [ object ] :
4 new list : List [ object ] = list ()
5 idx : int = 0
6 while idx < len (x ) :
7 obj : object = x [ idx ]
8 new list . append( obj )
9 idx = idx + 1
10 return new list
Please trace copy([1, 3, 5]).
(j) (XX points) Please consider the following code:
1 from typing import List
2
3 def reverse (x : List [ object ] ) −> List [ object ] :
4 if len (x) == 0:
5 return list ()
6 # we know there is at least 1 element in x
7 obj : object = x [ 0 ]
8
9 # get the rest of the list
10 rest of x : List [ object ] = x [ 1 : ] 11
12 # reverse the rest of the list
13 reversed rest of x : List [ object ] = reverse ( rest of x ) 14
15 # add obj to the end (was at front , place at end)
16 reversed rest of x . append( obj )
17
18 # return answer
19 return reversed rest of x
Please trace reverse(["a", "b"]).
(k) (XX points) Please consider the following code:
1 from typing import List
2
3 def fizzbuzz (x : List [ int ] ) −> List [ str ] :
4 out : List [ str ] = list ()
5 for e in x :
6 if ( e % 5 == 0) and ( e % 3 == 0):
7 out . append(” fizzbuzz ”)
8 el if ( e % 3 == 0):
9 out . append(” fizz ”)
10 el if ( e % 5 == 0):
11 out . append(”buzz”)
12 else :
13 out . append(”buzz”)
14 return out
Please draw this code as a flowchart.
(l) (XX points) Please trace fizzbuzz([1,3,5,15]) from the previous problem.
(m) (XX points) Please consider the followign code:
1 def bat (x : List [ int ] ) −> int :
2 c : int = 0
3 idx : int = 0
4 while idx < len (x ) :
5 e : int = x [ idx ]
6 if e == 3:
7 c = c + 1
8 x = x + 1
9 return c
What does bat do?
(n) (XX points) Please consider the following code:
1 def inside out for ( l : List [ object ] ) −> List [ List [ object ] ] :
2 # out list must have an odd number of elements
3 if len ( l ) % 2 != 1:
4 return None # cant do it sorry !
5
6 # now we know that our list has an odd number of elements in it
7 middle element pos : int = int ( len ( l ) / 2)
8 result : List [ List [ object ] ] = [ ]
9
10 result . append ( [ l [ middle element pos ] ] )
11
12 for hops away in range(1 , middle element pos +1):
13 result . append ( [ l [ middle element pos − hops away ] , l [ middle element pos +
14
15 return result
Please trace inside out(["a", "b", "c", "d", "e"]).
(o) (XX points) What is a class?
(p) (XX points) Please consider the following code:
1 class Rectangle ( object ) :
2 def in it ( self , width : float , height : float ) −> None :
3 self . width : float = width
4 self . height : float = height
5
6 def area ( self ) −> float :
7 return self . width * self . height
8
9 def maxdim( self ) −> float :
10 if self . width > self . height :
11 return self . width
12 return self . height
13
14 r 1 : Re c t a n gl e = R e c t a n gl e ( 1 , 8 )
15 r2 : Rectangle = Rectangle (2 , 7)
Please draw r1 and r2 in memory.
(q) (XX points) Please consider the following code:
1 class Rectangle ( object ) :
2 def in it ( self , width : float , height : float ) −> None :
3 self . width : float = width
4 self . height : float = height
5
6 def area ( self ) −> float :
7 return self . width * self . height
8
9 def maxdim( self ) −> float :
10 if self . width > self . height :
11 return self . width
12 return self . height
13
14 r 1 : Re c t a n gl e = R e c t a n gl e ( 2 , 4 )
15 r2 : Rectangle = Rectangle (3 , 6)
16 a : float = r1 . area ()
17 d : float = r2 .maxdim()
What value does the argument self contain when the maxdim method is called in line 17?
(r) (XX points) What is a method?
(s) (XX points) What is a field? (also called an attribute)
(t) (XX points) What does the word instantiation mean?
Question 7: Data Mining (XX points)
(a) (XX points) Please consider an arbitrary data matrix A. What do the number of rows in A tell us?
(b) (XX points) Please consider an arbitrary data matrix A. What do the number of columns in A tell us?
(c) (XX points) Please consider the following supervised learning dataset. Let us assume that the goal is to predict whether or not to play outside.
Is this a balanced dataset?
(d) (XX points) How would we convert this data into a numeric representation?
(e) (XX points) What properties does a “good” feature have?
(f) (XX points) When we plot the singular values from svd, what do we look for to tell us how many dimensions to reduce to?
(g) (XX points) What does a singular value of 0 tell us?
(h) (XX points) How do we (the humans) know if our representation is good (for the machine)?
(i) (XX points) What is the difference between KMeans and KMediods clustering?
(j) What does the DBScan algorithm do?
(k) What is the difference between hard and soft clustering?
(l) Is KMeans an objective-based or an objective-less clustering algorithm?