MIS602 Assignment 2 Page 1 of 5
| ASSESSMENT BRIEF |
|
| Subject Code and Title | MIS602 Data Modelling & Database Design |
| Assessment | Two – Database programming evaluation practical |
| Individual/Group | Individual |
| Length | See below for details |
| Learning Outcomes | b, c |
| Submission | 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 Marks |
Context:
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.
MIS602 Assignment 2 Page 2 of 5
Note: The following tables give you an idea of what sample data looks like:
Building
| Building_ID | Building_Name | Built_year | Building_Capacity | Building_Address |
| 1001 | Lilly Pilli | 1995 | 5000 | Wakefield street |
| 1002 | Early Settler | 2006 | 2000 | Flinders Street |
| 1003 | Horizon East | 2018 | 5,000 | Maldives Street |
| 1004 | Ocean Blue | 2020 | 15,000 | Wakefield Street |
Apartment
| Apartment_ID | Apartment_Rent | Total_rooms | Building_ID | Owner_ID |
| 1001 | 500 | 2 | 1001 | 2001 |
| 1002 | 600 | 3 | 1001 | 2002 |
| 1003 | 1000 | 2 | 1001 | 2004 |
| 1004 | 389 | 1 | 1003 | 2003 |
| 1005 | 400 | 1 | 1004 | 2001 |
| 1006 | 590 | 2 | 1001 | 2002 |
| 1007 | 345 | 2 | 1003 | 2004 |
| 1008 | 789 | 3 | 1004 | 2001 |
Owner
| Owner_ID | Owner_Fname | Owner_Lname | Owner_Email | Owner_Phone |
| 2001 | Hazel | Alex | hazel@gmail.com | 04023466 |
| 2002 | Saber | Khan | khan@gmail.com | 04034566 |
| 2003 | Adam | Smith | Adam@gmail.com | 04345567 |
| 2004 | Lisa | Owen | owen@gmail.com | 04078066 |
MIS602 Assignment 2 Page 3 of 5
For tasks, 4-24, make sure to insert data that satisfy the criteria before executing them.
| Task # | Task Description |
| Task 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. |
MIS602 Assignment 2 Page 4 of 5
| 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 Instructions
This assessment comprises of TWO parts.
PART I
Requirements: 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.
PART II
Requirements: Based on the assignment you received, you are required to write a critique of approximately 500 words.
Please note that your critique should provide constructive feedback highlighting the strengths and areas for improvement
in the report, as well as any new knowledge gained from evaluating the authors report. You should provide feedback on
overall design of the database, and fulfil the following minimum requirements:
| |
Comment on the overall usage of SQL statements and commands used. Provide constructive criticism on how the author can improve their understanding of SQL statements. Provide some useful readings the author may pursue to help in developing the SQL skills in the future. |
Submission: Your submission must composed of the following files, which must be submitted to Blackboard in a single
zipped file on or before Week 7, Sunday 23:59 (Sydney time)
1. Critique of 500 words.
2. The author’s report, which your critique was based upon.
Note: All non-submission or late submissions will be treated in accordance with the late assessment policy of the
university.
Learning Rubric: Assessment Three
| Assessment Attributes |
Fail (Unacceptable) 0-49% |
Pass (Functional) 50-64% |
Credit (Proficient) 65-74% |
Distinction (Advanced) 75 -84% |
High Distinction (Exceptional) 85- 100% |
| Understanding of the SQL commands 30% |
Demonstrates limited understanding of SQL commands |
Fair understanding of SQL commands demonstrated. May neglect to provide resources or that these are cursorily provided without reference to specific areas in the source. |
Good understanding of SQL commands demonstrated. May provide a limited number of sources the peer can use to develop their technique from. |
Very good understanding of SQL commands demonstrated. Makes recommendations to other external sources the peer can access to develop their understanding. |
Outstanding understanding of SQL commands demonstrated through recommendation of other sources with specific references to components of it that the peer will benefit from. |
| Completion of SQL Statements for all Tasks 40% |
Less than 50% Tasks are completed |
50-64% Tasks completed | 65-74% Tasks completed | 75-84% Tasks completed | 85-100% Tasks completed |
| Effective communication 30% |
Difficult to understand for audience, no logical/clear structure, poor flow of ideas, argument lacks supporting evidence. Audience cannot follow the line of reasoning. |
Information, arguments and evidence are presented in a way that is not always clear and logical. Line of reasoning is often difficult to follow. |
Information, arguments and evidence are well presented, mostly clear flow of ideas and arguments. Line of reasoning is easy to follow. |
Information, arguments and evidence are very well presented; the presentation is logical, clear and well supported by evidence. Demonstrates cultural sensitivity. |
Expertly presented; the presentation is logical, persuasive, and well supported by evidence, demonstrating a clear flow of ideas and arguments. Engages and sustains audience’s interest in the topic, demonstrates high levels of cultural sensitivity. |
MIS602 Assignment 3 Page 5 of 5
The post MIS602 Data Modelling & Database Design appeared first on My Assignment Online.