List course along with names of students from database table

Assignment Help Database Management System

Reference no: EM1363507

Consider the following University Database schemas:
Department (D-code, D-Name, Chair-SSn)
Course (D-code, C-no, Title, Units)
Prereq (D-code, C-no, P-code, P-no)
Class (Class-no, D-code, C-no, Instructor-SSn)
Faculty (Ssn, F-Name, D-Code, Rank)
Student (Ssn, S-Name, Major, Status)
Enrollment (Class-no, Student-Ssn)
Transcript (Student-Ssn, D-Code, C-no, Grade)
In the above tables, primary keys are underlined. Some explanations of attributes are as follows. Chair-Ssn in Department refers to Ssn in Faculty table. Instructor-SSn refers to Ssn in Faculty table, and Student-Ssn refers to Sssn in Student table. Class-no is a unique ID for each offering of a course. (For example, a class with class-no='12020' may be an offering of INFS 614, in which case, we may have a row like <12020, INFS, 614, 100009399> in the Class table.) <P-code, P-no> in Prereq table refers to <D-code, C-no> in the Course table, i.e., they refer to courses. A example row in Prereq table is <INFS, 614, INFS, 501> , which means that INFS614 needs INFS501 as a prerequisite course. Other foreign keys and meanings of the tables should be obvious. Specify the following queries in the relational algebra, relational calculus and implement them in SQL.

1. List the courses (D-code and C-no), along with the names of the students who are currently taking them.
2. List all the courses (D-code and C-no) that John (i.e., S-Name=''John'') got 'A' grade.
3. List the courses (D-Code and C-No) that do not require any pre-requisites.
4. Give the students (Ssn) who are enrolled in INFS614 (i.e., D-code=''INFS'' and C-no=''614'') and have satisfied all its prerequisites.

Reference no: EM1363507

Questions Cloud

Changes mean to health care industry : Could you help me evaluate the impact technological changes have had on the economics of health care and what these changes mean to the health care industry? I need to address least two (2) changes.

Measure effectiveness of the pre-departure training program : Pre-Departure Training - How will you measure the effectiveness of the pre-departure training program provided by this external consultant

Define how fast are the waves moving : define How fast are the waves moving.

Sketch the payoff matrix for this game : Sketch the payoff matrix for this game. Identify any possible Nash equilibria in pure strategies for this game."

List course along with names of students from database table : List the courses (D-code and C-no), along with the names of the students who are currently taking them. List all the courses (D-code and C-no) that John (i.e., S-Name=''John'') got 'A' grade.

Recording issuances of common stock-treasury stock : Prepare the journal entries for these transactions, assuming that the common stock has a par value of $3 per share. Prepare the journal entries for these transactions, assuming that the common stock is on-par with a stated value of $2 per share.

Imagine the water is in freefall as it drops : imagine the water is in freefall as it drops. What charge must be on each plate.

Exposure to second-hand smoking : Exposure to second-hand smoke increases the risk of adverse health effects. Second-hand smoke has been classified by the EPA as a known carcinogen (cancer causing agent).

Explain internal and external change agents : Explain Internal and External Change Agents - When are internal change agents more likely to be successful as compared to external change agents?

User Account

All Pages