Database Systems and Database Models

21 Aug No Comments

Assignment 2: Database Systems and Database Models

CIS 515

Running Head: Database Systems and Database Models

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. 

First of all, the tables were generated in Strayer Oracle database. The tables were filled were filled using “insert” command. Then, the following query was created to determine commissions paid to sales department employees for the month of December.

SQL> select e.empnumber,e.emplastname,e.empfirstname,i.invnumber,i.invdate,((e.commissionrate/100)*i.invamount)as “Commission Paid”

2 from employee e, invoice i

3 where e.empnumber=i.empnumber

4 and i.invdate>=’01-Dec-2017′

5 and i.invdate<='31-Dec-2017';

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.

The query below showed how much total compensation was paid to sales employee for the month of December.

SQL> select e.empnumber,e.emplastname,e.empfirstname,((e.yrlysalary/12)+(e.commissionrate/100)*i.invamount)as “Dec Compensation”

2 from employee e, invoice i

3 where e.empnumber=i.empnumber

4 and i.invdate>=’01-Dec-2017′

5 and i.invdate<='31-Dec-2017';

3. Determine and explain the factors necessary to ensure referential integrity.

The factors necessary to ensure referential integrity are:

– a value in primary key of primary table must be matched to the value in the foreign key of related table

Running Head: Database Systems and Database Models

– value of a primary key, present as foreign key in related table, cannot be changed if their records match

– records from parent table cannot be deleted if corresponding records exist in child table

4. Create an object-oriented model to show how the tables are interrelated through the use of graphical tools. Make sure that you are able to show the relationship types such as 1:M, 1:1, or M:1. 

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.

In the above diagram, the entities are Employee, Invoice, Department, Job, and Invoiceline; whereas the attributes are EmpNumber, EmpFirstName, EmpLastName, DepartmentID, JobID, etc.

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

An organization’s well being is dependent on its decision-making abilities. The quality and accuracy of decision-making is reliant on the type of data that organizations are able to acquire. Especially, in today’s digital age, unlimited amount of data is produced in websites. Big Data helps to collect and process large volume of both- structured and unstructured- data at a high speed from a variety of different sources. It processes incredibly complex processing with relative ease. The

Running Head: Database Systems and Database Models

raw data is refined to fit in a format that can be used as business intel to assist in productivity and forecasting of the organization’s products and resources.

Running Head: Database Systems and Database Models

References

Coronel, C., Morris, S., and Rob, P. (2015, 2017). Database Systems (12th ed.). Boston, MA: Cengage.




Click following link to download this document

CIS 515 Assignment 2 - Database Systems and Database Models.docx