You are required to: (a) Identify and list all entities, attributes, and primary keys of the…

You are required to: (a) Identify and list all entities, attributes, and primary keys of the…
You are required to:(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. [30 Marks](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). [10 Marks]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.
Part 3 SQL [25 marks total]Based on theentities, attributes, and primary keys of your solution forCanberra Work Group (CWG)in Part 2 of this Take-Home Assessment paper,Write the following queries using SQL:1.List details of all customers from Canberra that have enrolled in a course order by Customer Number.[5 Marks]2.How many customers from Canberra have taken an appointment for a repair work with staff with Staff number = 12345?[5 Marks]3.List the number of female staff in each section.[5 Marks]4.List the first and last name of all female staff that have made an appointment with a customer from Canberra for repair work.[5 Marks]5.For each section display the staff number of all staff working in that section.[5 Marks]
Part 4 Normalization [15 marks total]The tables below list student/subject, department number, and enrolment data. Each student may enroll in many subjects from various departments in a university. Subjects are offered by different departments.SubjectNoSubjectNameDepartmentNo1122Database ID1001123Chemistry IID881124Biology IID121124Biology IID121199Chemistry ID61199Chemistry ID6StudentNameEnrolmentDateEnrolment feeSubjectNoStudentNoJ. Smith14/2/2016$12001122u100P. Ross17/2/2011$12001123u201J. Smith16/2/2016$11001124u100K. Lee14/1/2013$11001124u313J. Smith20/1/2016$10001199u100T. Khan20/1/2010$10001199u295(a) The data in the above table is susceptible to update anomalies. Provide examples of how insertion, deletion, and modification anomalies could occur in the above tables.[5 marks](b) Design a database to store the data from the above table. Your designed database must be in thethird normal form (3NF).Draw the E-R diagram of your model in 3NF.[10 marks]
Attachments: Database-Desi….docxMay 18 2022 05:42 AM

Place your order
(550 words)

Approximate price: $22

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more
Open chat
1
You can contact our live agent via WhatsApp! Via + 1 (409) 205-8984

Feel free to ask questions, clarifications, or discounts available when placing an order.

Order your essay today and save 30% with the discount code DISCOUNT