Part 2: Software Skills, Database Design, And Querying

Part 2software Skills Database Design And Queryingbusiness Skills In

Part 2 software skills: Database design and querying Business skills: Inventory management In this exercise, you will use database software to design a database for managing inventory for a small business. Kamen Rider, located in New York, sells road, mountain, hybrid, leisure, and children’s bicycles. Currently, Kamen Rider’s purchases bikes from three suppliers but plans to add new suppliers in the near future. Using the information found in the tables given in the small desktop database, help manage information about Kamen Rider’s suppliers and products. Perform the following.

1. Execute a query that identifies the five most expensive bicycles (for customers). The query should list the bicycles in descending order from most expensive to least expensive (by Selling Price), the quantity on hand for each, and the markup percentage for each. Save this query as Q1_Five_Most_Expensive_Bicycles. Hint: Profit Mark Up = (Selling Price – Cost Price)/Cost Price. (2.5 points)
2. Execute a query that lists each supplier, its products, the quantities on hand, and associated reorder levels. For each supplier, the products should be sorted alphabetically. Include a criterion for bicycles that have Quantity_on_Hand greater than 5. Hint: This requires hierarchical sorting and a numerical filter. Save this query as Q2_Supplier_Product (2.5 points)
3. Execute a query listing only the bicycles with names starting with “E” as well as those that end with “er”, and their corresponding suppliers. In your query, include the Product Name, Purchase Cost, and the supplier’s Company_Name. This needs a little bit of thinking. Save this query as Q3_BR_bicycles (2.5 points)
4. To enhance the database, create a table called Supplier_Info that has supplier telephone numbers. Include an appropriate input mask. Populate the table with the suppliers to demonstrate entering data and checking that it works. For full credit, include other relevant fields to ensure normalization and avoid redundancies. Save this table as Supplier_Info (2.5 points)

Paper For Above instruction

The following paper provides a comprehensive overview of designing and querying a bicycle inventory database for a small business, Kamen Rider, which operates in New York. The exercise focuses on developing relevant queries and creating a supportive supplier information table to improve inventory management and business operations. This essay elaborates on database design principles, query formulation, normalization, and practical application, referencing reputable sources in database development and management.

Introduction

In contemporary business environments, efficient inventory management is crucial for maintaining competitiveness and profitability. Small businesses such as Kamen Rider, which sells various types of bicycles, rely heavily on robust database systems to oversee product information, supplier details, and sales data. Proper database design ensures data integrity, facilitates quick data retrieval, and supports decision-making processes. Queries in database systems serve as powerful tools to analyze data, identify trends, and streamline operations. This paper elucidates the process of designing specific queries for a bicycle inventory database and creating a normalized supplier information table, emphasizing best practices in database management.

Database Design and Querying for Bicycle Inventory

The primary goal of this exercise is to develop a set of SQL queries that facilitate insightful analysis of the bicycle inventory and supplier data. The first query aims to identify the top five most expensive bicycles listed for customers, ordering the results from most to least expensive based on the selling price, alongside their quantities on hand and markup percentage. This type of query helps identify premium products and assess profit margins, critical for strategic pricing and inventory decisions.

The calculation of markup percentage, defined as (Selling Price - Cost Price) / Cost Price, provides insights into profitability and sale efficiency. SQL queries that incorporate calculated fields enhance data analysis capabilities, enabling business owners to make informed choices (Elmasri & Navathe, 2015).

Hierarchical Sorting and Filtering

The second query leverages hierarchical sorting—sorting products alphabetically within each supplier—and filters products with a quantity on hand greater than five. Such filtering helps determine stock levels that require replenishment and prioritizes restocking efforts, thereby reducing stockouts and overstock scenarios. Proper filtering and sorting ensure the data presented is relevant and actionable (Date, 2019).

Pattern Matching in Product Names

The third query focuses on pattern matching within product names, retrieving bicycles whose names start with “E” or end with “er.” This involves using SQL pattern matching operators like LIKE with wildcards, which allow for flexible searches based on specific string patterns. Including supplier names and critical cost information helps evaluate supplier performance and product cost structures (Rob & Coronel, 2007).

Normalization and Creating the Supplier_Info Table

Enhancing the database with a dedicated supplier information table—Supplier_Info—helps normalize data and reduce redundancy. Normalization ensures each piece of data is stored only once, maintaining consistency and integrity across the database (Coronel & Morris, 2015). Including an input mask for telephone numbers improves data entry accuracy, which is crucial for communication and record-keeping.

Populating the Supplier_Info table with five suppliers, each with a fictional telephone number, demonstrates practical data entry skills. Additional relevant fields such as supplier address and contact name might be included to further normalize the database and facilitate comprehensive supplier management (Date, 2019).

Conclusion

This exercise exemplifies essential concepts in database design and query development for small business inventory management. Developing targeted SQL queries allows for efficient data analysis, supporting strategic business decisions. Creating a normalized supplier information table emphasizes best practices in database normalization and data quality. Overall, leveraging these techniques enhances operational efficiency and provides valuable business insights, demonstrating the power of well-designed databases in real-world applications (Elmasri & Navathe, 2015; Coronel & Morris, 2015).

References

• Coronel, C., & Morris, S. (2015). Database Systems: Design, Implementation, & Management. Cengage Learning.
• date, C. J. (2019). Database Design and Relational Theory: Normal Forms and All That Jazz. O'Reilly Media.
• Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems. Pearson.
• Rob, P., & Coronel, C. (2007). Database Systems: Design, Implementation, and Management. Thomson Course Technology.