ISY1002(ISY 103): Database Management for Business - IT - Report Writing Assignment Help

Internal Code: 1ABCBJ

IT Report Writing Assignment Help

Task: 1

1. Your first task is to study the Case study requirements and gather as much knowledge to list all the main Entities and attributes.
2. Create an Entity Relationship Diagram (ERD) to help you decide on the relationships.

Your entity relation diagram that models your database design should:

1. 1. 1. Include all entities, relationships (including names) and attributes.
      2. Identify primary and foreign keys.
      3. Include cardinality/ multiplicity and show using crow’s feet or UML notation.
      4. Include participation (optional / mandatory) symbols if applicable.

The E-R should be created as part of a Microsoft Word document.  Hand-drawn diagrams will not be accepted. It is recommended that you complete your ERD using Draw.io, Visio or try www.gliffy.com (Search for ERD gliffy to get started.).

1. Create relationships between tables and enforce the referential integrity as shown below.

Relationships:

• • • Student can enrol themselves into more than one Dance Class.  
    • One type of Dance can have more than one classes timetabled and they can run at various locations
    • The dance styles can be Jazz, Belle, Hip-hop, Salsa etc.
    • Each room can be timetabled more than once as long as the classes don’t overlap.

• • • Each enrolment needs a payment that needs to be pre-paid, but a student is allowed to make part payments. Means they can pay the whole amount using different payment methods like cash, debit card, credit card etc.

1. The database should include suitable validation and integrity checks as well as appropriate referential integrity checks.  That is, AS A MINIMUM, your system should ensure that the following events cannot occur:

Referential Integrity Constraints:

• • • A class cannot have a student who is not registered means does not exist in the system beforehand.
    • A student cannot be deleted from the system if a student is enrolled in a current dance class. Similarly an instructor cannot be deleted from the system if they are instructing a current dance class.
    • An enrolment cannot be deleted if a payment has been made and dance class is allocated to the student. SDA does not cater any refunds.
    • Only Dance styles entered in the database can be attached to a dance class. A new form of dance style for a dance class may be only added to the system after the required dance style is added first.
    • Rooms cannot be deleted if there are dance classes already allocated to that room.
    • Payment methods cannot be deleted once payment methods have been recorded against payments.

Task: 2

• Using MySQL, you are required to develop a demonstration prototype system that handles new Registrations, enrolments, timetabling, room allocations and payments.  

Use MySQL to create a new database called SDADB.  Create tables according to your ERD.  Follow a standard naming convention for table names and also field names.    Avoid using spaces and any special characters in table and also field names. Use underscore_case or use camelCase to separate parts of a name. 

• Add at least two new records into the appropriate tables to include students details, instructor details, location details, dance styles, dance class timetable, rooms details etc.

Task: 3 Use the Sydney Dance Academy (SDADB) database that you created in MySQL to design and execute SQL queries that answer the following questions.   Number your answers to each question clearly.  The answer to each question must be tabulated as shown in the example below and include the SQL statement and also the output that is produced when you execute the statement in your database.  The output includes the records that are listed and also the message that appears when you run the SQL statement. Do not use screen captures to display the SQL statement or the output.   You should right-click on the MySQL Command Prompt window; choose Mark and then press the [Enter] key to Copy and then Paste into your Word document that includes the answers to all questions.   Format and indent the clauses in your SQL statements for better readability and understanding as shown in the example above.  Statements must be syntactically and semantically correct. Format both the SQL and also the Output in Courier New

1. List the first name, last name of students (join student’s first and last name with a space in between and use the alias Student Name for the column heading), and email address. Sort the output in ascending order by the Student Lastname.
2. List all the dance styles that has got a rate higher than $12 per class.
3. Retrieve Students’ last name, first name, mobile, and email for all students who do not have a mobile phone number recorded in the Student table. Sort the output in ascending order by the Student last name, and then first name.
4. List all Student last name for all students that have a suburb that has the word ‘hill’ anywhere in the suburb name.  Sort the output ascending order by the last name of student.
5. List all the instructors who are currently timetable for 5 or more dance classes.
6. For each student who has been enrolled to dance classes multiple times display the total amount of money they paid.
7. Count the number of students enrolled in each type of Dance style.
8. Display the current timetable of the classes done in Room 1 and 3 at Chatswood location.
9. Display all the students’ information who are currently not enrolled in any of the dance classes.
10. List the payment method description and sum of payment amounts for all payments which were made after 1-July-2017.

Task: 4

1. Write a page to the department manager as a personal reflection that describes your experience building the database.  You can discuss any challenges / difficulties that you experienced or solutions that you found. Comment on any limitations and / or strengths of your database design. Comment on whether your database meets all the system requirements as specified in Part A Question 4.  Avoid making excuses or comments that reflect negativity. Include an acknowledgement of all students you have spoken to about the assignment.

Task: 5

1. Deliverables for Parts A, B, & C must be printed as a report with a cover sheet attached.  See Administrative Details on page for more details. Your report must include headers and footers that include your name, student number, unit name, assignment name, and page numbers.  Your report must be checked for spelling and grammar. Your report must also be formatted so that it is well set out and easy to read.

This ISY1002(ISY 103): IT Assignment has been solved by our IT experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics & referencing style.