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 ehabtutor Your Own Question

ehabtutor
ehabtutor, Computer Software Engineer
Category: Programming
Satisfied Customers: 1256
Experience:  Bachelor of computer science, 5+ years experience in software development, software company owner
13535664
Type Your Programming Question Here...
ehabtutor is online now
A new question is answered every 9 seconds

ORACLE Question

Customer Question

Create Oracle database tables using SQL Data Definition Language (DDL) for each table listed in Project 1

Attachment: 2013-10-19_215113_writeup.docx

. Make sure that entity and referential integrity are enforced by declaring a primary key for each table (these may be composite keys) and declaring all appropriate foreign keys. Your CREATE TABLE statements must show integrity constraints, as appropriate, for NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, REFERENCES, and CHECK constraints. Be sure to save your script used to create these tables as yournameproject2step1.sql. You should test your script to make sure it runs without error.



  1. Provide two examples of SQL DML (i.e., "INSERT") commands that fail different table integrity constraints you set up in one of your table. Explain why the statements fail. Be sure to save your script used to as yournameproject2step2.sql. You can include comments in the SQL script describing why the insert statements failed.

  2. Populate each of your tables with at least five valid rows of data each and show the SQL you used. Populate other tables in your database, as necessary, to satisfy referential integrity. Be sure to save your script used to create these records as yournameproject2step3.sql. You should test your script to make sure it runs without error.

  3. Write SQL to perform the following queries and updates. Be sure to save your script used to create these records as yournameproject2step4.sql. You should test your script to make sure it runs without error:

    1. Retrieve all of your customers' names, account numbers, and addresses (street and zip code only), sorted by account number.

    2. Retrieve all of the videos rented in the last 30 days and sort in chronological rental date order.

    3. Produce a list of your distributors and all their information sorted in order by company name.

    4. Update customer names to change their maiden names to married names. You can choose which records to update.

    5. Delete customers from the database. You can choose which records to delete.


Submitted: 10 months ago.
Category: Programming
Expert:  ehabtutor replied 10 months ago.

ehabtutor :

Welcome to Just answer. My Name isXXXXX and i will be assisting you today.

ehabtutor :

So all of the sql questions here are for the tables defined in the document you uploaded?

ehabtutor :

When is this needed, do you have a deadline?

ehabtutor :

I will start working on it, but please let me know the deadline for these questions.

ehabtutor :

regards,

ehabtutor :

ehab tutor

Customer:

My deadline is this time tomorrow. But the earlier is better :)

ehabtutor :

ok I will have it ready earlier than same time tomorrow so you can recheck it

Customer:

thank you

ehabtutor :

do you have any other data related to the question

ehabtutor :

other than the word document attached

ehabtutor :

I presume the document attached is part one of the question right?

Customer:

yes. just in case it will help the instructions for the first part were as follows:


You are a database consultant with Ace Software, Inc. and have been assigned to develop a database for the Mom and Pop Johnson video store in town. Mom and Pop have been keeping their records of videos and DVDs purchased from distributors and rented to customers in stacks of invoices and piles of rental forms for years. They have finally decided to automate their record keeping with a relational database.


You sit down with Mom and Pop to discuss their business, and watch their operation for about a week. You discover quickly that a video and a DVD are both copies of a movie kept in a separate plastic case that is rented out. They have several copies of each movie they rent; therefore there are several videos and DVDs for each movie title. You learn that in their inventory they have several thousand videos and DVDs, which they get wholesale from about a half dozen distributors. The video and DVD prices to them are based on the quantity of their shipment and the past business they have done with each company.


 


The price of a DVD for a movie might be different than the price of a video for the same movie, even from the same distributor. Each distributor provides different types of movies (e.g., suspense, horror, mystery, comedy, etc.). A single distributor may provide several different types of movies in both video and DVD format. It is possible to obtain the same movie from multiple distributors, and at different wholesale prices.


 


Each video and DVD has a unique identification number that Mom and Pop assign in their inventory, in addition to the distributor's serial number for the item. Each movie also has a unique identification number Mom and Pop assign in addition to the title, and any movie IDs the distributors use in their electronic catalogs. Distributors provide electronic catalogs to Mom and Pop and the information from these catalogs must be included in the database.


 


Mom and Pop need to record when a video or DVD is rented, when a video or DVD is returned, and all customer charges such as late and damaged fees, failure to rewind fees, and taxes. They need a report of which videos are returned late because there are standard and late charges. On occasion there are discount prices for certain movies or types of movies. Customers want to rent movies based on actors or actresses, running length, type of movie, rating, year released, the director, and the academy awards won (by the movie, the actors, the actresses and/or the directors). Customers also want to know how many videos they have rented in the last month, year, and so forth. Mom and Pop need to keep only basic information on customers in their database, such as name, address, telephone numbers, etc.


 


