Do the problem 9; The Journal of Commerce

9-1) Write all the BUSINESS RULES from the given narrative. If in doubt, please ask your instructor for clrification.

9-2) Determine all the entities their keys and attributes of each entity.

9-3) Determine all Supertype and Subtype entities and their attributes

9-4) Determine all the relations along with their attributes.

9-5) Draw the EERD using UML notation

9-6) Draw the RELATIONAL DIAGRAM using UML notation

9-7) Write the DB SCHEMA

9-8) Write CREATE statement for each relation and create your DB

9-9) Make up DATA for your relations and write INSEERT statement

9-10) Make up 10 queries and execute them and save the output in a file

ALL YOUR WORK WILL BE SUBMITTED IN A REPORT ( only single copy)

DO NOT COPY PLEASE. I NEED YOUR OWN WORK

The Journal of E-commerce Research Knowledge is a prestigious information systems

research journal. It uses a peer-review process to select manuscripts for publication.

Only about 10 percent of the manuscripts submitted to the journal are accepted for

publication. A new issue of the journal is published each quarter. Create a complete

ERD to support the business needs described below.

Unsolicited manuscripts are submitted by authors. When a manuscript is

received, the editor assigns it a number and records some basic information

about it in the system, including the title of the manuscript, the date it was

received, and a manuscript status of received. Information about the author(s)

is also recorded, including each authors name, mailing address, email address,

and affiliation (the authors school or company). Every manuscript must have

an author. Only authors who have submitted manuscripts are kept in the system.

It is typical for a manuscript to have several authors. A single author may have

submitted many different manuscripts to the journal. Additionally, when a manuscript

has multiple authors, it is important to record the order in which the

authors are listed in the manuscript credits.

At his or her earliest convenience, the editor will briefly review the topic of

the manuscript to ensure that its contents fall within the scope of the journal.

If the content is not appropriate for the journal, the manuscripts status

is changed to rejected, and the author is notified via email. If the content is

within the scope of the journal, then the editor selects three or more reviewers

to review the manuscript. Reviewers work for other companies or universities

and read manuscripts to ensure their scientific validity. For each reviewer, the

system records a reviewer number, name, email address, affiliation, and areas

of interest. Areas of interest are predefined areas of expertise that the reviewer

has specified. An area of interest is identified by an IS code and includes a

description (e.g., IS2003 is the code for database modeling). A reviewer can

have many areas of interest, and an area of interest can be associated with

many reviewers. All reviewers must specify at least one area of interest. It is

unusual, but possible, to have an area of interest for which the journal has

no reviewers. The editor will change the status of the manuscript to under

review and record which reviewers received the manuscript and the date

it was sent to each reviewer. A reviewer will typically receive several manuscripts

to review each year, although new reviewers may not have received any

manuscripts yet.

The reviewers will read the manuscript at their earliest convenience and provide

feedback to the editor. The feedback from each reviewer includes rating the

manuscript on a 10-point scale for appropriateness, clarity, methodology, and

contribution to the field, as well as a recommendation for publication (accept

or reject). The editor will record all of this information in the system for each

review received, along with the date the feedback was received. Once all of the

reviewers have provided their evaluations, the editor will decide whether to publish

the manuscript and change its status to accepted or rejected. If the manuscript

will be published, the date of acceptance is recorded.

Once a manuscript has been accepted for publication, it must be scheduled. For

each issue of the journal, the publication period (fall, winter, spring, or summer),

publication year, volume, and number are recorded. An issue will contain

many manuscripts, although the issue may be created in the system before it is

known which manuscripts will be published in that issue. An accepted manuscript

appears in only one issue of the journal. Each manuscript goes through a

typesetting process that formats the content, including fonts, font size, line spacing,

justification, and so on. Once the manuscript has been typeset, its number

of pages is recorded in the system. The editor will then decide which issue each

accepted manuscript will appear in and the order of manuscripts within each

issue. The order and the beginning page number for each manuscript must be

stored in the system. Once the manuscript has been scheduled for an issue, the

status of the manuscript is changed to scheduled. Once an issue is published,

the print date for the issue is recorded, and the status of each manuscript in that

issue is changed to published.

Business Rules: 1. Unsolicited manuscript received by author 2. Manuscript has many authors 3. Single author submits many manuscripts 4. Reviewer can have many areas of interest 5. Area of interest is associated with many reviewers 6. Issue will contain many manuscripts 7. Accepted manuscript will appear in one issue of journal. 8. It is possible to have an area of interest with no reviewers 9. When a manuscript has multiple authors, it is important to record the order in which the authors are listed in the credits. 10. If the editor decides to publish manuscript, the status is changed to accepted. 11. The manuscript must be either accepted or rejected- if it is published it is accepted. 12. Once a manuscript is accepted for publication it must be scheduled. Attributes and Entities: Authors Publication title Pub_Period Feedback Date(FK) Pub_Year Status Pub_Volume Auth_Name (SK) Pub_Number(SK) Auth_Address Auth_Affiliation Area of Interest Manuscript Editor Reviewer Feedback IS_Code (SK) Manu_Number(SK) Feeback date (FK) Rev_Name appropriateness Description Manu Title Rev_Name Rev_Email Add. (SK) clarity Manu_Date Rev_Affiliation methedology Manu_Status IS_Code (FK) contribution recomendation Feedback_Date (si Relationships: Entity Author Editor Reviewer Business rule Selects Reviews Have Destination Entity Reviewers Manuscripts Areas of interest Publications Issues Reviewer Relationship one or more: Many Three or more: One One or Many: Zero or Many One or many: one One or many: One One: One or many Manuscript Editor Feedback Accepted Schedules Received Business Rules: 1. Unsolicited manuscript received by author 2. Manuscript has many authors 3. Single author submits many manuscripts 4. Reviewer can have many areas of interest 5. Area of interest is associated with many reviewers 6. Issue will contain many manuscripts 7. Accepted manuscript will appear in one issue of journal. 8. It is possible to have an area of interest with no reviewers 9. When a manuscript has multiple authors, it is important to record the order in which the authors are listed in the credits. 10. If the editor decides to publish manuscript, the status is changed to accepted. 11. The manuscript must be either accepted or rejected- if it is published it is accepted. 12. Once a manuscript is accepted for publication it must be scheduled. Attributes and Entities: Authors Publication title Pub_Period Feedback Date(FK) Pub_Year Status Pub_Volume Auth_Name (SK) Pub_Number(SK) Auth_Address Auth_Affiliation Area of Interest Manuscript Editor Reviewer Feedback IS_Code (SK) Manu_Number(SK) Feeback date (FK) Rev_Name appropriateness Description Manu Title Rev_Name Rev_Email Add. (SK) clarity Manu_Date Rev_Affiliation methedology Manu_Status IS_Code (FK) contribution recomendation Feedback_Date (si Relationships: Entity Author Editor Reviewer Business rule Selects Reviews Have Destination Entity Reviewers Manuscripts Areas of interest Publications Issues Reviewer Relationship one or more: Many Three or more: One One or Many: Zero or Many One or many: one One or many: One One: One or many Manuscript Editor Feedback Accepted Schedules Received