Week 3 Assignment: Use SQL Query Language
week 3 assignment use sql query lang
Imagine that you have been contracted to develop a system for processing data for an organization that sells household items and electronics. There are departments such as IT, customer service, marketing, sales, payroll, accounts payable, and operations. Employees within each department have specific roles, and all are paid a salary and benefits, except for sales employees who also receive a commission based on their sales. The assignment involves creating entities and attributes using SQL Data Definition Language (DDL), inserting data with SQL Data Manipulation Language (DML), updating records, retrieving data through SELECT statements, and generating sales reports on commissions paid to employees for December.
Paper For Above instruction
The development of a database system for a retail organization involves meticulous planning and execution of SQL commands to ensure data integrity, efficient data retrieval, and accurate reporting. This comprehensive approach includes creating the database schema, populating tables with data, updating records as needed, and executing queries to generate insightful reports, particularly for the sales department’s commission calculations. This paper discusses the critical aspects of this process, including designing entities, relationships, and constraints, supported by SQL code examples and considerations for maintaining referential integrity.
Designing Entities and Attributes
The foundation of the database is built on defining entities that mirror real-world objects within the organization. At a minimum, five entities—such as Employees, Departments, Roles, Sales, and Products—are created to represent key data components. Employees will have attributes including EmployeeID, Name, DepartmentID, RoleID, Salary, and Benefits. The Departments entity will include DepartmentID and DepartmentName. Roles encompass RoleID and RoleName, with specific roles like Supervisor, Administrative Assistant, and Janitor. The Sales entity records transactions, including SaleID, EmployeeID, ProductID, Quantity, SaleDate, and SaleAmount. Lastly, the Products entity contains ProductID, Name, Category, Price, and CommissionAmount.
Creating Tables Using DDL Statements
The SQL DDL commands facilitate creating tables with primary and foreign keys to establish relationships. For example:
CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(50) NOT NULL
);
CREATE TABLE Roles (
RoleID INT PRIMARY KEY,
RoleName VARCHAR(50) NOT NULL
);
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
DepartmentID INT,
RoleID INT,
Salary DECIMAL(10,2),
Benefits VARCHAR(255),
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID),
FOREIGN KEY (RoleID) REFERENCES Roles(RoleID)
);
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
Name VARCHAR(100),
Category VARCHAR(50),
Price DECIMAL(10,2),
CommissionAmount DECIMAL(10,2)
);
CREATE TABLE Sales (
SaleID INT PRIMARY KEY,
EmployeeID INT,
ProductID INT,
Quantity INT,
SaleDate DATE,
SaleAmount DECIMAL(10,2),
FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);
Factors to ensure referential integrity include defining correct foreign key constraints, using cascading updates or deletes where appropriate, and validating data before insertion to prevent orphaned records and maintain consistency across related tables.
Populating Tables with DML Statements
Data is inserted into each table with INSERT statements, for example:
INSERT INTO Departments VALUES (1, 'IT');
INSERT INTO Departments VALUES (2, 'Customer Service');
INSERT INTO Departments VALUES (3, 'Marketing');
INSERT INTO Departments VALUES (4, 'Sales');
INSERT INTO Departments VALUES (5, 'Payroll');
INSERT INTO Roles VALUES (1, 'Supervisor');
INSERT INTO Roles VALUES (2, 'Administrative Assistant');
INSERT INTO Roles VALUES (3, 'Janitor');
INSERT INTO Employees VALUES (101, 'John Doe', 4, 1, 55000.00, 'Health Insurance');
INSERT INTO Employees VALUES (102, 'Jane Smith', 2, 2, 40000.00, 'Dental Benefits');
INSERT INTO Employees VALUES (103, 'Emily Johnson', 3, 2, 42000.00, 'Retirement Plan');
INSERT INTO Employees VALUES (104, 'Michael Brown', 1, 3, 38000.00, 'Paid Time Off');
INSERT INTO Employees VALUES (105, 'Sarah Davis', 4, 1, 60000.00, 'Health Insurance');
INSERT INTO Products VALUES (1001, 'Wireless Mouse', 'Electronics', 25.00, 2.00);
INSERT INTO Products VALUES (1002, 'Laptop Bag', 'Household Items', 40.00, 3.00);
INSERT INTO Products VALUES (1003, 'Smart TV', 'Electronics', 500.00, 25.00);
INSERT INTO Products VALUES (1004, 'Blender', 'Household Items', 60.00, 4.00);
INSERT INTO Products VALUES (1005, 'Desk Lamp', 'Electronics', 30.00, 2.50);
INSERT INTO Sales VALUES (2001, 105, 1003, 2, '2023-12-15', 1000.00);
INSERT INTO Sales VALUES (2002, 101, 1002, 3, '2023-12-10', 120.00);
INSERT INTO Sales VALUES (2003, 105, 1004, 5, '2023-12-20', 300.00);
INSERT INTO Sales VALUES (2004, 102, 1001, 10, '2023-12-05', 250.00);
INSERT INTO Sales VALUES (2005, 103, 1005, 4, '2023-12-12', 120.00);
Updating Records Using DML Statements
Records are updated with UPDATE statements. For instance:
UPDATE Employees SET Salary = 56000 WHERE EmployeeID = 101;
UPDATE Products SET Price = 55.00 WHERE ProductID = 1004;
UPDATE Sales SET Quantity = 3 WHERE SaleID = 2002;
UPDATE Employees SET Benefits = 'Updated Benefits' WHERE EmployeeID = 102;
UPDATE Products SET CommissionAmount = 3.50 WHERE ProductID = 1002;
Retrieving Data with SELECT Statements
To verify data and generate reports, SELECT statements are used:
SELECT * FROM Departments;
SELECT * FROM Roles;
SELECT * FROM Employees;
SELECT * FROM Products;
SELECT * FROM Sales;
Sample output would include the data inserted, allowing cross-verification and validation of data integrity.
Generating Sales Department Commission Report
The core of this report is a SQL query calculating commissions for employees in the sales department during December. The query joins the Employees, Sales, and Products tables to identify total commission payments:
SELECT
E.EmployeeID,
E.Name,
SUM(P.CommissionAmount * S.Quantity) AS TotalCommission
FROM
Employees E
JOIN
Sales S ON E.EmployeeID = S.EmployeeID
JOIN
Products P ON S.ProductID = P.ProductID
WHERE
E.RoleID = (SELECT RoleID FROM Roles WHERE RoleName = 'Salesperson')
AND S.SaleDate BETWEEN '2023-12-01' AND '2023-12-31'
GROUP BY
E.EmployeeID, E.Name;
Executing this query provides precise figures on commissions paid to each sales employee for December, assisting management evaluation and commission management.
Conclusion
Developing a robust database system involves careful design of entities, relationships, and constraints, along with precise SQL coding for data operations. Ensuring referential integrity safeguards data consistency, while rigorous data insertion and updating activities maintain current and accurate records. Constructing targeted queries, such as commission reports, offers valuable insights into organizational performance. This process underpins effective data management for retail organizations, supporting operational and strategic decision-making.
References
- Beaulieu, A. (2016). Database Systems: A Practical Approach to Design, Implementation, and Management. Cengage Learning.
- Database System Concepts. Pearson.