Part 3 SQL [25 marks total] Based on the entities, attributes, and primary keys of your solution for Canberra Work Group
Posted: Fri May 20, 2022 10:00 am
Part 3 SQL [25 marks total]
Based on the entities, attributes, and
primary keys of your solution for Canberra Work Group (CWG) in Part
2 of this Take-Home Assessment paper, Write the following
queries using SQL:
[5 Marks]
[5 Marks]
[5 Marks]
[5 Marks]
Data is here,
Canberra Work Group (CWG) was established recently. It has
several offices in Canberra. CWG requires your team to design a
database system for the CWG. CWG database will record and store all
data about each of its customers including their first and last
name, postal address and home address (street number, street name,
suburb, post-code, city), gender, and type/s of work a customer
requires to be done at their property. The name (first and last
name), contact telephone number, and address (street number, street
name, suburb, post-code, city, state) of the customer's next-of-kin
are also stored in the CWG database.
CWG offers several types of home repair services. Repair
services are managed under the CWG repair section. There exist
several types of repairs that CWG provides. Details of all repair
services are stored in the CWG database and these details include:
service name, service number, service type, and price. When a
customer joins CWG, he or she is assigned a customer number and
his/her details are recorded. Every customer is provided with a
customer number and a sheet of paper describing the rules relating
to CWG.
CWG has several staff members. For each staff member the
following data is stored in the CWG database system: staff first
and last name, staff number, position, gender, date of birth,
salary, position, name of the section he/she works in, internal
telephone number, and office number. Staff can work in several
sections. CWG has 3000 customers with 15 full-time staff
members.
CWG consists of a few sections. These are: the information
section, service repair section, customer support section, customer
feedback, and account section. The information about each section
is: section name, email address, and location. Each section has
several telephone and fax numbers.
CWG offers several gardening and home repair courses. To take a
course a customer makes an appointment with one of the staff in the
customer support section. The customer can also browse the CWG
website to find out about the details of all courses that are
offered each month.
A customer can enroll in several types of courses. However, a
customer is limited to a maximum of 6 courses at any particular
time. Customers' identity is established with their card number and
their customer number. The customer's card number and their
customer number are used to access customer records.
Customers can enroll in courses or request repair services from
CWG. An invoice is issued for customers who enroll in a course or
request a repair service. Each invoice has a unique invoice number.
The data stored on each invoice includes the invoice number, course
number and course name or repair service number and repair service
name, course duration (start date and end date), payment due date,
amount to be paid, client’s full name, and name of staff who issued
the invoice.
The details of all courses offered by CWG are stored in the CWG
database. These details are: course number, course name, start
date, end date, course fees, instructor number, and instructor
name.
CWG has several instructors who conduct courses at CWG. The
details of instructors are stored in the CWG database. Instructor
details are: Instructor first and last name, instructor number,
position, gender, date of birth, salary, internal telephone number,
section number, and office number.
Each customer is required to make a payment for his/her invoice.
The method of payment can be cash or credit cards. If there are any
outstanding invoices for a customer then that customer will be sent
a notice to pay his/her outstanding invoice. The details of each
payment (payment number, customer number, invoice number, amount
paid, date of paid) are stored in the CWG database. The details of
each notice sent to a customer are also stored in the CWG database.
These details include: the notice number, customer name, invoice
number, and date of issue of notice.
For each payment made by a customer, a receipt that lists
details of the payment made is provided to the customer and it is
recorded in the CWG database. Receipt details include: customer
name, receipt number, date of payment, the amount paid, invoice
number, and payment number.
CWG provides repair services upon a request from its customers.
A customer first contacts CWG and informs the staff at CWG about
the repair services that he/she is required for their property. A
job is then logged into the CWG database which includes the
following details: job number, customer name, repair number, repair
name, date of job lodgement, and staff name of the staff who logged
the repair job.
CWG provides an appointment for the customer where a CWG repair
will visit the customer and to provide a quotation for the cost of
the repair. For an appointment reservation, a customer enters
his/her name (first and last name), contact details (telephone
number and postal address), and preferred time and date of
appointment. An appointment is then made for the customer at that
particular time with a staff member. A repairer from CWG then will
visit the customer at the date and time of the appointment and
provides a quotation. The details of all appointments by CWG
customers are stored in the CWG database. These details are:
Customer number, customer property address, staff number, repair
description, and quotation price. Once the quotation is accepted by
the customer a repairer will contact the customer and organize a
time to finalize the repair. The repair details are stored in the
CWG database. The details stored about each repair are: customer
name, customer contact details (telephone number and postal
address), and time and date of repair and repairer name, and repair
number.
An invoice is issued for each repair job performed for a
customer. The data stored about each invoice includes: invoice
number, repair number, payment due date, amount to be paid,
property address, customer full name, and name of staff who issued
the invoice. Each customer is required to make a payment for
his/her invoice, quotation number, and quotation price. The details
of each payment (payment number, customer number, invoice number,
amount paid, date of paid, invoice number) are stored in the CWG
database. For each payment, a receipt that lists details of the
payment made is provided to the customer and it is recorded in the
database. Receipt details include: customer number, receipt number,
date of payment amount paid, payment number, and invoice
number.
CWG has two seminar rooms. CWG customers can book seminar rooms.
Details of CWG customers who book seminar rooms are stored in the
CWG database. These details are: booking number, customer number,
customer first and last name, seminar room number, and date and
time for which a seminar room is booked.
Details of staff that book a seminar room for a customer is also
recorded in the CWG database. These details are: staff number,
staff first and last name, the position of staff, date and time for
which a seminar room is booked.
Based on the entities, attributes, and
primary keys of your solution for Canberra Work Group (CWG) in Part
2 of this Take-Home Assessment paper, Write the following
queries using SQL:
[5 Marks]
[5 Marks]
[5 Marks]
[5 Marks]
Data is here,
Canberra Work Group (CWG) was established recently. It has
several offices in Canberra. CWG requires your team to design a
database system for the CWG. CWG database will record and store all
data about each of its customers including their first and last
name, postal address and home address (street number, street name,
suburb, post-code, city), gender, and type/s of work a customer
requires to be done at their property. The name (first and last
name), contact telephone number, and address (street number, street
name, suburb, post-code, city, state) of the customer's next-of-kin
are also stored in the CWG database.
CWG offers several types of home repair services. Repair
services are managed under the CWG repair section. There exist
several types of repairs that CWG provides. Details of all repair
services are stored in the CWG database and these details include:
service name, service number, service type, and price. When a
customer joins CWG, he or she is assigned a customer number and
his/her details are recorded. Every customer is provided with a
customer number and a sheet of paper describing the rules relating
to CWG.
CWG has several staff members. For each staff member the
following data is stored in the CWG database system: staff first
and last name, staff number, position, gender, date of birth,
salary, position, name of the section he/she works in, internal
telephone number, and office number. Staff can work in several
sections. CWG has 3000 customers with 15 full-time staff
members.
CWG consists of a few sections. These are: the information
section, service repair section, customer support section, customer
feedback, and account section. The information about each section
is: section name, email address, and location. Each section has
several telephone and fax numbers.
CWG offers several gardening and home repair courses. To take a
course a customer makes an appointment with one of the staff in the
customer support section. The customer can also browse the CWG
website to find out about the details of all courses that are
offered each month.
A customer can enroll in several types of courses. However, a
customer is limited to a maximum of 6 courses at any particular
time. Customers' identity is established with their card number and
their customer number. The customer's card number and their
customer number are used to access customer records.
Customers can enroll in courses or request repair services from
CWG. An invoice is issued for customers who enroll in a course or
request a repair service. Each invoice has a unique invoice number.
The data stored on each invoice includes the invoice number, course
number and course name or repair service number and repair service
name, course duration (start date and end date), payment due date,
amount to be paid, client’s full name, and name of staff who issued
the invoice.
The details of all courses offered by CWG are stored in the CWG
database. These details are: course number, course name, start
date, end date, course fees, instructor number, and instructor
name.
CWG has several instructors who conduct courses at CWG. The
details of instructors are stored in the CWG database. Instructor
details are: Instructor first and last name, instructor number,
position, gender, date of birth, salary, internal telephone number,
section number, and office number.
Each customer is required to make a payment for his/her invoice.
The method of payment can be cash or credit cards. If there are any
outstanding invoices for a customer then that customer will be sent
a notice to pay his/her outstanding invoice. The details of each
payment (payment number, customer number, invoice number, amount
paid, date of paid) are stored in the CWG database. The details of
each notice sent to a customer are also stored in the CWG database.
These details include: the notice number, customer name, invoice
number, and date of issue of notice.
For each payment made by a customer, a receipt that lists
details of the payment made is provided to the customer and it is
recorded in the CWG database. Receipt details include: customer
name, receipt number, date of payment, the amount paid, invoice
number, and payment number.
CWG provides repair services upon a request from its customers.
A customer first contacts CWG and informs the staff at CWG about
the repair services that he/she is required for their property. A
job is then logged into the CWG database which includes the
following details: job number, customer name, repair number, repair
name, date of job lodgement, and staff name of the staff who logged
the repair job.
CWG provides an appointment for the customer where a CWG repair
will visit the customer and to provide a quotation for the cost of
the repair. For an appointment reservation, a customer enters
his/her name (first and last name), contact details (telephone
number and postal address), and preferred time and date of
appointment. An appointment is then made for the customer at that
particular time with a staff member. A repairer from CWG then will
visit the customer at the date and time of the appointment and
provides a quotation. The details of all appointments by CWG
customers are stored in the CWG database. These details are:
Customer number, customer property address, staff number, repair
description, and quotation price. Once the quotation is accepted by
the customer a repairer will contact the customer and organize a
time to finalize the repair. The repair details are stored in the
CWG database. The details stored about each repair are: customer
name, customer contact details (telephone number and postal
address), and time and date of repair and repairer name, and repair
number.
An invoice is issued for each repair job performed for a
customer. The data stored about each invoice includes: invoice
number, repair number, payment due date, amount to be paid,
property address, customer full name, and name of staff who issued
the invoice. Each customer is required to make a payment for
his/her invoice, quotation number, and quotation price. The details
of each payment (payment number, customer number, invoice number,
amount paid, date of paid, invoice number) are stored in the CWG
database. For each payment, a receipt that lists details of the
payment made is provided to the customer and it is recorded in the
database. Receipt details include: customer number, receipt number,
date of payment amount paid, payment number, and invoice
number.
CWG has two seminar rooms. CWG customers can book seminar rooms.
Details of CWG customers who book seminar rooms are stored in the
CWG database. These details are: booking number, customer number,
customer first and last name, seminar room number, and date and
time for which a seminar room is booked.
Details of staff that book a seminar room for a customer is also
recorded in the CWG database. These details are: staff number,
staff first and last name, the position of staff, date and time for
which a seminar room is booked.