- Part1 A Identify And List All Entities Attributes And Primary Keys Of The System Below Make Sure That Your Data Mod 1 (140.51 KiB) Viewed 21 times
Part1 a) Identify and list all entities, attributes, and primary keys of the system below. (Make sure that your data model is normalized to 3NF). You need to include with your data model the details of all assumptions that you have made for your designed database. 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. Customers can eocall 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. (b) Draw an E-R diagram of the system below. (Make sure that your E-R diagram is based on your data model that is normalized to 3NF). 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 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, date of issue of notice. An invoice is issued for each repair job performed for a customer. The data stored about each invoice includes: the 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. 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. 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. Part 2 SQL 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 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: , • List details of all customers from Canberra that have enrolled in a course order by Customer Number. 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. • How many customers from Canberra have enrolled in a course conducted by an instructor with Instructor Number = 12345? A customer can enxell 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. • List the number of male staff in each section. • List the first and last name of all female staff that have made an appointment with a customer from Canberra for repair work • What is the average salary of male staff from Canberra?