Week 3: Use SQL Query Language

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

Week 4: Identify Entity Relationships

For this assignment, you will need to use a graphical tool such as Microsoft Visio or DIA which is an open source, free alternative. In a Word document, provide the following:

Referring to the tables that you developed in Week 3, create an object-oriented model showing the relationships between the tables. You will also need to show the relationship types such as 1:M, 1:1, M:M, or M:1. Be sure to include the determined factors from the previous assignment requirement.

Using your chosen diagramming tool, identify which data components are the entities and attributes. Demonstrate the relationship between each using an object representation diagram.

Determine the normal form of your database design. Justify your choice with evidence from your design and the definitions of the normal forms.

If your design is not in at least third normal form, revise it so it is in at least third normal form. Provide the revised design in addition to the original. Justify why the new design is now in at least third normal form with evidence from the design and the definitions of the normal forms.

Provide five specific examples of data anomalies (including at least one insert, update, and delete anomaly each) that might happen if your database were not in at least third normal form. Provide sample non-normalized data and tables, the action taken, and the resulting anomalous data. Explain why your design avoids each possible problem.