Question

The queries listed below must be implemented with a set algebra operation.

1)Find the department name and code for all departments that only offered subjects that worth 6 credits. Note that a subject offered by a department means a lecturer of the department has been assigned to teach the subject.

CREATE TABLE LPOSITION(
   position       VARCHAR(20)   NOT NULL,   /* Position occupied       */
   salary_level   CHAR   NOT NULL,       /* Salary level   */
   CONSTRAINT LPosition_PKey PRIMARY KEY (position)
);

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

CREATE TABLE DEPARTMENT (
   name           VARCHAR(50)   NOT NULL,   /* Department name   */
   code           CHAR(8)       NOT NULL,   /* Code of department   */
   head           DECIMAL(7),               /* Head of department   */
   budget           DECIMAL(10,2),           /* Budget of department   */
   CONSTRAINT DEPARTMENT_PKey PRIMARY KEY (name)
);

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

CREATE TABLE EMPLOYEE(
   eno               DECIMAL(7)   NOT NULL,    /* Employee number       */
   first_name       VARCHAR(30)   NOT NULL,    /* First name           */
   last_name       VARCHAR(30)   NOT NULL,    /* Last name           */
   email           VARCHAR(50)   NOT NULL,    /* Email address       */
   bldg_no           DECIMAL(5)   NOT NULL,    /* Building number of office   */
   room_no           VARCHAR(5)   NOT NULL,    /* Office room number+suffix   */
   phone           DECIMAL(4)   NOT NULL,    /* Office phone extension number   */
   dname           VARCHAR(50)   NOT NULL,   /* Department name       */
   position       VARCHAR(20)   NOT NULL,    /* Position occupied   */
   CONSTRAINT EMPLOYEE_PKey PRIMARY KEY(eno),
   CONSTRAINT EMPLOYEE_CKey1 UNIQUE(email),
   CONSTRAINT EMPLOYEE_CKey2 UNIQUE(phone),
   CONSTRAINT EMPLOYEE_FKey1 FOREIGN KEY (dname) REFERENCES DEPARTMENT(name),
   CONSTRAINT EMPLOYEE_FKey2 FOREIGN KEY (position) REFERENCES LPOSITION(position)
);

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

CREATE TABLE SUBJECT(
   code             CHAR(7)           NOT NULL, /* Subject code       */
   title           VARCHAR(200)   NOT NULL, /* Subject title       */
   credits           DECIMAL(2)       NOT NULL, /* Credit points       */
   description       VARCHAR(2000)   NOT NULL, /* Subject description   */
   CONSTRAINT SUBJECT_PKey PRIMARY KEY(code)
);

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
/* Association: RUNNING SUBJECT Is-instance-of SUBJECT           */

CREATE TABLE RUNNINGSUBJECT(
   code             CHAR(7)           NOT NULL, /* Subject code       */
   rsession       VARCHAR(7)       NOT NULL, /* Session running       */
   ryear           DECIMAL(4)       NOT NULL, /* Year running       */
   enrolment       DECIMAL(3)       NOT NULL, /* Total enrolment       */
   CONSTRAINT RUNNINGSUBJECT_PKey PRIMARY KEY(code, rsession, ryear),
   CONSTRAINT RUNNINGSUBJECT_FKey1 FOREIGN KEY (code)
       REFERENCES SUBJECT(code)
);

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

CREATE TABLE TEACHES(
   code             CHAR(7)           NOT NULL, /* Subject code       */
   rsession       VARCHAR(7)       NOT NULL, /* Session running       */
   ryear           DECIMAL(4)       NOT NULL, /* Year running       */
   lecturer       DECIMAL(7)       NOT NULL, /* Lecturer           */
   CONSTRAINT TEACHES_PKey PRIMARY KEY(code, rsession, ryear, lecturer),
   CONSTRAINT TEACHES_FKey1 FOREIGN KEY (code, rsession, ryear)
       REFERENCES RUNNINGSUBJECT(code, rsession, ryear),
   CONSTRAINT TEACHES_FKey2 FOREIGN KEY (lecturer) REFERENCES EMPLOYEE(eno)
);

Answer 1

Hey! here is my answer.............please give positive Rating to appreciate my work...........

The queries listed below must be implemented with a set algebra operation.

1)Find the department name and code for all departments that only offered subjects that worth 6 credits. Note that a subject offered by a department means a lecturer of the department has been assigned to teach the subject.

CREATE TABLE DEPARTMENT (
   name           VARCHAR(50)   NOT NULL,   /* Department name   */
   code           CHAR(8)       NOT NULL,   /* Code of department   */
   head           DECIMAL(7),               /* Head of department   */
   budget           DECIMAL(10,2),           /* Budget of department   */
   CONSTRAINT DEPARTMENT_PKey PRIMARY KEY (name)
);

CREATE TABLE SUBJECT(
   code             CHAR(7)           NOT NULL, /* Subject code       */
   title           VARCHAR(200)   NOT NULL, /* Subject title       */
   credits           DECIMAL(2)       NOT NULL, /* Credit points       */
   description       VARCHAR(2000)   NOT NULL, /* Subject description   */
   CONSTRAINT SUBJECT_PKey PRIMARY KEY(code)
);

ANSWER:-   this query require 2 tables DEPARTMENT and SUBJECT

• Query 1:-   ∏ DEPARTMENT.name, DEPARTMENT.code (σ _{credit = 6} ( σ _{DEPARTMENT.code = SUBJECT.code} (DEPARTMENT x SUBJECT)))

• Query 2:-   ∏ _{DEPARTMENT.name, DEPARTMENT.code} ( σ _{DEPARTMENT.code = SUBJECT.code}  (σ _{credit = 6} (DEPARTMENT) x SUBJECT))

Thanks................for any query please ask ....