Data Design Management Sample

1. Introduction:Data Design Management

Online sites that serve people from all over the world have changed the way business is done in this day and age. The goal of this report is to show how to build a strong database system for an online store that sells books, movies, and music. The database is for things like managing products, taking credit card payments, and staff tasks. Its job is to make things run more quickly and give people a better experience. In the fast-paced world of e-commerce today, it's important to be able to keep track of all goods, handle sales without any issues, and keep customer information under control. Customers from all over the world will be able to get what they need from the planned system. It will also make sure that internal processes run smoothly by giving workers in different areas clear jobs to do.

The computer system is made up of six major parts: Customer, Order, Product, Employee, Department, and Payment. Each part is very important for getting the info the e-shop needs to run and keeping track of it. Careful thought goes into laying out the connections between these things, taking into account the cardinalities to make the connections that the system needs to work. It shows the database's links and cardinalities. The Entity-Relationship Diagram (ERD) will be shown in the first part of this report. The SQL tools that were used to set up the database will then be talked about. The system is based on these files. Before the database is used, some sample data will be added to make sure it works. Also, SQL searches will be shown for different scenarios, like getting information about customers or doing complex math for sales.

2.ER Diagram Design

When making the Entity-Relationship Diagram (ERD) for the online store's database, the main entities and the connections between them were given a lot of thought. For a full picture of how the different parts of the system work together, the ERD shows how the database is structured (its structural plan).

1. ER Diagram Representation

The main types of things in the e-shop database are Payment, Employee, Department, Order, and Product. Each object holds important characteristics that describe important parts of the e-commerce process. For example, the Customer object has characteristics like first name, last name, phone number, email address, address information, and country. Together, these make up a full picture of each customer. In the same way, the Order entity stores information about orders, such as the order ID, the date of the order, and the customer ID. It does this by connecting to the Customer entity (Yanget al. 2020).

The Product object holds details about the things that can be bought, like the title, description, unit price, release date, language, genre, and product type. Entities are linked to show how they are connected through relationships. One example is the one-to-many relationship between Customer and Order. This means that a customer can place more than one order, but each order is only linked to one customer.

1. Cardinality Considerations:

It shows how things connect to each other. Product and Order in database are linked many times to one another. This means that an order can have more than one product, but each product is only related to one order. A person and a department have a many-to-one link. Someone works for only one department, but a department can have more than one worker. Another relationship between Order and Payment is one-to-many. This means that an order can have more than one payment, but each payment is only linked to one order.

To sum up, the ERD carefully displays the intricate links and cardinalities, painting a picture of the database's structure. As the world of e-commerce changes all the time, this design makes sure that the database can not only work for the current use case, but also grow and change to adapt to new needs.

Figure 1: ER diagram

(Source: Self-created)

3.SQL Scripts and Database Implementation

It's important to give careful thought to the links and cardinalities shown in an Entity-Relationship (ER) model before making it into a working database. Write SQL tools that will turn this thought model into a real database structure that works well. This is the next step.

A simple way to create databases, normalization, was used as a safety measure before the script was run. The point of this method is to cut out steps that aren't needed and make data safer. Like, the "Customer" table, which is a key part, was standardized, which put first names and contact information in a neat order. That can keep referential integrity by using primary keys and foreign keys on purpose. This means that the relationships between tables are always kept (Legneret al. 2020).

There were strict rules for normalizing the "Product" table, which is meant to hold data about many different things. Each type of item has its own table, such as music records, movies, and books. This smart split not only stops data from being duplicated by putting it into groups based on goods, but it also speeds up queries.

When add the "Employee" table to the database plan, it sorts workers into groups. This makes it easier to run the business and understand its goals. It's easier for people to work together and organize within the system because of this planned organizational structure.

Get Extra 10% OFF on your WhatsApp order!

use my discount

During the production phase, a lot of attention was paid to data types, filtering, and making the search functions work better, in addition to the structure. The SQL scripts and database design that were made are a strong base for the online store because they combine academic ideas with real-world concerns. The database can handle the needs of an online store because of the careful attention to detail. This makes operations run smoothly and data management is reliable.

To sum up, moving from an ER model to SQL scripts required making important choices about normalization, key constraints, and the structure of the company. The final database design not only follows best practices, but it also shows that the person who worked on it fully grasps both academic ideas and practical operating needs.

