Assignment: Use SQL Query Language
You may use any Database Management System (DBMS) for this assignment. This week's resource list provides you with some e-books with information specific to your DBMS of choice to use for reference.
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:
Employee's 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.
Format your assignment according to the following formatting requirements:
1. The answer should be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides.
2. The response also include a cover page containing the title of the assignment, the student's name, the course title, and the date. The cover page is not included in the required page length.
3. Also Include a reference page. The Citations and references should follow APA format. The reference page is not included in the required page length.