RDBMS-Assignment-2

CREATE TABLE person (
    p_id INT PRIMARY KEY,
    name VARCHAR(255),
    gender VARCHAR(10)
);
Copy

CREATE TABLE employ (
    p_id INT PRIMARY KEY,
    position VARCHAR(100),
    salary DECIMAL(10, 2),
    FOREIGN KEY (p_id) REFERENCES person(p_id)
);
Copy

CREATE TABLE customer (
    p_id INT PRIMARY KEY,
    class VARCHAR(50),
    phone VARCHAR(20),
    FOREIGN KEY (p_id) REFERENCES person(p_id)
);
Copy

CREATE TABLE purchase (
    b_id INT PRIMARY KEY,
    date DATE,
    p_id INT,
    FOREIGN KEY (p_id) REFERENCES person(p_id)
);
Copy

CREATE TABLE use(
  id INT PRIMARY KEY,
  b_id INT PRIMARY KEY,
)
Copy

CREATE TABLE coupon(
  id INT PRIMARY KEY,
  disconut DECIMAL(10,2),
  p_id INT,
  t_id INT,
  FOREIGN KEY (p_id) REFERENCES person(p_id)
  FOREIGN KEY (t_id) REFERENCES product_type(t_id)
)
Copy

CREATE TABLE product_type(
  t_id INT PRIMARY KEY,
  manufacturer VARCHAR(50),
  type VARCHAR(20),
  price DECIMAL(10,2),
)
Copy

CREATE TABLE contain(
  b_id INT PRIMARY KEY,
  i_id INT,
  FOREIGN KEY (b_id) REFERENCES purchase(b_id),
  FOREIGN KEY (i_id) REFERENCES product(i_id)
)
Copy

CREATE TABLE product(
  i_id INT PRIMARY KEY,
  t_id INT PRIMARY KEY,
  exp_date date,
  man_date date,
  FOREIGN KEY (t_id) REFERENCES product_type(t_id)
)
Copy

The issue with the given ER model is that it lacks a direct relationship between the coupon and the products it can be applied to. This absence of a direct link creates a challenge in enforcing the constraint that a purchase using a coupon must include at least one product instance that corresponds to the type eligible for that coupon.

1. Add a table/entity to represent coupon-product type associations:
   Create a table like coupon_product_type with columns such as coupon_id and product_type_id to denote which product types a coupon can be applied to.

2. Modify the purchase entity to include coupon usage and enforce the constraint:
   Within the purchase entity, introduce a column coupon_used to denote whether a coupon was used in a purchase. Then, use a constraint or validation rule to ensure that if coupon_used is true, there must be at least one corresponding product instance in the purchase that matches the product type eligible for the coupon.

• Find the number of courses taught by Goliath (instructor’s name) over the years.

SELECT COUNT(DISTINCT cID) AS num_courses_taught
FROM teach
WHERE iID = 'Goliath';
Copy

• Find the number of courses taught by each instructor in the semester 23F.

SELECT COUNT(DISTINCT cID) As num_courses_taught_each_instructor
FROM instructor
JOIN teach USING(iID)
GROUP BY (i.name)
WHERE teach.semester='23F'
Copy

• Find the semester in which Goliath teaches more courses than other semesters.

SELECT teach.semester, COUNT(teach.cID) AS num_courses
FROM teach JOIN instrtuctor USING(iID)
WHERE instructor.iname='Goliath'
GROUP BY teach.semester
ORDER BY num_courses DESC
LIMIT 1;
Copy

• Insert a new student of ID: 123456, name: ‘Tomas’, gender: Male, GPA: unknown, major: ACCT, and phone number: 32165498701.

INSERT INTO student VALUES 
(123456, 3, 'Tomas','Male',NULL,'ACCT', 32165498701);
Copy

• Student ‘Dennis’ quit from the college. Please remove his information from the database.

DELETE FROM student
WHERE sname='Dennis'
Copy