Question
Imagine that you have been contracted to develop a system for processing data for an organization that sells household items and electronics. Just like any
Imagine that you have been contracted to develop a system for processing data for an organization that sells household items and electronics. Just like any organization, there are departments such as IT, customer service, marketing, sales, payroll, accounts payable, and some sort of operations. Within each department, employees will have specific jobs (supervisor, administrative assistant, janitor). All employees are paid a salary and benefits. The only exception is the sales department where employees are paid a commission as well. To calculate commission, this is the formula:
Employees commission rate = product commission amount X quantity sold.
For this assignment, you will need to create the entities and attributes via SQL queries. In a Word document, submit the following:
DDL statements targeted towards the DBMS of your choice (Microsoft SQL Server, Oracle DB, or MySQL) to create the database, entities, indexes, and dependencies to solve the problem described above. You will need at least five entities with attributes for your solution. Write the DDL statements on your own and do not use any graphical or automatic coding tools.
Discuss the factors needed to ensure referential integrity.
A screenshot or screenshots of your DDL code having executed properly in your DBMS
DML statements to add at least five records per table
A screenshot or screenshots of your insert DML statements having executed properly in your DBMS
DML statements to update at least one record in each table (at least 5 statements total)
A screenshot or screenshots of your update DML statements having executed properly in your DBMS
Select statements to retrieve all rows and all columns from each table (at least 5 statements total)
Screenshots of the resulting output of your select statements (at least 5 screenshots total)
A SQL query for the sales department to determine the commissions paid to specific employees for the month of December
A screenshot of the resulting output of your sales report query
Length: All required code and screenshots
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started