Description

Problem 1

Problem 1

The ER- diagram identifies the following: all the entity sets; all the relationship sets and their multiplicities; the primary key for each entity set and each relationship set.

The property which was not reflected in the design is “a contact person whose company sells a book can’t be selling the same book as an individual seller at the same time,” because according to the diagram a company’s contact person can also be an individual seller selling the same book.

Problem 2

P2.Q2. Indicate feature(s)/property(ies) in the above description that are NOT captured by your ER-diagram
The ER- diagram doesn’t reflect that ‘no more than 10 students can wait on a class at the same time,” because the numerical limitation/constraint can’t be depicted in the diagram.

P2.Q3. Give 2 examples of the types of reports that can be obtained from the database, and state the involved entity sets and/or relationship sets.

Example 1

Example 2 List of GTAs and Professor working in a team to teach course 550.

/* THE COMMANDS/QUERIES GIVEN IN HW1.SQL FILE */

CREATE TABLE PROFESSOR( SSN INT PRIMARY KEY NOT NULL, NAME varchar(50));
CREATE TABLE STUDENT( SSN INT PRIMARY KEY NOT NULL, NAME varchar(50));
CREATE TABLE GTA( SSN INT PRIMARY KEY NOT NULL, NAME varchar(50), SALARY FLOAT(10), FOREIGN KEY(SSN) REFERENCES STUDENT(SSN) ON DELETE CASCADE);
create TABLE TEAM( TEAM_NUMBER INT, PROFESSOR_ID INT, GTA_ID INT, FOREIGN KEY(PROFESSOR_ID)
REFERENCES PROFESSOR(SSN), FOREIGN KEY(GTA_ID) REFERENCES GTA(SSN));
CREATE TABLE CLASS(DEPARTMENT_NAME VARCHAR(50), COURSE_NUMBER INT, PREREQ_COURSE_NUM INT, PRIMARY
KEY(DEPARTMENT_NAME, COURSE_NUMBER, PREREQ_COURSE_NUM));
CREATE TABLE SECTION( SEC_NUM INT NOT NULL, DEPARTMENT_NAME VARCHAR(50), COURSE_NUMBER INT,
PREREQ_COURSE_NUM INT, PRIMARY KEY( SEC_NUM),FOREIGN KEY(DEPARTMENT_NAME,COURSE_NUMBER,
PREREQ_COURSE_NUM ) REFERENCES CLASS(DEPARTMENT_NAME,COURSE_NUMBER, PREREQ_COURSE_NUM) ON DELETE CASCADE);
CREATE TABLE CLASSES_TAUGHT_BY_PROF (SSN INT, DEPARTMENT_NAME VARCHAR(50), COURSE_NUMBER INT,
PREREQ_COURSE_NUM INT, SEC_NUM INT, COURSE_EVALUATION VARCHAR(70), FOREIGN KEY(SSN) REFERENCES
PROFESSOR(SSN), FOREIGN KEY(DEPARTMENT_NAME, COURSE_NUMBER, PREREQ_COURSE_NUM) REFERENCES CLASS(DEPARTMENT_NAME, COURSE_NUMBER, PREREQ_COURSE_NUM), FOREIGN KEY(SEC_NUM) REFERENCES SECTION(SEC_NUM));
CREATE TABLE CLASSES_TAKEN_BY_STUDENT (SSN INT, PROFESSOR_ID INT, SEC_NUM INT, DEPARTMENT_NAME
VARCHAR(50), COURSE_NUMBER INT, PREREQ_COURSE_NUM INT, GRADE VARCHAR(2), FOREIGN KEY(ssn)
REFERENCES STUDENT(SSN), FOREIGN KEY(PROFESSOR_ID) REFERENCES PROFESSOR(SSN), FOREIGN
KEY(SEC_NUM) REFERENCES SECTION(SEC_NUM), FOREIGN KEY(DEPARTMENT_NAME,COURSE_NUMBER,
PREREQ_COURSE_NUM) REFERENCES CLASS(DEPARTMENT_NAME,COURSE_NUMBER,PREREQ_COURSE_NUM));
CREATE TABLE PREREQ_FOR_CLASS (REQUIRED_COURSE_DEPARTMENT VARCHAR(50), REQUIRED_COURSE_NUMBER
INT, PREREQ_COURSE_NUM INT, FOREIGN KEY (REQUIRED_COURSE_DEPARTMENT, REQUIRED_COURSE_NUMBER,
PREREQ_COURSE_NUM) REFERENCES CLASS(DEPARTMENT_NAME, COURSE_NUMBER, PREREQ_COURSE_NUM));
CREATE TABLE STUDENT_ENROLLMENT (E_ID INT PRIMARY KEY, GNUMBER INT NOT NULL, SEC_NUM INT NOT
NULL, WAIT_LIST VARCHAR(5), WL_RANK INT NOT NULL, CHECK(WL_RANK >=1 AND WL_RANK<=10),
PROFESSOR_ID INT, GTA_ID INT, FOREIGN KEY(GNUMBER) REFERENCES STUDENT(SSN) ON DELETE CASCADE,
FOREIGN KEY(SEC_NUM) REFERENCES SECTION(SEC_NUM), FOREIGN KEY(PROFESSOR_ID) REFERENCES
PROFESSOR(SSN), FOREIGN KEY (GTA_ID) REFERENCES GTA(SSN));

/******************** INSERT VALUES ******************************/
INSERT INTO PROFESSOR(SSN, NAME) VALUES (123,’JOHN DOE’);
INSERT INTO PROFESSOR(SSN,NAME) VALUES (456,’JAMES CHARLES’);

INSERT INTO STUDENT(SSN, NAME) VALUES (789,’MARY SMITH’);
INSERT INTO STUDENT(SSN,NAME) VALUES (101,’HARRY SMITH’);
INSERT INTO STUDENT(SSN,NAME) VALUES (103,’LARRY G.’);

INSERT INTO GTA(SSN,NAME,SALARY) VALUES (789,’MARY SMITH’,1000); INSERT INTO GTA(SSN,NAME,SALARY) VALUES (101,’HARRY SMITH’,2000);

INSERT INTO TEAM(TEAM_NUMBER, PROFESSOR_ID, GTA_ID) VALUES (1, ‘456’, ‘789’);
INSERT INTO TEAM(TEAM_NUMBER, PROFESSOR_ID, GTA_ID) VALUES (2, ‘123’, ‘101’);

INSERT INTO CLASS( department_name, course_number, prereq_course_num) VALUES (‘COMP SCI’, 451, 450);
INSERT INTO CLASS( department_name, course_number, prereq_course_num) VALUES (‘COMP SCI’, 550,
451);

INSERT INTO SECTION( sec_num, department_name, course_number, PREREQ_COURSE_NUM) VALUES (001, ‘COMP SCI’, 451, 450);
INSERT INTO SECTION( sec_num, department_name, course_number, PREREQ_COURSE_NUM) VALUES (002,
‘COMP SCI’, 550, 451);

INSERT INTO CLASSES_TAUGHT_BY_PROF( SSN, DEPARTMENT_NAME, COURSE_NUMBER, PREREQ_COURSE_NUM, SEC_NUM, COURSE_EVALUATION) VALUES (123, ‘COMP SCI’, 451, 450, 001, ‘NA’);
INSERT INTO CLASSES_TAUGHT_BY_PROF( SSN, DEPARTMENT_NAME, COURSE_NUMBER, PREREQ_COURSE_NUM,
SEC_NUM, COURSE_EVALUATION) VALUES (456, ‘COMP SCI’, 550, 451, 002, ‘NA’);
INSERT INTO CLASSES_TAKEN_BY_STUDENT( SSN, PROFESSOR_ID, SEC_NUM, DEPARTMENT_NAME, COURSE_NUMBER, PREREQ_COURSE_NUM, GRADE) VALUES (789, 123, 001, ‘COMP SCI’, 451, 450,’A+’);
INSERT INTO CLASSES_TAKEN_BY_STUDENT( SSN, PROFESSOR_ID, SEC_NUM, DEPARTMENT_NAME, COURSE_NUMBER,
PREREQ_COURSE_NUM, GRADE) VALUES (101, 456, 002, ‘COMP SCI’, 550, 451, ‘B’);
INSERT INTO PREREQ_FOR_CLASS (REQUIRED_COURSE_DEPARTMENT, REQUIRED_COURSE_NUMBER, PREREQ_COURSE_NUM) VALUES (‘COMP SCI’, 451, 450);
INSERT INTO PREREQ_FOR_CLASS (REQUIRED_COURSE_DEPARTMENT, REQUIRED_COURSE_NUMBER,
PREREQ_COURSE_NUM) VALUES (‘COMP SCI’, 550, 451);

INSERT INTO STUDENT_ENROLLMENT(E_ID, GNUMBER, SEC_NUM, WAIT_LIST, WL_RANK, PROFESSOR_ID, GTA_ID) VALUES (1, 101, 001, ‘YES’, 2, ‘456’, ‘789’);
INSERT INTO STUDENT_ENROLLMENT(E_ID, GNUMBER, SEC_NUM, WAIT_LIST, WL_RANK, PROFESSOR_ID, GTA_ID)
VALUES (2, 103, 001, ‘YES’, 9, ‘123’, ‘101’);