MIS 64082 Practicum 2, Part 1 Solution

INSTRUCTIONS
What You Have to Do
Full instructions on how to document and submit your work for grading are found on the following pages. The files you need, as well as the submission instructions, are structured in a similar fashion as to how this practicum is administered in the online version of the course. These detailed instructions will likely seem confusing to you at first, so take your time and read them very carefully (and ask me for clarification if you are still confused about anything)! You MUST follow these instructions properly in order to receive credit!
How Long It Should Take to Complete
What is Allowed
What is Not Allowed
CHECKLIST FOR SUBMISSION:

o Use the column names and datatypes as shown in the diagram. (30 points total) o Specify the ‘null/not null’ constraints as shown on the diagram. (10 points total) o Implement the constraints for the primary keys, foreign keys, and unique / alternate keys (if any) as shown in the diagram. Use the constraint names shown in the diagram.
(40 points total) o Implement the CHECK constraints described in the details found later in this document.
(60 points total) o Implement the DEFAULT values described in the details found later in this document. (10 points total)

• Be sure to order your CREATE TABLE statements (and ALTER TABLE statements, if used) properly in the DDL script you turn in to me. In other words, you must create any “parent” tables (i.e., tables on the “one” side of a one-to-many relationship, or tables representing a supertype in a one-to-one supertype-subtype relationship) first, and only then should you list the code to create the “child” tables (i.e., tables on the “many” side of a one-to-many relationship, or tables representing subtypes in a one-to-one supertype-subtype relationship). Otherwise, you will find that your foreign keys in the “child” table have no corresponding primary key in the “parent” table to reference. See the example script from last semester’s class practicum for examples if needed.


first error, then run the script again and find the first error again. If it is the same error as before, then your script change did not fix it, so try something else. If it is a new error, then this is now a valid error. Debug and repeat until no errors remain. Errors will usually be caused by misspellings of tables or columns, syntax errors such as extra or missing punctuation symbols, or could be caused by an improperly configured CHECK constraint. Start debugging an error by validating these. Search the ORA-##### error message you receive for more information on the specific error – you can do an internet search for Oracle documentation of the error message if necessary.

• Once the REFRESHDATA script inserts all the data without any errors, you are fundamentally finished except for testing and submission. Please keep in mind that if the script runs properly, this simply means that “good” data is being allowed to be inserted into your database the way it is supposed to. It does not necessarily mean that all “bad” data will be properly rejected! I highly suggest taking the additional step of testing your implementation’s CHECK and FOREIGN KEY constraints by trying to submit data that should be rejected as well as additional data that should not (if rejected, it will give you an error that it violates the check or foreign key constraint you are testing). Make sure INSERT statements that should be rejected are rejected, AND that INSERT statements that should not be rejected are not rejected (in other words, test both).


• Confirm that your account has the fully-implemented database. You can drop and recreate the tables fresh, as many times as you wish, so that they reflect only what is in your final CREATE TABLE script statements. Also make sure that the final data is inserted into your tables and has not been modified in any way. You can run the REFRESHDATA script before submitting to ensure this. Note that if you run the CREATE TABLE script again you must also run the REFRESHDATA script, or your tables will be empty.

• Submit your CREATE TABLE script that recreates your entire database as a text document with a .sql extension, in Blackboard. The file name should be your Kent userid (in your email address, it is the part before @kent.edu) followed by _DDL. For example, a submission from me would have the filename of ‘gpolites_DDL.sql’ . After you submit this file you are to make no more changes to your database until it has been graded and any grading questions have been resolved. You need not do anything to submit what you did in the database itself, it is considered submitted when you submit the script. I will log into each Oracle account directly.


Check Constraint Detail (create each listed constraint with the exact name provided):
Table: CONF_TRACKS
• CONF_TRACK_ACCEPTED_CHK1: Limit NUMBER_ACCEPTED_PAPERS to 70% (.70) of the value for NUMBER_SUBMISSIONS. In other words, a track cannot accept more than 70% of that track’s submitted papers.
• CONF_TRACK_ACCEPTED_CHK2: Limit NUMBER_ACCEPTED_PAPERS to 20 or less.

Table: MEMB_ACADEMIC
• MEMB_ACAD_DIRECTORY_CHK: Limit DIRECTORY_FLAG to only these values (make sure you match the case (upper/lower) of the acceptable values as well): YES, NO

Table: INDIV_MEMBER
• INDIV_MEMBER_TYPE_CHK: Limit MEMBER_TYPE to only these values: A, S, M, O (first letter of each subtype).
• INDIV_MEMBER_GENDER_CHK: Limit GENDER to only these values (make sure you match the case (upper/lower) of the acceptable values as well): Man, Woman, Non-binary, Prefer to selfdescribe, Prefer not to indicate

Table: CONFERENCE
• CONF_END_DATE_CHK: Limit CONF_END_DATE to values that fall on the same day as CONF_START_DATE or later.

Default values:

TABLE & COLUMN DEFAULT VALUES
CONFERENCE.CONF_COUNTRY USA
INDIV_MEMBER.COUNTRY USA
INDIV_MEMBER.JOINDATE SYSDATE INDIV_MEMBER.TITLE DR.
MEMB_ACADEMIC.AGREE_CODE_RESEARCH Y
MEMB_ACADEMIC.AGREE_CODE_CONDUCT Y