SOLUTION AT Australian Expert Writers
aASSESSMENT BRIEFSubject Code and Title MIS602 Data Modelling & Database DesignAssessment Two – Database programming evaluation practicalIndividual/Group IndividualLength See below for detailsLearning Outcomes b, cSubmission Part 1: Thursday 11:59 PM AEST/AEDT of Module 4.2 (week 7)Part 2: Sunday 11:59 PM AEST/AEDT of Module 4.2 (week 7)Weighting 35%Total Marks 35 MarksContext:The MIS602 Data Modelling & Database Design subject is designed for you to progressively add to your understanding of data and database management and its relevance with in business context. It also introduces you to some of the key features of database management system and designing database systems that will feature in later modules of this topic. In order for you to do well in this subject, it is imperative that you undertake all of the learning activities in the modules. The learning activities are presented as a way of scaffolding your learning so that you can attempt the building blocks of the assessments and be in a safe environment to fail and to learn from them. Therefore, doing your learning activities and seeking feedback from them from peers and from the learning facilitator is the single best way of preparing for doing well in this assessment.Introduction:You are required to create the database tables as per the ERD below, and then generate the SQL tasks given in the table.Note: The following tables give you an idea of what sample data looks like:BuildingBuilding_ID Building_Name Built_year Building_Capacity Building_Address1001 Lilly Pilli 1995 5000 Wakefield street1002 Early Settler 2006 2000 Flinders Street1003 Horizon East 2018 5,000 Maldives Street1004 Ocean Blue 2020 15,000 Wakefield StreetApartmentApartment_ID Apartment_Rent Total_rooms Building_ID Owner_ID1001 500 2 1001 20011002 600 3 1001 20021003 1000 2 1001 20041004 389 1 1003 20031005 400 1 1004 20011006 590 2 1001 20021007 345 2 1003 20041008 789 3 1004 2001OwnerOwner_ID Owner_Fname Owner_Lname Owner_Email Owner_Phone2001 Hazel Alex email@example.com 040234662002 Saber Khan firstname.lastname@example.org 040345662003 Adam Smith Adam@gmail.com 043455672004 Lisa Owen email@example.com 04078066For tasks, 4-24, make sure to insert data that satisfy the criteria before executing them.Task # Task DescriptionTask 1 Create a schema called “BuildingGo” and then write DDL queries to create the three tables with relevant keys as suggested in the above diagram and the sample data shown in the tables.Task 2 Write queries to insert 5 records into Owner and Building each.Task 3 Write queries to insert 10 records into the Apartment table.Task 4 Write a query to display all the information about the buildings in the Building table.Task 5 Write a query to display the building names of all buildings in the Building table.Task 6 Write a query to display all the building names and their capacity.Task 7 Write a query to update the Building_Capacity of ‘Lilly Pilly’ to 2000 people.Make sure to insert some data that satisfy the criteria before executing the query.Task 8 Write a query to display the Building_ID and Building_Name of all the buildings with a capacity of above 3000 people.Task 9 Write a query to increase the rent of all apartments by 2% for all the apartments of Ocean Blue.Task 10 Write a query to display all the details of the apartments owned by Owner_ID ‘2003’.Task 11 Write a query to display all the unique Building_Locations.Task 12 Write a query to display Building_Name and Built_Year for all buildings built in 2001.Task 13 Write a query to display the list of all the Builidng_Names with Buiding_capacity in the range of 1000 – 2000 people in descending order.Task 14 Write a query to display the total number of apartments in the Apartment table.Task 15 Write a query to display the Owner_ID and the total number of apartments owned by each owner in ascending order.Task 16 Write a query to delete the record of the owners whose Owner_Fname contains the word ‘James’.Task 17 Write a query to display all the apartments owned by the Owner ‘Hazel’ as the Owner_Fname.Task 18 Write a query to display all the apartment details and their corresponding Building_Names.Task 19 Write a query to display all the apartment details in Building ‘Ocean Blue’.Task 20 Write a query to display all the building names having more than 5 apartments along with total count of apartments for each building displayed.Task 21 Write a query to display Owner_ID and Owner_Fname of all the owners who do not own any apartments.Task 22 Write a query to display the building name, which has the apartment with the lowest weekly rent.Task 23 Write a query to display all the Apartment_IDs, Apartment_Rent and their owner names of the apartments, which has a rent greater than 600 per week ordered in descending order by owner name.Task 24 Write a query to display the details of all apartments including the Apartment_ID, Apartment_Rent, associated building name and owner details.Submission Requirements and InstructionsThis assessment comprises of TWO parts.PART IRequirements: Complete all the SQL tasks given in the table above. After completing the SQL tasks, you are required produce and experience/reflection report (1000 words) in a word document summarizing your experience of doing these tasks.Submission: Your submission must composed of the following files, which must be submitted to Blackboard on or before Week 7, Thursday 23:59 (Sydney time)1. SQL file (24 tasks) with results and outputs in a word document.2. Experience/Reflection report.Note: On the following day, you will receive another student’s assignment via email to review, for which you are required to write a short critique. This is explained in the following section.
- Assignment status: Already Solved By Our Experts
- (USA, AUS, UK & CA Ph. D. Writers)
- CLICK HERE TO GET A PROFESSIONAL WRITER TO WORK ON THIS PAPER AND OTHER SIMILAR PAPERS, GET A NON PLAGIARIZED PAPER FROM OUR EXPERTS
QUALITY: 100% ORIGINAL PAPER – NO PLAGIARISM – CUSTOM PAPER