382745 SQL Accounting Database

SQL Accounting Database Description of Request: Design an SQL database that contains the fields found in the company's Chart of Accounts. Add a balance field to the database. Create a query that will display all of the fields of the database and run a report totaling the balance field using test data added to the database. Background of Request: Kudler's Chart of Accounts currently exists as a Microsoft Excel spreadsheet. In anticipation of new reports that management will be requesting, we want to move the Chart of Accounts from the current spreadsheet to a SQL database. We want to create the database in order to facilitate decision making at the store and department levels. Expected Results/Impact when completed: An SQL database containing the fields in the current Chart of Accounts, plus a "balance" field. An SQL query that will display all of the database fields. A test of the database by means of a query by account number and a report totaling the balance field test data entered by the database design team. The query will display all fields (description, short description, and balance) using the account number as the key to the query. The report will display all fields with a break based on the first two digits of the account number and subtotal of the balance field at each break. A grand total of the balance field will be provided at the end of the report. The database design team will load sufficient entries into the balance field to prove their total routine is working - test balance entries are not necessary for all account numbers. Please create each step in different file: Stage 1: Create the following two tables using the following fields: Note: Supply the SQL Server data types when creating the tables. In the Employee table, the Employee ID field should generate a unique number for each employee record and designate the field as the Primary Key. In the Job Title table, a suitable field to use as a primary key is not present. You will need to create an additional field to use as the primary key that will generate a unique number for each job title record. The primary key from the Job Title table will appear as the foreign key in the Employee table. • Employee  Employee_ID  Last_name  First_name  Address  City  State  Telephone_area_code  Telephone_number  EEO-1 Classification  Hire_date  Salary  Gender  Race  Age  Job_Title_ID • Job_title  EEO-1 Classification  Job_title  Job description  Exempt / Non-Exempt Status Using the SQL INSERT statement: • Go to the Human Resources department in the Kudler Fine Foods intranet. Using information found in the Employee Files and the Job Classification information for the La Jolla and Encinitas stores, enter records into the employee table for the following employees:  Glenn Edelman  Eric McMullen  Raj Slentz  Erin Broun  Donald Carpenter  David Esquivez  Nancy Sharp • Using the Kudler Fine Foods Job Classifications and Job Descriptions information, enter records into the job_title table for the following job titles:  Accounting Clerk  Asst. Manager  Bagger  Cashier  Computer Support Specialist  Director of Finance & Accounting  Retail Asst. Bakery & Pastry  Retail Asst. Butchers and Seafood Specialists  Stocker Non-Exempt employees at Kudler Fine Foods are paid by hour wage and are required to track their working hours. Check the results by selecting all of the columns from both of your tables. Stage 2: data. Your document should include screen shots of the SQL tab from SQL Server Management Studio for each SQL statement. Using the database and tables from Week Two, incorporate any recommended changes by the instructor and write SQL statements and enter the records into the Employee table for the workers identified in the Employee Files for the administrative offices and the Del Mar location. Check the results by selecting all of the columns from both of your tables. Write SQL queries using Between, and Like: • Write a SQL query that joins two tables in the example database and uses BETWEEN to restrict record selection. (Use salary to restrict the data.) • Write a SQL query that joins two tables in the example database and uses BETWEEN to restrict record selection. (Use hire dates to restrict the data.) • Write a SQL query that joins two tables in the example database and uses LIKE to restrict record selection. (Use telephone area codes to restrict data.) • Write a SQL query that joins two tables in the example database and uses LIKE to restrict record selection. (Use age to restrict data.) Using the updated database, write the following queries using the SQL GROUP statement: • Group employees by job classification: Select the employees' last names and group them by EEO-1 Classification. • Group employees by salary: Select the employees' last names and group them by salary. • Group employees by salary within their job classification: Select the employees' last names and group them by salary within their EEO-1 Classification. • Select the employees' last names and group them by salary within job titles that are grouped into exempt and non-exempt. Submit your database by creating a backup of your database and posting the “.bak” file generated by SQL Server Stage 3: Using the database and tables from Week Three, incorporate any recommended changes by the instructor and write SQL statements. Note: Create a backup of your database before running queries that modify data. • Choose an EEO-1 Classification: Increase all employees' salaries that have: the selected EEO-1 classification by 10%. • Increase all employees' salaries by 5%. • Choose an employee from the employee table; delete this employee. Note: Restore your database before running the following queries. Using the database and tables from Week Three, write SQL statements to: • Calculate the average salary for all employees. • Calculate the maximum salary for exempt employees and the maximum salary for non-exempt employees. • Calculate the maximum salary for all employees. • Calculate the minimum salary for exempt employees and the minimum salary for non-exempt employees. • Calculate the minimum salary for all employees. Submit your database by creating a backup of your database and posting the “.bak” file generated by SQL Server Please create a backup of the database “.bak” file generated by SQL Server Information: 1. Analyze the current Chart of Accounts and create an entity relationship diagram of the database that your team will create based on the chart. Remember, a database consists of multiple tables. Your team will also develop a project plan defining all tasks and work assignments for each team member. Post your ERD and project plan in a single MS Word document. Please note that the ERD must be created using a drawing tool such as MS Visio, but copy and pasted into the MS Word document. 2. Create the tables based on your design and feedback from the instructor in a SQL Server database. Prepare test data and load it into the SQL Server database. Load data from the Chart of Accounts making sure to only store the data into the relevant tables. Every tables needs to have data added to it with several rows of data per table. In addition to posting your database, post a MS Word document to the individual forum of the student designated as the “team poster” for this week. This MS Word document should include the SQL code used to create your database and tables as well as load the data. Your document should include screen shots of the SQL tab from SQL Server Management Studio for each SQL statement. 3. Create and test an account number query. This query should retrieve account data from multiple tables using joins to gain meaningful information from the data. In addition to posting your database, post a MS Word document to the individual forum of the student designated as the “team poster” for this week which includes the SQL code used in the SQL statements above. Your document should include screen shots of the SQL tab from SQL Server Management Studio for each SQL statement. 4. Create and test SQL database Chart of Accounts report. The report should organize the data in a manner that is presentable to an individual at the management level: fields should have unambiguous names, data should be grouped and sorted, subtotals should be included, a clean looking font should be used, etc. The report should look aesthetically pleasing. In addition to posting your database, post a MS Word document to the individual forum of the student designated as the “team poster” for this week which includes the SQL code used to populate the report as well as an export of the report from Business Intelligence Development Studio. Adding the SQL code to the exported MS Word document of the report will be the best way to post this assignment.