Spring 2024 BIS 324 PS3 Problems

.docx

School

Lehigh University *

*We aren’t endorsed by this school

Course

324

Subject

Information Systems

Date

Apr 3, 2024

Type

docx

Pages

Uploaded by MasterMusicHamster4

BIS 324 - Business Data Management Spring 2024 Problem Set 3 General Requirements: 1. While writing your SQL code, make sure you are displaying enough result lines to show all results. Execute the code by pressing RUN to make sure all result lines are visible. 2. Make sure to submit your code under its respective assignment link in Course Site . This must be submitted no later than the specified due date located in the assignment’s link on Course Site, otherwise you will receive reduced or zero credit. Grading Rubric: Functionality of Query (90% of points/problem) Query must correctly produce output asked for in problem, including all columns, ordering of data, etc. Readability/SQL Conventions (5% of points/problem): A. All column headings must be user friendly, in mixed case, and include spaces if appropriate. “MOVIEID” is not user friendly. “Movie ID” is user friendly. B. All column headings and output (other than given data that is in the table) should be proofread for spelling/grammar errors. C. Your SQL code should be readable and on multiple lines with indentation. Note that the select , from and where clauses need to be on separate lines . Efficiency/Scalability (5% of points/problem): SQL must be efficient and straightforward and scalable to all situations. Even if your query produces the correct output, if it does so with extraneous steps and code, it is not efficient. Some examples are given below, but these are NOT all inclusive: Example 1: (eliminate unnecessary math) Less efficient: SELECT merfnbr FROM merchant WHERE merfnbr + 3000 < 5000; More efficient: SELECT merfnbr FROM merchant WHERE merfnbr < 2000; Example 2: including unnecessary WHERE clauses Example 3: including unnecessary subqueries

Example 4: unless stated otherwise, it must work for all data possible, not just data in our database currently - your query should work for any type of data that MAY be in the database, not only the data that is present now. For example, suppose that there are only 2 movies in the database with the word “love” in them: “I Love Lucy” and “The Love Letter”; You are asked to select all movies that contain the word “love” in the title regardless of case, and you submit the query: Not Scalable: SELECT title FROM movies WHERE title LIKE '% Love %' Although that may pull all the movies CURRENTLY that have the word love in their titles, it will not work for possible future data (for example if we add the move “Love Reinvented”, the above query would not work). Scalable: SELECT title FROM movies WHERE title LIKE '% Love %' OR title LIKE '% love %' OR title LIKE '%Love %' OR title LIKE '%love %' OR title LIKE '% Love' OR title LIKE '% love%’ Scenario: Netflix Netflix is an online movie and TV episode streaming and rental service that delivers DVDs at a flat rate by mail or via online streaming to customers in the United States. A customer can choose from thousands of titles and stream instantly on multiple devices by enlisting in the Streaming plan. When the feature is over, the customer may choose to rate it, view a different title, or exit the software. In addition to the Streaming service, customers may have DVDs, one at a time, delivered to their home addresses via the DVD plan. When the customer is done with the DVD, they send it back to Netflix via the mail and then Netflix sends the next available movie from the queue to the customer. You only need to access the tables customers, movies, ratings100, strmplan, actors , movies_genres, and movies_actors in Oracle for this assignment: Note that there will be additional tables that will be forthcoming for future assignments that also deal with this same scenario.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help