How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask TheDoctor Your Own Question

TheDoctor
TheDoctor, Professional w/Adv. Degree
Category: Homework
Satisfied Customers: 1485
Experience:  M.S. in Internet Information Systems
59032869
Type Your Homework Question Here...
TheDoctor is online now
A new question is answered every 9 seconds

I need help with an SQL database which is supposed to run under

Resolved Question:

I need help with an SQL database which is supposed to run under Oracle Database Express. It is a video store with basic functions and must be based on my own ERD and metadata.
Additionally the following tasks have to be performed:
a. Retrieve all of your customers' names, account numbers, and addresses (street and zip code only), sorted by account number.
b. Retrieve all of the videos rented in the last 30 days and sort in chronological rental date order.
c. Produce a list of your distributors and all their information sorted in order by company name.
d. Update customer names to change their maiden names to married names. You can choose which records to update.
e. Delete customers from the database. You can choose which records to delete.

I need this by this evening (3 March 2013)
Submitted: 1 year ago.
Category: Homework
Expert:  TheDoctor replied 1 year ago.
Hello and thank you for using JustAnswer. It would be my pleasure to assist you with this.

Please send the ERD and data by zipping up your files and uploading the zip file to http://ge.tt

Once uploaded, you will be given a link on the right. Click on the 'Copy Link' option and then paste it here.

Thank you
Customer: replied 1 year ago.

Thank you for the quick reply.


 


I uploaded the needed information to http://www.ge.tt/2nMe9wZ?c


 


The tables should match as closely as possible my ERD and metadata; however, I know there is room for improvement as I didn't know all the possible data types when I generated the outline.


 


Would you be able to complete this so it functions as required by project 2 today?


 


Thank you again

Expert:  TheDoctor replied 1 year ago.
Yes, I can get this to you.

The only issue I see with you ERD is the Movie Title. First, you should generally never use a character field as a key. It will make joins much slower. Second, there are a ton of movies that have the same title. So it is both a functional issue and a logical issue.

I will contact you once this is complete.
Customer: replied 1 year ago.

Whatever you modify, as long as it doesn't seem to advanced as this is my first SQL class ever. Just give me a basic explanation why and what is different from my initial turn in and I will write something explaining myself.


 


Thank you

Expert:  TheDoctor replied 1 year ago.
Thank you. I will contact you once this is complete.
Expert:  TheDoctor replied 1 year ago.
Hello again!

You can download the document here:


This is a word document which contains all of the SQL needed for these requirements.

You will find my comments about a few changes in red.

First you will find all of the CREATE TABLE statements. These have to be run in the order they are in the document because of foreign key constraints.

Then I included enough insert statements to make the queries (a-e) actually return data. You can, of course, add as many inserts as you want/need. Simply use the inserts that I have provided as templates.

After that, you will find the SELECT queries (A-E).

All queries in the file have been run in Oracle and work correctly.

Let me know if you have any issues or questions. I'm here to help.

If you require assistance with databases or web development in the future, please feel free to ask for me directly by starting your questions with **For TheDoctor**. It would be my pleasure to assist you again.

Please remember to rate my answer. It is the only way that I receive credit for my time and effort. If you require more assistance, simply continue our conversation before leaving any rating.

Thank you so much for using JustAnswer. Have a great day!
Customer: replied 1 year ago.


Looks good so far. I'm getting ready to load it into SQL. The only part I didn't catch yet was the failed input (step 2 in the project description document). Could you write the two failing statements also?


 


I will be gone for a couple hours, then be able to run through everything and test it out.


 


Thank you again for the fast help

Expert:  TheDoctor replied 1 year ago.
I apologize. I completed the items that you requested in your question, not the additional items that you did not ask for in your question.

Since you are now saying that you require these additional parts that were not part of your original request, they will require many additional hours of work.

I will contact you once that is complete.
Customer: replied 1 year ago.


I'm sorry, I did not notice that I didn't add that. But I am very happy so far, and will be depositing a little more than originally posted. I was half asleep and meant to mention that there was more detailed info in the projected document.

Expert:  TheDoctor replied 1 year ago.
Hello again!

Here is the assignment in all four parts:


I included all of the inserts also. I had to change one create table to include a CHECK constraint.

Again, I ran everything in oracle and reran all of the queries in Step 4 to ensure that they still work correctly.

Let me know if you have any other questions. Again, if you need anything in the future, please feel free to request me directly by starting your questions with **For TheDoctor**

Please remember to rate my answer. Thank you so much!
Customer: replied 1 year ago.

Thank you. It will be a couple hours till I get back to my computer. I'll load everything up as soon as I can.

Expert:  TheDoctor replied 1 year ago.
No problem. I look forward to hearing back from you. I promise that everything works and is complete.

Once you have reviewed everything, please remember to rate my answer.

