Database Systems & Database Models Research Paper

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

[Accessed: March 28, 2024]

"The terms offer and acceptance." freeessays.club, 17 May 2016.

[Accessed: March 28, 2024]

freeessays.club (2016) The terms offer and acceptance [Online].
Available at:

[Accessed: March 28, 2024]

"The terms offer and acceptance." freeessays.club, 17 May 2016

[Accessed: March 28, 2024]

"The terms offer and acceptance." freeessays.club, 17 May 2016

[Accessed: March 28, 2024]

"The terms offer and acceptance." freeessays.club, 17 May 2016

[Accessed: March 28, 2024]

"The terms offer and acceptance." freeessays.club, 17 May 2016

[Accessed: March 28, 2024]
close
Haven't found the right essay?
Get an expert to write you the one you need!
print

Professional writers and researchers

quotes

Sources and citation are provided

clock

3 hour delivery

person