Create a data model, Create a data model, that complies

Create a data model

Assignment Help Database Management System

Reference no: EM132609751

Part 1:

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.

"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:

Choose TWO large datasets and analyse them with pivot tables. Document the insights and trends that you find during the analysis. Address the following requirements and give FOR EACH 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).

Reference no: EM132609751

Questions Cloud

Research and defineterrestrial and in an aquatic habitat : Research and defineterrestrial and in an aquatic habitat the role that disturbance plays in maintaining species diversity in a

Briefly discuss the development of bio-prospecting : Briefly discuss the development of bio-prospecting with regard to Hoodia gordonii. Your discussion should include the CSIR and the San people of Southern Africa

Identity can request access to information asset : In identity and access management an identity can request access to an information asset.

Social inequalities in health have been documented : Identify one disease for which social inequalities in health have been documented ( heart disease, cancer, diabetes, depression) in two priority populations

Create a data model : Create a data model, that complies with Referential Integrity and being in third normal form - Focus on the insight you are trying to gain and the business

What earned value management concepts : Your Project Sponsor pulls you aside and admits that he has no idea what earned value management concepts (EVM),

Analyzing a complete skeleton : Imagine that you are a forensic detective and you have been tasked with analyzing a complete skeleton.

What amount should be shown in Valley December : As of December 31, 2015, Valley Company has $16,920 cash in its checking account, What amount should be shown in Valley's December 31, 2015

What are the primary internal organization considerations : What are the Primary Internal Organization Considerations for the Development of a Strategic Plan? Explain in detail and discuss with an example.

User Account

All Pages