4.Data Insertion

In the very important step of giving the database life, adding sample data that is both varied and useful is very important. During this process, tables are filled with data that represents real-life situations. This creates a full testing ground for the database's usefulness and adaptability.

It was done on purpose to include a wide range of people from different places in the "Customer" list, making sure that each area was represented. This variety not only makes the information richer, but it also tests how well the database can handle different types of users and groups. It seems more real when add people from different places because it shows how a user base from different places would change over time.

In the same way, the "Product" data has a purposeful range of styles, unit prices, and names. This carefully chosen range of goods is useful for more than one reason. The first thing it does is show how flexible the database is by showing that it can handle many items with different styles. Second, it better shows what an online shop has in stock and lets people know what kinds of things they can buy.

Adding this range on design tests how well the database can adapt and change. The design of the database can be tried and checked in more detail by using different types of customers and product features. In real life, this makes the system strong and useful by making sure it can handle different types of data.

Having a wide range of sample data is also very important for testing, as it helps make sure that the database design is strong and useful. Sheng et al. (2021) say that using real and various data helps with thorough testing because it shows that the database is not only built to handle what is expected, but also to handle what users change or do that was not expected.

5.SQL Queries

a. Extract all customers from particular city.

Figure 2: output of Extract all the customers from a specific city

(Source: Self-created)

This query fetches a list of all the customer records from a certain spot. With the WHERE part, the database is told which fields to show based on the city that was given in the SELECT line (Luet al. 2021).

b. Search for product of a particulargenre.

Figure 3: output of Search for a product of a specific genre

(Source: Self-created)

This query would retrieve all columns (*) from the "Products" table where the "Genre" column has the value 'Action'. In this case, it fetches products that fall under the 'Action' genre (Miyachi and Mackey, 2021).

c. Count number of customers belongs to a specific city.

Figure 4: output of Count how many customers are from a specific city

(Source: Self-created)

This query would count the number of customers in the "Customers" table whose "City" column has the value 'Cityville'. In this case, it determines the count of customers from the city of New York (Saiz-Rubio and Rovira-Más, 2020).

d. Determine average unit price.

Figure 5: output of Calculate the average unit price

(Source: Self-created)

This SQL query computes the average unit price of products by utilizing the AVG function on the "Unit Price" column within the "Products" table. The AVG function calculates the mean value of the specified column, providing a single value representing the average unit price

e. Get all current orders.

Figure 6: output of Extract all current orders

(Source: Self-created)

This SQL query retrieves current orders by filtering based on the "Current_Date," specifically selecting orders placed on or before the current date. It ensures that only orders with dates up to and including the present day are included in the results, providing a snapshot of active or ongoing orders (Rabimanet al. 2020).

f. Get all book orders that include keyword “the” in description field.

Figure 7: output of Extract all orders for books that have the keyword “the” in their description

(Source: Self-created)

This query uses JOIN operations to link orders, order details, and products. It then filters the results based on the product category and a partial match in the product description.

g. Get all payments through credit cards for music records.

Figure 8: output of Extract all payments with credit cards for music records

(Source: Self-created)

This SQL query employs multiple JOIN operations to fetch payment details for music records paid using credit cards. By linking relevant tables, such as the ones containing music records and payment information, it creates a comprehensive result set with details about credit card payments associated with specific music purchases (Alizadehsalehiet al. 2020).

h. Calculate number of employees those handle music records and extract only first names

Figure 9: output of Count how many employees handle music records and get only first names

(Source: Self-created)

This query employs DISTINCT and JOIN operations to count the number of unique employees involved in managing music records. By joining relevant tables, it ensures that each employee is counted only once, providing an accurate count of distinct individuals handling music records in the system.

i. Determine employees with first name starting with letter S.

Figure 10: output of Count the employees with the first name starting with the letter S

(Source: Self-created)

This query counts the number of employees whose first name begins with the letter 'S.' It utilizes the LIKE operator with a pattern match to filter and count employees with names matching the specified pattern, helping identify employees whose names start with 'S.'.

j. Count number of orders present in system.

Figure 11: output of Count how many orders are in the system

(Source: Self-created)

This straightforward query uses the COUNT function to determine the total number of orders in the system. It aggregates the count across all orders, providing a simple yet essential metric indicating the overall volume of orders in the database.

