1. **State the problem:**
We need to refactor the database schema by creating a new supertype table `person` that holds common columns from `customer` and `staff` tables, then adjust `customer` and `staff` tables accordingly.
2. **Step 1: Review columns**
Examine `Initialize.sql` to identify common columns in `customer` and `staff` tables, including their data types and constraints.
3. **Step 2: Create `person` table**
Use the following SQL structure:
$$\text{CREATE TABLE person (}
\quad person\_id SMALLINT UNSIGNED PRIMARY KEY,
\quad \text{common columns with same data types and constraints},
\quad address\_id SMALLINT UNSIGNED,
\quad \text{FOREIGN KEY (address\_id) REFERENCES address(address\_id) ON DELETE ... ON UPDATE ...}
\text{);}$$
4. **Step 3: Drop common columns from `customer` and `staff`**
- Drop foreign key constraints on `address_id` first:
$$\text{ALTER TABLE customer DROP FOREIGN KEY fk\_customer\_address;}$$
$$\text{ALTER TABLE staff DROP FOREIGN KEY fk\_staff\_address;}$$
- Drop the common columns including `address_id`:
$$\text{ALTER TABLE customer DROP COLUMN common\_column1, DROP COLUMN common\_column2, ..., DROP COLUMN address\_id;}$$
$$\text{ALTER TABLE staff DROP COLUMN common\_column1, DROP COLUMN common\_column2, ..., DROP COLUMN address\_id;}$$
5. **Step 4: Implement IsA relationships**
- Drop existing primary key columns in `customer` and `staff`:
$$\text{ALTER TABLE customer DROP PRIMARY KEY;}$$
$$\text{ALTER TABLE staff DROP PRIMARY KEY;}$$
- Add new primary key `person_id`:
$$\text{ALTER TABLE customer ADD COLUMN person\_id SMALLINT UNSIGNED PRIMARY KEY;}$$
$$\text{ALTER TABLE staff ADD COLUMN person\_id SMALLINT UNSIGNED PRIMARY KEY;}$$
- Add foreign key constraints referencing `person` with CASCADE rules:
$$\text{ALTER TABLE customer ADD FOREIGN KEY (person\_id) REFERENCES person(person\_id) ON DELETE CASCADE ON UPDATE CASCADE;}$$
$$\text{ALTER TABLE staff ADD FOREIGN KEY (person\_id) REFERENCES person(person\_id) ON DELETE CASCADE ON UPDATE CASCADE;}$$
This completes the refactoring to implement the supertype `person` table and IsA relationships.
Person Supertype C40105
Step-by-step solutions with LaTeX - clean, fast, and student-friendly.