Project IS

Project Stage 2: Oracle Implementation

25 Points. Submit all documents to Canvas.

Size of Database

The ideal size of the project is about 5-7 tables. This offers enough complexity and is also manageable. More than seven tables may be too large.

Oracle Account to Use and Clean Up

1. Each student has another account with the userid ORA##PRJ. The initial password is dbclass. (So you log in using ORA##PRJ as userid and dbclass as password. ## is the two digits assigned to you.)

2. You must use ORA##PRJ for this part of the project (otherwise you’ll lose points).

3. Clean up your ORA##PRJ account first by removing all tables irrelevant to your project. By the time I grade your project, your ORA##PRJ must contain only tables relevant to your project (otherwise you’ll lose points).

Be sure to change your password otherwise someone else may accidentally log in to your account, remove all your tables, and put his/her tables in your account. You would lose a lot of points if that happens to you.

PRJ2 Steps

Step 1) (5 Points). Implement all tables in ACCESS. Enter some data in each table. Define the PKs and the FKs. You may use fictitious data. For tables like Customer, Emp, and Product, enter 5 or more rows of data; for tables like Order, Purchase, Transaction, Class, enter 7 or more rows of data; For tables like TransactionDetails, OrderDetails (i.e., tables created from m-n relationships), enter 12 or more rows of data.

Step 2: (20 Points): Oracle SQL

a). Drop all tables in your project account (ORA##PRJ) that are irrelevant to your project. Each group member must do this and is evaluated separately for a)..

b). Choose 6 tables connected through PK-FK pairs (no islands) AND create those tables in each group member’s ORA##PRJ account. In a script file, create the tables, define all primary keys and foreign keys, and insert the same data that you entered in ACCESS. If a table has many rows of data, inserting up to 12 rows of data is sufficient. See cretabs.sql posted in Oracle module for example. (For example, the tables that we have been working with: Product—Item—Ord—Customer—Emp—Dept are 6 tables that are connected by PK-FK pairs as indicated by “—“. If you have 6 or fewer tables, create all your tables in Oracle.)

Note, I ask you to create the tables in ACCESS first so you only need to type the actual data once. After entering the data in ACCESS, from ACCESS, you can use the “Export” function to export the data in each table to a text file. Then you can combine the text files and modify the combined text file to create the script file for Oracle.

c). At the beginning of the script, add some comments (use /* */ or use --) to provide a “Cover Page.” The comments area should contain i) name of project, course number and title, semester and year, ii) all group member names, and iii) Oracle account Ids for all group members. I need your account user names but not your passwords to grade your project.

d). Each group member should run the script in his/her own ORA##PRJ account and generate the same project tables. If your account does not contain your project tables, you’ll lose 12 points for Step 2. Group members are graded separately for d).

You’ll lose points if 1) you did not indicate on your cover page your oracle account(s) used for your project, or 2) your ora##prj account has irrelevant tables.

Deliverables

1. An ACCESS file containing all your tables, with the primary keys and foreign keys defined and data inserted.

2. ONE script file creating 6 of your tables, related keys, and data, with a comments area at the beginning that serves as a the “Cover Page.” Do not put the “Cover Page” on a separate file.

3. The 6 Tables with data and related keys created in your Oracle project accounts (ORA##PRJ). This is the result of Step 2. I'll check them in your Oracle project accounts using my own script. There is nothing to actually submit for this deliverable item. Run the script in each group member’ account to create the tables, define constraints, and insert data.

Submission of project

Submit both documents (ACCESS file and Script file) through Canvas in one group member’s Canvas account. Make sure you have attached both files before hitting the submit button. Note that multiple files can be attached, one-by-one, BEFORE clicking on the Submit button.

Do not make two submissions each with a different file. When I download students’ submissions for grading, only files in the last submission will be downloaded. So if you have to make another submission before the deadline, attach both files again.

Do not make submissions from two or more group members’ Canvas accounts. Submit both files from one group member’s Canvas account only.

2 | Page