Online Food Ordering Database – Task 4, Thesis of Community Corrections

Download Online Food Ordering Database – Task 4 and more Thesis Community Corrections in PDF only on Docsity!

Running head: ONLINE FOOD ORDERING DATABASE – TASK 4 1 Online Food Ordering Database – Task 4 CPT 310 Database Systems & Management Database Tables Screenshots

Some of the data from my week 3 assignment has been changed in order to fit in better with an online food ordering database. For example, the data in department_name has been changed to customer service, delivery, and management because it makes more sense from a food ordering database perspective.

SQL Script Screenshots

Data Definition Language (DDL) When utilizing DDL to use an insert, update, or delete clause, the script must be typed into the console of the database management system (DBMS) that is being used. Some knowledge of programming is required to understand how to do this as well as the format that is required when inputting the script.Using these DDLscripts and the built-in functions of the DBMS will allow the user to insert, edit, or remove single rows of data whereas, without using the clauses, the table would have to be deleted and added to the database again with the appropriate data. Moreover, using these clauses reduces the redundancy of inputting one row of data at a time in addition to the workload that is required if a table or rows of data were to be deleted.The insert, update, and delete clauses are also especially helpful when working with databases where a large volume of data exists but only a handful of database administrators to manage the data. One caveat to keep in mind when using clauses is the fact that the scripts need to be precise and correct when being typed. When inputting scripts, every comma, parenthesis, and colon need to be placed correctly or else the DBMS will give you an error and the mistake must be found before continuing.Learning how to type and input scripts can be a daunting task, especially for a beginner, but a good way to test scripts before implementing them into a database is to create a test database and try them there first. The process of redoing SQL scripts because of syntax errors can be overwhelming especially if the database encompasses a large amount of data. Overall, SQL clauses like insert, update, and delete can be incredibly helpful when implementing a database in a DBMS because they allow the user to insert data in specific tables, update data that has already been added, and delete selected data all while keeping the original tables intact. The following examples will demonstrate SQL scripts for each of these clauses.

Insert: This clause is used to input data or add data into database tables (Colonel & Morris, 2019). This clause is used in conjunction with INTO and VALUES in order to designate which database and table the data is being inserted into and what values are being inserted. After the INTO clause, database name, and table name, the column names that they data will be inserted into are added. After that, the VALUES clause is used and then the data itself is added in parenthesis.The following example is inserting four data values into the ‘employee’ table that is in a database called ‘cpt310week4assignment’. Example: INSERT INTO cpt310week4assignment.employee (employee_id, employee_gender, employee_salary, department_name) VALUES ('5', 'Male', '7300', 'Management'); Update: This clause is used to update rows of data in a database table and is used in conjunction with the SET and WHERE clauses (Colonel & Morris, 2019).The SET clause is used to determine what column is being updated and the WHERE clause is restricting the number of rows that are affected by the UPDATE query. Example: UPDATE cpt310week4assignment.employee SET employee.employee_id = 6 WHERE employee.employee_id = 5; This script will change the employee_id attributein the employee table from 5 to 6. Delete: This clause is an“SQL command that allows data rows to be deleted from a table” (Colonel & Morris, 2019, p. 385).This clause is used in conjunction with the FROM and

Inner Join, Sum, and Order By The inner join clause is used to connect rows in two different tables while also allowing the user to query rows from both tables(”MySQLTutorial”, 2020). In other words, using inner join will return data that meets a specific criterion. The inner join clause establishes a separate table by connecting rows that both tables have in common. In order to use the inner join clause, a relationship between the tables must be present. The inner join clause is used with the SELECT statement and FROM clause so that the table in the selected database can be joined with the other table that the user specifies. This clause is useful because it makes joining two tables together a very simple process. Additionally, once the tables are joined, specific information can be easily searched for because the query operation is searching through both tables at once rather than one at a time. The sum function is an aggregate function that accomplishes a calculation on a group of data. In this case, the sum function will provide the total of all numerical values in a column (“Guru99”, 2020). That said, this function only works with numerical values and null values are not included in the end result.Although the inner join clause and the sum function provide different types of data, they both have a capability to combine data. Using the sum function is helpful for calculating columns of data, especially if the size of the database is large or if the numbers in the database are too complicated to calculate by hand. Utilizing the sum function improves the efficiency of the database because it allows the user to calculate data without spending time calculating my hand and spending time inputting the data themselves. The next statement that will be covered is the ORDER BY statement. This statement is part of the data manipulation language (DML) and is used to provide a query result that is sorted according to an attribute in a specific column (Colonel & Morris, 2019). The ORDER BY

statement is used in conjunction with the SELECT statement, which selects the columns, and the FROM statement, which designates the database and table that the columns will be selected from. From there the user can use the ORDER BY statement to sort the columns according one or more of the column’s attributes. That said, user can also designate the column(s) to be sorted in either descending or ascending order. This is done by inputting the DESC or ASC modifier at the end of the GROUP BY statement. One stipulation of these modifiers is if DESC or ASC is not included at the end of the GROUP BY statement, the column that was designated to be sorted will automatically default to ascending order. This makes the ASC modifier completely optional. It is common practice, if sorting by ascending order, to leave out the modifier at the end of the statement. Moreover, another optional component to the ORDER BY statement is the WHERE condition. Using the WHERE condition allows the user to establish conditions that need to be met in order for the column(s) to be selected.The following examples will show how INNER JOIN, SUM, and ORDER BY are used. INNER JOIN: This clause will join two or more tables and will return a table that contains attributes from both tables as long as there is a relationship between them, and the established conditions are met. The following example will show the table called employee being joined with the table called department because they both share the common attributedepartment_name.

Reference Coronel, C., & Morris, S. (2019). Database systems: Design, implementation, and management (13th ed.). Boston, MA: Cengage. Guru99. (2020). MySQL aggregate functions tutorial: SUM, AVG, MAX, MIN, COUNT, DISTINCT. Retrieved from https://www.guru99.com/aggregate-functions.html MySQL Tutorial. (2020, April 11). MySQL inner join. Retrieved from https://www.mysqltutorial.org/mysql-inner-join.aspx/