Database Design Project

Database Design Project: Book Bidding System Phase I Step 1: This project must be done using the Google Cloud Platform (GCP). each of you will develop “BookBidding System” based on RDBMS, specifically MySQL on GCP. • Your should use RDBMS running on GCP Compute Engine for your back-end storage (e.g., MySQL, Postgres, or SQL Server). A local RDBMS, i.e., running on your computer or laptop, is NOT accepted for your final project. • You can design your presentation layer (i.e., interface layer) based on your preference. Your system can be web-based system (i.e., based on Web Server) using Web Application server, or stand along system (i.e., directly connect to RDBMS using GUI). Another option can be App using smart phone. You need to decide and specify what system architecture you are using in this project, i.e., Web Application server and program language (e.g., Django for Python) Programming Language: Any, such as Python, C, C++, Java, ASP .Net, etc Step 2: Problem Statement Here is the list of minimum requirements of your bidding system: • There are two types of users: “Admin” and “Normal” users o Admin user: can manage entire your system, so can access any data. In addition, admin user can access daily/weekly reports o Normal user can be both “Seller” and “Bidder” • Normal user can register the system, and then sign-in the system • Normal user can be seller, so post a new auction item • Other normal user can bid on any item • Payment system can be offline, such as personal check • Shipping system can create UPS tracking number • Winner and seller can rate the service each other • Any users can see each user profile • Normal user can send a message to other user regarding to the bidding item • Normal user can send a message to Admin user regarding to any issue on the item • Admin user can see the receive the message and related items In addition to the minimum requirements, each system should add at least 15 more requirements. It is also good idea to specify the area of your bidding system. Last, you should present the defined problems using a model tool, such as UML model or Use Case model. Step 3: Formulate in English at least +15 realistic queries The queries would be useful to somebody using the data. The queries must be realistic and you should have more than few complex queries. Note, the queries that the database system must be able to answer ultimately determine what information needs to be maintained in the database. For example, you need not include the information about best friends of students if no user of your database will be asking for that. On the other hand, some additional information, may be included in the database if you feel that somebody may be interested in it. Phase III In this phase,you should setup database server and web server using GCP. You are going to create Google Compute Engine for the database server where your selected RDBMS should be installed. On the other hand, you are going to create Google App Engine for the web application server for the selected web application server. Step 8: Creating Database Server First, create a project on GCP, (Bookbidding) “ADB-[LastName]-[FirstName]”, e.g., “ADB-jhon john”. Also, assign a billing account which is based on a coupon in the exercise lab. For the database server, you can select any RDBMS including MySQL, Postgres, SQL Server. However, your RDBMS should be installed on GCP Compute Engine. Step 9: Creating Web Application Server For the Web Application Server, you can use Google App Engine. You can select any app frameworks such as Django, Flask, Spring and webapp2. Phase IV Step 10: Creating DDL Script Filename: dbDDL.sql Make a file containing the SQL statements that create your entire database schema, named dbDDL.sql. This includes the tables with their constraints, view, indexes, triggers, and all other database objects if you have them. To keep the project consistent, make sure you have at least 8 tables. Make sure you have at least 2 database objects among trigger, function, procedure or view. Step 11: Creating DML Script Filename: dbDML.sql Make a file containing INSERT statements which populate the table created in Step 9, named dbDML.sql. This script will contain SQL commands to fill data in your data. Each table should have around 7 ~ 10 sample data. If needed, other DML statement, such UPDATE, and DELETE can be included here Step 12: Creating Drop Script Filename: dbDROP.sql Create a script that will drop all the objects you have created for your project including table, trigger, index, and etc.. This will be used to start from a clean state after some inserts and deletes have been added to your application to check the correctness of your queries. You should be able to clean everything through this script and re-create the database instance. Step 13: Creating SQL Script Filename: dbSQL.sql Create a script with queries from the relation algebra in Step 7, named dbSQL.sql. This script should contain at least 5 queries on your database. Use the comment facility in SQL*Plus (starting a line with — , or /* */ ) to write the English version of your query, followed by the SQL version of the query. Also show the expected output in the file. These queries need to satisfy the following: • Should be at least join queries (some involving more than 2 relations) • At least two of them should be aggregate queries including GROUP BY and HAVING clauses with ORDER BY clause as well • At least one of them should have nested subquery . Phase V Step 14: Application Development In this phase, you will develop a front-end application that will interface with your DBMS at the backend. The user will interact with the DBMS only through this interface. The GUI is mandatory, the GUI will be used by the user to retrieve and update the database without having to write SQL queries. The interface will have a menu-driven input through which all interaction with the DBMS is accomplished. The results will be displayed to the user as well. If you want to do it with as a web-based interface which can be accessed from anywhere as long as connected to internet