SQL Relational Database Implementation and Analyses

{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "## UC1PR2101 - Assessment 3 \n", "##### SUBMITTED BY: ENTER YOUR NAME HERE\n", "\n", "###### Note:\n", "This jupyter notebook serves as a template in support of the PDF document which outline the assessment. Please refer to the PDF document for more information regarding due dates, grading, and other information for this assignment. **You are required to submit your final version of this jupyter notebook for grading**" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Please read the following scenario\n", "*“Academic Booksellers Inc is a new online book retailer. They specialise in selling academic textbooks and ship orders worldwide at no additional charge. All their books are sourced from specialised academic publishers. Customers often order multiple books, or even multiple copies of each book, in the same order.”*\n", "\n", "A design team has already produced the following ERD for the business database. \n", "- Primary keys of all tables should be autoincrement integers \n", "- Currency values should be stored as real numbers \n", "- Quantities should be stored as integers\n", "- All other fields, including phone numbers and dates, should by stored as text\n", "\n", "<img src = \"AcademicBooksellers.png\" style=width:500px;/>\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The following code will create a new database for you, if it does not already exist, or connect to an existing database should it already exist in the same folder. It will also ensure that we are enforcing referential integrity in our database.\n", "\n", "Execute this code before starting **Task 1**. You only need to replace the name of the database with yourown name. Do not modify this rest of this code at all." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import sqlite3" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "conn = sqlite3.connect('YourName.sqlite')\n", "cursor = conn.cursor()\n", "cursor.execute('PRAGMA foreign_keys = ON;')\n", "print(\"Created and/or Opened database successfully\")\n", "cursor.close()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Task 1 - DDL\n", "\n", "The first task in implementing the tables and relationships for the above database schema. \n", "\n", "- Write and execute all the necessary DDL SQL statements needed to create the tables for the Academic Booksellers database\n", "- SQL statements must be in the sequence in which you would execute them\n", "- SQL must be formatted appropriately to ensure readability (refer to https://www.sqlstyle.guide/ for additional info).\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Code to create your database - \n", "#including needed relationships must be written here\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Task 1 - Reflection and assumptions \n", "#### Database Definition\n", "Your discussion of the above DDL statements plus any assumptions you made must be written here (replace this text). Any other reflective thoughts you wish to add about the above topic should be made here. Note: Professional layout and attention to detail is important. Refer to https://wordpress.com/support/markdown-quick-reference/ for additonal markdown syntax if needed. " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Task 2 - Schema Exploration\n", "In order to provide all students with the same pre-existing data, a database based on the same design and rules as discussed above has been created and pre-populated for you. The code below connects to this database file 'AcademicBooksDB.sqlite'. **This database should be used for all remaining tasks in this assessment**\n", "\n", "However, you can rarely assume a given ERD has been implemented correctly, or that you may be provided with an ERD. You should be abke to explore a database design without having access to any additional information, provided that you know what DBMS is being used and have the necessary rights to connect to the database.\n", "\n", "1. Write code to extract schema information you might need to explore the provided database\n", "2. Discuss what the code written in point 1 above means, and what you can use the output for" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "conn = sqlite3.connect('AcademicBooksDB.sqlite')\n", "cursor = conn.cursor()\n", "cursor.execute('PRAGMA foreign_keys = ON;')\n", "print(\"Created and/or Opened database successfully\")\n", "cursor.close()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "## Write your code to explore the database design here" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Task 2 - Reflection and assumptions \n", "#### Schema Exploration\n", "Your discussion of the above schema exploration plus any assumptions you made must be written here (replace this text). Any other reflective thoughts you wish to add about the above topic should be made here. Note: Professional layout and attention to detail is important. Refer to https://wordpress.com/support/markdown-quick-reference/ for additonal markdown syntax if needed. " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Task 3 - Business Logic\n", "\n", "In a three-tier architecture we often use stored procedures to ensure that DML operations such as INSERT, UPDATE, or DELETE commands execute correctly. These procedures also serve to ensure input have been appropriately validated and that any possible exceptions have been dealt with. Your task is to write the code for selected procedures in the business logic tier. \n", "\n", "**For each procedure the following will apply:**\n", "1. The signature will be provided. This signature may not be changed.\n", "2. Some parameters might be optional. You need to ensure you add appropriate logic to deal with all possible cases correctly\n", "3. All procedures must return appropriate messages to the calling function to communicate the result of running the code (was the call successful? Did an error occur? With what error message?)\n", "4. A test call to each procedure is provided in cells following the procedure. These should work as expected if your code was correct\n", "\n", "\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Stored Procedure 1: Adding a New Customer\n", "- To register, a customer **must** provide their Name and Surname. \n", "- Address information is optional but must be provided before a first order can be shipped\n", "- If an address is provided it must include the values for Adress, City, and Country, a customer cannot provide and address without specifying both City and Country. If this information is missing or incomplete the customer record needs to be created but the calling application should receive a message that the address information was not stored.\n", "- Providing a phone number is **always** optional\n", "- On successful creation of a new customer the autogenerated ID for the new customer must always be returned as part of the success message" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def NewCustomer(Name, Surname, Address = None, City = None, Country = None, Phone = None):\n", " # Your code should go here\n", " \n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "##### Test your stored procedure below \n", "Add each of the following customers:\n", "\n", " Name: Jill\n", " \n", " Surname: Emmerson\n", " \n", " Address: 23 Surrey Road\n", " \n", " City: London\n", " \n", " Country: UK\n", " \n", " \n", "\n", " Name: Leo\n", " \n", " Surname: Cohen\n", " \n", " Address: \n", " \n", " City: Boston\n", " \n", " Country: USA\n", " \n", " Phone: +1 555-93240\n", " \n", " \n", "\n", " Name: Winona\n", " \n", " Surname: Rider\n", "\n", "\n", "- Ensure your test demonstrates what happens for both complete and incomplete records being added" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Add your test code here" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Stored Procedure 2: Updating an existing Customer\n", "- To Update a Customer record the customer ID must be provided\n", "- A customer may only change their Address, City, Country, or Phone Number\n", "- Address, City, and Country information must always be updated together\n", "- Phone Number is always optional\n", "- A phone number may only contain numerical values \n", "- A phone number **might** start with a country code. For example a South African number might start with 0027 and a Norwegian number with 0047. You **do not** have to verify that the correct country code was used" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def UpdateCustomer(CustID, Address = None, City = None, Country = None, Phone = None):\n", " # Your code should go here" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "##### Test your stored procedure below \n", "- Test by updating the customer with ID 5\n", "\n", " Extra details provided:\n", " \n", " Address: 12 Starwars Park - Disneyland\n", " \n", " City: Orlando\n", " \n", " Country: USA" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Add your test code here" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Stored Procedure(s) 3: Order Processing\n", "To support the interface for placing order, one or more stored procedures may be needed. You have to do the following:\n", "1. Discuss the overall business process, as well as any assumptions you have made to design this process. You may make assumptions about the user interface, or any other aspect of the program you deem necessary. **You may assume that we will always have unlimited stock of any book that is listed in our database**\n", "2. Provide the procedure, or procedures, needed to support your business process. Note: You may define additional variables or objects to help support your process. Should you define additional variables or objects their purpose and role(s) in the process must be clearly discussed. \n", "3. Reflect on the above process, what you have learned, what you feel you still need to learn, any other reflection you may deem relevant to DML commands and database systems.\n", "\n", "##### The expected fuctionality for our website include the following:\n", "- To place an order we need to ensure the user exist and that we have their address information. \n", "- We need to create a new order and return the new order ID, or an appropriate message if it is not possible to create the order (i.e. if the user information is incomplete you cannot place an order for some other reason)\n", "- To start creating an order the user will initiate the process by attempting to add a first product. The UI (presentation layer) will supply the CustID and the BookID for the first product.\n", "- Orders should always be given the current date in the format \"DD-MM-YYYY\"\n", "- The first product will always be for quantity 1\n", "- Any **successful** change to an order (whether creating a new order or updating an order) must always return both the OrderID and the new TotalAmount for the order in a message.\n", "- A user should be able to Add an additonal product to their order. \n", "- A user must be able to increment the quantity of a product on the order by 1 \n", "- A user must be able to decrease the quantity of a product on the order by 1. Should this result in a quatity of 0 (zero) the orderline should be deleted completely from the order\n", "- Once complete an order should succeed or fail as a whole\n", "\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Order process code and stored procedures start below" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Add all your code to support the above process below\n", "# Note that good code layout and commenting is essential\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Test order process code below\n", "Your test, or tests, should clearly demonstrate the features of your process code. \n", "\n", "**Please add any additional information**, i.e. which books are being ordered, how many, etc. here (replace the text in this cell)\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Code that will demonstrate the correct working of your order process\n", "# This code will call the above procedures with appropriate parameters" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Task 3 - Reflection and assumptions \n", "#### Business Processes\n", "Your discussion of the business process plus assumptions must be written here (replace this text). Any other reflective thoughts you wish to add about the above topic should be made here. Note: Professional layout and attention to detail is important. Refer to https://wordpress.com/support/markdown-quick-reference/ for additonal markdown syntax if needed. \n", "\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Task 4 - Reporting and Queries\n", "\n", "The ability to extract data from a database and present it clearly, or load it into local objects for further manipulation, is an essential database programming skill. For this task you are required to:\n", "1. Write SQL and code for each of the three reports specified below\n", "2. Design (specify) two additional reports and write all the code needed to extract the data and present the results for your own two reports.\n", "\n", "**Note:** You may use any python tools or libraries of your choice (i.e. Pandas, Numpy, Matyplotlib, etc) to better present your reports. However, these must be included in your code. Additional marks will be allocated for good layout, clear presentation, and any other factors the assessor might deemed relevant. **It is important to motivate why a specific library or tool was chosen in the reflection for this task.**" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Reporting and Queries 1\n", "\n", "The sales manager is really interested in knowing who our top customers are for a given month. He needs a report with the Firstname, Lastname, and TotalAmountSpend, for all orders for a specified month. The report should be sorted so that the customer that spend the most money with us appears at the top.\n", "\n", "Write the report below.\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Report 1" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Reporting and Queries 2\n", "\n", "We need a list of all Books in our database. The list should provide the name of the Publisher, the book Title, and the, year of publication for all books. The list must be sorted alphabetically in order of publisher and then title.\n", "\n", "Write the report below." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Report 2" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Reporting and Queries 3\n", "\n", "We need a daily salesreport which lists all orders for a given day (the date will be supplied as parameter to the query. For each order we want to know the OrderID, Customer’s fullname (Firstname + Lastname), TotalAmount, and the number of books purchased. We do not need to know which books, just how many was bought in total for the order.\n", "\n", "Write the report below." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Report 3" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Reporting and Queries 4 - Your own report\n", "\n", "Describe the first report of your own design. Include any assumptions an/or business requirements that may be needed.\n", "\n", "Write the code for the report below" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Report 4" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Reporting and Queries 5\n", "\n", "Describe the second report of your own design. Include any assumptions an/or business requirements that may be needed.\n", "\n", "Write the code for the report below" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Report 5" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Task 4 - Reflection and assumptions \n", "#### Reporting and Queries\n", "Your discussion of the business process plus assumptions must be written here (replace this text). Any other reflective thoughts you wish to add about the above topic should be made here. Note: Professional layout and attention to detail is important. Refer to https://wordpress.com/support/markdown-quick-reference/ for additonal markdown syntax if needed. " ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.7.4" } }, "nbformat": 4, "nbformat_minor": 4 }