Access

Java Python Access Assignment

Total Points: 50

General Information:

· All work should be completed, formatted correctly, appropriately labeled and laid out.

· When you open any access file, a Security Warning will appear below the ribbon saying “Certain content in the database has been disabled”. Be sure to click on the Options button and click on the button “Enable this content”. 

1) Academic Software

Launched in 2005 in Boston, Massachusetts, Academic Software has consistently been the fastest-growing, education-focused software retailer in North America. It is committed exclusively to academic customers, offering thousands of full-version software titles at great discounts. Academic Software has partnered with the top technology manufacturers, including Adobe, Microsoft, Sibelius, Sony Media Software, and Wacom, to bring excellent service and prices, which are available only to students, schools, and teachers.

From the very beginning, Academic Software has relied heavily on technology to ensure a positive shopping experience for its customers. The company’s philosophy is simple: Hire amazing people, give them the best tools, and help them deliver an unbeatable customer experience.

One facet of Academic Software’s business that needs assistance is its database organization. You have been asked to assist Academic Software with creating a relational database structure for organizing software, vendors, and academic categories. Currently this information is stored in an Excel spreadsheet, AcademicSoftware_Data New.xlsx, which Academic Software has provided to you. While Excel is a useful tool to perform. certain kinds of analyses, you realize that Access is a better tool to compile some of the aggregated information Academic Software is looking for. Complete the following:

1. Create a Microsoft Access database titled AcademicSoftware.accdb (Make sure you create this file in the location you want to save it in e.g. in a folder under My Documents on the C: drive).

2. Import all the data from the AcademicSoftware Excel file into a table in this new database. Rename the table (Right click on the table>>Rename) Software. Copy this table (Right click on the table>>Copy) and paste it in the white space below the other table. Name this table Category. Copy the Category table and paste it – name this third table Vendor.
You now have three tables called Vendor, Category, and Software in the new database.  For each of these tables, modify the fields in each table based on the information provided below. Please note that you will need to delete certain rows and/or columns to do this. For example, the Category table should only have two fields: Category ID and Software Category. You should therefore first delete all the other columns. Now notice that there are only three unique categories in the list: 1 for Math, 2 for Science, and 3 for English. You should therefore first delete all the other rows.

Follow the same process to correctly set up the other two tables. Choose appropriate data types and primary key(s) for the tables. As part of the database design, you may need to add fields to some tables e.g. a Vendor ID to the Vendor table to create a primary key for this table as one is not provided. Each table should have fields related to the specific content of that table as indicated below:

a. Vendor: Vendor ID, Vendor Name, Vendor Address, Vendor City, Vendor State, Vendor Zip, Vendor Phone

b. Category: Category ID, Software Category

c. Software: Software ID, Software Name, Software Price, Category ID, Vendor ID

(Note that Category ID and Vendor ID represent foreign keys in this table).

3. Create the appropriate relationship(s) between the tables. As you review the data in the tables you’ve created, you should think about how these tables are related to each other. Consider the following in establishing the relationships between the tables:

a. One Category can have multiple types of software, but each type of software only belongs to one category.

b. Similarly, one Vendor can provide multiple types of software, but each type of software is only provided by one vendor. 

Note: When creating relationships, all other objects (tables etc) and windows should be closed. Only the relationship window should be open. Also, make sure you Enforce Referential Integrity (check the box) when creating the relationships.

4. The purchasing manager at Academic Software wants to compare the prices of software under the science category. Create a select query called Science Software to include the software category, software name, software price, vendor name, and vendor’s phone. Sort the query in descending order by price.

5. Recent marketing surveys have indicated that customers are reluctant to pay over $40.00 for education software. Create a select query to identify software over $40.00. Name the query Software Over $40. Include the software category, software name and price, as well as the vendor name, vendor’s city, the vendor’s state and vendor’s phone.

2) Our Students

ABC University has a large database of students in a file called Our Students.mdb. They need someone to manage this database and create various queries on an on-going basis.  Given your database management skills, the university has hired you to perform. the tasks listed below. All queries should be named as specified and saved. Use the Students table for the steps below.

1. Create a select query for undecided majors with over 30 credits. Include the last name, first name, phone, major, and credit fields. Call this query “Undecided Majors With Over 30 Credits”.

2. Create a select query called GPA by Major. Include the student’s major, last name, first name, gender, financial aid status, quality points, credits, and a calculation to determine each student’s GPA (create a new calculated field called GPA in this query).

