MAN6905 Databases and Business Intelligence
Assignment 1 – Database Design and Programming (50 Marks)
Due Date: Week 11 – 11th Oct, 2020 (23:59pm)
PART 1 Database Design and Implementation
SPECIFICATIONS
Read the following Case study carefully. You will be asked to create a data model, that complies with Referential Integrity and being in third normal form.
CASE STUDY
“Property Rental WA” offers specialist property services to rent real estate throughout Western Australia. The company has more than 10 branches across Western Australia and has at least one branch in every major city such as Perth and Joondalup. The company plans to develop a website to provide online rental service to tenants and landlords. Potential tenants can retrieve property details posted by landlords. The website also allows potential tenants to make appointments to view chosen properties including apartments, houses, villas and townhouses. These tasks can all be performed 24 hours of the day, 7 days a week on the website.
Potential tenants are free to retrieve property details (such as address, size, price, property type and so on) but they must register before they can make appointments to inspect properties. The website needs to be able to store client details such as name, date of birth, address and so on. A valid email address and phone number must also be provided and stored.
Once an appointment is confirmed, details of the appointment will be saved and a confirmation email will be sent to the potential tenant. A client service representative will be assigned to follow up with the appointment by phone calls.
CIO of Property Rental WA, Clinton Wilson, has asked your team to come up with a database that can support the online rental services website. Clinton wants the new database to be able to store data about landlords, tenants, properties and appointments. He also wishes to use this data to generate the following reports using SQL:
• A list of the most/least popular properties in a particular year (per property type)
• A list of staff who handles the most/least appointments in a particular month and year
• A list of potential tenants who has never made an appointment to inspect properties
• Display name, age and gender of the oldest female potential tenant(s) as well as the youngest male potential tenant(s).
For PART 1 of the assignment, you are required to submit:
• an ER Diagram
• SQL Script to create tables. Each table creation script must include two extra queries to demonstrate data insertion
• at least 5 SQL queries to generate the business reports
• submit any Business Rules or Assumptions made
PART 2 Business Intelligence
There are many large datasets available online, from governments in particular, such as: http://data.london.gov.uk/ https://data.gov.au/
Choose One large dataset and analyse the dataset with pivot tables. Document the insights and trends that you find during the analysis.
For PART 2 of the assignment, you are required to submit the following for your chosen dataset:
1. Dataset details:
a. The URL of the dataset.
b. A description of the dataset.
c. A screen capture showing the first page of the Excel spreadsheet containing the dataset.
2. Screen captures of ONE pivot table analysis on the dataset utilised together with ONE chart output that you have created.
3. A clear written analysis of your findings from the pivot table and chart.
a. Focus on the insight you are trying to gain and the business intelligence you derive.
b. Try differentiating dimensions from facts in the dataset. You will usually have dimensions as the rows (time, location, product type) and facts in the centre (revenue, cost etc).
Marking Homework help – Summary
Mark (Total 50)
PART 1 (40 Marks)
Entity
– Any essential entity hasn’t been included, deduct 2 each
– If entity appears that doesn’t make sense and hasn’t been substantiated in the assumption, deduct 1 each
– Other excessive entity, deduct 1 each 6
Attribute
– If an attribute appears in a place where an anomaly will occur (insert/update/delete), deduct 1 each
– Any essential attribute hasn’t been included, deduct 0.5 each
– If an attribute appears that doesn’t make sense, deduct 0.5 each 6
Relationship
– If a redundant relationship has been added to show the same thing as another relation, deduct 2 each
– Any essential relationship hasn’t been included, deduct 1 each
– Incorrect cardinality (related to 1:M), deduct 0.5 each
– Incorrect cardinality (related to M:N), deduct 1 each
– Other incorrect relationship, deduct 0.5 each 6
Assumption 2
Tables Created by SQL Script
Cohesiveness of Tables and SQL Script
– Deduct 1 mark per indiscretion
– No Primary Key in a table, deduct 1 each
– No Foreign Key (and Reference) when needed, deduct 1 each
– Incorrect declaration of Primary/Foreign essay writing Key, deduct 1 each
– Incorrect SQL for Data Insertion, deduct 1 each
– No SQL for Data Insertion, deduct 1 each 10
Data Type
– Incorrect data type, deduct 0.5 each 3
Report
Business Questions / SQL Queries 7
PART 2 (10 Marks)
The URL of the dataset
A description of the datasets
A screen capture showing the first page of the Excel spreadsheet containing the dataset 1
Screen captures of one pivot table on the dataset utilised together the CHART output 4
A clear analysis of your findings:
Focus on the insight you are trying to gain
– try differentiating dimensions from facts in the dataset. You will usually have dimensions as the rows (time, location, product type) and facts in the centre (revenue, cost etc.) 5
