SQL Queries: Student Enrollment & Department Summary

• The following is a closed book examination.
• The exam duration is 2 hours.
• The total score is 100 points.
• Two letter-size information sheets (both sides) are permitted.
• All questions must be answered, and all materials must be submitted together.
• Academic honesty will be strictly enforced, with penalties for cheating.
• The toyu database is required for SQL and Python questions.

SQL Queries

• Construct SQL statements to get exact results for the following queries.

Student Enrollment

• List student ID, names, and the number of CSCI courses each student has enrolled in.
• Order the results in descending order according to the number of CSCI courses.
• Tony Hawk has enrolled in only three courses, despite enrolling in four CSCI classes.
• He has enrolled in one course twice across two different class offerings.

Department Summary View

• The DepartmentSummary view should return department ID, department name, number of faculty, and number of classes offered.
• Executing "SELECT * FROM DepartmentSummary;" will display the view's contents.

Stored Function

• Write a SQL stored function numCommonClasses.
• It takes two student IDs (INT) as input.
• It returns the number of classes the students have enrolled in together.

True or False Questions

• A string is an object in Python (True).
• It's possible to define an index on three fields in MySQL (True).
• SQL injection is based on attempts to change backend SQL statements (True).
• It's possible to use OUT parameters in MySQL stored functions (True).
• The HTTP protocol is used for communication between web browsers and web servers (True).
• ACID properties are fully supported by NoSQL DB (False).
• Given R(A,B,C,D) where A and B are the only prime attributes, R may have three candidate keys (True).
• In SQL, a stored procedure can have a DELETE statement (True).
• MongoDB is more object-oriented than a relational database (True).
• R(A,B,C,D) may have three canonical covers for a set of functional dependencies (True).
• As a bonus, cats have nine lives or are mammals (True).

Short Questions (Candidate Keys and Normal Forms)

Relation 1

• R(A,B,C,D) with {A->B, B->C, C->A}
• Determine the candidate keys and highest normal form.
• Assume the relations are at least in 1NF.

Relation 2

• R(A,B,C,D) with {A->BC, B->CD}
• Determine the candidate keys and highest normal form.
• Assume the relations are at least in 1NF.

Relation 3

• R(A,B,C,D) with {A->BD, B->C, C->A}
• Determine the candidate keys and highest normal form.
• Assume the relations are at least in 1NF.

Relation Analysis

• Consider the relation R(A,B,C,D,E) with functional dependencies {B->C, BC->A, AB->CD}.

Canonical Cover

• Provide a canonical cover.

Candidate Keys

• Show all candidate keys.

Normal Form

• Determine the highest normal form (up to BCNF). Explain your reasoning.

Decomposition

• If it is not in BCNF, determine if you can losslessly decompose R into component relations in BCNF while preserving functional dependencies, and if so detail how.

Python CGI Program

• Write a Python CGI program, t2a.py.
• It accepts an HTTP Get parameter fid (faculty ID).
• Displays the faculty member's information including: the number of classes taught and advisees.
• Error checking of the user input parameter fid is not required.

MongoDB Code

• Consider the 'student' collection in the 'toyu' database stored in MongoDB.
• The task is to construct Mongosh code to show information of all students majoring or minoring in ENGL or ARTS.
• Project the output field "status": "upper" if "ach" is greater than or equal to 60.
• If major or minor are null, the result should show 'undeclared'.

Relation R(A,B,C,D)

• D is a non-prime attribute. A and B are prime attributes. There are exactly four superkeys.

Candidate Keys

• List the candidate keys.

Membership Relation

• The Membership(StudentId, OrganizationId, OrganizationName, RoleId, RoleName, StartDate) relation stores information of student's participations in organizations and their roles.

Functional Dependencies

• List the functional dependencies representing the specification.

Candidate Keys

• What are the candidate keys?

Highest Normal Form

• What is the highest normal form for the Membership relation and why?