Thank you again!
Expert:  TheDoctor replied 1 year ago.

Hello again!

How is it going? Do you require additional assistance? All of the queries work in Oracle. Have you had an opportunity to run through them?

 

Make sure that you run them in order.

 

Also, the last one has to be run one query at a time because they are select queries. It doesn't really make sense to run them all at once, unless you are spooling.

 

And make certain you refer to the original docx file I sent for my comments in red.


Just let me know if you have any additional questions or you have any difficulty. I'm here to help.

Please remember to rate my answer. Thank you so much!

Customer: replied 1 year ago.

Just got back and managed to run the scripts.


Script 3 posted 40 errors, I uploaded a CSV of the results at http://www.ge.tt/1OoVnzZ/v/0?c


 


Everything else seemed just fine

Expert:  TheDoctor replied 1 year ago.
I would need to see EXACTLY what you ran. It doesn't look like what you ran is exactly everything that I sent you.

If you made any changes to any tables whatsoever, you are going to have problems.

If you made any changes to any of the inserts, there will be problems.

It is erroring out on dates. For instance, the movie_information inserts:

Here they are:

INSERT INTO MOVIE_INFORMATION VALUES ('Legend', 1, 114, 'A young man must stop the Lord of Darkness', 'Fantasy', '18-APR-1985');
INSERT INTO MOVIE_INFORMATION VALUES ('Step Up 3D', 2, 114, 'Dancing in New York', 'Fantasy', '06-Jul-2012');
INSERT INTO MOVIE_INFORMATION VALUES ('Clue', 3, 114, 'Figure out who killed whom', 'Fantasy', '18-APR-1980');
INSERT INTO MOVIE_INFORMATION VALUES ('Avatar', 4, 114, 'Blue people swinging in trees', 'Fantasy', '18-APR-2009');
INSERT INTO MOVIE_INFORMATION VALUES ('The Hobbit', 5, 114, 'Short people go on a holiday', 'Fantasy', '25-Dec-2012');


Did you do something to change the data type in movie_information, change the order of columns in movie information, or change the date format in the inserts? Doing any of that will break these queries. Subsequently, all the queries that have foreign keys to these tables will error out because of a key violation, giving you 40 errors.
Customer: replied 1 year ago.


No, didn't change any of it. Loaded all the scripts in Application Express and ran them in order.


How do I unload the database? I even set a new account and re-ran all without any changes, same errors though

Expert:  TheDoctor replied 1 year ago.
Run this query on your database and let me know what it returns:

SELECT VALUE FROM nls_session_parameters WHERE PARAMETER = 'NLS_DATE_FORMAT';
Customer: replied 1 year ago.

it returned


mm/dd/yyyy

Expert:  TheDoctor replied 1 year ago.
Alright, that is not the default ORACLE date_format. The ORACLE Default is DD-MON-RR, which is the format the queries I sent you are in.

That is why you are receiving all these errors. We can change it to be int he format that your installation is using, but I don't know that it will be acceptable for you to hand it, as that is not the default ORACLE format. So if your instructor attempted to run your queries on a default ORACLE installation, and you made this change, your queries would fail.

You will see throughout the queries that I sent you the data format is like this:

18-APR-2012

That is the ORACLE default. For whatever reason, you installation is using a different format that is not the default, causing you these errors. When the queries that I sent you are run on a default configuration oracle setup, they all run fine.

I would suggest that you not change the queries that I sent you. Instead, I would suggest you run the following query:

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR';

Then you rerun script 3. You will get errors for those items that were previously inserted and it attempts to insert again (primary key violation). But the items that failed to insert the first time will be inserted.

I promise you that the queries are accurate for default oracle settings and are good to turn in to your course.

Let me know if you have any other questions.

Please remember to rate my answer. Thank you again.
Customer: replied 1 year ago.

After running that line I was only able to add one extra line of code (line 10).


 


Why am I getting an error with MOVIE.FK saying parent key not found?

Expert:  TheDoctor replied 1 year ago.
You would get a foreign key constraint violation if a previous insert didn't succeed.

All of the queries are written perfectly. I have run every single one a bunch of times. I have written countless Oracle databases in professional environments and hold a Master's degree in Internet technology. The issues you are running into has to do with your specific setup. The queries are 100% accurate and 100% functional.

Unfortunately, I cannot connect to your computer and run the queries for you.

After changing the date format, and confirming that it is updated by running the previous select statement I gave you, you should attempt running just these:

