I would like to get a solution for the questions I have posted
Assignment1P1 University of Canberra Faculty of Sciences and Technology Semester 2, 2021 Database Systems PG 6681 Assignment 1 Due date: Friday Week 7 Semester 2 2021 at 11:00 pm 40 marks (which constitutes 20% of the total mark of this unit) 1. Instructions The marking of this assignment is based on following instructions, providing correct solutions, and demonstrating knowledge of the unit materials. Complete each question and submit your solution to this assignment on time to the Canvas site of this unit. Your answers to the assignment questions must be easy to read, neat, and easy to understand and address the question. Include with your assignment a cover page containing your first and last name as well as your student id, your tutorial day and time, and unit name and unit number. Be sure to maintain regular back-ups for any models or material prepared with the aid of software. Loss of files will not be accepted as an excuse for the non-completion of this assignment. Marked assignments will be available from the Canvas website of this unit. Answer the following questions: 2. The Canberra Cooling and Heating Services (CCH) was established recently. CCH has several branches in Canberra. The company has a database and it requires you to review the designed database system and optimize and improve the performance of the reports generated from the database of the system. The CCH database records and stores the data about each of the CCH customers including their first and last name, street number, street name, suburb, post-code, city, sex, and type of products and services (of CCH) used by the customer. CCH offers several types of Cooling and Heating products and services, namely residential cooling and heating systems, commercial cooling, and heating system. For all these systems CCH provides installation, repair, and maintenance services. CCH also provides several types of services, namely quarterly maintenance services and yearly imminences services. When a customer joins CCH for any product or service, he or she is assigned a customer number and his/her details are recorded. Every customer is also provided with an account number (which is different from the customer number). Upon issuing the customer number for each customer CCH also provides customers with a sheet of paper showing the rules relating to the rules and regulations of the CCH. The CCH has 35 staff members. For each staff member the following data is stored in the database system of CCH: staff first and last name, staff number, position, sex, date of birth, department and section number in which the staff works, internal telephone number, office number, and branch number. The CCH has several sections. These are the customer service section, cooling, and heating system section (both residential and commercial), administration section, and complaint section. The information about each section is stored in the CCH database. The information about each section is section number, section name, location, telephone, and fax number. To install a cooling and heating system a customer makes an appointment with one of the staff in the cooling and heating system section. The customer can also browse the CCH website about the details and estimated price of a cooling and heating system. The customers can customize the type of cooling and heating systems to their needs using the website. A customer enters his/her required cooling or heating system, the size of the house in square meters, the type of the house (single or double story), number of rooms, and number of windows in each room. The cooling and heating systems fall into several different types, each with a different purchase price as well as installation and maintenance fees. The cooling system types are high-performance residential systems, medium performance residential systems, and high-performance commercial systems. The heating system types are high-performance residential systems and high-performance commercial systems. A customer can have several types of cooling and heating systems installed and maintained by CCH. The customer's identity is established with their customer number and their account number. The customer number and account number (of customers) are used to access customer records. The payment for each cooling or heating system maintained or installed is billed to the customer on monthly basis. Each bill provides the customer with two weeks to pay their bills. If there are any outstanding charges for a customer the computer system will provide a notice to the customer with the due date for payment of outstanding amounts. This notice is then sent to the customer. The details of the customer and the product and services that are provided to the customer are stored in the CCH database. If a customer has any queries about his products and/or services then he/she needs to contact CCH at the following telephone number: 1800 123456. The staff at the customer service section once notified by the customer will record the customer details, date and time, and the customer queries. A customer query then will be queued and is responded to within 24 hours. The customer queries fall usually into the following categories: quotation for the installation of new systems, faults of systems, servicing of systems. If a customer has a cooling or heating system installed by CCH then the customer should make a payment for the maintenance of his/her system every month. The amount of payment depends on the type of the system. If a customer makes a late payment then a late fee charge of $20.00 per month is added to his/her account. CCH system also provides an appointment reservation facility where customers can make an appointment to consult with staff members about their system. For reservation, a customer enters his/her name (first and last name), contact details (telephone number and postal address), the preferred time and date of appointment on the website of the CCH company, or by calling the CCH main switchboard at 1800 789123. A customer can request his/her appointment at a particular branch. An appointment is then made for the customer if a customer service staff is available at that particular time (and the branch requested). If the customer uses the CCH website then the timetable for all staff at the requested branch is displayed on the screen. The customer can then browse the timetable, check the availability of staff and make an appointment. CCH database provides the following types of computer-generated reports: The first report shows all overdue payments. This report is stored with customer details (i.e., first and last name, street number, street name, suburb, post-code, city, sex, ) in alphabetical order using customer last name. It is used to contact the customers with overdue payments (A computer prepared notice is sent to the customers that have overdue payments). Customers are reminded to make a payment for the specified overdue payment and they are informed about late charges that are incurred. A late charge of $10.00 is added to the customer account every month for overdue payments. This report will provide the following information: first and last name of customer, postal address (street number, street name, suburb, post-code, city), contact telephone number, and all records of overdue payments for the customer as well as the total amount of overdue charges. This report is generated on daily basis. The second report shows the complaints made to the customer service center. This report is generated daily. The third report shows the details of the new systems installed and maintained on daily basis with the details of staff responsible for such services. This report is provided to the CCH management for staffing purposes of each branch. Given the following tables from the CCH Database: Customer (CstomerID, AccountID, FirstName, LastName, StreetNumber, StreetName, Suburb, Post-code, City, Sex) Product (ProductID, Type, Price) Installed_Systems (InstallationID, Installation-Date-Time, CustomerID, AccountID, StaffID, location-of-Installed-System, Type-of-System) Staff (StaffID, FirstName, LastName, Position, Sex, Date-of-Birth, SectionNumber, TelephoneNumber, OfficeNumber, BranchNumber) Section (SectionNumber, SectionName, location, BranchNumber) Branch (BranchNumber, BranchName, StreetNumber, StreetName, Suburb, Postcode, City, Number-of-Sections) Appointment (AppointmentID, CustomerID, AccountID, StaffID, Date-Time-of-Appointment, Date-Time-Appointment-Made, Type-of-Appointment) CustomerQuery (QueryID, QueryDetails, DateTime, CustomerID, AccountID) Montly-Service-Payment (PaymentID, ProductID, AmountPaid, CustomerID, Date-Time-of-Payment, AccountID) Overdue-Charges (OverChargeID, CustomerID, Amount, AccountID, Number-of-Days-Overdue) Provide suggestion/s to improve the query and reporting process for: a) the performance of the first report that shows the details of the overdue-charge payments. (5 Marks) b) the performance of the third report. (5 Marks) Hint: You may like to suggest a new way for designing the tables or add new tables in this database and use denormalization. c) explain why the suggestions you made above will improve the performance of the above report generation from the CCH database. Would this change cause deficiencies in this database? (5 Marks) d) Briefly explain the advantages and disadvantages of the suggestions you have made in par (a) and (b) of this question. (5 Marks) PAGE 1 Chapter 3 Chapter 19 Methodology – Monitoring and Tuning the Operational System * Chapter 19 - Objectives Meaning of denormalization. When to denormalize to improve performance. Importance of monitoring and tuning the operational system. How to measure efficiency. How system resources affect performance. * Consider the Introduction of Controlled Redundancy To determine whether introducing redundancy in a controlled manner by relaxing normalization rules will improve the performance of the system. * Consider the Introduction of Controlled Redundancy Result of normalization is a design that is structurally consistent with minimal redundancy. However, sometimes a normalized database does not provide maximum processing efficiency. May be necessary to accept loss of some benefits of a fully normalized design in favor of performance. * Consider the Introduction of Controlled Redundancy Also consider that denormalization: makes implementation more complex; often sacrifices flexibility; may speed up retrievals but it slows down updates. * Introduction of Controlled Redundancy Denormalization refers to a refinement to relational schema such that the degree of normalization for a modified relation is less than the degree of at least one of the original relations. Also use term more loosely to refer to situations where two relations are combined into one new relation, which is still normalized but contains more nulls than original relations. * Consider the Introduction of Controlled Redundancy Consider denormalization in following situations, specifically to speed up frequent or critical transactions: Combining 1:1 relationships Duplicating non-key attributes in 1:* relationships to reduce joins Duplicating foreign key attributes in 1:* relationships to reduce joins * Consider the Introduction of Controlled Redundancy Duplicating attributes in *:* relationships to reduce joins Introducing repeating groups Creating extract tables Partitioning relations. * Sample Relation Diagram * Sample Relations * Combining 1:1 relationships * Duplicating non-key attributes in 1:* relationships to reduce joins * Duplicating non-key attributes in 1:* relationships: Lookup Table * Duplicating non-key attributes in 1:* relationships: