Data Driven Web Technologies
Data Driven Web Technologies
Assignment 1
Youre working for the new RealRealEstate company, and you have several data sets that need to be merged so that you can extract interesting information about housing and incidents reported to police from each suburb. The aim is to eventually provide a website where verified people can explore the data and revisit to their past searching. At present, the data can be only viewed in the numerous supplied excel spreadsheets.
The current database holds the information about different types of housing (e.g., unit, house, townhouse), including number of bedrooms, number of toilets, number of washrooms, separate living room, lounge and dining area, study room, front-yard, backyard, energy used like electricity, gas, solar etc, internet options, parking options, lot size, location, and price by purchase and rental. There is also a series of data supplied about recent incidents that police responded to. In addition, they would like to capture and store some extra data including:
Nearest shopping malls with distance,
Zones and close-by schools (public vs private, preschool, primary, secondary) with distance,
Nearby important landmarks or attractions,
Additional data you think may be relevant to property data or incident data (please add at least 3 types of data)
The original data sets and their explanations can be found on Kaggle:
https://www.kaggle.com/datasets/syuzai/perth-house-priceshttps://www.kaggle.com/datasets/anthonypino/melbourne-housing-markethttps://www.kaggle.com/datasets/karthikbhandary2/property-rentals
And the crime dataset can be found https://data.sa.gov.au/data/dataset/crime-statistics.
Tasks
Below is a list of the tasks you need to complete:
Design a Domain Model Diagram
Clearly identify all relevant keys (including candidate keys).
Include appropriate relationships.
Capture all the relevant data reducing the possibility of anomalies. Where possible, re-use existing record information.
Provide Justification for your design decisions.
There are multiple ways to design this database, you need to justify why you chose specific classes/tables and associations.
The design needs to meet 3rd normal form.
Show one example of the Normalisation for one of the excel spreadsheets.
Translate your Domain Model Diagram into Table Schemas.
Clearly identify all relevant keys.
Construct Table creation statements
Using appropriate data types and sizes.
Using the conventions discussed in class.
Provide example queries of how you might extract data from the excel spreadsheet and use it to populate your tables. For this, you treat the excel sheet as a standard table in the relational database. If you really want to test it out, here are a couple of instructions on how to do it:
https://www.sqlshack.com/import-data-excel-file-sql-server-database/
https://sqlspreads.com/blog/how-to-insert-data-in-excel-to-sql-server/
Submission
Submit Task 1 in a single Word Document clearly identifying your domain model, justification, and normalisation check. Save your Word Document file with file name username_DDWT_a1t1, where the username is your university network username/email ID (e.g., bonjy007).
Submit Tasks 2 4 in a separate Word Document, clearly identifying each task. In your table creation statements make sure your data types are meaningful to the example data and provide justification if necessary. Save your Word Document file with file name username_DDWT_a1t24, where the username is your university network username/email ID (e.g., bonjy007).
Marking Scheme
Domain Model UML [40]
Classes, Attributes, Keys [10]
Style [10]
Normalisation Form [10]
Justification [10]
Schemas [25]
Relations and Attributes [10]
Keys [10]
Consistency with the Domain Model applies to both! [05]
Table Creation [25]
SQL Statements [10]
Constraint names and other conventions [10]
Consistency with the Schemas and Domain Model also applies to both! [05]
Table Population from original data [10]
Example SQL Statements to move clean data into at least 3 tables of the new DB structure. [10]
Extensions
All extension requests require a valid medical certificate or supporting documentation, or they will be declined without consideration.
