ISYS1055/1057 Database Concepts
Database systems are a key technology for the storage, management, manipulation, and retrieval of structured data. In this
assignment you will apply the skills and concepts that you have learned about database systems in the course so far to analyse data,
and then write a report based on your findings.
2. Assessment Criteria
This assessment will determine your ability to:
1. Follow coding, convention and behavioural requirements provided in this document and in the lessons.
2. Independently solve problems by using database concepts taught in the course.
3. Understand the relational model.
4. Write and understand SQL queries.
5. Meet deadlines.
Seek clarification from your instructor, when needed, via discussion forums.
This assignment is worth thirty points in total, which accounts for 30% of the overall assessment for the course. The
revised assessment components and weights for the course are:
Assignment 1 Assignment 2 Assignment 3
20% 50% 30%
3. Learning Outcomes
This assessment is relevant to the following Course Learning Outcomes:
• CLO 1: Describe various data modelling and database system technologies.
• CLO 2: Explain the main concepts for data modelling and characteristics of database systems.
• CLO 3: Identify issues with and compare, justify relational database design using the functional dependency
• CLO 4: Apply SQL as a programming language to define database schemas and update database contents.
• CLO 5: Apply SQL as a programming language to extract data from databases for specific users’ information
It also supports the following Graduate Learning Outcomes:
• Enabling Knowledge: You will gain skills as you apply data modelling knowledge effectively in diverse contexts.
• Critical Analysis: Analyse and model requirements and data to understand underlying issues.
Page 2 of 9
• Problem solving: Design and implement database solutions that accommodate specified requirements and
constraints, based on analysis or modelling or requirements specification.
8. Assignment Questions
For this assignment, you will be applying your SQL skills to analyse research data, and write a report that
details your investigations into the question of whether particular variables such as class size and the
perceived attractiveness of teaching staff influence course evaluations.
As part of this assignment, you are likely to need to carry out some research and refer to additional
information beyond what was covered in the course. This is an important skill. Keep a note of any external
references that you use, as these will need to be detailed in your report.
Analysing Variables That Influence University Course Evaluations
At most universities, teaching is evaluated through a process whereby students complete course experience
surveys, rating courses in response to questions regarding the content, clarity of material, presentation, and
other factors. These questions are typically distilled into a single score that is supposed to reflect overall
teaching quality. In most Australian universities including RMIT, this is the Good Teaching Score (or GTS).
Prior research has indicated that many factors can influence student feedback, and these may include things
that are directly asked as part of the surveys (Were the teaching staff good at explaining things? Did the staff
work hard to make the course interesting?) and other factors that are not explicitly asked (Was the lecture
room too crowded and noisy? Did an unexpected event occur part-way through the semester that required
fundamental changes in teaching delivery? Are the teaching staff attractive?).
Daniel Hamermesh and Amy Parker, two researchers form the USA, collected data to investigate the question
of whether teaching evaluations are influenced by the attractiveness of teaching staff . In this assignment,
you will be analysing their data to carry out a preliminary investigation into answering this question. The data
was collected at the University of Texas at Austin, USA, and includes information about 455 courses, taught by
teaching staff in various departments (note that some staff taught multiple courses included in the data set).
Courses were of various sizes in terms of the number of enrolled students. Each course was evaluated using
student surveys, with responses to the question “Overall, this course was…?” being collected on a 5-level
ordinal scale with a minimum score of (1) “very unsatisfactory” and a maximum score of (5) “excellent”.
Information was obtained on each faculty member, based on characteristics including their gender, whether
they are on a tenure track (roughly speaking, working towards being offered a permanent position at their
university), whether they are part of a minority group, and whether they received their education in an
Separately, a picture of each teaching staff member was rated by 6 undergraduate students. Hamermesh and
Parker describe the rating process as follows: “The raters were told to use a 1 (lowest) to 10 (highest) rating
scale, to concentrate on the physiognomy of the instructor in the picture, to make their ratings independent
of age, and to keep 5 in mind as an average” . The ratings – subsequently referred to as “beauty” scores –
were then normalised to have a mean score of zero. (This means that someone with a rating greater than zero
was judged to be more “beautiful” than the average, while someone with a negative score was judged to be
less “beautiful” than the average”.)
Page 4 of 9
The raw research data that you need to analyse is in the file profEvaluations.csv, available from the Course
Canvas as part of the Assignment 3 specification.
The file is in comma-separated value or “CSV” format. This is a format for representing table data, where each
row of the file corresponds to a single record (row, or tuple); and the individual data items (attributes/cells)
are separated by the comma (“,”) symbol. The first row gives the column headings (schema). To explore the
file, you can open it in a text editor, or in a spreadsheet program (e.g. MS Excel, Numbers).
Notice that each row of the original file corresponds to observations about a single course, and includes details
such as number of students, and course evaluation score. It also includes information about the teaching staff
member who taught the course, including a staffid, their age, and their educational background. Notice that a
particular teaching staff member can teach more than one course – that is, their individual information may
be repeated for each course that they teach.
The meaning of the variables is explained in the following table. Each variable can be for courses (C), or
teaching staff (T), indicated in the third column.
Variable Description C/T
id Course identifier. Each row gives the data for a particular
staffid Identifier for a particular member of teaching staff. One staff
member can teach multiple courses in the dataset.
age The age of the staff member. T
gender Gander of staff member: female (f), male (m). T
tenuretrack Whether the staff member is on the tenure track (working
towards a permanent position): yes (1), no (0).
nonenglish Did the staff member complete their undergraduate
education in a non-English speaking country: yes (1), no (0).
beauty Rating of the staff member’s appearance in a photo, averaged
across responses by 6 undergraduate students, and
normalised to have a mean score of zero.
students Total number of students in course C
division Is the course lower or upper division: lower (L) [usually firstor second-year courses], upper (U) [usually third- or fourthyear courses].
courseevaluation Mean student course evaluation score on a scale from 1
(lowest) to 5 (highest).
Note: The data you will be using is a subset of the original data collected by Hamermesh and Parker.
Therefore, your results will not be identical to those reported in their paper. The identifier variables (id and
staffid) are not necessarily a contiguous sequence. We thank Daniel Hamermesh for supplying the original
Page 5 of 9
Your first task is to load the raw CSV data into the Oracle database, so that you can analyse it.
• You need to design the relation schemas for two appropriate tables (to reflect that the data is at two
levels of granularity). Note that there is data redundancy in the provided (starting) CSV data file.
• You can use the “import data” function from SQL Developer to import data from .CSV files to tables.
The following links provide some helpful suggestions.
Confirm that you have loaded the full research data into your database by comparing the number of rows in
your database tables with the number of rows that you would expect, based on your decomposition of the
Now that the data is loaded into a database, you can begin to analyse it. The broad goal is to investigate the
effect that different variables such as age, gender, and beauty, have on course evaluation scores.
In the following subsections, you will be asked to carry out numerical analysis of a particular variable or
variables. For each, you should format your numerical results and present them in a table in your report. You
should also briefly comment on your findings, explaining what the numbers show about the variable(s) in
question. This commentary should be brief, one or two sentences at most for each specific analysis below.
For each analysis, you should consider carefully whether it is at the course level, or at the teaching staff level.
If the analysis is at the teaching staff level, each data point for a staff member may only be included once. If
the analysis is at the course level, data points must be included for each course; this also applies if the analysis
uses both course and teaching staff variables (unless noted otherwise below).
Note that the table layouts as shown in the following subsections indicate the formatting that should be used
in your report document for presentation. You should write SQL queries to obtain data to complete the tables.
Your queries do not need to generate tables in the exact format given, and you may sometimes need to write
several SQL queries to complete one analysis table.
Course Sizes – Number of Students
Calculate the minimum, mean and maximum number of students in a course. Present the results in your
report, in a table similar to the following:
Minimum Mean Maximum
Number of students
Course Sizes – Course Evaluation Score
Analyse the minimum, mean, and maximum course evaluation score for groups of courses, binned into size
groups of 18 or less, 19—28, 29—60, 61 or more. (For example, a course size group of 19—28 includes all
those courses that had from 19-28 students enrolled, inclusive).
本网站支持淘宝 支付宝 微信支付 paypal等等交易。如果不放心可以用淘宝交易！
E-mail: [email protected] 微信:itcsdx