tailieunhanh - Database Management systems phần 2

Ý tưởng này có thể được mở rộng để đối phó với mối quan hệ thiết lập liên quan đến hơn hai bộ thực thể. Nói chung, nếu một thiết lập mối quan hệ liên quan đến bộ thực thể n và một số m trong số đó là liên kết thông qua các mũi tên trong sơ đồ ER, các mối quan hệ tương ứng với bất kỳ một trong các bộ m có thể được tăng cường để nắm bắt những mối quan hệ. | The Relational Model 71 CREATE TABLE Dept_Mgr did INTEGER dname CHAR 20 budget REAL ssn CHAR 11 since DATE PRIMARY KEY did FOREIGN KEY ssn REFERENCES Employees Note that ssn can take on null values. This idea can be extended to deal with relationship sets involving more than two entity sets. In general if a relationship set involves n entity sets and some m of them are linked via arrows in the ER diagram the relation corresponding to any one of the m sets can be augmented to capture the relationship. We discuss the relative merits of the two translation approaches further after considering how to translate relationship sets with participation constraints into tables. Translating Relationship Sets with Participation Constraints Consider the ER diagram in Figure which shows two relationship sets Manages and WorksJn. Figure Manages and Works_In 72 Chapter 3 Every department is required to have a manager due to the participation constraint and at most one manager due to the key constraint. The following SQL statement reflects the second translation approach discussed in Section and uses the key constraint CREATE TABLE Dept_Mgr did INTEGER dname CHAR 20 budget REAL ssn CHAR 11 NOT NULL since DATE PRIMARY KEY did FOREIGN KEY ssn REFERENCES Employees ON DELETE NO ACTION It also captures the participation constraint that every department must have a manager Because ssn cannot take on null values each tuple of Dept_Mgr identifies a tuple in Employees who is the manager . The NO ACTION specification which is the default and need not be explicitly specified ensures that an Employees tuple cannot be deleted while it is pointed to by a Dept_Mgr tuple. If we wish to delete such an Employees tuple we must first change the Dept_Mgr tuple to have a new employee as manager. We could have specified CASCADE instead of NO ACTION but deleting all information about a department just because its manager has been fired seems a bit extreme The constraint that every .