There must be no limit to the number of video and/or DVD copies of a movie that Mom and Pop can have in their inventory. Video/DVD ID numbers, movie ID numbers, and distributor ID numbers for videos, DVDs, and movies are all different. Also, each movie must be able to have an unlimited number of actors, actresses, directors, and academy awards (i.e., Oscars). Other types of awards (e.g., Golden Globe, People's Choice, etc.) are not of interest for this application. The rental of equipment, sale of videos, DVDs, popcorn, etc., is not to be kept in the database.


ehabtutor :

ok great

Customer:

thanks :)

ehabtutor :

you're welcome

Customer:

How is it coming?

ehabtutor :

Almost done. I need an extra hour to test everything and send you the final version

Customer:

sounds good thanks

Expert:  ehabtutor replied 10 months ago.
Hi I am done.

I needed to change most of question one as it didn't comply to the requirements many tables were missing and no foreign keys were defined for the different tables.

It took me double the amount of time and work, but i had to do it the right way.

Let me know when you are online so i can give you the files.

I hope you will be able to compensate me with a decent bonus.

regards,
ehab tutor
Customer: replied 10 months ago.

I am online now. I always give decent bonuses :)

Expert:  ehabtutor replied 10 months ago.
Please download fro Here

You will find 2 zip files. The first one is for project one named project1.zip

The second onw is named oracle.zip. This is the one for this question. Remember to rename the files with your name.

Regards,
ehab tutor
ehabtutor, Computer Software Engineer
Category: Programming
Satisfied Customers: 1256
Experience: Bachelor of computer science, 5+ years experience in software development, software company owner
ehabtutor and 4 other Programming Specialists are ready to help you
Expert:  ehabtutor replied 10 months ago.
Where you able to download the files?

Do you have any further comments?

regards,
ehab tutor
Expert:  ehabtutor replied 10 months ago.
If you don't have any further comments or inquiries.
Please take a minute to leave me a positive rating. I am not paid unless you do.

To rate my answer, please use the stars, faces or ratings under the answer.

A bonus will be highly appreciated.

regards,
ehab tutor
Customer: replied 10 months ago.

Sorry having computer issues. everything looks good from what i can see.

Expert:  ehabtutor replied 10 months ago.
Yeah i also tested everything on Oracle 10g to make sure all queries run smoothly.

Thanks in advance.

Regards,
ehab tutor
Customer: replied 10 months ago.

Thank you for all your help. I sent a bonus as well you should be receiving it soon

Expert:  ehabtutor replied 10 months ago.
Thank You very much for the generous bonus.

In case you need my help on any upcoming SQL or programming questions.

Just remember to Start your question with "To Ehab Tutor Only" to make sure the question comes to me.

Or you can use this link: http://www.justanswer.com/computer/expert-ehabtutor/

regards,
ehab tutor

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:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • 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
 
 
 

Meet The Experts:

 
 
 
  • ATLPROG

    Computer Software Engineer

    Satisfied Customers:

    7463
    MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML
< Last | Next >
  • http://ww2.justanswer.com/uploads/SP/spatlanta2010/2011-6-23_12450_photo.64x64.gif ATLPROG's Avatar

    ATLPROG

    Computer Software Engineer

    Satisfied Customers:

    7463
    MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML
  • http://ww2.justanswer.com/uploads/ComputersGuru/2010-02-13_051118_Photo41.JPG LogicPro's Avatar

    LogicPro

    Computer Software Engineer

    Satisfied Customers:

    5603
    Expert in C, C++, Java, DOT NET, Python, HTML, Javascript, Design.
  • http://ww2.justanswer.com/uploads/unvadim/2010-11-15_210218_avatar.jpg unvadim's Avatar

    unvadim

    Computer Software Engineer

    Satisfied Customers:

    1158
    Good knowledge of OOP principles. 3+ years of programming experience with Java and C++. Sun Certified Java Programmer 5.0.
  • http://ww2.justanswer.com/uploads/lifesaver333/2010-10-17_191349_ls.jpeg lifesaver's Avatar

    lifesaver

    Computer Software Engineer

    Satisfied Customers:

    950
    Several years of intensive programming and application development experience in various platforms.
  • http://ww2.justanswer.com/uploads/EH/ehabtutor/2012-8-2_202016_1.64x64.jpg ehabtutor's Avatar

    ehabtutor

    Computer Software Engineer

    Satisfied Customers:

    864
    Bachelor of computer science, 5+ years experience in software development, software company owner
  • http://ww2.justanswer.com/uploads/RA/rajivsharma086/2012-6-6_17128_displaypic.64x64.jpg Raj's Avatar

    Raj

    Computer Engg.

    Satisfied Customers:

    860
    BE CS, 4+ Experience in Programming and Database (ERP)
  • http://ww2.justanswer.com/uploads/eljonis/2010-01-06_130406_eljon2.jpg Eljon's Avatar

    Eljon

    Consultant

    Satisfied Customers:

    590
    11 yrs of programming (PHP, WordPress, XSL, SQL, JavaScript)