2. (20 Points) Create an ERD for a CONFERENCE_REVIEW | Solution

Assignments

2. (20 Points) Create an ERD for a CONFERENCE_REVIEW database in which researchers submit their research papers for consideration. Reviews by reviewers are recorded for use in the paper selection process. The database system caters primarily to reviewers who record answers to evaluation questions for each paper they review and make recommendations regarding whether to accept or reject the paper.  Cut and paste the diagram into the space below.

The data requirements are summarized as follows:

·         Authors of papers are uniquely identified by e-mail id. First and last names are also recorded. 

·         Each paper is assigned a unique identifier by the system and is described by a title, abstract, and the name of the electronic file containing the paper.

·         A paper may have multiple authors, but only one of the authors is designated as the contact author.

·         Reviewers of papers are uniquely identified by e-mail address. Each reviewer’s first name, last name, phone number, affiliation, and topics of interest are also recorded.

·         Each paper is assigned between two and four reviewers. A reviewer rates each paper assigned to him or her on a scale of 1 to 10 in four categories: technical merit, readability, originality, and relevance to the conference. Finally, each reviewer provides an overall recommendation regarding each paper.

·         Each review contains two types of written comments: one to be seen by the review committee only and the other as feedback to the author(s).

 

3. (30 Points) Give the DDL commands to create the family database described below.

The family database has four tables, twenty fields, four 1:N relationships and one 1:1 relationship. Use Oracle to build the database.

Data Fields

The ER diagram indicates the name and data type for each of the fields. You must use the table and field names (and capitalization) indicated in the diagram.

All but five of the fields must contain data (i.e., not null). Only the WifeID and DOM (i.e., Date of Marriage) fields in tblMan, the MaidenName field in tblWoman, and the FatherID and MotherID fields in tblChild are allowed to have missing data (i.e., nulls).

Primary Keys

ManID, WomanID and ChildID should be simple primary keys for tblMan, tblWoman and tblChild, respectively. ChildID1 and ChildID2 should be a composite primary key for the tblSiblingPair bridge table.

Secondary Indices

Six secondary indices need to be defined in order to improve the database performance (speed) once thousands of records have been inserted into the tables:

• LastName and FirstName should be a composite index for tblMan. (Note: LastName, FirstName is the classic phonebook sort order. It is not the same as FirstName, LastName.) The index should be named idxManName.
• LastName and FirstName should be a composite index for tblWoman. The index should be named idxWomanName.
• LastName and FirstName should be a composite index for tblChild. The index should be named idxChildName.
• FatherID should be a simple index for tblChild. The index should be named idxFather.
• MotherID should be a simple index for tblChild. The index should be named idxMother.
• DOB (in descending order) should be a simple index for tblChild. (Note: Descending date order means the youngest children will appear first.) The index should be named idxChildDOB.

A seventh index needs to be defined in order to handle the 1:1 spousal relationship:

• WifeID should be a simple unique secondary index for tblMan. This index will prevent two men from identifying the same woman as their wife. The index should be named idxMarriage.

All indices except the child's DOB should be in ascending order.

Data Validation

A number of data validation rules (i.e., check constraints) need to be defined in order to ensure a high level of data quality:

• All FirstNames and LastNames in tblMan, tblWoman and tblChild must have lengths of at least two. (B is not a name, but Bo is.)
• MaidenName can be blank (null). If it isn't blank, then it must have a length of at least two.
• Gender in tblChild must either be F or M. (Note: SQL is case insensitive, so it isn't necessary to check for f and m.)
• No dates (DOB and DOM) in tblMan, tblWoman and tblChild can be in the future. All dates have to be less than or equal to today's date.
• ManID, WomanID and ChildID must be greater than or equal to 1001.
• If WifeID is blank, then DOM must also be blank. If WifeID is non-blank, then DOM must also be non-blank.
• A child does not have to have a father. A child does not have to have a mother. But, every child must have at least one parent in the database.

DDL: