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. This assignment deals with subqueries. You may use the DCRIS views.
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_all_pub_counts(cursor, memberId=None):
"""
-------------------------------------------------------
Queries the pub and member tables.
Use: rows = pub_counts(cursor)
Use: rows = pub_counts(cursor, memberId=v1)
-------------------------------------------------------
Parameters:
cursor - a database cursor (cursor)
memberId - a member ID (int)
Returns:
rows - (list of member's last name, a member's first
name, and the numbers of publications of each type data.
Name these three fields "articles", "papers", and "books")
if memberId is not None:
rows containing memberId
else:
all member and publication rows
Sorted by last name, first name
-------------------------------------------------------
"""
Add this function to the PyDev module functions.py. Test
it from t01.py.
Examples:
| memberSurname | memberForename | articles | papers | books |
|---|---|---|---|---|
| Bedeski | Robert | 1 | 2 | 0 |
(Do not return the headers.)
Write and test the following function:
def get_expertise_counts(cursor, memberId=None):
"""
-------------------------------------------------------
Use: rows = get_expertise_counts(cursor)
Use: rows = get_expertise_counts(cursor, memberId=v1)
-------------------------------------------------------
Parameters:
cursor - a database cursor (cursor)
memberId - a member ID number (int)
Returns:
rows - (list of member's last name, a member's first
name, and the number of broad and narrow expertises
for the member data. Name these fields "broadCount" and "narrowCount")
if memberId is not None:
rows containing memberId
else:
all member, broad, and narrow expertise rows
Sorted by last name, first name
-------------------------------------------------------
"""
Add this function to the PyDev module functions.py. Test
it from t02.py.
Examples:
| memberSurname | memberForename | broadCount | narrowCount |
|---|---|---|---|
| Bedeski | Robert | 6 | 5 |
(Do not return the headers.)
Write and test the following function:
def get_broad_counts(cursor, broadId=None):
"""
-------------------------------------------------------
Use: rows = get_broad_counts(cursor)
Use: rows = get_broad_counts(cursor, broadId=v1)
-------------------------------------------------------
Parameters:
cursor - a database cursor (cursor)
broadId - a keyword ID number (int)
Returns:
rows - (list of a broad expertise descriptions and the number of
narrow expertises that belong to it data. Name the
second field "narrowCount".)
if broadId is not None:
rows containing broadId
else:
all broad and narrow rows
Sorted by broad expertise description
-------------------------------------------------------
"""
Add this function to the PyDev module functions.py. Test
it from t03.py.
Examples:
| broadDesc | narrowCount |
|---|---|
| Arms Control and Non-Proliferation Studies | 25 |
(Do not return the headers.)
Write and test the following function:
def get_broad_member_counts(cursor, broadId=None):
"""
-------------------------------------------------------
Use: rows = get_broad_memberCounts(cursor)
Use: rows = get_broad_memberCounts(cursor, broadId=v1)
-------------------------------------------------------
Parameters:
cursor - a database cursor (cursor)
broadId - a keyword ID number (int)
Returns:
rows - (list of a keyword description and the number of members
that have it data. Name the second field "memberCount".)
if broadId is not None:
rows containing broadId
else:
all member and keyword rows
Sorted by keyword description
-------------------------------------------------------
"""
Add this function to the PyDev module functions.py. Test
it from t04.py.
Examples:
| broadDesc | memberCount |
|---|---|
| Civil-Military Relations | 15 |
(Do not return the headers.)
Write and test the following function:
def get_narrow_member_counts(cursor, narrowId=None):
"""
-------------------------------------------------------
Use: rows = get_narrow_memberCounts(cursor)
Use: rows = get_narrow_memberCounts(cursor, narrowId=v1)
-------------------------------------------------------
Parameters:
cursor - a database cursor (cursor)
narrowId - a supp_key ID number (int)
Returns:
rows - (list of a broad expertise description, a narrow
expertise description, and the number of members that have that
narrow expertise data. Name the last field "memberCount".)
if narrowId is not None:
rows containing narrowId
else:
all member, broad, and narrow expertises rows
Sorted by broad description, narrow description
-------------------------------------------------------
"""
Add this function to the PyDev module functions.py. Test
it from t05.py.
Examples:
| broadDesc | narrowDesc | memberCount |
|---|---|---|
| Military History | First World War | 12 |
(Do not return the headers.)