Question for problem 1.4, 1.5, 1.6, 1.9, 2.4, and 2.8. Consider the JSON databas

Question

Question for problem 1.4, 1.5, 1.6, 1.9, 2.4, and 2.8.

Consider the JSON database in the file sampleUnivDB.jsonencodes in JSON the
following tables:
Department (dcode, dname, chair)
Course (dcode, cno, title, units)
Prereq (dcode, cno, pcode, pno)
Class (class, dcode, cno, instr)
Faculty (ssn, name, dcode, rank)
Student (ssn, name, major, status)
Enrollment (class, ssn)
Transcript (ssn, dcode, cno, grade)
Note that the
The primary keys are underlined. The meaning of the primary key is that there cannot be
two different tuples in the relation with identical values for the primary key. Some
explanations of attributes (variable names) are as follows. Chair in Department refers to
SSN in Faculty table. Instr refers to Ssn in Faculty table, and ssn in Transcript refers to
Ssn in Student table. Class is a unique ID for each offering of a course. For example, a
class with class = 21may be an offering of CS 330, in which case, we may have a tuple
like <21, ‘CS’,330, 100009399> in the Class table. <pode, pno> in Prereq table refers to
<dcode, cno> in the Course table, i.e., they refer to courses. A example row in Prereq
table is <’CS’, 450, ‘CS’, 330> , which means that CS 450 needs CS 330 as a prerequisite
course. Other foreign keys and meanings of the tables should be obvious.
Consider each of these table names (Department, Course, etc.) as predicates, with the
interpretation that the predicate returns TRUE for a tuple if and only if this tuple is in the
database table. For example, Enrollment(class, ssn) will now represent a predicate that
evaluates to TRUE if the pair (class, ssn) is in the corresponding relation, and FALSE
otherwise.
Problem 1. Write predicate logic sentences using JSONiq that describe the following
university rules:
1. The number of units for a course can not exceed 6.
2. Everyone who is enrolled in a class (in Enrollment) must be a student (in Student)
3. For courses with cno <= 400, the possible grades are ‘A’,’B’,’C’, ‘D’ and ‘F’.
4. The instructor for a course offered by a department, must be a faculty in that
department
5. Students majoring in CS must be enrolled in a class titled “Vacation in Hawaii”
6. Courses with C-no between 300 and 399 must have at least one prerequisites; and
courses with C-no between 400 and 499 must have at least two prerequisites
7. Students enrolled in a class must have taken (in Transcript) all prerequisites of the
corresponding course with the grade of ‘B’ or better.
8. Students majoring in CS are allowed to enroll only in classes offered by the
History department (dcode = “History”)
9. The chair of a department must be a faculty in that department
Problem 2: Specify the following queries in JSONiq (mimicking the set builder notation
and predicate logic formulae).
1. List the courses (dcode and cno), along with the names of the students who are
currently taking them.
2. List all the courses (dcode, cno) that John (i.e., name =``John'') got 'A' grade.
3. List the courses (dcode, cno) that do not require any pre-requisites.
4. Give the students (Ssn) who are enrolled in CS 330 (i.e., dcode=``CS'' and cno
=``330'') and have satisfied all its prerequisites.
5. List all pairs (dcode, chair) from the Department table
6. Find (Student-SSN, Student-name) for students who received at least one A grade
7. Find (Student-SSN, Student-name) for students all of whose grades are A (i.e.,
students who did not receive any grade other than A).
8. Find (Student-SSN, Student-name) for students who are taking a class without
having satisfied all its prerequisites
9. Find classes (class, dcode, cno) in which at least two students are enrolled
10. Find classes (class, dcode, cno) in which at most two students are enrolled
11. Find classes (class, dcode, cno) in which exactly two students are enrolled
12. Find instructors (Instructor-SSN, Insructor-name) who teach a class offered by a
department other then their home department (in the Faculty table)
13. Find departments (dcode) that offer classes taught by its faculty
14. Find departments (dcode) with all their offered classes taught by its faculty

Explanation / Answer

1. Select Course where Units<=6.

2. Select Enrollment where ((Select Class where Class.class=Enrollment.class) & (Select Student where Student.ssn=Enrollment.ssn))

3. Select Transcript where cno='400' & Grades=('A','B','C','D','F')

4. Select Faculty where (Select Department Where Department.dcode=Faculty.dcode (Select Course where Course.dcode=Department.dcode))

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.