RDBMS-Assignment-2

Q1. Convert the following ER diagram into logical schemas. (20pt)









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

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

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

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

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

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)
)

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

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)
)

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)
)

Q3

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.

Q4

  • 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';
  • 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'
  • 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;
  • 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);
  • Student ‘Dennis’ quit from the college. Please remove his information from the database.
DELETE FROM student
WHERE sname='Dennis'
Table Of Contents
RDBMS-Assignment-2
Q1. Convert the following ER diagram into logical schemas. (20pt)
Q2. Implement your logical design in SQL. You need to select suitable data types and link foreign keys properly. (20pt)
Q3
Q4