Transformation Rules (Cont.)

Transformation Rule 4. N-1 Relationships
When two entities E and F take part in an N-1 relationship R: Below shows the many-to-one relationship teach, where an instructor can teach many courses, and a course must be taught by one and only one instructor. The relationship teach is not mapped to any table.

The table course now includes a column from the primary identifier of the entity instructor.
⇓
instructor
insid lname office_no ext
309 O’Neil S-3-223 78543
123 Regen S-3-547 78413
113 Smith S-3-115 78455
... ... ... ...
course
cid insid name room period
120 309 CS240 M-1-213 MW6
940 309 CS630 N-1-214 MW7:30
453 123 CS632 M-2-614 TTH6
... ... ... ... ...

Below is the corresponding SQL code: 

DROP TABLE instructor;
DROP TABLE course;

CREATE TABLE instructor (
  insid      INTEGER PRIMARY KEY,
  lname      VARCHAR(32) NOT NULL,
  office_no  VARCHAR(16),
  ext        INTEGER );

INSERT INTO instructor VALUES( 309, 'O''Neil', 'S-3-223', 78543 );
INSERT INTO instructor VALUES( 123, 'Regen',   'S-3-547', 78413 );
INSERT INTO instructor VALUES( 113, 'Smith',   'S-3-115', 78455 );
...

CREATE TABLE course (
  cid     INTEGER PRIMARY KEY,
  insid   INTEGER,
  name    VARCHAR(32) NOT NULL,
  room    VARCHAR(16),
  period  VARCHAR(16),
  FOREIGN KEY ( insid ) REFERENCES instructor( insid ) );

INSERT INTO course VALUES( 120, 309, 'CS240', 'M-1-213', 'MW6' );
INSERT INTO course VALUES( 940, 309, 'CS630', 'M-1-214', 'MW7:30' );
INSERT INTO course VALUES( 453, 123, 'CS632', 'M-2-614', 'TTH6' );
...