You are a database consultant with Ace Software, Inc., and havebeen assigned to develop a database f

Relax! Stop worrying about deadlines and let our professional writers help you. Hire an essay writer helper and receive a professional assignment before your deadline. We provide writing services for all types of academic assignments.


Order a Similar Paper Order a Different Paper

You are a database consultant with Ace Software, Inc., and havebeen assigned to develop a database for the Mom and Pop Johnsonvideo store in town. Mom and Pop have been keeping their records ofvideos and DVDs purchased from distributors and rented to customersin stacks of invoices and piles of rental forms for years. Theyhave finally decided to automate their record keeping with arelational database. You sit down with Mom and Pop to discuss their business, andwatch their operation for about a week. You discover quickly that avideo and a DVD are both copies of a movie kept in a separateplastic case that is rented out. They have several copies of eachmovie they rent, therefore there are several videos and DVDs foreach movie title. You learn that in their inventory they haveseveral thousand videos and DVDs, which they get wholesale fromabout a half dozen distributors. The video and DVD prices to themare based on the quantity of their shipment and the past businessthey have done with each company. The price of a DVD for a movie might be different than the priceof a video for the same movie, even from the same distributor. Eachdistributor provides different types of movies (e.g., suspense,horror, mystery, comedy, etc.). A single distributor may provideseveral different types of movies in both video and DVD format. Itis possible to obtain the same movie from multiple distributors andat different wholesale prices. Each video and DVD has a unique identification number that Momand Pop assign in their inventory, in addition to the distributor’sserial number for the item. Each movie also has a uniqueidentification number Mom and Pop assign in addition to the titleand any movie IDs the distributors use in their electroniccatalogs. Distributors provide electronic catalogs to Mom and Pop,and the information from these catalogs must be included in thedatabase. Mom and Pop need to record when a video or DVD is rented, when avideo or DVD is returned, and all customer charges such as late anddamaged fees, failure to rewind fees, and taxes. They need a reportof which videos are returned late because there are standard andlate charges. On occasion there are discount prices for certainmovies or types of movies. Customers want to rent movies based onactors or actresses, running length, type of movie, rating, yearreleased, the director, and the Academy Awards won (by the movie,the actors, the actresses and/or the directors). Customers alsowant to know how many videos they have rented in the last month,year, and so forth. Mom and Pop need to keep only basic informationon customers in their database, such as name, address, telephonenumbers, etc. There must be no limit to the number of video and/or DVD copiesof a movie that Mom and Pop can have in their inventory. Video/DVDID numbers, movie ID numbers, and distributor ID numbers forvideos, DVDs, and movies are all different. Also, each movie mustbe 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 forthis application. The rental of equipment, sale of videos, DVDs,popcorn, etc., is not to be kept in the database. Identify and describe the entities and their attributes. Develop relationship sentence pairs. Draw an ERD with Visio. Develop metadata from the ERD and document in an Excelspreadsheet. Using your selected RDBMS (SQL Server, Oracle, or MySQL),develop and execute an SQL script file of DDL SQL to create thedatabase tables in the metadata document. Using your selected RDBMS, develop and execute an SQL scriptfile of DML SQL INSERT statements to populate the tables using SQLINSERT statements for at least 5 rows of data per table. Using your selected RDBMS develop and execute an SQL script fileto: Show the contents of all tables Retrieve all of the customers’ names, account numbers, andaddresses (street and zip code only), sorted by account number Retrieve all of the DVDs rented in the last 30 days and sort inchronological rental date order Update a customer name to change their maiden names to marriednames. You can choose which row to update. Make sure that you usethe primary key column in your WHERE clause to affect only aspecific row. Delete a specific customer from the database. You can choosewhich row to delete. Make sure that you use the primary key columnin your WHERE clause to affect only a specific row. All other outputs for the database design, SQL code, and SQLresults should be submitted in a single Word file in order, bystep, and clearly labeled. The Word file should also have a preface describing the databaselife cycle steps and methodologies and a conclusion sectioncontaining lessons learned . . .

Great students hand in great papers. Order our essay service if you want to meet all the deadlines on time and get top grades. Professional custom writing is the choice of goal-focused students. Word on the online streets is... we're simply the best!

Get a 15% discount on your order using the following coupon code SAVE15


Order a Similar Paper Order a Different Paper