Question

Your firm maintains a citation index of biomedical journal articles used by a variety of medical research institutes and pharmaceutical firms. Each article is identified by a unique "DOI number"; in addition to this number, you want the database to store each article's title, abstract, date of publication, journal volume number, journal issue number, and journal pages. Each article is authored by one or more researchers. For each researcher, your database should store a family name, given name, date of birth, and current employer. The database should remember which researchers authored each article, and the order they appear in the article writing credits.

Each article appears in a single journal, and each journal is identified by a unique "ISSN" number. The database should be able to identify which journal each article appeared in. Furthermore, for each journal, the database should remember its name and the name of the organization that publishes it. Sometimes journals change names, and one journal "continues' another journal. For example, the ORSA Journal on Biomedical Computation might become the INFORMS Journal on Biomedical Computation when its publishing organization merges with another organization. The name change makes it a different journal but the database should be able to remember which journal (if any) "continues" any given journal.

Finally, articles usually contain multiple citations to other articles. For example, an article about clinical treatment of a particular disease might contain citations to dozens of prior research articles about that disease. This information can be very useful to researcher, so the database should be able to store the full pattern of citations between articles. Note that an article not only usually cites many other articles but may also be cited by many other articles.

1. Create a high-level ERD which shows only the cardinality and modality between the main entities.
2. Write out the tables including the primary keys and the foreign keys.(see details below)
3. Complete a full ERD with all tables, cardinalities and modalities.

Answer 1

Diagram:

The above diagram is completely based upon the requirements mentioned in the case study. In the diagram the entities are defined along with their primary key and foreign keys which interlink to other entity. Also, the relationships are defined along with different cardinalities.

The tables along with the required keys is as follows:

(I) Article (DOI Number, Title, Abstract, DateOfPublication, ISSNNumber)

Primary key: DOI Number

Foreign Key: ISSN Number (Journal)

(ii) Journal (ISSNNumber, Name, Organization, Pages, VolumeNumber)

Primary key: ISSN Number

Foreign key: None

(iii) Researcher (ResearcherID, FamilyName, GivenName, DateOfBirth, CurrentEmployee)

Primary Key: ResearchID

Foreign key: None

(iv) Citation (CitationID, CitationName, ISSNNumber, DOI Number, Date)

Primary key: CItationID

Forieign key: ISSN Number(Journal), DOI Number(Article)

a. As a particular journal may contain several articles in it, the relationship between the entity article and journal is many to one. But a particular article must belong to at least one journal where the cardinality of the relationship is mandatory one.

b. Each article is authored by one or more researcher where the primary key of the researcher entity is researchID and all the other attributes are provided as mentioned in the case study.

c. A particular journal can be renamed as another journal and it becomes a separate one but also the details of the previous one should be tracked. To represent this the relationship rename is used which surrounds with the entity journal.

d. Citation holds a primary key named citationID and some foreign keys which relates them to other entities. The cardinality between the entities article and citation is one to many.