DO NOT JUST COPY AND PASTE THE ANSWER FROM ASSIGNMENT 1 THAT IS POSTED ON CHEGG ALREADY. TO FINISH THIS ASSIGNMENT, YOU HAVE TO ADD WHAT HAS ALREADY BEEN CREATED ON ASSIGNMENT 1 PLUS ALL THE NEW DATA THAT IS ASKED FOR BELOW. PLEASE DO NOT REPOST AN ANSWER THAT IS ALREADY ON CHEGG, PLEASE.
BSYS 2060 Database Assignment #1 Normalized Table Design Task: Design a Normalized Relational Database for "Pets-We-B A retail Pet Store has asked you to design a database to capture the important aspects of their business data. Your task is to design a relational database which follows the basic design principles found in Chapter 3. The pet store will need to store Customer data. This data includes the name of the customer, as well as address and phone Numbers. Pet data is stored as well, including the species of pet, the colour, gender, birthdate, price, and an| indication whether the pet is spayedeutered. Employee data includes names, addresses, phone numbers, position and hourly pay rate. Pets-We-B has several retail locations. You will need to store data on store location, the date the store opened, the type of store (mall, standalone, kiosk, etc.), as well as the name of the manager of the store, and the store phone number and address. Finally, Sales are stored. Sales data captures the pet being sold, the store at which it was purchased, the employee which conducted the sale, the date and time of the sale, and the customer making the purchase. All records in all tables must be uniquely identified. Begin by reviewing the requirements for the data, above, and then design a system of tables which will allow Pets-We-B to capture at least this critical data (you MAY decide to add details to these data if it is required.) Hint: use a spreadsheet application like Excel to assist you or use pen and paper to design the tables similarly to the method used in the textbook readings. Once you have developed a sufficient design, implement this design in an MS Access 2016 database, complete with Tables and Relationships. Relationships must enforce referential integrity. All tables must have a minimum of five (5) records. You may make up data as you desire, however it must make sense within the context of the assignment. BSYS 2060 - Database Assignment #2 Implementing the User Interface (REVISED) Task: Extend the "Pets-We-B" database to include the UI A retail Pet Store has asked you to design a database to capture the important aspects of their business data. In this assignment, you will build on the basic design to add tools to assist the user to interact with the database. Tables The store manager has asked if you can add two new tables to the database to help capture Invoice and Payment data. Each Sales record should have at least one Invoice associated with it, and each of these Invoices will have at least one Payment record. Invoices need to capture the Salel that the invoice is for the Date that the Invoice was created, and the Shipping Address data (which may or may not be the same as the Customer address). Payments need to capture the Invoicell the Date of the payment, the Amount of the payment, and the Type of payment (Cash, Cheque or Credit Card-you do NOT need to record any details of credit cards at this time.) The manager has also asked you to modify the Pets table to include a Final Price for each pet, by calculating the sales tax amount and adding it to the Price (assume a 12% tax rate for this field). The basic design of the database also needs to be extended to include user tools, like Forms and Reports. Forms There should be a basic form for editing or adding records to each of the Locations, Pets, Employees and Customers tables. There needs to be a form for recording basic Sales records, which should contain Lookup fields to select the required field values from the Locations, Pets, Employees and Customers tables. There should be a form for editing Sales and Invoices together. This form should show all of the Sale record data, and contain a Sub-form (in datasheet format) that allows the user to create Invoice records. The main Sales form should contain a calculation that COUNTS all the invoices for that Sale (there may be more than one). There should be a form for editing Invoice and Payment records. This form should show Invoice data and contain a Sub-form to display and allow the user to enter Payment records. The main Invoice form should contain a calculation to show the total of the Payment amounts associated with each Invoice. Reports The manager would like to see two Reports created. One report will show a list of all existing Sales records for the current year, organized by store Location, and sorted by Customer last name. You should show the total count of Sales records for each Location (Group Totals), and for the company overall (Grand Totals). The other report will show a list of unpaid Invoices, grouped by Customer last name, showing the total dollar amount outstanding for each customer. This report should also show the number of days each Invoice has gone unpaid (the difference between the invoice date and the current date, in days.) To test this report, you will need to create several Invoice records without creating any Payment records. In order to produce the forms and reports above, you may need to add queries to generate or calculate the required data. You may build any query you need to do this, although the final database you build should only contain useful queries, do not leave "testers" or experimental queries in the final design. BSYS 2060 Database Assignment #1 Normalized Table Design Task: Design a Normalized Relational Database for "Pets-We-B A retail Pet Store has asked you to design a database to capture the important aspects of their business data. Your task is to design a relational database which follows the basic design principles found in Chapter 3. The pet store will need to store Customer data. This data includes the name of the customer, as well as address and phone Numbers. Pet data is stored as well, including the species of pet, the colour, gender, birthdate, price, and an| indication whether the pet is spayedeutered. Employee data includes names, addresses, phone numbers, position and hourly pay rate. Pets-We-B has several retail locations. You will need to store data on store location, the date the store opened, the type of store (mall, standalone, kiosk, etc.), as well as the name of the manager of the store, and the store phone number and address. Finally, Sales are stored. Sales data captures the pet being sold, the store at which it was purchased, the employee which conducted the sale, the date and time of the sale, and the customer making the purchase. All records in all tables must be uniquely identified. Begin by reviewing the requirements for the data, above, and then design a system of tables which will allow Pets-We-B to capture at least this critical data (you MAY decide to add details to these data if it is required.) Hint: use a spreadsheet application like Excel to assist you or use pen and paper to design the tables similarly to the method used in the textbook readings. Once you have developed a sufficient design, implement this design in an MS Access 2016 database, complete with Tables and Relationships. Relationships must enforce referential integrity. All tables must have a minimum of five (5) records. You may make up data as you desire, however it must make sense within the context of the assignment. BSYS 2060 - Database Assignment #2 Implementing the User Interface (REVISED) Task: Extend the "Pets-We-B" database to include the UI A retail Pet Store has asked you to design a database to capture the important aspects of their business data. In this assignment, you will build on the basic design to add tools to assist the user to interact with the database. Tables The store manager has asked if you can add two new tables to the database to help capture Invoice and Payment data. Each Sales record should have at least one Invoice associated with it, and each of these Invoices will have at least one Payment record. Invoices need to capture the Salel that the invoice is for the Date that the Invoice was created, and the Shipping Address data (which may or may not be the same as the Customer address). Payments need to capture the Invoicell the Date of the payment, the Amount of the payment, and the Type of payment (Cash, Cheque or Credit Card-you do NOT need to record any details of credit cards at this time.) The manager has also asked you to modify the Pets table to include a Final Price for each pet, by calculating the sales tax amount and adding it to the Price (assume a 12% tax rate for this field). The basic design of the database also needs to be extended to include user tools, like Forms and Reports. Forms There should be a basic form for editing or adding records to each of the Locations, Pets, Employees and Customers tables. There needs to be a form for recording basic Sales records, which should contain Lookup fields to select the required field values from the Locations, Pets, Employees and Customers tables. There should be a form for editing Sales and Invoices together. This form should show all of the Sale record data, and contain a Sub-form (in datasheet format) that allows the user to create Invoice records. The main Sales form should contain a calculation that COUNTS all the invoices for that Sale (there may be more than one). There should be a form for editing Invoice and Payment records. This form should show Invoice data and contain a Sub-form to display and allow the user to enter Payment records. The main Invoice form should contain a calculation to show the total of the Payment amounts associated with each Invoice. Reports The manager would like to see two Reports created. One report will show a list of all existing Sales records for the current year, organized by store Location, and sorted by Customer last name. You should show the total count of Sales records for each Location (Group Totals), and for the company overall (Grand Totals). The other report will show a list of unpaid Invoices, grouped by Customer last name, showing the total dollar amount outstanding for each customer. This report should also show the number of days each Invoice has gone unpaid (the difference between the invoice date and the current date, in days.) To test this report, you will need to create several Invoice records without creating any Payment records. In order to produce the forms and reports above, you may need to add queries to generate or calculate the required data. You may build any query you need to do this, although the final database you build should only contain useful queries, do not leave "testers" or experimental queries in the final design
