Assignment 2: Database Systems And Database Models Due Week

Assignment 2 Database Systems And Database Modelsdue Week 2 And Worth

Design a query that will allow the finance department to determine the commissions paid to specific employees of the sales department for the month of December.

Compare the code of the query you designed in Question one to one that would show how much total compensation is paid to each employee for the same month. Determine and explain the factors necessary to ensure referential integrity. Create an object-oriented model to show how the tables are interrelated through the use of graphical tools, including relationship types such as 1:M, 1:1, or M:1. Identify which data components are entities and attributes, and the relationships between each using an object representation diagram.

Describe how Big Data could be used to assist in the productivity and forecasting of the organization’s products and resources.

Paper For Above instruction

In contemporary organizational environments, efficient management of data is crucial for operational success and strategic advantage. This paper discusses the development of specific SQL queries for an organization that manages high-end electronics sales, focusing on employee compensation, referential integrity, data modeling, and the application of Big Data in organizational forecasting and productivity enhancement.

SQL Query for Determining December Commissions

The primary task involves creating a query to assist the finance department in calculating commissions paid to sales employees for December. Given the provided database schema—comprising Employee, Invoice, InvoiceLine, Product, Department, and Job tables—the query must accurately retrieve relevant data by joining these tables on appropriate keys.

Assuming that the relevant data, such as invoice dates, employee identifiers, and sales amounts, are stored accurately, the SQL query is constructed as follows:



SELECT 


e.EmpNumber,


e.EmpFirstName,


e.EmpLastName,


SUM(il.Quantity * p.ProductCost) AS TotalSales,


e.CommissionRate,


SUM(il.Quantity  p.ProductCost)  e.CommissionRate AS CommissionPaid


FROM Employee e


JOIN Invoice i ON e.EmpNumber = i.EmpNumber


JOIN InvoiceLine il ON i.InvNumber = il.InvNumber


JOIN Product p ON il.ProductNumber = p.ProductNumber


WHERE 


i.InvDate BETWEEN TO_DATE('01-12-2023', 'DD-MM-YYYY') AND TO_DATE('31-12-2023', 'DD-MM-YYYY')


AND e.DepartmentID = (SELECT DepartmentID FROM Department WHERE DepartmentDescription = 'Sales')


GROUP BY e.EmpNumber, e.EmpFirstName, e.EmpLastName, e.CommissionRate;

This query retrieves each sales employee's total sales for December and computes their commission as a percentage of sales, facilitating the finance department's payment calculations.

Comparison of Commission vs. Total Compensation Queries

While the previous query calculates commissions based solely on sales, the total compensation query extends this by including base salary and benefits. To accomplish this, the SQL must incorporate the employee’s yearly salary data from the Employee table and any additional compensation components, if present. An example of a total compensation query is as follows:



SELECT 


e.EmpNumber,


e.EmpFirstName,


e.EmpLastName,


e.YrlySalary,


e.CommissionRate,


-- Total sales for December


SUM(il.Quantity * p.ProductCost) AS TotalSales,


-- Commission earned


SUM(il.Quantity  p.ProductCost)  e.CommissionRate AS CommissionPaid,


-- Total compensation


e.YrlySalary + (SUM(il.Quantity  p.ProductCost)  e.CommissionRate) AS TotalCompensation


FROM Employee e


JOIN Invoice i ON e.EmpNumber = i.EmpNumber


JOIN InvoiceLine il ON i.InvNumber = il.InvNumber


JOIN Product p ON il.ProductNumber = p.ProductNumber


WHERE 


i.InvDate BETWEEN TO_DATE('01-12-2023', 'DD-MM-YYYY') AND TO_DATE('31-12-2023', 'DD-MM-YYYY')


GROUP BY e.EmpNumber, e.EmpFirstName, e.EmpLastName, e.YrlySalary, e.CommissionRate;

This query combines base salary and commissions for a comprehensive view of employee compensation, vital for payroll and financial planning.

Factors Ensuring Referential Integrity

Ensuring referential integrity within this database relies on establishing correct foreign key relationships. Critical factors include:

  • Primary Key Constraints: Unique identifiers such as EmpNumber, InvNumber, and ProductNumber ensure each record uniquely identifies entities.
  • Foreign Key Constraints: Linking Employee to Invoice via EmpNumber, InvoiceLine to Invoice via InvNumber, and InvoiceLine to Product via ProductNumber maintains data consistency.
  • Cascade Rules: Proper ON DELETE and ON UPDATE rules prevent orphan records and maintain data coherence when records are modified or removed.
  • Data Validation: Constraints on data types, NOT NULL conditions, and check constraints ensure integrity at the data input level.
  • Transaction Control: Using transactions ensures that related data modifications complete fully or roll back to preserve integrity.

Object-Oriented Modeling and Entity-Relationship Diagram

Modeling the database using object-oriented principles involves identifying classes, attributes, and relationships. Entities include Employee, Invoice, InvoiceLine, Product, Department, and Job, with attributes as specified initially.

Relationships are characterized as follows:

  • Employee to Invoice: 1:M (one employee can generate multiple invoices)
  • Invoice to InvoiceLine: 1:M (each invoice has multiple invoice lines)
  • InvoiceLine to Product: M:1 (multiple invoice lines can refer to one product)
  • Employee to Department: M:1 (employees belong to one department, but each department has many employees)
  • Employee to Job: M:1 (employees hold one job position, but each job can be held by many employees)

This object model can be visualized through UML diagrams created using tools like Visio, illustrating classes with attributes and relationships with appropriate multiplicities.

Application of Big Data for Organizational Productivity and Forecasting

Big Data analytics plays a transformative role in modern organizations by processing vast datasets beyond traditional database capacities. Using Big Data tools like Hadoop and Spark, the organization can analyze various data sources including sales, customer behavior, inventory levels, and supply chain data to enhance productivity and make more accurate forecasts.

One application involves predictive analytics for demand forecasting, which enables aligning production schedules with anticipated sales patterns. Machine learning algorithms can analyze historical sales data to predict future trends, optimizing inventory management and reducing overstock or stockouts. For example, by aggregating sales data across geographic regions and timeframes, businesses can identify seasonal variations and adjust marketing or production strategies accordingly (Manyika et al., 2011).

Additionally, Big Data can facilitate real-time analytics for monitoring operations, detecting inefficiencies, and responding swiftly to market changes. For instance, social media data and online reviews provide insights into customer sentiment and emerging preferences, informing product development and marketing campaigns (Mayer-Schönberger & Cukier, 2013). Integrating Big Data analytics into enterprise resource planning (ERP) systems enhances decision-making capabilities, aligning resource allocation with predictive insights.

Overall, leveraging Big Data allows organizations to increase productivity through smarter operational management and improve forecasting accuracy, resulting in competitive advantages and increased profitability (McAfee et al., 2012).

References

  • Manyika, J., Chui, M., Brown, B., Bughin, J., Doherty, R., et al. (2011). Big Data: The next frontier for innovation, competition, and productivity. McKinsey Global Institute.
  • Mayer-Schönberger, V., & Cukier, K. (2013). Big Data: A Revolution That Will Transform How We Live, Work, and Think. Eamon Dolan/Houghton Mifflin Harcourt.
  • McAfee, A., Brynjolfsson, E., Davenport, T. H., Patil, D. J., & Barton, D. (2012). Big Data: The Management Revolution. Harvard Business Review, 90(10), 60-68.
  • Elragal, A. (2014). Big data analytics for business process management. International Journal of Business Intelligence and Data Mining, 9(2), 109-125.
  • Chen, H., Chiang, R. H. L., & Storey, V. C. (2012). Business Intelligence and Analytics: From Big Data to Big Impact. MIS Quarterly, 36(4), 1165-1188.
  • Karunakaran, K., & Dey, P. K. (2018). Big Data in supply chain management: A review. International Journal of Logistics Systems and Management, 30(1), 1-21.
  • Verma, A., & Mahmood, A. (2017). Big Data Analytics: A Literature Review. International Journal of Computer Applications, 168(10), 20-24.
  • Groves, P. (2013). Designing a Big Data Strategy. Gartner Reports.
  • Attia, N., & Fiore, U. (2018). Big Data and Business Analytics: Concept, Techniques, Applications and Challenges. Journal of Computer Science & Systems Biology, 11(2), 16-25.
  • Redman, T. C. (2013). Data Driven: Profiting from Your Most Important Business Asset. Harvard Business Review Press.

Related Assignments