Q8. You are now more sure about your choice. You still wish to broaden your horizon, but you also want to deepen your knowledge. To do this, you want to find courses that are offered by only one faculty (note, faculty and not department). For each faculty code (e.g, SOC), find the number of distinct course codes that is offered only by that faculty. This quickly excludes courses offered by departments that is in more than one faculties such as Computer Science. However, this includes courses that may be offered by two different departments as long as the two departments are in the same faculty. For instance, the course PC333 Statistical Physics is offered by both Physics department and Statistics department. But because both are in SCI, it should be part of the 14 other courses offered exclu- sively by SCI. Exclude faculties that do not offer any such courses. In other words, if the count is to be 0, it should not even be in the result at all. You should see ('BIZ',5) and this can be easily double-checked from the data. There are 5 courses offered exclusively by the Marketing department: MT101, MT201, MT303, MT304, and MT481. For SOC, because Computer Science is in both SOC and SCI, none of the courses offered by Computer Science can be considered. This leaves us with only 4 other courses by Information System, namely: IS108, IS221, IS312, and IS406. Unfortu- nately we have to exclude IS101 because it is also offered by Computer Science in this sample data. Hint: Can you solve the simpler problem of finding all courses that is only offered by one faculty first (i.e., which tables are involved?). Then check your understanding. Did you manage to get 14 courses for SCI? CREATE OR REPLACE VIEW 98 (faculty, count) AS
Q8. You are now more sure about your choice. You still wish to broaden your horizon, but you also want to deepen your knowledge. To do this, you want to find courses that are offered by only one faculty (note, faculty and not department). For each faculty code (e.g, SOC), find the number of distinct course codes that is offered only by that faculty. This quickly excludes courses offered by departments that is in more than one faculties such as Computer Science. However, this includes courses that may be offered by two different departments as long as the two departments are in the same faculty. For instance, the course PC333 Statistical Physics is offered by both Physics department and Statistics department. But because both are in SCI, it should be part of the 14 other courses offered exclu- sively by SCI. Exclude faculties that do not offer any such courses. In other words, if the count is to be 0, it should not even be in the result at all. You should see ('BIZ',5) and this can be easily double-checked from the data. There are 5 courses offered exclusively by the Marketing department: MT101, MT201, MT303, MT304, and MT481. For SOC, because Computer Science is in both SOC and SCI, none of the courses offered by Computer Science can be considered. This leaves us with only 4 other courses by Information System, namely: IS108, IS221, IS312, and IS406. Unfortu- nately we have to exclude IS101 because it is also offered by Computer Science in this sample data. Hint: Can you solve the simpler problem of finding all courses that is only offered by one faculty first (i.e., which tables are involved?). Then check your understanding. Did you manage to get 14 courses for SCI? CREATE OR REPLACE VIEW 98 (faculty, count) AS
Chapter2: Database Design Fundamentals
Section: Chapter Questions
Problem 15RQ
Related questions
Question
Transcribed Image Text:Q8. You are now more sure about your choice. You still wish to broaden your horizon,
but you also want to deepen your knowledge. To do this, you want to find courses
that are offered by only one faculty (note, faculty and not department).
For each faculty code (e.g, SOC), find the number of distinct course codes that is
offered only by that faculty. This quickly excludes courses offered by departments
that is in more than one faculties such as Computer Science.
However, this includes courses that may be offered by two different departments as
long as the two departments are in the same faculty. For instance, the course PC333
Statistical Physics is offered by both Physics department and Statistics department.
But because both are in SCI, it should be part of the 14 other courses offered exclu-
sively by SCI.
Exclude faculties that do not offer any such courses. In other words, if the count is
to be 0, it should not even be in the result at all.
You should see ('BIZ',5) and this can be easily double-checked from the data.
There are 5 courses offered exclusively by the Marketing department: MT101, MT201,
MT303, MT304, and MT481.
For SOC, because Computer Science is in both SOC and SCI, none of the courses
offered by Computer Science can be considered. This leaves us with only 4 other
courses by Information System, namely: IS108, IS221, IS312, and IS406. Unfortu-
nately we have to exclude IS101 because it is also offered by Computer Science in
this sample data.
Hint: Can you solve the simpler problem of finding all courses that is only offered by
one faculty first (i.e., which tables are involved?). Then check your understanding.
Did you manage to get 14 courses for SCI?
CREATE OR REPLACE VIEW q8 (faculty, count) AS
Transcribed Image Text:2.1 ER Diagram
code
title
name
Faculties
confers
Degree
name
kind
has
requires
code
name
Departments
offers
address
Courses
name
mc
sem
pre
prereq
post
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
Step by step
Solved in 3 steps
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Recommended textbooks for you
A Guide to SQL
Computer Science
ISBN:
9781111527273
Author:
Philip J. Pratt
Publisher:
Course Technology Ptr
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:
9781337508841
Author:
Carey
Publisher:
Cengage
A Guide to SQL
Computer Science
ISBN:
9781111527273
Author:
Philip J. Pratt
Publisher:
Course Technology Ptr
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:
9781337508841
Author:
Carey
Publisher:
Cengage