When writing and submitting your assignments follow these requirements:
Name your Eclipse project with your Network login, an underscore, 'a' (for
'assignment', then the assignment number: login_en.zip.
For example, if the user barn4520 submits Assignment 1, the
name should be: barn4520_a01. Give your .zip file the same
name when exporting your project. The project above would be exported in a
.zip file named barn4520_a01.zip. Use only Eclipse's built-in
archive capability to create these .zip files. No other format will be
accepted.
Some assignments consist of multiple Eclipse projects where one project
references another. Use the same naming scheme for all your projects.
Thus, a project named data_structures should be barn4520_data_structures.
Test your programs thoroughly - show us how it works. Copy the outputs
from your testing to a file in your Eclipse/Pydev project named testing.txt.
Make sure the tests are well labelled so that the markers know which
program the results represent.
There should be no errors or warnings in your code. Watch out particularly for bad or unused imports - these generate warnings and must be removed.
If you are asked to provide discussion put this discussion in your
Eclipse/Pydev project in a file named discussion.txt.
Put all your programs, testing, and discussion files into one Eclipse/Pydev project.
Zip the entire project using Eclipse and submit the resulting .zip file through MyLearningSpace.
Marks will be deducted from any questions where these requirements are not met.
Follow the assignment instructions to the letter (e.g. name the function
module functions.py), as this assignment is auto-graded. If
your Eclipse project is not defined correctly, the auto-grading fails, and
your assignment will be given a mark of 0.
Pages of use for this assignment:
These questions use the DCRIS database.
These functions return lists of tuples containing the results of the
various database calls. How you display these results is up to you.
Do not call print from within the functions. Do not
close the database from within the functions. It is the responsibility
of whatever opens the database to close the database.
Assignment 1 dealt with single tables and views, Assignment 2 deals with joined tables and aggregation. You may not work with views - you must provide the appropriate joins.
We suggest you use Pythons triple quotes to make long SQL statements more readable:
sql = """SELECT ...
FROM ...
JOIN ...
ON ...
WHERE ...
ORDER BY ..."""
Write and test the following function:
def get_member_publications(cursor, pubTitle=None, pubType=None):
"""
-------------------------------------------------------
Queries the pub and member tables.
Use: rows = get_member_publications(cursor)
Use: rows = get_member_publications(cursor, pubTitle=v1)
Use: rows = get_member_publications(cursor, pubType=v2)
Use: rows = get_member_publications(cursor, pubTitle=v1, pubType=v2)
-------------------------------------------------------
Parameters:
cursor - a database cursor (cursor)
pubTitle - a partial pubTitle (str)
pubType - a single letter publication type (str)
Returns:
rows - (list of member's last name, member's first
name, the title of a publication, and the full publication
type (i.e. 'article' rather than 'a') data)
if pubTitle and/or pubType are not None:
rows containing pubTitle and/or pubType
else:
all member and publication rows
Sorted by last name, first name, publication title
-------------------------------------------------------
"""
Add this function to the PyDev module functions.py. Test it
from t01.py.
Write and test the following function:
def get_publication_counts(cursor, memberId=None, pubType=None):
"""
-------------------------------------------------------
Queries the pub and member tables.
Use: rows = get_publication_counts(cursor)
Use: rows = get_publication_counts(cursor, memberId=v1)
Use: rows = get_publication_counts(cursor, pubType=v2)
Use: rows = get_publication_counts(cursor, memberId=v1, pubType=v2)
-------------------------------------------------------
Parameters:
cursor - a database cursor (cursor)
memberId - a member ID number (int)
pubType - a publication type (str)
Returns:
rows - (list of member's last name, member's first
name, and the number of publications of type
pubType data)
if memberId or pubType is not None:
rows containing memberId and/or pubType
else:
all member names and publication counts
Sorted by last name, first name
-------------------------------------------------------
"""
Add this function to the PyDev module functions.py. Test it
from t02.py.
As examples, asking for the number of books written by William Bain returns:
[('Bain', 'William', 4)]
Asking for the number of any kind of publication written by William Bain returns:
[('Bain', 'William', 6)]
Write and test the following function:
def get_broad_counts(cursor, memberId=None):
"""
-------------------------------------------------------
Queries the member and broad tables.
Use: rows = get_broad_counts(cursor)
Use: rows = get_broad_counts(cursor, memberId=v1)
-------------------------------------------------------
Parameters:
cursor - a database cursor (cursor)
memberId - a member ID number (int)
Returns:
rows - (list of member's last name, member's first
name, and the number of broad expertises they hold data)
if memberId is not None:
rows containing memberId
else:
all member and broad expertise rows
Sorted by last name, first name
-------------------------------------------------------
"""
Add this function to the PyDev module functions.py. Test it
from t03.py.
For example, Terry Copp has two broad expertises:
[('Copp', 'Terry', 2)]
Write and test the following function:
def get_all_expertises(cursor, memberId=None):
"""
-------------------------------------------------------
Queries the member, broad, and narrow tables
Use: rows = get_all_expertises(cursor)
Use: rows = get_all_expertises(cursor, memberId=v1)
-------------------------------------------------------
Parameters:
cursor - a database cursor (cursor)
memberId - a member ID number (int)
Returns:
rows - (list of member's last name, member's first
name, a broad description, and a narrow description data)
if memberId is not None:
rows containing memberId
else:
all member and expertise rows
Sorted by last name, first name, broad description, narrow
description
-------------------------------------------------------
"""
Add this function to the PyDev module functions.py. Test it
from t04.py.
Narrow expertises are subsets of broad expertises. Thus "Cold War" is a subset of "Military History", and "Canada" is a subset of "Geographic Focus". (See the second selection box in: DCRIS Search by Expertise for a list of these relationships.) This function should then return these relationships with respect to a Member.
For example, Terry Copp has, among other expertises and supplementary expertises:
[… ('Copp', 'Terry', 'Military History', 'First World War') …]