CMS Project Phase II Instructions

CMS Project Phase II Instructions in This Phase You Will Create Table

In this phase, you will create tables based upon the ERD and SQL code provided. You will then populate each table with the provided data. Finally, you will create queries to support reports for Accounting and Management, including screenshots for documentation. The process involves creating a new database, executing CREATE TABLE statements in the specified order, inserting the data into these tables, and then writing several SQL queries that fulfill specific reporting requirements. The queries include reporting on employee details, invoicing, benefits, project overages, and employee overtime logging. You will need to produce screenshots of the table data and query results, include them in a Word document, and adhere to specific formatting instructions, including naming conventions.

Paper For Above instruction

Creating a comprehensive database for CMS involves establishing structured tables based on an ERD, populating them with representative data, and producing key reports that support various departmental functions. This process requires careful execution of SQL commands to ensure data integrity and facilitate accurate reporting. The task begins with setting up the database and creating tables in a specific order to respect foreign key constraints, followed by inserting the provided data into the tables. Once the database is populated, tailored SQL queries must be constructed to generate reports for Human Resources, Invoicing, Benefits Tracking, Project Management, and Payroll compliance. Each report addresses distinct informational needs, such as employee listings, billing details, benefit utilization, project overages, and overtime hours logged, respectively.

For example, the HR report requires a list of all employees sorted by region, country, last name, title, and salary. The invoicing report involves aggregating project hours, employee rates, and client information for a specific month. The benefits report must summarize benefit days and holidays for employees within a calendar year, showing allotted, taken, and remaining days. Management overage reports focus on projects exceeding maximum allowed hours, providing project names, total and overage hours, and employee contributions. The payroll report identifies employees who have logged hours exceeding their weekly contractual hours, highlighting potential overtime wages. Each report necessitates intricate joins, aggregations, and filtering to meet the specified criteria.

Screenshots of the data and query results must accompany the work, illustrating successful data retrievals. These outputs should be embedded in a Word document, following strict labeling and naming conventions, such as "Phase II CMS Project – [last name][first initial]." The entire process demonstrates proficiency in SQL Server, data management, and report generation, essential for supporting operational decision-making in CMS.

References

• Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
• Silberschatz, A., Korth, H. F., & Sudarshan, S. (2019). Database System Concepts (7th ed.). McGraw-Hill Education.