数据库辅导 | DSC 450: Database Processing for Large-Scale Analytics

DSC 450: Database Processing for Large-Scale Analytics

Take-home Midterm

Don’t forget to include all python code and SQL code in every question.

Part 1

 (8 points) Please give a “True” or a “False” rating to each statement below.

Part 2

Student(StudentID, Name, Address, GradYear)

Grade(CName, StudentID, CGrade)

Course(CName, Department, Credits)

For each part below, write a single SQL query.

Q1.  Display the list of student IDs and names for the students who graduated in the first 3 years (e.g., if first graduation = 1898, within 3 years should include 1898, 1899, 1900. This is an example; do not use these numbers in your answer).  You can assume that GradYear is an integer.

Q2.  Display student names and their taken course names for all students with the middle name of ‘Muriel’. You may assume that name is always written as ‘First Middle Last’.  Your query output should be sorted from the highest grade to the lowest grade.

Q3.  For students who are either not enrolled in any courses or are enrolled in only 1 course, list those student’s names and graduation years.

Q4.  For each department, return the average length of a student name (you can use LEN function to get the necessary number, i.e. LEN(Name) will give you length of student name) .

Q5. Update all student records, to increase the graduation year by 1 for all students who live in Chicago

Q6. Modify the course table to add a Chair column that can contain up to 25 characters (that question requires a DDL rather than a DML SQL statement)

Part 3

R(X, Y, Z, W, A)  with  XYàZA ,  AàW

does W determine A? (WàA?) Why or why not?

S(P, Q, U, M, N)

What functional dependencies (if any) can you assume?

The table is already in First Normal Form, and its primary key is (Show, Year).

The schema for the TVShows table is:

(Show, Year, Channel, Length, Cost, Zip, City, State, Lead, Salary)

You are given the following functional dependencies:

Show à Cost

Zip à City, State

Lead à Salary

Show, Year à Channel, Cost

Given the schema R and the following functional dependencies:

R(A, B, C, D, E)  with  ABàC,  CàD

Part 4

(50 points) 

Create the schema from Part 2-c in SQLite and populate it with at least 7 students, 4 courses, and 14 enrollments (at least one of the students should not be enrolled in any courses and at least one course should have zero current enrollments).

Include a screenshot of (any) one output .txt file

Include a screenshot of the output .txt file

CName à Department, Credits

(you can do so by creating a new record that repeats the course name but does not match either the department or the number of credits associated with this course)

Execute the following query in parts f, g, and h below.

Q1: For every department, display the average grade (grade should be numeric) and the number of rows.

Include a screenshot of the output of your code

Include a screenshot of the output of your code