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. 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!
| 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...