This assignment will give you practice creating database tables and creating queries. Imagine you are helping set up the database tables for a new Accounts Payable (AP) system. This individual database assignment you will be expected to: * Do additional verification queries by creating queries using the Access visual interface * Explain the SQL code generated by these additional queries * Make appropriate modifications to existing Create Table commands The skills needed to complete this assignment are introduced in the previous walkthrough.
If you try to complete the assignment without understanding what was done in the walkthrough you won’t learn as much and you will probably spend more time. Try to think through how you can complete each step before you begin. Look back to the walk through for related examples and explanations. This approach (think before you type) is much better for your learning. Jumping in without thinking will also, probably, take longer. Watch carefully for what is requested in the assignment: sometimes a typed answer, sometimes the query (that means SQL), sometimes a relationship chart, and sometimes data.
Need essay sample on Accounting Information System Project ?We will write a custom essay sample specifically for you for only $12.90/pageorder now
The following suggestions will help you complete the steps quickly: Try out the Windows snipping tool that way you can remove extra clutter from your figures, dedicating more of the space on the page to content. This is good because small fonts can make documentation hard to read. Show the SQL version of a query: * Create a query using Access’s design interface * Choose SQL view * Copy the selected text and paste it into the document Show results from a query: * Run a query * Click the triangle as shown in the picture * That selects the data, copy it (ctrl-c) Paste into word – It makes a table! Query1| Terms_Code_ID| Description| | Un-specified Terms| 210Net30| 2% discount within 10 Days Net 30 Days| Net10| Net 10 Days| Net30| Net 30 Days| ACTG 378 Individual Database Assignment162 Points Possible (but exactly 5% of the term grade) Required: Upload to blackboard a Microsoft Word document which includes your answers. You will NOT be turning in a database. Your work should be done on the provided database schema. Do not change the names of columns or tables. We may test your queries by putting them on a database with known errors.
It is all right to work together on this assignment BUT each person needs to do the actually work on their own database and Word document, and if you do work with someone else, note that at the end of your assignment, e. g. “I received help from Joe on questions 2 and 3,” or “Bob, Linda, and I worked together on the whole assignment. ” Download the AP_V2 database from Blackboard. It should look familiar. It is supposed to be just like the one from the walkthrough importing the tables. But I gave you a clean one in case you made any small errors. Part 1: Modifying create table scripts to track authorization rules.
Create a new query. Don’t select any tables. Go to SQL view, paste in the following code. Save the query, calling it ‘Create Users Table’. Run the query. CREATE TABLE Users([User_ID] AUTOINCREMENT primary key, [User_Name] text ); 1a) In one short sentence explain what this query does. (2 pts) Create and run queries for these two tables as well: CREATE TABLE Roles( [Role_ID] AUTOINCREMENT primary key, [Role_Name] text,[Role_Description] text); CREATE TABLE Users_In_Roles( [UIR_ID] AUTOINCREMENT primary key,[User_ID] integer,[Role_ID] integer, CONSTRAINT FK_UsersInRolesRoleId FOREIGN KEY (Role_ID) REFERENCES Roles (Role_ID) );
Create one more script. Call it ‘Drop User Tables’. Include the following code: DROP TABLE Users_In_Roles, Users, Roles; Practice by running each of the create scripts then running the drop script. All the tables have to exist for the drop script to work. Of course, you can always delete the tables using the Access interface. But, as with creation scripts, drop scripts help when procedures need to be automated. 1b) Explain why you can’t run the create script for Users_In_Roles unless the Roles Table exists. (3 pts) These scripts create tables to store data needed to implement role-based authorization.
Note, you are not setting up a program to use the data or add it; you are just making tables to store the data. 1c) Change the scripts to meet the additional AP system requirements A through D listed below. You don’t need any new tables. You just need to adjust the provided creation scripts. Name columns meaningfully, using the naming pattern discussed in class. When you are done, run the creation scripts in your database AND paste the SQL for all three creation scripts into your Word document. (20 pts) A. An additional foreign key is needed to connect the UsersInRolesTable to the Users Table. B.
You need a new field (type DateTime) to track when the user last accessed the system. C. You want to store the DateTime the user was authorized for a particular role and the DateTime their privileges were revoked. D. You want a place to store a count of how many users are authorized for each role. Part 2: Add appropriate foreign keys. Create and organize a relationship chart that includes all the tables in the database. Add all appropriate foreign keys. A relationship diagram showing all the tables in your database with all appropriate foreign keys added should be pasted into your Word document answer sheet (see how in the walkthrough).
Check Enforce Referential Integrity for your foreign keys. Hints: * To do the terms codes foreign key you will have to add a row to the Terms_Code Table. * You also need to add a primary key to at least one table before the appropriate foreign key can be specified. * If you want a refresher on the design patterns (category tables, intersection tables, 1:M) look at the last few slides in the slide deck ‘ACTG378_Designing_and_Making_Tables’. Some of these questions ask for written answers. Relationships will be checked for on the diagram for credit. a) The diagram should include the tables and foreign key specifications for the tables in Part 1. (3 pts) 2b)
The system includes two clear examples of the CATEGORY table pattern. List them. (4 pts – 1 each for identifying them, 1each for related relationship on the diagram) 2c) Explain why you could not add a foreign key with enforced referential integrity for the terms code table until you added a row of data in the categorization table? (5 pts) One sentence will do nicely! 2d) The system includes three clear examples of the INTERSECTION Table pattern. List them. 9 pts1 each for identifying them, 1each for related relationship on the diagram) 2e) The system includes several other one-to-many relationships (header detail or Object/Transaction relationships) which should be supported by foreign keys. List them. (10pts – 1 each for listing, +1 each for showing the relationship) 2f) Name at least one table for which a primary key was needed before adding the corresponding foreign key relationship. (2 pts) 2g) Arrange the diagram nicely, avoid crossing lines (you can’t help it, but avoid it), minimize the ribbon, hide the navigation panel, show all fields, etc… (5 pts)
Part 3: Create Queries. For each of the following queries, paste the SQL version of your query into the Word document. Also include your results. Don’t forget to think about how data should be sorted. None of the queries will result in more than 50 rows of data. If you can’t make it work right, partial credit is available, but don’t print out pages and pages of data please – let’s save a few trees. 3a) Make a list of Vendors – perhaps to populate a pull-down box on a screen. The user wants to select by company but wants to see the name of the contact as well to make the selection so show vendor name and contact. 5 pts) 3b) List the checks issued in November, beginning with the highest check number and ending with the lowest. In addition to the check numbers, amounts, and dates, show the name of the vendor and a count of how many invoices are being paid on this check. Include both the query and the result. (20pts) Hint: you can put the word ‘and’ between two criteria. 3c) Check the amounts in the check batches in the Batch Table. (20 points) Create a single query which ‘compares’ the check amounts in the Check Table against the subtotals in the Batch Table for corresponding check posting batches.
Include the batch type (not just the batch type code), the batch number, the batch date, the total amount from the batch, and the corresponding amount found in the Check Table. You should list ONLY batches where the batch total does not match the amounts found in the Check Table. Hint: this is a lot like a task we performed in the walkthrough. Part 4: Create Reports. 4) Now explore the project activity for each vendor in the system. This will be more complex for two reasons: items on an invoice can go to multiple projects and we don’t have an YTD invoice count store in the project table.
So here we will do it a bit at a time. To begin, let’s create the queries we need for this report: Make three queries and run them for project number 1. You can test using project 2 above, but run on project 1 for your answer sheet. Put both SQL and results in your answer sheet. 4a) Firstly, retrieve the project number, project name, description, and budget amount for the headings. (4 pts) (Return one row with the information in the headings. ) 4b) Then retrieve the values for amount-by-vendor details. Be sure the data will be presented in the right order. 12 pts)(One row for each vendor which has an amount, three columns in each row) 4c) Finally, retrieve the total amounts and invoices. (6 pts)(Just one row with two columns) NOTE: THIS IMAGE CHANGED ON FEB 14th TO CORRECT NUMBER ERRORS 5) The total budget for each project is allocated over the months when the project is active. The company wants to check that the total monthly allocations equal the total budgeted amount for each project. They only want the projects where the amounts are not equal. Include both your SQL and the results of your query in your answer sheet.
This query will need to be done it two parts: 5a) Firstly, retrieve the project number, the allocated budget amount, and the budget allocation id. Only include rows where amount is greater than zero. The results for this query for project 5 are shown here. Your query should NOT include a where clause to show only one project – you should get 33 rows in the result (12 pts). Query5a| Project_Num| Bud_Alloc_ID| Amt| 5| 113| $250. 00| 5| 114| $3,000. 00| 5| 115| $3,000. 00| 5| 116| $690. 00| 5b) Homestretch!!!! This will be a little more complicated, but it is similar to one of the reports in the PRJ2 walkthrough.
Use your query from 5a just as you would use a table to get to the final result. When you make a query, the ‘show table’ window has tables on one tab while queries are on another. Include the project table as well. Select the project number, the monthly budget allocation amount, the total project budget amount, and the budget allocation id. We want to compare the total of the monthly budget allocations to the total budget amount with a count of the periods with a budget amount. Make sure to only show rows where the amount is not equal.