COMP 636代做、代写SQL编程设计

项目预算：开发周期：发布时间：要求地区：

COMP 636: Web App Assessment
Milestone submission due: 5pm Wednesday 24 January 2024
Final submission due: 5pm Wednesday 14 February 2024
Worth: 50% of COMP636 grade
Submit via Akoraka | Learn, with files set up and available on GitHub and pythonanywhere.
Introduction
Selwyn Panel Beaters have decided that they want to upgrade their internal system from the textbased system to a web-based system. Your task is to develop a small Web Application to help them
manage customers, jobs, services, parts and billing. You will also write a report.
In the existing system, Selwyn Panel Beaters record multiple items (services, parts) by entering them
multiple times. In the web system they want to be able to enter a single job for a vehicle and then
record the quantities of services and parts within that job.
There are two different types of users who will use the system. The first is a technician who can
select a customer job and add services and parts to that job.
The second is an office administrator who can book (create) jobs, add customers, services and parts,
bill customers and enter payments.
Download the Web Application Project Files from the Assessment block on the Learn page. These will
get you started, including for the Milestone. You will add more routes and templates as you develop
your app.
Important
This is an individual assessment. You may not collaborate or confer with others. You
may help others by verbally explaining concepts and making suggestions in general
terms, but without directly showing or sharing your own code. You must develop the
logical structure and the detail of your code on your own, even if you are working
alongside others. Code that is copied or shares a similar logic to others will receive zero
marks for both parties. The University policy on Academic Integrity can be found here.
Functional Requirements
There will be two interfaces:
• An interface for the technicians as the main users of the system. This allows technicians to
update customer jobs with parts and services.
• An Administrator interface for admin staff to use to update, edit and add data. This interface
requires administrators to access the system via a link from the menu system (do not add
password functionality – security would be added to a full system to restrict access, but will lose
marks if added for this assessment). The Admin link will provide a gateway to the Admin
features. The Admin features must not be visible in any of the technician interface (apart from
the Admin link in the menu).
2
Technician Interface on the default / route
• Current Jobs: Change the currentjobs page to display the customer’s name, rather than the ID
only. Modify or tidy the template as appropriate. This page only shows jobs that are not
completed.
• Job: A job is selected (via a link) from the currentjobs page. On this page services and parts can
be added to a job. The technician can select a part or service from existing lists (which are
maintained by the Admins). The technician enters the number of units of each part or service
used (e.g., 2 headlights or 1 respray). This page should also show the services and parts that
have already been used in the job. A job can also be marked as completed.
After a job is marked complete, the job total cost is calculated. Once a job is marked complete it
cannot be modified.
Administrator Interface
• Customer list: Display a list of customers, ordered by surname, then by first name.
• Customer search: Search for customers by first name or surname, allowing for partial text
matches. Order the results appropriately.
• Add customer: Add a new customer to the system (surname, phone number and email address
are required fields)
• Add Service: Add a new service to the system (name and cost are required)
• Add Part: Add a new part to the system (name and cost are required)
• Schedule Job: Select a customer and a date for the job to be booked for. The date must be
today or in the future. Only the date is required – a time on that day is not required.
• Unpaid bills & Pay Bills: Shows all unpaid bills in date then customer order, which can be
filtered by customer. A bill can then be selected so that it can be marked as paid by the Admin.
Bills that are paid should not show on the list.
• Billing History & Overdue Bills: A report showing all bills that, with bills that are unpaid more
than 14 days after the date of the job highlighted in red. The display should include the
customer details, the date of the job, the total cost of the job. Bills should be grouped by
customer, so that the customer details are only shown once above the list of bills for that
customer. Customers should be shown in last name, first name order. Bills should be shown
with the oldest bill first.
3
Report
Your report must be created using GitHub Markdown format and saved in the README.md file of
your GitHub repository. It does not need to be a formal report – a tidy document using the following
headings will be sufficient. Write a brief project report that includes:
• Web application structure:
o Outline the structure of your solution (routes & functions, and templates). This should be
brief and can be text-based or a diagram (as an image).
o It must indicate how your routes, functions and templates relate to each other, as well as
what data is being passed between them.
o Do not just give a list of your routes. Do not include all of your code. This relates to the
code, not the user experience – so do not describe the interface, user experience or HTML
layout.
• Design decisions:
o Discuss the design decisions you made when designing and developing your app: what
design options you weighed up, why you designed your app the way that you did, your
decisions about the routes, templates, navigation, broad layout, etc., that you made.
o For example, when the edit button is clicked on a page, does that open a different template
for editing or does it use the same template with IF statements to enable the editing? Did
you use GET or POST to request and send data, and how and why? You will have considered
many design possibilities. These are only two examples.
o Note your decisions as you work, so you do not forget them!
• Database questions: Refer to the supplied spb_local.sql file to answer the following questions:
1. What SQL statement creates the job table and defines its fields/columns? (Copy and
paste the relevant lines of SQL.)
2. Which line of SQL code sets up the relationship between the customer and job
tables?
3. Which lines of SQL code insert details into the parts table?
4. Suppose that as part of an audit trail, the time and date a service or part was added
to a job needed to be recorded, what fields/columns would you need to add to
which tables? Provide the table name, new column name and the data type. (Do not
implement this change in your app.)
5. Suppose logins were implemented. Why is it important for technicians and the office
administrator to access different routes? As part of your answer, give two specific
examples of problems that could occur if all of the web app facilities were available
to everyone.
• Image sources: It is not necessary to use any external images in your web app, but if you do,
ensure you reference the image source in your report.
4
Data Model
Model Notes:
Child table.field * (refers to) Parent table.field
job.customer customer.customer_id
job_part.job_id job.job_id
job_part.part_id part.part_id
job_service.job_id job.job_id
job_service.service_id service.service_id
* the ‘Foreign Key’
Project Requirements
You must:
• Use only the COMP636 technologies (Python & Flask, Bootstrap CSS, MySQL). Do not use
SQLAlchemy or ReactJS (or other similar technologies) in your solution.
Do not use any scripts, including JavaScript, except for the