Database Management Fall 2003 The one-to-one and recursive relationships

Database Management Fall 2003 The one-to-one and recursive relationships

An organization chart Assume each department has one and only one manager.

Modeling a 1:1 relationship • 1:1 relationship is labeled • A relationship descriptor • Obvious relationships are not labeled DEPT EMP deptname empno deptfloor empfname empsalary deptphone deptname (FK) empno (FK) Department’s boss

Mapping a 1:1 relationship • Usual rules apply • Where do you put the foreign key? • DEPT • EMP • Both tables (Watson)

Results of mapping Manager

The SQL… (revises Watson, p. 139) CREATE TABLE DEPT (DeptName CHAR(15) NOT NULL, DeptFloor SMALLINT NOT NULL, DeptPhone SMALLINT NOT NULL, EmpNo SMALLINT NOT NULL, PRIMARY KEY(DeptName), CONSTRAINT FK_DeptHead FOREIGN KEY(EmpNo)REFERENCES EMP )

The SQL, continued… (revises Watson, p. 139) CREATE TABLE EMP (EmpNo SMALLINT NOT NULL, EmpFName CHAR(10), EmpSalary DECIMAL(7,0), DeptName CHAR(15), BossNo SMALLINT, PRIMARY KEY(EmpNo), CONSTRAINT FK_DeptMember FOREIGN KEY(DeptName)REFERENCES DEPT, CONSTRAINT FK_Boss FOREIGN KEY(BossNo) REFERENCES EMP(EmpNo) )

Oops! One small problem… You can’t execute both of the preceding Create Table statements, as written, because DEPT’s foreign key (EmpNo)requires that EMP already exist… and EMP’s foreign key (DeptName) requires that DEPT already exist. Solution: [1] Create DEPT without the foreign key. [2] Create EMP with its foreign keys. [3] Add the foreign key to DEPT: ALTER TABLE DEPT ADD CONSTRAINT FK_DeptHead FOREIGN KEY(EmpNo)REFERENCES EMP

Querying a 1:1 relationship List the salary of each department’s boss. SELECT empfname, deptname, empsalary FROM emp WHERE empno IN (SELECT empno FROM dept);

An organization chart Now model the fact that managers are also employees.

Recursion Factorial 5! = 5 x 4 x 3 x 2 x 1 OR n! = n x (n - 1) x (n – 2) x (n – 3) … OR recursive function (definition refers to itself): f(1) = 1 f(n) = f(n-1) x n

Modeling a recursive relationship • A recursive relationship relates an entity to itself • Label recursive relationships This recursive relationship is one-to-many See Watson, p. 137

Mapping a recursive relationship • Usual rules • 1:m • The entity gets an additional column for the foreign key • Need a name different from the primary key

Results of mapping bossno is a foreign key to empno in emp table

Querying a recursive relationship Use table aliases to join table to itself. Find the salary of Nancy’s boss. SELECT wrk.empfname, wrk.empsalary, boss.empfname, boss.empsalary FROM emp wrk, emp boss WHERE wrk.empfname = 'Nancy' AND wrk.bossno = boss.empno;

Joining a table with itself

Querying a recursive relationship Another perspective: display all data in a single row. Find the names of employees who earn more than their boss. SELECT wrk.empfname FROM emp wrk, emp boss WHERE wrk.bossno = boss.empno AND wrk.empsalary > boss.empsalary;

Modeling a 1:1 recursive relationship • The English monarchy succession MONARCH monarch name monarch number monarch type reign begin date previous mon name (FK) previous mon number (FK)

Mapping a 1:1 recursive relationship

SQL for the 1:1 (revises Watson, p. 143) CREATE TABLE MONARCH (MonName CHAR(15) NOT NULL, MonNum CHAR(5) NOT NULL, MonType CHAR(5) NOT NULL, RgnBeg DATETIME, PreMonName CHAR(15), PreMonNum CHAR(5), SucMonName CHAR(15), SucMonNum CHAR(5), PRIMARY KEY(MonName,MonNum), CONSTRAINT FK_Predecessor FOREIGN KEY(PreMonName,PreMonNum) REFERENCES MONARCH(MonName,MonNum), CONSTRAINT FK_Successor FOREIGN KEY(SucMonName,SucMonNum) REFERENCES MONARCH(MonName,MonNum))

A wrinkle: referential integrity complicates the initial data entry. So… ALTER TABLE MONARCH NOCHECK CONSTRAINT FK_Predecessor,FK_Successor INSERT INTO MONARCH VALUES('Victoria','I','Queen', ‘6/20/1837','William','IV','Edward','VII') . . . [INSERT statements for the other records] ALTER TABLE MONARCH CHECK CONSTRAINT FK_Predecessor,FK_Successor (Note: a similar thing must be done in entering the DEPT and EMP data in the earlier example. See SQL Server Exercise #4.)

Querying a 1:1 recursive relationship Who preceded Elizabeth II? SELECT premonname, premonnum FROM monarch WHERE monname = 'Elizabeth' and MONNUM = 'II';

Querying a 1:1 recursive relationship Was Elizabeth II's predecessor a king or queen? SELECT pre.montype FROM monarch cur, monarch pre WHERE cur.premonname = pre.monname AND cur.premonnum = pre.monnum AND cur.monname = 'Elizabeth' AND cur.monnum = 'II';

Querying a 1:1 recursive relationship List the kings and queens of England in ascending chronological order. SELECT montype, monname, monnum, rgnbeg FROM monarch ORDER BY rgnbeg;

The m:m recursive relationship … • Bill of materials (bom) problem • A product can appear as part of many other products and can be made up of many products See Watson, p. 146

Mapping an m:m recursive relationship

The SQL… (revises Watson, p. 147) CREATE TABLE product ( PRODID INT NOT NULL, PRODDESC VARCHAR(30), PRODCOST DECIMAL(9,2), PRODPRICE DECIMAL(9,2), PRIMARY KEY(prodid)); CREATE TABLE ASSEMBLY (QUANTITY INT NOT NULL, PRODID INTNOT NULL, SUBPRODID INTNOT NULL, PRIMARY KEY(PRODID,SUBPRODID), CONSTRAINT FK_MainProduct FOREIGN KEY(PRODID) REFERENCES PRODUCT, CONSTRAINT FK_SubProduct FOREIGN KEY(SUBPRODID) REFERENCES PRODUCT(PRODID) )

Querying an m:m recursive relationship List the product identifier of each component of the animal photography kit. SELECT subprodid FROM product, assembly WHERE proddesc = 'Animal photography kit' AND product.prodid = assembly.prodid;

Querying an m:m recursive relationship List the product description and cost of each component of the animal photography kit. SELECT proddesc, prodcost FROM product WHERE prodid IN (SELECT subprodid FROM product, assembly WHERE proddesc = 'Animal photography kit' AND product.prodid = assembly.prodid);

Database Management Fall 2003 The one-to-one and recursive relationships