INSERT INTO MOVIE_INFORMATION VALUES ('Legend', 1, 114, 'A young man must stop the Lord of Darkness', 'Fantasy', '18-APR-1985');
INSERT INTO MOVIE_INFORMATION VALUES ('Step Up 3D', 2, 114, 'Dancing in New York', 'Fantasy', '06-Jul-2012');
INSERT INTO MOVIE_INFORMATION VALUES ('Clue', 3, 114, 'Figure out who killed whom', 'Fantasy', '18-APR-1980');
INSERT INTO MOVIE_INFORMATION VALUES ('Avatar', 4, 114, 'Blue people swinging in trees', 'Fantasy', '18-APR-2009');
INSERT INTO MOVIE_INFORMATION VALUES ('The Hobbit', 5, 114, 'Short people go on a holiday', 'Fantasy', '25-Dec-2012');


If you receive any errors, please just tell me what they are.
Customer: replied 1 year ago.


I do believe you. I really don't know anything about the interface or how to run any of this as we never went over it in the material.


 


Would you mind doing me one favor? Could you write me a statement according to this question?


 


From your project 1 metadata, write a query to return the number of times a
movie with a pk value of 10 has been rented within the last month. You must use
either your project 1 table metadata - or if you had to make corrections - your
corrected metadata


 


I know that isn't part of the original request, but I would really appreciate it. You are getting an excellent rating no matter what, I will finalize it in 15 minutes.


 


Thank you again for your help

Expert:  TheDoctor replied 1 year ago.
THIS ANSWER IS LOCKED!
You can view this answer by clicking here to Register or Login and paying $3.
If you've already paid for this answer, simply Login.
TheDoctor, Professional w/Adv. Degree
Category: Homework
Satisfied Customers: 1485
Experience: M.S. in Internet Information Systems
TheDoctor and 8 other Homework Specialists are ready to help you
Expert:  TheDoctor replied 1 year ago.
Hello again,
Here is another copy of step4:

Attachment: 2013-03-04_024708_seanproject2step4.zip


I was going through it and noticed that in query B, I didn't make it for the last 30 days. They quey still works perfectly, it just didn't have that condition on it.
I apologize for that oversight. This was just such a massive amount of work. I am truly sorry. It is, overally, a rather minor difference.
If you have already turned the assignment in, most courserooms allow you to submit again. Otherwise, send your instructor a note immediately stating that you caught the issue and would like to resubmit (that way your instructor knows that you caught the issue before the instructor had a chance to look at it).
Anyway, let me know if you need anything else and if you ever need my assistance again, please feel free to request me directly by starting your questions with **For TheDoctor**.
Have a great night!

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
< Last | Next >
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
  • He answered my question promptly and gave me accurate, detailed information. If all of your experts are half as good, you have a great thing going here. Diane Dallas, TX
 
 
 

Meet The Experts:

 
 
 
  • Manal Elkhoshkhany

    Tutor

    Satisfied Customers:

    4520
    More than 5000 online tutoring sessions.
< Last | Next >
  • http://ww2.justanswer.com/uploads/BU/BusinessTutor/2012-2-2_115741_Kouki2.64x64.jpg Manal Elkhoshkhany's Avatar

    Manal Elkhoshkhany

    Tutor

    Satisfied Customers:

    4520
    More than 5000 online tutoring sessions.
  • http://ww2.justanswer.com/uploads/LI/lindaus/2012-6-10_04811_IMG20120609164157.64x64.jpg Linda_us's Avatar

    Linda_us

    Finance, Accounts & Homework Tutor

    Satisfied Customers:

    3121
    Post Graduate Diploma in Management (MBA)
  • http://ww2.justanswer.com/uploads/ComputersGuru/2010-02-13_051118_Photo41.JPG LogicPro's Avatar

    LogicPro

    Engineer

    Satisfied Customers:

    3035
    Expert in Java C++ C C# VB Javascript Design SQL HTML
  • http://ww2.justanswer.com/uploads/lanis/2009-4-1_233717_phput9xef_c1pm.jpg Lani S.'s Avatar

    Lani S.

    Tutor

    Satisfied Customers:

    2457
    Registered Nurse, Internet Researcher, Private Tutor
  • http://ww2.justanswer.com/uploads/chooser77/2009-08-18_162025_Chris.jpg Chris M.'s Avatar

    Chris M.

    M.S.W. Social Work

    Satisfied Customers:

    2341
    Master's Degree, strong math and writing skills, experience in one-on-one tutoring (college English)
  • http://ww2.justanswer.com/uploads/JawaadAhmed/2009-6-27_12137_SIs_SHadi.jpg F. Naz's Avatar

    F. Naz

    Chartered Accountant

    Satisfied Customers:

    1975
    Experience with chartered accountancy
  • http://ww2.justanswer.com/uploads/JK/jkcpa/2011-1-16_182614_jkcpa.64x64.jpg Bizhelp's Avatar

    Bizhelp

    CPA

    Satisfied Customers:

    1873
    Bachelors Degree and CPA with Accounting work experience
 
 
 

Related Homework Questions