In order to get the commission for each employ we first have to generate a database and its tables. CREATE TABLE Employee( EmpNumber int not null, EmpFirstName varchar(100) not null, EmpLastName varchar(100) not null, CommissionRate decimal(3,2) not null, YrlySalary int not null, DepartmentID int, JobID int, PRIMARY KEY (EmpNumber), FOREIGN KEY (DepartmentID) references Department(DepartmentID), FOREIGN KEY (JobID) references Job(JobID) ).
CREATE TABLE Job( JobID int not null, JobDescription varchar(400) not null, PRIMARY KEY (JobID) )
CREATE TABLE Department( DepartmentID int not null, DepartmentDescription varchar(400) not null, PRIMARY KEY(DepartmentID) )
CREATE TABLE Invoice (InvNumber int not null, InvDate date, EmpNumber int, InvAmount int not null, PRIMARY KEY (InvNumber), FOREIGN KEY (EmpNumber) references Employee(EmpNumber)).
CREATE TABLE InvoiceLine( InvLineNumber int not null, InvNumber int, ProductNumber int, Quantity int not null, PRIMARY KEY (InvLineNumber), FOREIGN KEY (InvNumber) references Invoice(InvNumber), FOREIGN KEY (ProductNumber) references Product(ProductNumber) )
CREATE TABLE Product (ProductNumber int not null, ProductDescription varchar(400) not null, ProductCost int not null, PRIMARY KEY (ProductNumber) )
We then have to use inner join in order to combine more than two tables so as to find the commission paid to specific sale employees. The tables that we will join include InvoiceLine, Invoice and Employee.
INNER JOIN Employee
ON Invoice.EmpNumber=Employee.EmpNumber;
Total compensation is the yearly salary that the employees are paid in the company. Below is the code for getting total compensation:
use electronics
In SQL, referential integrity refers to a foreign key found in any referencing table in a database. It also refers to a valid row in a referenced table. Referential integrity makes sure that existence between referenced tables is intact during the activities such as deletes or updates. To ensure referential integrity holds, you must ensure that a primary key of one table must exist in another table within the same database. For example, an Employee table with a primary key EmpID, and a Salary table with a primary Key SalID, for employee table to link to salary table primary key EmpID must exist in salary table. Foreign keys are keys that ensure referential integrity, for example, the EmpID I have described is a foreign key in salary table.
Referential integrity is standard and it means that for any EmpNumber value in Employee table might not be modified without editing a corresponding value in Department table. For instance, if John Doe’s EmpNumber is altered in the Employee table, then the change made also reflects to the Department table, hence allowing John Doe’s Department information to connect with his EmpNumber.
In relational database, we have relationships between database tables that maintain referential integrity. In our database, we have many relationships namely, 1 to many, many to one, and many to many relationships. Employees table has a many to one relationship with job table, many employees can have same job. Still on the same note, employee table is related to department table having many to one relationship. This means that many employees can be in one department. Employee table is related invoice table having one to many relationship. A single employee can have more than one invoice.
An entity refers to an object in the database structure which one need to model and then store information based on it. Entities are normally recognizable conceptions, both concrete and abstract, for example places, person, events, or things that have significance to a database. Some of the precise examples of entities include Employee, Lecturer, and Doctor.
In general, when it comes to the term attribute, we refer to it as a characteristic. An attribute in a database management system specifically denotes the component of the database, for instance, a table. In other instances, attributes may also denote a field in the database and it is good to note that attribute represent the values in a row of a database.
Entities in the above database are Employee, Job, Department, Invoice, InvoiceLine and product. Attributes include EmpNumber, EmpFirstName, EmpLastName, CommissionRate, YrlySalary, DepartmentID, JobID for Employee table. DepartmentID and JobID are the foreign Keys that maintain referential integrity in Job and Department tables jointly with Employee table. JobID, JobDescription for Job Table. DepartmentID, DepartmentDescription for department table. InvNumber, InvDate, EmpNumber, InvAmount for Invoice table, here we only have one foreign key which is EmpNumber referencing the Employee table. InvLineNumber, InvNumber, ProductNumber, Quantity for InvoiceLine table. In InvoiceLine table, the ProductNumber is the foreign key referencing Product table. ProductNumber, ProductDescription, ProductCost for product table.
Big Data refers to a data set which are large and complex for traditional data execution. The old data processing methods cannot handle or are inadequate to deal with such data Zikopoulos, Parasuraman, Deutsc , Giles, & Corrigan, 2012).
Data analytics, on the other hand, refers to a state in which the rule for big data and analytics are accessible to the organization by all the departments which require them. Because of the primary infrastructure and data streams, user tool sets are required to search valuable insights and come up with better decisions to solve real business problems. This is how the big data is supposed to work and majority of big firms employs this criteria.
The challenges faced during such data processing include, data curation, capture, information privacy, transfer, sharing, storage, analysis and visualization. The adoption of the big data technology in the organization helps in efficiency when it comes to innovation, cost and productivity (Zikopoulos et al., 2012). In addition, big data may be considered as an energy boost to performance of the organization. It is very efficient and effective in organizing various data . All the organization needs to do is to assemble the right components which include data source, data platforms and discovery platforms to enable capture plus data management.
Big data ideologies when applied in concepts of machine intelligence and perfect computer programming, the organization’s IT department is able to forecast potential problems and strive to give solutions early enough before even the issues forecasted occur. Big data integration is another major variable that will greatly help the organization deal with large customer data. The need for dependable storage plus tough data management, thus making data experts and data surveyors review and perfect it when need arises.
Reference
Zikopoulos, P., Parasuraman, K., Deutsch, T., Giles, J., & Corrigan, D. (2012). Harness the power of big data The IBM big data platform. McGraw Hill Professional.
