$35
ASSIGNMENT 4 Solution
This assignment will be submitted electronically through conneX (as described in `Submission Instructions' below). Do not submit a hard copy of your answers; paper submissions will not be marked. All code submissions must be your own work. How-ever, you are permitted to use the code discussed in lectures and labs (or code posted on conneX) as the basis for your submissions if proper attribution is given.
Question 1: Survey of Programming Languages [30 marks]
Consider the problem of nding the median of three numbers x; y and z. Recall that the median of a set of numbers is the value which lies in the middle when the numbers are sorted. For example, the median of 4; 9 and 1 is 4 and the median of 2; 5 and 3 is 3.
Choose 3 of the languages listed below and, for each language, write a program which reads three integer values from the user and prints the median of the three. Some of the languages listed will not be o cially covered by the course, so they may be more challenging to use.
Ada
C or C++ (but not both) C#
FORTRAN (any version, including Fortran-95, is ne) Java
Common Lisp Pascal
Perl
Pep/8 assembly Python
All of the languages above (except Pep/8) are supported by the online tools at http://www. tutorialspoint.com/codingground.htm. If you develop your program using the tools on that website, you can download the source le by right-clicking on the le in the left-hand pane of the IDE and selecting \Download le".
Your submission for this question will consist of three les (one for each chosen language). Each program will be worth 10 marks. Please name your les according to the table below.
1
Language
Filename
Ada
median3.adb
C
median3.c
C++
median3.cpp
C#
median3.cs
FORTRAN IV
median3.f
FORTRAN 77
median3.f
Fortran 90
median3.f90
Fortran 95
median3.f95
Java
Median3.java
Lisp
median3.lisp
Pascal
median3.pas
Perl
median3.pl
Python
median3.py
Bonus (optional): You will receive 10 bonus marks if you submit implementations for all 10 options above. (This is mostly a character building exercise, since the work required far outweighs the number of bonus marks o ered)
Question 2: Database Systems [30 marks]
One example of a large scale database system is the University of Victoria's registration database, which tracks the enrollment and scheduling for all courses o ered at the university. Consider the three tables below, which contain a subset of the course scheduling information for Spring 2016. The table course names maps each course to its name, the table course sections speci es the instructor for each section of each course, and the table prerequisites maps each course to its prerequisite courses. Note that a course may have multiple sections and multiple prerequisites.
Table course names
subject
code
course
number
course
name
CSC
106
The Practice of Computer Science
CSC
110
Fundamentals of Programming I
CSC
115
Fundamentals of Programming II
CSC
205
2d Graphics and Image Processing
CSC
225
Algorithms & Data Structures I
CSC
226
Algorithms & Data Structures II
CSC
230
Intro. to Computer Architecture
SENG
265
Software Development Methods
CSC
370
Database Systems
SENG
310
Human Computer Interaction
2
Table course sections
subject
code
course
number
section
name
instructor
rstname
instructor
lastname
CSC
106
A01
Bill
Bird
CSC
106
A02
Bill
Bird
CSC
110
A01
Tibor
van Rooij
CSC
110
A02
Tibor
van Rooij
CSC
115
A01
LillAnne
Jackson
CSC
115
A02
LillAnne
Jackson
CSC
115
A03
Tibor
van Rooij
CSC
115
A04
Tibor
van Rooij
CSC
205
A01
Bill
Bird
CSC
225
A01
Venkatesh
Srinivasan
CSC
226
A01
Frank
Ruskey
CSC
226
A02
Frank
Ruskey
CSC
230
A01
Sudhakar
Ganti
SENG
265
A01
Daniel
Ho man
SENG
265
A02
Daniel
Ho man
CSC
370
A01
Alex
Thomo
SENG
310
A01
Peggy
Storey
Table prerequisites
subject
code
course
number
prereq
subject
prereq
number
CSC
115
CSC
110
CSC
225
CSC
115
CSC
226
CSC
225
CSC
230
CSC
115
SENG
265
CSC
115
CSC
370
CSC
226
CSC
370
SENG
265
SENG
310
SENG
265
A le courses.sql has been posted to conneX (in the Lectures section) which creates a database containing the three tables above (and the data for each table). In the space indicated in the courses.sql le (without modifying any of the existing SQL statements), write SQL queries for each of the questions below. Your submission for this question should be a single modi ed version of courses.sql, with all of the table data intact, containing your answers for all of the questions below.
Note that it is acceptable for errors to be reported on lines 3; 4 and 5 of the courses.sql le (since those lines are used to clear any existing data from the database).
When you experiment with sqlite3, you may want to use the .mode column and .header on commands to improve the readability of the output.
The sample output given below was generated after running .mode column and .header on. It is not necessary for your queries to produce output in the same order as the samples, unless the ordering is speci ed in the question.
Write a query to print the subject code, course number and course name of every course listed above, sorted by the course number.
Sample output:
subject_code course_number course_name
------------
-------------
--------------------------------
CSC
106
The Practice of
Computer Science
CSC
110
Fundamentals of
Programming I
CSC
115
Fundamentals of
Programming II
CSC
205
2d Graphics and
Image Processing
CSC
225
Algorithms & Data Structures I
CSC
226
Algorithms & Data Structures II
CSC
230
Intro. to Computer Architecture
SENG
265
Software Development Methods
SENG
310
Human Computer Interaction
CSC
370
Database Systems
Write a query to print the subject code, course number, section name and instructor name ( rst/last) for all sections of CSC 115.
Sample output:
subject_code course_number section_name instructor_firstname instructor_lastname
------------
-------------
------------
--------------------
-------------------
CSC
115
A01
LillAnne
Jackson
CSC
115
A02
LillAnne
Jackson
CSC
115
A03
Tibor
van
Rooij
CSC
115
A04
Tibor
van
Rooij
Write a query to print the subject code, course number and course name of all second year (200-level) courses.
Sample output:
subject_code course_number course_name
------------
-------------
--------------------------------
CSC
205
2d Graphics and Image Processing
CSC
225
Algorithms & Data Structures
I
CSC
226
Algorithms & Data Structures
II
CSC
230
Intro. to Computer Architecture
SENG
265
Software Development Methods
Write a query to print the subject code, course number, course name and section name of all courses taught by Tibor van Rooij.
You should use the command .width 15 15 30 15 before your query to prevent the course
name from being truncated.
Sample output:
subject_code
course_number
course_name
section_name
---------------
---------------
------------------------------
---------------
CSC
110
Fundamentals of Programming I
A01
4
CSC
110
Fundamentals of Programming I
A02
CSC
115
Fundamentals
of Programming
II
A03
CSC
115
Fundamentals
of Programming
II
A04
Write a query to print the instructor name ( rst/last), course name and section name for every SENG course.
You should use the command .width 15 15 30 15 before your query to prevent the course name from being truncated.
Sample output:
instructor_firs instructor_last
course_name
section_name
---------------
---------------
------------------------------
---------------
Daniel
Hoffman
Software
Development Methods
A01
Daniel
Hoffman
Software
Development Methods
A02
Peggy
Storey
Human Computer Interaction
A01
Write a query to print the subject code, course number, course name and the total number of sections for each course, sorted by course number. Note that your output is not required to have num sections as the heading for the last column (you should use the SQL COUNT function).
You should use the command .width 15 15 30 15 before your query to prevent the course name from being truncated.
Sample output:
subject_code
course_number
course_name
num_sections
---------------
---------------
-----------------------------------
---------------
CSC
106
The Practice
of Computer Science
2
CSC
110
Fundamentals
of Programming I
2
CSC
115
Fundamentals
of Programming II
4
CSC
205
2d Graphics and Image Processing
1
CSC
225
Algorithms &
Data Structures I
1
CSC
226
Algorithms &
Data Structures II
2
CSC
230
Intro. to Computer Architecture
1
SENG
265
Software Development Methods
2
SENG
310
Human Computer Interaction
1
CSC
370
Database Systems
1
Write a query to print the subject code, course number and course name of every course which has CSC 115 as a prerequisite.
You should use the command .width 15 15 32 before your query to prevent the course name from being truncated.
Sample output:
subject_code
course_number
course_name
---------------
---------------
--------------------------------
CSC
225
Algorithms & Data Structures I
CSC
230
Intro. to Computer Architecture
SENG
265
Software Development Methods
5
(Bonus - 4 marks - optional) Write a query to print the name ( rst/last) of every instructor who teaches more than one section, sorted by last name.
You should use the command .width 20 20 before your query to prevent the output data from being truncated.
Sample output:
instructor_firstname instructor_lastname -------------------- --------------------
Bill Bird
Daniel Hoffman
LillAnne Jackson
Frank Ruskey
Tibor van Rooij
Submission Instructions
All submissions for this assignment will be accepted electronically. You are permitted to delete and resubmit your assignment as many times as you want before the due date, but no submissions or resubmissions will be accepted after the due date has passed.
Ensure that each le contains a comment with your name and student number, and that the les for each question are named as dictated by the question. If you do not name your les correctly, or if you do not submit them electronically, it will not be possible to mark your submission and you will receive a mark of zero.
After submitting your assignment, conneX will automatically send you a con rmation email. If you do not receive such an email, your submission was not received. If you have problems with the submission process, send an email to the instructor before the due date.
6