Note: To calculate the GPA, use the following in the new column GPA:[QualityPoints]/[credits]. Make sure you format the GPA to two de Access Assignment cimal places. You can do so by right-clicking on the GPA field (in Design View) and select Properties. This opens the Property Sheet. Change the Format property to Standard and the Decimal Places property to 2 (make sure the General tab is selected to see these properties).

3. Create a query for students on the academic probation (GPA<=2.00). Include the student’s name, major, qualitypoints, credits, and GPA. Call this query “Academic Probation”. Hint: Instead of creating a new query, you can modify the GPA by Major query and save it under the new name.

4. Create a parameter query to identify students in any specific major. Learn something new: Set up the new query as you normally would in Design View. Instead of typing a specific major as the criteria in that column, type [Enter Major:]. Be sure to include the square brackets. Run your query. You should see a prompt asking you to enter a major. Type a major and you will see the results for that major. What is the advantage of this? Instead of creating and saving separate queries for each major, you can save this one parameter query and specify the major for which you want information on the fly when you run the query.

5. Create a query to identify any student with a 55 sequence (in any position) of their postal code. Hint: use the wildcard character to specify the criteria together with the 55!

3) WasteNot Recycling

WasteNot Recycling picks up recyclables from homeowners in Boulder, Colorado. The owners of WasteNot Recycling have asked you to assist with creating a form. and several reports. They have provided you with an updated database file, WasteNotRecycling_Data.mdb. Specifically they want you to do the following:

1. Create a form. that will allow the owners to enter data into the Customer and the Customer Record tables. The easiest way to do this is to click on the Customer table to select it (don’t open it), and under the Create tab, in Forms, click on Form. Notice that the form. Access creates includes data from both the Customer and Customer Record tables. Make any adjustments if needed to the controls so that all the data is clearly visible on your form. Save the form. as Customers.

Think about this. Why did it connect both tables? If you check the relationships in this database (Database Tools, Relationships), you will see that there is a one-to-many relationship between the Customer and Customer Record tables and this underlying relationship is what Access used in setting up the form.

2. Create a report called Customer Weights that groups the records by customer. The report should include data on the customer first and last name (from the Customer table), and service date of pickup, weight of paper, and weight of other (from the Customer Record table).

In creating the report, view your data by customer and don’t add any grouping levels in your report. Click on the Summary Options button on the bottom of this screen and check the Sum box for both the weight fields. This will sum both the weight columns in your report for each customer. After your report is created, make sure that all fields are clearly visible on your report.

4) National Bank

National Bank offers various types of loans to its customers.  Each month, customers make payments on their loan(s). Data regarding these transactions is stored in the National Bank database. Your proficiency in BIS has helped you secure an internship at National Bank. You will be assisting your manager in collecting necessary data and analyzing it as required.

Open the National Bank.mdb database and review the tables in the database to get a general idea about this database and its contents. Perform. the following in the National Bank database:

1. Establish the appropriate relationships between the tables based on the attributes provided and your general knowledge. Remember:

a. a customer can have multiple loans (for example a student loan, a car loan etc.) but each loan belongs to one customer

b. a loan has multiple payments on it and each payment is for a (one) specific loan

Note: When creating relationships, all other objects (tables etc.) and windows should be closed. Only the relationship window should be open. Also, make sure you Enforce Referential Integrity (check the box) when creating the relationships.

2. You need to add new customers’ personal information to the database. Create a form. that includes only customer data and call this form. Customers Only.

3. Create a form. that includes Customers (all fields), Loans (all fields except CustomerID), and Payments data (all fields except LoanID). This form. should allow you to see each customer record with associated loan and payment information. Call this form. Customers with Loan and Payments. Make sure all fields are clearly visible and all columns of data are arranged for best fit.

4. Your manager wants to see how many loans each customer has taken and how much money each one owes the bank. Create a report called Loans by Customer. Select CustomerID, Lastname, Firstname, and PhoneNumber from the Customers table and select Amount and Type from the Loans table. In creating the report, view your data by customer and don’t add any grouping levels in your report. Sort your data by the loan type and click on the Summary Options button on the bottom of this screen and check the Sum box for the Amount field. This will sum the Amount column in your report to give you the total amount of loans per customer. After your report is created, make sure that all fields are clearly visible on your report.

5. Create a report called Loan Types to show the various types of loans customers have taken. Select Lastname, Firstname, and PhoneNumber from the Customers table and select LoanID, Amount, and Type from the Loans table. In creating the report, view your data by loans and group your data by loan type. Sort your data by the LoanID and click on the Summary Options button on the bottom of this screen and check the Sum box for the Amount field. This will sum the Amount column in your report to give you the total amount of loans by loan type. After your report is created, make sure that all fields are clearly visible on your report