RDBMS-Assignment-2

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

$person=(\underset{―}{p\mathrm{\_}id},name,gender)$$employ=(\underset{―}{p\mathrm{\_}id},position,salary)$$customer=(\underset{―}{p\mathrm{\_}id},calss,phone)$$purchase=(\underset{―}{b\mathrm{\_}id},date,p\mathrm{\_}id)$$coupon=(\underset{―}{id},discount,p\mathrm{\_}id,t\mathrm{\_}id)$$use=(\underset{―}{id},b\mathrm{\_}id)$$product\mathrm{\_}type=(\underset{―}{t\mathrm{\_}id},manufacturer,type,price)$$contain=(\underset{―}{b\mathrm{\_}id},l\mathrm{\_}id)$$product=(\underset{―}{i\mathrm{\_}id},exp\mathrm{\_}date,man\mathrm{\_}date,\underset{―}{t\mathrm{\_}id})$

Q2. Implement your logical design in SQL. You need to select suitable data types and link foreign keys properly. (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'