Creating the Database
In order to construct the queries retrieving the commission of specific employees from sales department and the overall commission for December 2017, it is necessary to create the table in the database and to populate it with data. The following code creates a populates a database that corresponds to the model of a high-end electronics store.
CREATE TABLE product
( ProductNumber number(10) NOT NULL,
ProductDescription varchar2(500) NOT NULL,
ProductCost number(6,2) NOT NULL,
CONSTRAINT product_pk PRIMARY KEY (ProductNumber)
);
CREATE TABLE department
( DepartmentID number(10) NOT NULL,
DepartmentDescription varchar2(500) NOT NULL,
CONSTRAINT department_pk PRIMARY KEY (DepartmentID)
);
CREATE TABLE job
( JobID number(10) NOT NULL,
JobDescription varchar2(100) NOT NULL,
CONSTRAINT job_pk PRIMARY KEY (JobID)
);
CREATE TABLE employee
( EmpNumber number(10) NOT NULL,
EmpFirstName varchar2(50) NOT NULL,
EmpLastName varchar2(50) NOT NULL,
CommissionRate number(4,2) NOT NULL,
YrlySalary number(10) NOT NULL,
DepartmentID number(10) NOT NULL,
JobID number(10) NOT NULL,
CONSTRAINT employee_pk PRIMARY KEY (EmpNumber),
CONSTRAINT fk_department
FOREIGN KEY (DepartmentID)
REFERENCES department(DepartmentID),
CONSTRAINT fk_job
FOREIGN KEY (JobID)
REFERENCES job(JobID)
);
CREATE TABLE invoice
( InvNumber number(10) NOT NULL,
InvDate date NOT NULL,
EmpNumber number(10) NOT NULL,
InvAmount number(10,2) NOT NULL,
CONSTRAINT invoice_pk PRIMARY KEY (InvNumber),
CONSTRAINT fk_employee
FOREIGN KEY (EmpNumber)
REFERENCES employee(EmpNumber)
);
CREATE TABLE invoiceline
( InvLineNumber number(10) NOT NULL,
InvNumber number(10) NOT NULL,
ProductNumber number(10) NOT NULL,
Quantity number(6) NOT NULL,
CONSTRAINT invoiceline_pk PRIMARY KEY (InvLineNumber),
CONSTRAINT fk_invoice
FOREIGN KEY (InvNumber)
REFERENCES invoice(InvNumber),
CONSTRAINT fk_product
FOREIGN KEY (ProductNumber)
REFERENCES product(ProductNumber)
);
INSERT ALL
INTO job (JobID, JobDescription) VALUES (1, ‘Full-time’)
INTO job (JobID, JobDescription) VALUES (2, ‘Part-time’)
SELECT * FROM dual;
INSERT ALL
INTO product (ProductNumber, ProductDescription, ProductCost) VALUES (1, ‘Fanta 1 Pack’, 6.5)
INTO product (ProductNumber, ProductDescription, ProductCost) VALUES (2, ‘Sprite 1 Pack’, 5.5)
INTO product (ProductNumber, ProductDescription, ProductCost) VALUES (3, ‘Pepsi 1 Pack’, 6)
SELECT * FROM dual;
INSERT ALL
INTO department (DepartmentID, DepartmentDescription) VALUES (1, ‘Sales’)
INTO department (DepartmentID, DepartmentDescription) VALUES (2, ‘Finance’)
INTO department (DepartmentID, DepartmentDescription) VALUES (3, ‘Human Resources’)
SELECT * FROM dual;
INSERT ALL
INTO employee (EmpNumber, EmpFirstName, EmpLastName, CommissionRate, YrlySalary, DepartmentID, JobID) VALUES (1, ‘John’, ‘Smith’, 0.4, 65000, 1, 2)
INTO employee (EmpNumber, EmpFirstName, EmpLastName, CommissionRate, YrlySalary, DepartmentID, JobID) VALUES (2, ‘Mary’, ‘Sanders’, 0.7, 70000, 1, 1)
INTO employee (EmpNumber, EmpFirstName, EmpLastName, CommissionRate, YrlySalary, DepartmentID, JobID) VALUES (3, ‘Peter’, ‘Chambers’, 0, 85000, 2, 1)
SELECT * FROM dual;
INSERT ALL
INTO invoice (InvNumber, InvDate, EmpNumber, InvAmount) VALUES (1, ’13-Nov-17′, 2, 97.5)
INTO invoice (InvNumber, InvDate, EmpNumber, InvAmount) VALUES (2, ’01-Dec-17′, 1, 160)
INTO invoice (InvNumber, InvDate, EmpNumber, InvAmount) VALUES (3, ’12-Dec-17′, 1, 327.5)
INTO invoice (InvNumber, InvDate, EmpNumber, InvAmount) VALUES (4, ’11-Dec-17′, 2, 25.5)
INTO invoice (InvNumber, InvDate, EmpNumber, InvAmount) VALUES (5, ’20-Dec-17′, 2, 275)
INTO invoice (InvNumber, InvDate, EmpNumber, InvAmount) VALUES (6, ’17-Dec-17′, 1, 110)
SELECT * FROM dual;
INSERT ALL
INTO invoiceline (InvLineNumber, InvNumber, ProductNumber, Quantity) VALUES (1, 1, 1, 15)
INTO invoiceline (InvLineNumber, InvNumber, ProductNumber, Quantity) VALUES (2, 2, 1, 20)
INTO invoiceline (InvLineNumber, InvNumber, ProductNumber, Quantity) VALUES (3, 2, 3, 5)
INTO invoiceline (InvLineNumber, InvNumber, ProductNumber, Quantity) VALUES (4, 3, 2, 10)
INTO invoiceline (InvLineNumber, InvNumber, ProductNumber, Quantity) VALUES (5, 3, 1, 5)
INTO invoiceline (InvLineNumber, InvNumber, ProductNumber, Quantity) VALUES (6, 3, 3, 40)
INTO invoiceline (InvLineNumber, InvNumber, ProductNumber, Quantity) VALUES (7, 4, 1, 3)
INTO invoiceline (InvLineNumber, InvNumber, ProductNumber, Quantity) VALUES (8, 4, 3, 1)
INTO invoiceline (InvLineNumber, InvNumber, ProductNumber, Quantity) VALUES (9, 5, 2, 50)
INTO invoiceline (InvLineNumber, InvNumber, ProductNumber, Quantity) VALUES (10, 6, 1, 10)
INTO invoiceline (InvLineNumber, InvNumber, ProductNumber, Quantity) VALUES (11, 6, 2, 10)
SELECT * FROM dual;
2. Queries
Based on the database model and on the created implementation, the query that will allow the finance department to determine the commissions paid to specific employees of the sales department for the month of December is the following:
SELECT department.DepartmentDescription, employee.EmpFirstName, employee.EmpLastName, COALESCE(SUM(TotalInvUnits*CommissionRate),0) as DecemberCommission from employee
LEFT JOIN (
SELECT invoice.InvNumber, invoice.InvDate, invoice.EmpNumber as invoiceEmpNumber, TotalInvUnits FROM invoice
LEFT JOIN (
SELECT InvNumber as invoiceNumber, SUM(Quantity) AS TotalInvUnits FROM invoiceline
GROUP BY invoiceline.InvNumber
) ON invoice.InvNumber = invoiceNumber
WHERE invoice.InvDate >= ‘1-Dec-2017′ AND invoice.InvDate <= ’31-Dec-2017’
) ON employee.EmpNumber = invoiceEmpNumber
LEFT JOIN department ON employee.DepartmentID = department.DepartmentID
GROUP BY DepartmentDescription, employee.EmpFirstName, employee.EmpLastName
HAVING DepartmentDescription = ‘Sales’;
The query that would show how much total compensation is paid to each employee for the same month:
SELECT department.DepartmentDescription, employee.EmpFirstName, employee.EmpLastName, (COALESCE(SUM(TotalInvUnits*CommissionRate),0) + employee.YrlySalary/12) as DecemberCompensation from employee
LEFT JOIN (
SELECT invoice.InvNumber, invoice.InvDate, invoice.EmpNumber as invoiceEmpNumber, TotalInvUnits FROM invoice
LEFT JOIN (
SELECT InvNumber as invoiceNumber, SUM(Quantity) AS TotalInvUnits FROM invoiceline
GROUP BY invoiceline.InvNumber
) ON invoice.InvNumber = invoiceNumber
WHERE invoice.InvDate >= ‘1-Dec-2017′ AND invoice.InvDate <= ’31-Dec-2017’
) ON employee.EmpNumber = invoiceEmpNumber
LEFT JOIN department ON employee.DepartmentID = department.DepartmentID
GROUP BY DepartmentDescription, employee.EmpFirstName, employee.EmpLastName, employee.YrlySalary
HAVING DepartmentDescription = ‘Sales’;
The difference in the second query is the following: it takes the value of each sales employee’s commission, adds the part of the salary that should be paid in December (which equals to annual salary divided by 12) and displays the results as DecemberCompensation column.
3. Referential Integrity
In order to ensure referential integrity, it is necessary to ensure that all references are valid, that they are associated with correct data points and meet the model constraints as defined in the database design. Referential integrity in a database is maintained using a series of rules that cover three major cases: adding new records, updating existing records and deleting records (Powell, 2006).
When new records are added, the system should validate the associations with other tables or fields and should not complete the transaction if some of the links are not valid. When some records are deleted, the system should have rules that perform cascade deletion of related records (Fong, 2015). For example, if an employee is deleted from database, all his invoices and invoiceLine records should also be removed. When the records are updated, the system should have specific rules that ensure cascade update of the related records in other tables.
4. Database Model
Figure 1 shows the model of the database with primary and foreign keys, constraints and relationships between data. It should be noted that all fields in the tables are listed as non-nullable in order to keep referential integrity and data consistency. The relationships existing in the model are all one-to-many, where the “parent” table represents has the 0..1 constraint, and the “child” table in the relationship has 1..* constraint.
Figure 1. Database Model
References
Fong, J. (2015). Information Systems Reengineering, Integration and Normalization. Springer.
Powell, G. (2006). Beginning Database Design. John Wiley & Sons.
The terms offer and acceptance. (2016, May 17). Retrieved from
"The terms offer and acceptance." freeessays.club, 17 May 2016.
freeessays.club (2016) The terms offer and acceptance [Online].Available at:
"The terms offer and acceptance." freeessays.club, 17 May 2016
"The terms offer and acceptance." freeessays.club, 17 May 2016
"The terms offer and acceptance." freeessays.club, 17 May 2016
"The terms offer and acceptance." freeessays.club, 17 May 2016