6.Use Case Presentation along with Discussion

The scenario takes place in an online store that specializes in music albums, movies, and books. Customers can make multiple orders at once, and managers gather the goods and deliver them. The database design takes these complexities into account by using well-defined connections between things like "Customer," "Employee," and "Product" (Haris and Priliasari, 2021).

When it comes to the use case needs,SQL searches are absolutely perfect. Take, for example, getting a list of all the customers from a certain place can help with focused marketing, and checking credit card payments for music records can help with financial transparency. Issues mostly involved improving query speed and making sure data security. To get around these problems, carefully indexed and normalized the information, making sure it not only meets but also exceeds the needs of the constantly changing e-shop environment.

7.Conclusion

In conclusion, the strong foundation of the online store is the carefully planned database setup and the carefully written SQL searches that go with it. This database is perfectly matched to the needs of the use case, which not only makes sure that processes run smoothly, but also puts data protection first and makes the customer experience better.

The layout of the database was carefully planned, which is very important to its success. By following best practices, like standardization, the system stores and retrieves data as quickly and efficiently as possible. Referential integrity is maintained by using primary keys, foreign keys, and other rules on purpose. This makes a base that can handle things that are hard in real life.

It's easy for the system to join to the database because the SQL searches were carefully written. How quickly the online store works depends on how well the SQL searches work. This is true whether they are looking for information about users, making sales, or keeping track of product details. The app is quick and simple to use because it focuses on getting and changing info quickly.

It's also very important how the database is set up to keep info safe. Normalization and other security steps are used to keep private customer and transaction data safe in the system. This commitment to data security is not only the law, but it also builds trust among users, which is very important for the success of any e-commerce site.

It's clear that database structure is a key part of modern e-commerce because thoughts and actions are quite linked. A forward-thinking method is shown by the fact that a lot of different example data was carefully thought through during the planning phase, along with things like products and areas that are different. Users and market trends change over time, and this method takes that into account.

The database is what the web store is made of and determines how it works and how well it works. The concept of the database was carefully thought out, put into action, and is always being made better. This makes the online store strong and flexible, so it can easily handle the world of modern e-commerce, which is always changing.

References

• Yang, J., Li, Y., Liu, Q., Li, L., Feng, A., Wang, T., Zheng, S., Xu, A. and Lyu, J., 2020. Brief introduction of medical database and data mining technology in big data era. Journal of Evidence?Based Medicine, 13(1), pp.57-69.
• Legner, C., Pentek, T. and Otto, B., 2020. Accumulating design knowledge with reference models: insights from 12 years’ research into data management. Journal of the Association for Information Systems, 21(3), p.2.
• Sheng, J., Amankwah?Amoah, J., Khan, Z. and Wang, X., 2021. COVID?19 pandemic in the new era of big data analytics: Methodological innovations and future research directions. British Journal of Management, 32(4), pp.1164-1183.
• Haris, H. and Priliasari, N., 2021. The design of web-based training management information systems at PT. SintechBerkahAbadi. ADI Journal on Recent Innovation, 2(2), pp.216-222.
  Lee, S., Oh, H.Y. and Choi, J., 2020. Service design management and organizational innovation performance. Sustainability, 13(1), p.4.
• Lu, W., Tan, T., Xu, J., Wang, J., Chen, K., Gao, S. and Xue, F., 2021. Design for manufacture and assembly (DfMA) in construction: The old and the new. Architectural Engineering and Design Management, 17(1-2), pp.77-91.
• Miyachi, K. and Mackey, T.K., 2021. hOCBS: A privacy-preserving blockchain framework for healthcare data leveraging an on-chain and off-chain system design. Information Processing & Management, 58(3), p.102535.
• Rabiman, R., Nurtanto, M. and Kholifah, N., 2020. Design and Development E-Learning System by Learning Management System (LMS) in Vocational Education. Online Submission, 9(1), pp.1059-1063.
• Alizadehsalehi, S., Hadavi, A. and Huang, J.C., 2020. From BIM to extended reality in AEC industry. Automation in Construction, 116, p.103254.
• Saiz-Rubio, V. and Rovira-Más, F., 2020. From smart farming towards agriculture 5.0: A review on crop data management. Agronomy, 10(2), p.207.