Background
Nowadays, most commercial shops have their own web sites for sale and databases for managing their sales. They use relational database system to manage their new arrivals, the products kept in stock, the customers’ transactions or orders and their customers’ information. These maintained information will help shop managers to track their sales in their shops. In addition, they often provide online sales that allow customers make their orders via shop web sites. The online web sites are often developed and connected to their database systems. The online web sites can also help shop managers to manage their databases for those who don’t have specific IT knowledge. To develop this application, you need to have the basic skills and knowledge about relational database concepts, MySQL queries, HTML, PHP. All these have been delivered in this unit of CITS1402 - relational database management system, 2017 Semester 2. Therefore, this project based assignment includes three components as below:
Part 1: Database Designs
You are required to design a database including all the mandatory tables. Your designed database should meet the requirements described in the practical application. Regarding Part 1, you MUST create all the tables in your database on the web server cits1402.csse.uwa.edu.au. For each table, you MUST insert at least 10 rows as test data. Meanwhile, you MUST submit a *.sql file named as yourown-studentID.sql (e.g., 123456.sql). In the .sql file, you have to include all your mysql queries used for creating tables, inserting rows.
Part 2: SQL Queries
Query 1: What SQL query will produce the row of customer information who have made three or more successful orders from this shop?
Query 2: What SQL query will produce the row of customer information who have spent $500 or more in total in this shop?
Query 3: What SQL query will list the product information where no customers bought this product before?
Query 4: What SQL query will list the product information where only one customer bought this product before?
Query 5: What SQL query will list the total number of customer orders per product in the database?
Query 6: What SQL Query will produce the total amount of the sold products in the database?
Query 7: Write a function CostOfBestBuyers(number INT) Return Double that returns the total price of the top number of customers who spent in this shop. For instance, if number =5, the returned price is the total cost of the 5 customers who spent the most in this shop.
Query 8: Create a view BuyerCostPerProduct with columns Customer ID, Customer Name, Product ID, Product Name, Date which provides a more accessible way for the DB user to run queries regarding customers and products. The date is the time to produce the view. In other words, the virtual table view maintains each buyer’s cost for each product by a view generation date.
The above 8 MySQL queries MUST be included in the submitted file yourown-studentID.sql (e.g., 123456.sql).
Part 3: Database Driven Web Site Development
Task 3.1: New Arriving Product Submission Web Page
In this page, you need to develop a web page NewArriving.php including HTML form part and PHP part. Shop manager can submit the new arriving product item information and write it into the Database. The written item information must include product code, product name, product price, and quantity. After the item information is written into database successfully, it should display a confirmation message on the web page and let the shop manager to know the successful insertion.
Task 3.2: User Buying Product Transaction Web Page
In this page, you need to develop a web page BuyingTransaction.php including HTML form and PHP part. A user can make her/his order through the HTML form. It is required to provide a drop down list allowing the user to choose one product. It also needs an input field allowing the user to specify the quantity of product she or he wants to buy. It also asks user to provide her/his personal information including user name, phone number, and address. PHP part will be used to calculate the total price and print a message for the user. If the number of her/his ordered product doesn’t exceed the available quantity of the product in database, then you need print a message on the web page including a successful confirmation about the user’s order and the total price of this order.
You also need to update the available quantity of the product in the database. If the transaction is successful, you also need to record the user information, transaction information in the database. If the number of her/his ordered product exceeds the available quantity of the product in database, then it says the transaction failed. So you need to show a message on the web page. It tells the user the failed transaction and the available quantity of the product.
Task 3.3: User-friendly Web Page Design
All the web pages should be designed in a well format. It should be reasonable, attractive, user-friendly, and informative.
Regarding Part 3, you MUST submit TWO web pages, each for Task 3.1 and each for Task 3.2. The first web page is named as NewArriving.php. The second web page is named as BuyingTransaction.php.
Submission Requirements and Format
This is an individual assignment. You have to develop by your own and cannot copy the codes from the others. You have to submit 4 files including your-ownstudentID.sql, NewArriving.php, and BuyingTransaction.php, declaration.txt via the link in Blackboard by the Assignment Due Date.
Meanwhile, you have to test NewArriving.php and BuyingTransaction.php on your web server, and also upload the two web pages to your web folder