Introduction to Databases Assignment Sample

Introduction to Databases Assignment

This document aims at explaining the concept of DBMS and how queries are performed in relation to shoppers, products, and sellers by using coding language SQL. That is why it proposes different requests to query and control the content of an SQLite database. These are selections to get details about shoppers, relating shoppers to their orders and to their sellers, and product sales and category reports. The document also contains examples of SQL code for table creation, insertion of data and test processes with the exception and error handling of primary and foreign keys constraints. Further, there is the use of Python for interface with the database to incorporate a functional shopping facility such as through command-line operations or an effective basket, or order history, or checking out. This all-encompassing view of database interaction features both SQL and Python in order to give a clear example of real-world database usage.

Figure 1: Entity Relationship Diagram

The entity relationship diagram of the database is displayed.

Part 1

Figure 2: Query A

(Source: Devised by Self in SQLite Studio using SQL Coding Language)

The first query selects the shopper’s first name, surname, email address, gender, and date joined (Toman and Weddell, 2022). The query checks if any of these fields are empty.

Figure 3: Output of Query A

(Source: Devised by Self in SQLite Studio using SQL Coding Language)

The output shows all the values that do not have any empty field.

Figure 4: Query B

The second query selects the shopper’s first name, surname, order ID, order date, product description, seller name, quantity ordered, price, and order status. The shoppers are connected with their orders, products, and sellers.

Figure 5: Input 10000

The input is taken as 10000 to find out the output.

Figure 6: Output of 10000

(Source: Devised by Self in SQLite Studio using SQL Coding Language)

The output shows the first name, surname, order ID, order date, product description, seller name, quantity, price and order status of 10 values.

Figure 7: Input 10019

(Source: Devised by Self in SQLite Studio using SQL Coding Language)

After that, a second input is taken as 100019.

Figure 8: Output of 10019

The output shows the first name of the shopper who has the ID 10019, and shows his first name, surname, order ID, order date, product description, seller name, quantity, price, and order status.

Figure 9: Query C

The third query selects the seller account reference, seller name, product code, product description, and number of order ID, and checks the quantity (Rigger and Su, 2020). The sellers are joined with their respective products and grouped by their account reference number. The order should display the products which are not sold.

Figure 10: Output of Query C

All the products that are not sold are displayed by their seller accounts, seller name, product code, product description, total quantity and total sales.

Figure 11: Query D

The fourth query selected the category ID from products which are joined with the ordered products where the ordered products are cancelled. The products should be displayed by their product ID, category ID, product code, and product description.

Figure 12: Output of Query D

The output of the fourth query shows the category description, product code, product description, average quantity sold per product and average quantity sold per category of 7 values.

Part 2

Figure 13: Add Tables and Insert Data

The sql code is used to create product review and seller review tables. Specific values are added in these tables which will be used for further purposes.

Figure 14: Testing Code

Some random values are also added which will show different errors because different types of inputs were given which are not valid.

Figure 15: Fail as Primary Key Present

The first input showed error as the primary key was present.

Figure 16: Fail if Foreign Key Present

The second input showed error as the foreign key was present.

Figure 17: Fail as it Violates the CHECK Constraint.

The third input shows error as it violates the CHECK constraint.

Figure 18: Fail as Review Star and Review Date cannot be NULL

The fourth input shows error because the review star and review date are kept NULL.

Part 3

Input

Figure 19: Menu and Display Order Function

The code starts by connecting the python with the SQL database. Then the main menu is displayed which will provide the user with multiple options to navigate through the system (Leventidis et al. 2020). The code then creates different functions to retrieve the shopper’s information and display order history.

Figure 20: Get Current Basket and Add Item to Basket Function

Then another function is created to check if there is an existing basket for that particular day. The next function creates an elaborate function which helps to add an item to the basket easily.

Figure 21: View Basket Function

The next function is created to view the current basket of the shopper.

Figure 22: Change Quantity Function

The next function is created to change the quantity of an item in the basket. Then another function is created to remove the item from the basket.

Figure 23: Remove Item and Checkout Function

After that, a function is created to checkout the basket.

Figure 24: Driver Function

This is the final and the most important function within the code where all the functions implemented and the process to call them has been mapped into one another.

Output:

Figure 25: Main Menu

This is the CLI main menu of the python system, where all the features of the CLI are highlighted carefully.

Figure 26: Order History

The order history option enables the user to view all the items that have been previously purchased like this one.

Figure 27: Add Items

The add items function has a log of subfunctions too, in herer the products can all be seen along with its respective seller, price variations and much more.

Figure 28: View Basket

The view basket options enable the user to view all the items that are currently present inside the basket of the logged in user.

Figure 29: Change Quantity

The change quantity option enables the user to change the number of items ordered by simply following the steps shown in the image above.

Figure 30: Remove Item

The remove item function helps in removing any unwanted products from the basket.

Figure 31: Checkout

The checkout option present in option 6 shifts the products in the cart to the recently ordered items.

Figure 32: Exit

The option 7 makes the CLI program end its course of action.

Conclusion

In conclusion, this document successfully presents the ability of writing SQL queries and using the Python script for manipulating the wide Shopping database system. The queries for obtaining the data and specifically the named Python functions are clear examples of effective organisation of data bases. This document provides an overview of several issues, such as data retrieval, errors and queries, thereby demonstrating the utility of SQL technology in managing product, seller, and shopper data. Moreover, the CLI written in Python is useful, providing real utility: user navigation of a shopping platform. Combining SQL and Python provides a cyclic overview of a broader toolset for handling Big Data systems and making best business processes.

Reference List

Journals

• Arendsee, Z.W., Baker, A.L.V. and Anderson, T.K., 2022. smot: A python package and CLI tool for contextual phylogenetic subsampling. Journal of Open Source Software, 7(80), p.4193.
• Besta, M., Gerstenberger, R., Peter, E., Fischer, M., Podstawski, M., Barthels, C., Alonso, G. and Hoefler, T., 2023. Demystifying graph databases: Analysis and taxonomy of data organisation, system designs, and graph queries. ACM Computing Surveys, 56(2), pp.1-40.
• Foster, E. and Godbole, S., 2022. Database systems: a pragmatic approach. Auerbach Publications.
• Leventidis, A., Zhang, J., Dunne, C., Gatterbauer, W., Jagadish, H.V. and Riedewald, M., 2020, June. QueryVis: Logic-based diagrams help users understand complicated SQL queries faster. In Proceedings of the 2020 ACM SIGMOD International Conference on Management of Data (pp. 2303-2318).
• Rigger, M. and Su, Z., 2020. Testing database engines via pivoted query synthesis. In the 14th USENIX Symposium on Operating Systems Design and Implementation (OSDI 20) (pp. 667-682).
• Sáinz-Pardo Díaz, J. and López García, Á., 2022. A Python library to check the level of anonymity of a dataset. Scientific Data, 9(1), p.785.
• Toman, D. and Weddell, G., 2022. Fundamentals of physical design and query compilation. Springer Nature.

Author Bio

Edward Campbell   7 years | MSc in Computer Science

Hi I am Edward Campbell. I am a professional academic writer and have been in the occupation for more than 7 years. I have completed my MSc in Computer Science from the University of Oxford with a gold medal. I like programming and found of teaching others. If you are stuck with your CS assignment, dissertation, or any other paper then you can connect with me. I will help you ace your academic work.