Great Deal! Get Instant $10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

2010 Assignment 3 1. Assignment Questions Assignment Overview For this assignment, you will be applying your SQL skills to analyse research data, and write a report that details your investigations...

1 answer below »
2010 Assignment 3
1. Assignment Questions
Assignment Overview
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 ca
y 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
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 [1]. In this assignment, you will be analysing their data to ca
y 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 English-speaking country.
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” [1]. The ratings – subsequently refe
ed 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”.)
Data File
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 co
esponds 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 co
esponds 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 course instance.
    C
    staffid
    Identifier for a particular member of teaching staff. One staff member can teach multiple courses in the dataset.
    T
    age
    The age of the staff member.
    T
    gende
    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).
    T
    nonenglish
    Did the staff member complete their undergraduate education in a non-English speaking country: yes (1), no (0).
    T
    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.
    T
    students
    Total number of students in course
    C
    division
    Is the course lower or upper division: lower (L) [usually first- or second-year courses], upper (U) [usually third- or fourth- year courses].
    C
    courseevaluation
    Mean student course evaluation score on a scale from 1
(lowest) to 5 (highest).
    C
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 data.
Data Preparation
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 source file.
Analysis
Now that the data is loaded into a database, you can begin to analyse it. The
oad 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 ca
y 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
iefly comment on your findings, explaining what the numbers show about the variable(s) in question. This commentary should be
ief, 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).
    Course size
    18 or less
    19-28
    29-60
    61 or more
    Number of courses in group
    
    
    
    
    Minimum course evaluation score
    
    
    
    
    Mean course
evaluation score
    
    
    
    
    Maximum course evaluation score
    
    
    
    
Division
Analyse minimum, mean, and maximum course evaluation score by division (course level).
    
    No. courses in group
    Minimum
    Mean
    Maximum
    Upper division
    
    
    
    
    Lower division
    
    
    
    
Gender – Course Evaluation Score
Analyse minimum, mean and maximum course evaluation score by gender.
    
    No. courses in group
    Minimum
    Mean
    Maximum
    Female
    
    
    
    
    Male
    
    
    
    
Gender – Beauty
Analyse minimum, mean and maximum beauty by gender.
    
    No. academics in group
    Minimum
    Mean
    Maximum
    Female
    
    
    
    
    Male
    
    
    
    
Tenure track
Analyse minimum, mean and maximum course evaluation by tenure track status.
    
    No. academics in group
    Minimum
    Mean
    Maximum
    Tenure track
    
    
    
    
    Not tenure track
    
    
    
    
Education Background
Analyse minimum, mean and maximum course evaluation by education background.
    
    No. academics in group
    Minimum
    Mean
    Maximum
    English education
    
    
    
    
    Non-English education
    
    
    
    
Interactions between Tenure Track, Gender and Education Background
Analyse course evaluation by gender, tenure track, and education background. Present the results in your report, in a table similar to the following:
    Tenure track
    Gende
    Education
    No. academics in group
    Mean
    Tenure track
    Female
    English
    
    
    Tenure track
    Female
    Non-English
    
    
    Tenure track
    Male
    English
    
    
    Tenure track
    Male
    Non-English
    
    
    Not tenure track
    Female
    English
    
    
    Not tenure track
    Female
    Non-English
    
    
    Not tenure track
    Male
    English
    
    
    Not tenure track
    Male
    Non-English
    
    
Co
elation Analysis
Age, course size, beauty and course evaluation score are variables that take on many different values (continuous), rather than defining groups (categorical). Therefore, it is useful to analyse the co
elation between these variables. Co
elation is a measure of association and gives a numerical value to quantify the degree of relationship between two variables. Here, you should use the Spearman rank co
elation, which compares the rank ordering of two variables and the extent to which these agree.
Oracle has a built-in Spearman rank co
elation aggregate function, CORR_S (Important: NOT the CORR_K function, or the CORR function):
Answered Same Day Jun 03, 2021

Solution

Shivani answered on Jun 05 2021
138 Votes
Analysing Variables That Influence University Course Evaluations
Data Preparation
This project presents data analysis conducted in Oracle based on a set of data in a .csv file. The file records few parameters based on which teaching in a university can be evaluated. The parameters include the age and gender of the faculty, tenure track (if the faculty is looking forward to get permanent with the university), English based education, beauty ratings for faculty’s appearance, total number of students in the course, division of the course and course evaluation scores.
Data File
The data has been recorded in a comma separated file with headers as:
· Id
· staffId
· age
· gende
· tenuretrack
· nonenglish
· beauty
· students
· division
· courseevaluation
Loading data into Sql Develope
In Sql Developer, we create an Oracle table to load the data from the .csv file into an Oracle database. The DDL for this table is as below:
CREATE TABLE PROFEVALUATIONS
(
    ID NUMBER(4) NOT NULL,
    STAFFID NUMBER(4) NOT NULL,
    AGE NUMBER(2) NOT NULL,
    GENDER CHAR(1) NOT NULL,
    TENURETRACK NUMBER(1) NOT NULL,
    NONENGLISH NUMBER(1) NOT NULL,
    COURSEEVALUATION NUMBER(3,1) NOT NULL,
    STUDENTS NUMBER(3) NOT NULL,
    DIVISION CHAR(1) NOT NULL,
    BEAUTY NUMBER(12,9) NOT NULL
);
Right-click the table ‘PROFEVALUATIONS’ in Sql Developer and click ‘Import’. Run through the Import Data wizard to load the data from the .csv file into the Oracle table PROFEVALUATIONS.
As the data is redundant in this table, we normalize the schema by creating two different database tables as below:
CREATE TABLE COURSE
(
    ID NUMBER(4) NOT NULL,
    COURSEEVALUATION NUMBER(3,1) NOT NULL,
    STUDENTS NUMBER(3) NOT NULL,
    DIVISION CHAR(1) NOT NULL,
    STAFFID NUMBER(4) NOT NULL,
    PRIMARY KEY(ID)
);
CREATE TABLE STAFFMEMBER
(
    STAFFID NUMBER(4) PRIMARY KEY NOT NULL,
    AGE NUMBER(2) NOT NULL,
    GENDER CHAR(1) NOT NULL,
    TENURETRACK NUMBER(1) NOT NULL,
    NONENGLISH NUMBER(1) NOT NULL,
    BEAUTY NUMBER(12,9) NOT NULL
);
ALTER TABLE COURSE
ADD CONSTRAINT course_staff_fk
FOREIGN KEY (STAFFID) REFERENCES STAFFMEMBER(STAFFID);
Filling data into normalized tables
The above tables can be populated by executing the following commands:
INSERT INTO COURSE (ID, COURSEEVALUATION, STUDENTS, DIVISION, STAFFID)
SELECT DISTINCT ID, COURSEEVALUATION, STUDENTS, DIVISION, STAFFID
FROM PROFEVALUATIONS;
SELECT COUNT(*)
FROM COURSE;
INSERT INTO STAFFMEMBER ( STAFFID, AGE, GENDER, TENURETRACK, NONENGLISH, BEAUTY)
SELECT DISTINCT STAFFID, AGE, GENDER, TENURETRACK, NONENGLISH, BEAUTY
FROM PROFEVALUATIONS;
SELECT COUNT(*)
FROM STAFFMEMBER;
Data Analysis
This section presents the SQL queries executed to analyze data upon different set of parameters.
Minimum, mean and maximum number of students in a course
SELECT MIN(STUDENTS),...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here