Model Transformation

Our original ER Diagram (See Deliverable 2)

Our original Entity-Relationship Diagram is shown as above. To transfrom the ER Model, we simplify it in several by combine similar entities and relationships.

we merge all the subtypes of Publication into one entity, and all the subtypes of Collection into one entity. Consequently, the "Publication.ID" is a foreign key of Collection.
All three kinds of relatonship collect are merged in one, and since collect is a 1-m relationship, we furtherly transform it as an attribute, the Publication.in_collection, which is a foreign key from Collection.
We combined the Author and Editor into Person, and write and edit are merged into publish relation distincted by "type" attribute.
Since name is a multi-valued attribute, we add another has relation into the schema, where the "name" and ""Person.ID" is the primary key.

The result ER Diagram is shown below:

Transformed ER Diagram

Relation Schema

We write our relation schema according to the transformed ER Diagram. (See the UML diagram below):

Person (person_id: NUMBER, affliation: VARCHAR2(200), homepage: VARCHAR(200))
Publication(publication_id: NUMBER, in_collection: NUMBER, title: VARCHAR2(200), year: INTERGER, pages: INTERGER, ee: VARCHAR2(200), url: VARCHAR2(200), type: VARCHAR2(32))
Collection(publication_id: NUMBER, booktitle: VARCHAR2(200), publisher: VARCHAR2(200),volume: INTEGER)
Name(name: VARCHAR2(200), person_id: `NUMBER**)
Publish(person_id: NUMBER, publication_id: NUMBER, type: VARCHAR2(32))
Coauthor(predecessor: NUMBER, successor: NUMBER)
Cite(predecessor: NUMBER, successor: NUMBER)

Notes

In Publication, the "type" can be book, proceeding, journal, article, inproceeding, and incollection. An article is collected inside a book, an inproceeding is collected inside a proceeding and an incollection is collected inside a collection. These relations are represented by the self referencing foreign key of "in_collection".
Inside Collection, more attributes about books, proceedings and journals are stored.
In publish, type can be "write" or "edit".

The UML Diagram of relation schema

Commands & Screenshots

Publication

CREATE TABLE publication (
  publication_id NUMBER,
  in_collection  NUMBER,

  title          VARCHAR2(200),
  year           INTEGER,
  pages          INTEGER,
  ee             VARCHAR2(200),
  url            VARCHAR2(200),
  type           VARCHAR2(32),

  CONSTRAINT ck_type_publication
  CHECK (type = 'inproceeding' OR type = 'incollection' OR type = 'article' OR
         type = 'book' OR type = 'proceeding' OR type = 'journal'),

  CONSTRAINT pk_publication
  PRIMARY KEY (publication_id),

CONSTRAINT fk_collection_publlication
FOREIGN KEY (in_collection)
REFERENCES publication(publication_id)
);

Publication SQL

Publication Table

Person

CREATE TABLE person (
  person_id   NUMBER,

  affiliation VARCHAR2(200),
  homepage    VARCHAR(200),

  CONSTRAINT pk_person
  PRIMARY KEY (person_id)
);

**Person** SQL

**Person** Table

Collection

CREATE TABLE collection (
  publication_id NUMBER,

  booktitle      VARCHAR2(200),
  publisher      VARCHAR2(200),
  volume         INTEGER,

  CONSTRAINT pk_collection
  PRIMARY KEY (publication_id),

  CONSTRAINT fk_publication_collection
  FOREIGN KEY (publication_id)
  REFERENCES publication (publication_id)
);

**Collection** SQL

**Collection** Table

Name

CREATE TABLE name (
  name      VARCHAR2(200),
  person_id NUMBER,

  CONSTRAINT pk_name
  PRIMARY KEY (name),

  CONSTRAINT fk_person_name
  FOREIGN KEY (person_id)
  REFERENCES person (person_id)
);

**Name** SQL

**Name** Table

Publish

CREATE TABLE publish (
  person_id      NUMBER,
  publication_id NUMBER,
  type           VARCHAR2(32),

  CONSTRAINT ck_type_publish
  CHECK (type = 'write' OR type = 'edit'),

  CONSTRAINT pk_edit
  PRIMARY KEY (person_id, publication_id),

  CONSTRAINT fk_editor_edit
  FOREIGN KEY (person_id)
  REFERENCES person (person_id),

  CONSTRAINT fk_publication_edit
  FOREIGN KEY (publication_id)
  REFERENCES publication (publication_id)
);

*publish* SQL

*publish* Table

Coauthor

CREATE TABLE coauthor (
  predecessor NUMBER,
  successor   NUMBER,

  CONSTRAINT pk_coauthor
  PRIMARY KEY (predecessor, successor),

  CONSTRAINT fk_author_coauthor_from
  FOREIGN KEY (predecessor)
  REFERENCES person (person_id),

  CONSTRAINT fk_author_coauthor_to
  FOREIGN KEY (successor)
  REFERENCES person (person_id)
);

*coauthor* SQL

*coauthor* Table

Cite

CREATE TABLE cite (
  predecessor NUMBER,
  successor   NUMBER,

  CONSTRAINT pk_cite
  PRIMARY KEY (predecessor, successor),

  CONSTRAINT fk_publication_cite_from
  FOREIGN KEY (predecessor)
  REFERENCES publication (publication_id),

  CONSTRAINT fk_publication_cite_to
  FOREIGN KEY (successor)
  REFERENCES publication (publication_id)
);

*cite* SQL