SQL Oracle Inheritance Relational Database

Question

这是一张图片：

这是我的编程进展情况：

CREATE TYPE appointment_list_type AS TABLE OF REF appointment_type;
/

CREATE OR REPLACE TYPE person_type AS OBJECT (
personID NUMBER,
Surname varchar2(10),
Forname varchar2(10),
dateOfBirth date,
AddressLine1 varchar2(30),
AddressLine2 varchar2(30),
Town varchar2(10),
contacTel1 varchar2(10),
contacTel2 varchar2(10)) NOT FINAL;
/

CREATE TYPE applicant_type UNDER person_type(
applicantID NUMBER,
maxPrice number(7,2),
desiredArea varchar2(10),
Attends appointment_list_type
);
/

CREATE TYPE salesperson_type UNDER person_type(
salespersonID NUMBER,
manager varchar2(10),
Makes appointment_list_type
);
/

这是将人员类型分为销售员和申请人继承的过程。

CREATE TYPE appointment_type AS OBJECT(
appointmentID NUMBER,
Appdate date,
Apptime timestamp,
appointmentType varchar2(10),
levelOfInterest varchar2(10),
offerMade varchar2(10),
Made_by REF salesperson_type,
Attends_by REF applicant_type
);
/

这是“约会类型”，引用起来可以将它们关联在一起。

创建表格的步骤如下：

CREATE TABLE person_table OF person_type (
personID PRIMARY KEY NOT NULL)
NESTED TABLE Attends STORE AS attend_meeting_table;

CREATE TABLE applicant_table OF applicant_type (
personID PRIMARY KEY NOT NULL)
NESTED TABLE Attends STORE AS attend_meeting_table;

CREATE TABLE salesperson_table OF salesperson_type (
personID PRIMARY KEY NOT NULL)
NESTED TABLE Makes STORE AS makes_meeting_table;    

CREATE TABLE appointment_table OF appointment_type (
appointmentID PRIMARY KEY NOT NULL,
SCOPE FOR (Made_by) IS person_table,
SCOPE FOR (Attends_by) IS person_table);

以下是我所做的一些代码，现在这是我的问题：

如果直接在约会中进行1对多操作，继承如何工作？

我非常困惑。有人能帮我解决如何处理此问题吗？

- Scot365

---

在常规的SQL语句中，您不需要使用;和/。实际上，两者一起使用会导致问题。有关详细信息，请参见此处：https://dev59.com/sHNA5IYBdhLWcg3wIqLr#10207695 - user330315

Answer 1

哎呀，我想我终于弄清楚了你的烦恼是什么了...

目前，在申请人表和销售员表中引用的约会完全是独立的。这意味着申请人可能与正在与其他人会面的销售人员会面 :)

当然，您希望所有约会都存储在约会表中。

这是对象视图的完美用例。您根本不需要这些对象表。关系表更容易管理。

只需创建普通表，然后创建对象视图，就像为 SALESPERSON 创建的这个一样：

create view ov_salesperson as
 (select personID,
         salespersonID,
         SALESPERSON_TYPE
          (personID
           Surname,
           Forname,
           dateOfBirth,
           AddressLine1,
           AddressLine2,
           Town,
           contacTel1,
           contacTel2,
           salespersonID,
           manager,
           CAST 
            (MULTISET 
              (Select appointment_type
                       (appointmentID,
                        Appdate,
                        Apptime,
                        appointmentType,
                        levelOfInterest,
                        offerMade,
                        salesperson_id,
                        applicant_id
                       )
               From   appointment_table  A
               Where  A.salesperson_id = S.salesperson_id
              ) 
              as appointment_list_type 
            )
          ) as salesperson_obj
  from salesperson_table S
 );

- HAL 9000