COMP 5120/6120 Database Systems I
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
COMP 5120/6120 Database Systems I
Term Project: Populating and Querying Databases with SQL
Spring 2023
Due: 11:50pm, Oct. 28, 2023
1. Project Description
1. Create and populate the bookstore database with MySQL.
2. Create a user interface using PHP and HTML to query and modify the data.
3. Write correct SQL statements for the queries given.
2. Access to PHP and MySQL
https://auburn.service-now.com/it?id=kb_article_view&sysparm_article=KB0012093&sys_kb_id=56660afe1baa5d50cb564005bd4bcb78&spa=1
Students may request to have a database created on MySQL server maintained by OIT.
Details are found athttps://cws.auburn.edu/oit/database/mySQL/Create .
Note that the request takes at least two days for the OIT to approve. Request this as soon as possible when you are starting this project. If there is a problem with the requests, e- mail the OIT Office Mark Bransby:[email protected].
AU Global Protect VPN is required for MySQL Workbench and Win SCP to connect to the web space and database.
For AU Global Protect VPN, visit:https://libguides.auburn.edu/vpn .
3. Populating the Database (20 points)
Create the following tables for the bookstore database and populate the database from the data.zip. You may optionally first create your user interface and populate the database through your interface.
4. Interface Implementation (30 points)
Implement an interface by using PHP and HTML. A simple example is as shown in the following figure, which includes a text box to accept a SQL statement and then submit it to the MySQL database.
1. Your interface should not accept SQL DROP statements.
2. For any other SQL statements, your interface should not only accept it, but also return the execution result. For example, a select statement will return the query results (including the attribute name for each column) and the number of rows retrieved. A create/delete/update/insert statement will display "Table Created/Updated", "Row Inserted" or "Row(s) Deleted" messages on your interface.
3. An error message should be displayed if an incorrect SQL statement was submitted.
4. You should also have a title for this interface, indicating your name.
5. Execution of SQL queries (50 points)
First, you need to write the SQL statements for the following queries. Then, you submit each of them through your interface to get the correct result.
6. Materials to Hand In
1. URL of your PHP/HTML interface in a file named url.txt. Please make sure the interface can be used to query the database. The TA will use the interface to verify that the database has been populated with correct schema and data.
2. Your code organized in src/ folder for implementing the interface. This folder includes the index.php file and other files of your website.
3. The execution of SQL queries in sql.txt. Please have queries numbered and in the same order as queries given.
7.Tips:LogintoMySQL serverwith MySQL Workbench8.0
2023-10-05