Assignment 5. Database Application and Transaction Management

Objectives

The assignment has two parts.

After this assignment, you will not only get experience with database application development and transaction management, but also get familiar with some popular tools such as Azure SQL server, pyodbc, and Flask.

If you have never written any python program, please take a look at "Learn the Basics" .

Download A5.zip. Complete connect_db.py, loaddata.py, and query.py.

Part 1. Database Programming (10 points)

Please first go through Azure SQL Server Setup and then do Task 1.

Task 1. Azure SQL Server Setup (2 points)

Step 1. Follow the lecture to create an empty SQL database (named VideoStore) on Azure.

Step 2. Since TA needs to check whether you succeed, please go to Firewall settings and add a rule that allows any IP to access your Azure services (see the screenshot below) .

Drawing

Step 3. Install pyodbc on your computer

Step 4. Install the ODBC driver (see Prerequisites (MacOS, Windows, or Ubuntu))

Step 5. Open connect_db.py in the A5 folder, and replace ODBC_STR with your connection string. Open a terminal and type python connect_db.py. You will see:

Drawing

Remarks

Task 2. Data Loading (8 points)

Imagine you open your own video rental store (like Netflix). Your store allows customers to rent movies. You need to keep track of which customers are currently renting what movies. You design the following database.

Note

In this task, your job is to write a python program to create four tables in the VideoStore database that you created on Azure, and then load data into the tables.

Open the loaddata.py file.

a. Complete the LoadRentalPlan function （2 points)

def LoadRentalPlan(filename, conn):
    """
        Input: 
            $filename: "RentalPlan.txt"
            $conn: you can get it by calling connect_db()
        Functionality:
            1. Create a table named "RentalPlan" in the "VideoStore" database on Azure
            2. Read data from "RentalPlan.txt" and insert them into "RentalPlan"
               * Columns are separated by '|'
               * You can use executemany() to insert multiple rows in bulk
    """
    # WRITE YOUR CODE HERE

b. Complete the LoadCustomer function （2 points)

def LoadCustomer(filename, conn):
    """
        Input: 
            $filename: "Customer.txt"
            $conn: you can get it by calling connect_db()
        Functionality:
            1. Create a table named "Customer" in the "VideoStore" database on Azure
            2. Read data from "Customer.txt" and insert them into "Customer".
               * Columns are separated by '|'
               * You can use executemany() to insert multiple rows in bulk
    """
    # WRITE YOUR CODE HERE

c. Complete the LoadMovie function （2 points)

def LoadMovie(filename, conn):
    """
        Input: 
            $filename: "Movie.txt"
            $conn: you can get it by calling connect_db()
        Functionality:
            1. Create a table named "Movie" in the "VideoStore" database on Azure
            2. Read data from "Movie.txt" and insert them into "Movie".
               * Columns are separated by '|'
               * You can use executemany() to insert multiple rows in bulk
    """
    # WRITE YOUR CODE HERE

d. Complete the LoadRental function （2 points)

def LoadRental(filename, conn):
    """
        Input: 
            $filename: "Rental.txt"
            $conn: you can get it by calling connect_db()
        Functionality:
            1. Create a table named "Rental" in the VideoStore database on Azure
            2. Read data from "Rental.txt" and insert them into "Rental".
               * Columns are separated by '|'
               * You can use executemany() to insert multiple rows in bulk
    """
    # WRITE YOUR CODE HERE

Note

Drawing

Part 2. Transaction Management (10 points)

Many websites provide a web API for developers to use their services. Examples include Twitter API, Yelp API, and Square API. In this part, your job is to implement a Web API for your video rental store. Be sure to use SQL transactions when appropriate.

Warmup: Flask Tutorials

You need to know some basic knowledge about Flask. Flask is a (micro) web framework written in Python. It's quite easy to learn and widely used in practice.

Step 1. Read A Minimal Application. Make sure you can see 'Hello, World!' when opening http://127.0.0.1:5000/ in your browser

Step 2. Enter the A5 folder and open a terminal. Run FLASK_APP=query.py flask run. Open the following URL in your browser and make sure you can see the response {"cid":1}.

Step 3. Read the code in query.py and make sure you understand why the response of the above URL is {"cid":1}

Hints.

Task 3: Who is the renter? (3 points).

Please provide a method to get the renter of a given movie. The renter is represented by cid; the movie is represented by mid. If the movie is not being rented by anyone, return cid = -1.

Please complete the getRenterID function in query.py.

@app.route('/getRenterID')
def getRenterID():
    """
       This HTTP method takes mid as input, and 
       returns cid which represents the customer who is renting the movie. 
       If this movie is not being rented by anyone, return cid = -1
    """
    mid = int(request.args.get('mid', -1))

    # WRITE YOUR CODE HERE

    response = {'cid': cid}
    return response

Test

Task 4: How many more movies that a customer can rent? (3 points).

Please provide a method to get how many more movies that a given customer can rent. Unlike Task 3, you may need to write multiple SQL statements for this task. In Azure SQL Server, by default each statement executes in its own transaction. To group multiple statements into a transaction, you need to set conn.autocommit = False.

Please complete the getRemainingRentals function in query.py.

@app.route('/getRemainingRentals')
def getRemainingRentals():
    """
        This HTTP method takes cid as input, and returns n which represents
        how many more movies that cid can rent.

        n = 0 means the customer has reached its maximum number of rentals.
    """
    cid = int(request.args.get('cid', -1))
    # Tell ODBC that you are starting a multi-statement transaction
    conn.autocommit = False 


    # WRITE YOUR CODE HERE


    conn.autocommit = True
    response = {"remain": n}
    return jsonify(response)

Test

Task 5: Rent a movie (4 points).

Please provide a method that handle the request when a customer wants to rent a movie. You must use SQL transactions in order to guarantee ACID properties. In particular, you must ensure that the following two constraints are always satisfied, even if multiple instances of your application talk to the database.

When a customer requests to rent a movie, you may need to deny this request if it violates a constraint. You can implement denying in many ways, but we strongly recommend using the SQL ROLLBACK statement (i.e., conn.rollback()).

@app.route('/rent')
def rent():
    """
        This HTTP method takes cid and mid as input, and returns either "success" or "fail".

        It returns "fail" if C1, C2, or both are violated:
            C1. at any time a movie can be rented to at most one customer.
            C2. at any time a customer can have at most as many movies rented as his/her plan allows.
        Otherwise, it returns "success" and also updates the database accordingly.
    """
    cid = int(request.args.get('cid', -1))
    mid = int(request.args.get('mid', -1))

    conn = get_db()

     # Tell ODBC that you are starting a multi-statement transaction
    conn.autocommit = False

    # WRITE YOUR CODE HERE

    conn.autocommit = True


    #response = {"rent": "success"} OR response = {"rent": "fail"}
    return jsonify(response)

Test

On the initial database, you will get the following test results.

Submission

Download A5.zip.

Put connect_db.py, loaddata.py, query.py and the four given data files (Customer.txt, Movie.txt, Rental.txt, and RentalPlan.txt) into A5-submission.zip.

Submit A5-submission.zip to the CourSys activity Assignment 5.