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.

Reviews

Write a Review

 

Database Management System Questions & Answers

  Stack and queue data structure

Identify and explain the differences between a stack and a queue data structure

  Create relational schema of database in 3nf

A Relational schema of your database in 3NF, clearly indicating attributes, the data type of each attribute, primary and foreign keys, candidate keys, and which attributes are nullable, giving reasons. List any assumptions you need to make.

  Develop an activity relationship chart for four departments

A machine shop located on the outskirts of Los Angeles accepts custom orders from a number of high-tech firms in southern California.

  How is replication control achieved in ddbms

How is Replication Control achieved in DDBMS?What is CAP Theorem in Distributed Systems? How is it different from ACID in Relational DBMS?

  How much indirect factory wages and factory equipment

How much indirect factory wages and factory equipment depreciation cost would be assigned to the Customer Orders activity cost pool

  Most spreadsheet software like that shown in the

most spreadsheet software like that shown in the accompanying figure includes a kind of analysis tool where users can

  Determine the role of firewalls and encryption

Determine the role of firewalls and encryption, and auditing RDBMS that could assist in protecting information and monitoring the confidentiality and integrity.

  What tools does your organization use to deliver rich

q 1 name 3 common vulnerabilities in web applications. how does a company protect against these vulnerabilities?q 2

  Analyze the sales data to determine the true track record

You are going to determine the percent of asking price for each home sold and analyze the sales data to determine the true track record of the company in selling homes for the asking price

  Write an sql statement to display data for all of the column

Write an SQL statement to display data for SKU_Description and SKU. Write an SQL statement to Create A View to display data for unique WarehouseIDs.

  Create a trigger in your todo database

Create a trigger in your ToDo database that adds a new line to an audit table when you add a new task. Using the ToDo database that you created.

  Submit a description of your proposed database application

Submit a description of your proposed database application, some examples of potential attributes, your proposed DBMS, and a description of your DBMS experience/proficiency (novice, some experience, expert).

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd