Assignment #1: Database Systems And Database Models ✓ Solved

Assignment #1: Database Systems and Database Models Imagine

Imagine that you have been hired as a consultant to assist in streamlining the data processing of an international based organization that sells high-end electronics. The organization has various departments such as payroll, human resources, finance, marketing, sales, and operations. The sales department is the only department where employees are paid a commission in addition to their yearly salary and benefits. All other departments compensate their employees with a yearly salary and benefits only. Commission is paid by multiplying the employee’s commission rate by the total amount of product units sold.

You have access to the following data sets: Employee (EmpNumber, EmpFirstName, EmpLastName, CommissionRate, YrlySalary, DepartmentID, JobID), Invoice (InvNumber, InvDate, EmpNumber, InvAmount), InvoiceLine (InvLineNumber, InvNumber, ProductNumber, Quantity), Product (ProductNumber, ProductDescription, ProductCost), Department (DepartmentID, DepartmentDescription), Job (JobID, JobDescription).

Write a two (2) page paper in which you:

  1. 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. Note: You will need to generate the tables described above (Employee, Invoice, InvoiceLine, Product, Department, and Job) in order to compare and validate your code. Validated query code must be part of your paper.
  2. Compare the code of the query you designed in Question one (1) to one that would show how much total compensation is paid to each employee for the same month.
  3. Determine and explain the factors necessary to ensure referential integrity.
  4. Create an object-oriented model to show how the tables are interrelated through the use of graphical tools such as Microsoft Visio, or an open source alternative such as Dia. Make sure that you are able to show the relationship types such as 1:M, 1:1, or M:1. Additionally, remember to include the determined factors from the previous assignment requirement. Note: The graphically depicted solution is not included in the required page length.
  5. Identify which data components are the entities and attributes, and the relationship between each using an object representation diagram through the use of graphical tools such as Microsoft Visio, or an open source alternative such as Dia. Note: The graphically depicted solution is not included in the required page length.
  6. Describe how Big Data could be used to assist in the productivity and forecasting of the organization’s products and resources.

Your assignment must follow these formatting requirements: Be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides; citations and references must follow APA or school-specific format. Check with your professor for any additional instructions.

Include a cover page containing the title of the assignment, the student’s name, the professor’s name, the course title, and the date. The cover page and the reference page are not included in the required assignment page length. Include charts or diagrams created in Excel, Visio, MS Project, or one of their equivalents such as Open Project, Dia, and OpenOffice. The completed diagrams / charts must be imported into the Word document before the paper is submitted.

Paper For Above Instructions

As a consultant to the international organization selling high-end electronics, this paper addresses the need for streamlining data processing across various departments, with a focus on the sales department, where commission is a vital part of employee compensation. This document encompasses the design of requisite queries, the establishment of referential integrity, visual modeling of database relationships, and the exploration of Big Data's potential impacts on operational efficiency.

1. Designing the Query for Commissions Paid

The sales department's commission structure requires a query to extract total commissions paid in December. The SQL query is designed as follows:

SELECT e.EmpFirstName, 


e.EmpLastName, 


SUM(i.InvAmount * e.CommissionRate) AS TotalCommission


FROM Employee e


JOIN Invoice i ON e.EmpNumber = i.EmpNumber


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


AND MONTH(i.InvDate) = 12


AND YEAR(i.InvDate) = YEAR(CURDATE()) -- Assuming current year for context


GROUP BY e.EmpNumber;

This query links the Employee and Invoice tables, computing each employee's commission based on the total invoice amounts generated in December. The crucial elements include the commission rate from the Employee table and the invoice amounts from the Invoice table.

2. Comparison of Compensation Queries

To illustrate the total compensation for each employee, including their base salary and commission, the following SQL query can be utilized:

SELECT e.EmpFirstName, 


e.EmpLastName, 


e.YrlySalary + COALESCE(SUM(i.InvAmount * e.CommissionRate), 0) AS TotalCompensation


FROM Employee e


LEFT JOIN Invoice i ON e.EmpNumber = i.EmpNumber 


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


AND MONTH(i.InvDate) = 12


AND YEAR(i.InvDate) = YEAR(CURDATE())


GROUP BY e.EmpNumber;

This query takes into account both fixed salaries and commission earnings, employing a LEFT JOIN to ensure all employees are listed. The use of COALESCE allows for handling employees with no sales data while still reflecting their salary in the results.

3. Ensuring Referential Integrity

Referential integrity is crucial in maintaining consistent, accurate relationships among the database tables. Key considerations include:

  • Primary Keys: Each table should have a primary key that uniquely identifies records.
  • Foreign Key Constraints: These should be employed to restrict entries to only valid identifiers, like ensuring that the EmpNumber in Invoice references an existent employee.
  • Data Type Consistency: Ensuring that foreign keys correspond closely in type and size with their referenced primary keys.
  • Cascade Options: Implementing cascading deletes or updates can also help in maintaining integrity by ensuring that relational associations remain valid after data changes.

4. Object-Oriented Model and Relationships

The interrelationship among tables can be graphically represented using an Object-Oriented model, showcasing relationships as follows:

  • 1:M (One-to-Many): Employee to Invoice, where one employee can have multiple invoices.
  • 1:1 (One-to-One): Department to Job, where each job can be designated to one department.
  • M:1 (Many-to-One): InvoiceLine to Invoice, where multiple invoice line items can belong to one invoice.

5. Entities and Attributes

The entities within these data sets include:

  • Employee: EmpNumber, EmpFirstName, EmpLastName, CommissionRate, YrlySalary, DepartmentID, JobID.
  • Invoice: InvNumber, InvDate, EmpNumber, InvAmount.
  • Product: ProductNumber, ProductDescription, ProductCost.
  • Department: DepartmentID, DepartmentDescription.
  • Job: JobID, JobDescription.

6. Utilization of Big Data

Big Data can significantly enhance the productivity and forecasting capabilities of the organization through:

  • Predictive Analytics: Analyzing historical purchase data to determine future trends, enhancing inventory management.
  • Customer Insights: Leveraging customer data to refine marketing strategies, specifically targeting demographics with relevant promotions.
  • Operational Efficiency: Streamlining operations based on data-driven metrics, thereby improving turnaround times for sales and logistics.

Conclusion

This analysis has provided a comprehensive overview of the necessary strategies for database design and management, focusing on sales commission processing and the broader implications of data integrity and utilization of analytics.

References

  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems. Pearson.
  • Rob, P., & Coronel, C. (2016). Database Systems: Design, Implementation, & Management. Cengage Learning.
  • Date, C. J. (2012). An Introduction to Database Systems. Pearson.
  • Hoffer, J. A., Venkataraman, R., & Topi, H. (2013). Modern Database Management. Pearson.
  • Connolly, T. M., & Begg, C. (2015). Database Systems: A Practical Approach to Design, Implementation, and Management. Pearson.
  • Hernandez, M. J. (2020). Data Management for Researchers: Organize, Maintain and Share Your Data for Research Success. Facet Publishing.
  • McKinsey Global Institute. (2011). Big Data: The Next Frontier for Innovation, Competition, and Productivity.
  • IBM. (2013). A New Era of Data. IBM Big Data Analytics.
  • Oracle. (2020). Cloud Applications and Data Management Solutions.
  • Microsoft. (2021). Azure Data Services: Modern Data Management and Analytics.

Related Assignments