Data and Information Management Assignment

Context

Imagine you work for the public housing agency of a city in Australia, and you need to keep track of who is living in the agency's buildings over time. To help you in this task, you have decided to use a relational database for your record keeping. Your task is to design a database that allows you to capture the facts described below:

• The city has several public housing buildings. You want to record their names, locations (address), the year they opened, and their height in stories/levels.
• For each unit in the building, you want to keep track of the number of bedrooms, the number of bathrooms, whether the unit has a kitchen or living room, and the unit area (in square meters).
• The database should keep track of the households living in the units. For each member of a household, you want to record their name, date of birth, sex, and mobile phone number and indicate whether or not they are the head of the household (more than one person can share that distinction). The database should assign a unique ID to each person. The ID should start at 100,000 and increase by one automatically for each new record (person).
• You also want to keep track of when a household moved into and out of a particular unit. You want to be able to follow households as they move from one unit to another. Think about how you will find the unit that the household is currently occupying (i.e., what query would you write to find the current unit of each household).

Instructions

A. Conceptual design

1. 1. Consider the problem, identifying the entities involved, their attributes, and the relationships among them.
2. Draw an entity-relationship diagram that captures your thinking. Turn in your E-R diagram with your problem set. You must create your diagram using diagramming software.
3. Make sure that your schema adheres to the third normal form (3NF) requirements.
4. For each attribute, identify the appropriate datatype and a constraint. For example, a date of birth cannot be a date in the future or a date 150 years ago. As another example, the postcode must be a 4-digit number (This needs to be done for all attributes. You can make reasonable assumptions for the valid data ranges).

B. Create a blank SQL database in Azure. Use your surname as the database name.

How to access Azure? Here are your options:

1. Sign up for a trial student account using your ACU email https://azure.microsoft.com/en-us/free/students/ [This should be done using your ACU email - Does not need bank card details - cannot be used for the second time or after the trial period). Important Note: The free trial period is one month only. So, before you sign up, finalize your conceptual design (ERD). In other words, do not sign up too early.
2. Use the virtual lab environment (Explore relational data in Azure) but save your work (SQL code) in a document on your own device (not in the virtual machine) as it will be deleted from the lab each time you close the lab. Once you completed writing the SQL codes paste them back into the Azure SQL database in the virtual Lab and run while recording your presentation.

C. Write SQL queries to create the database tables and relationships

D. Write SQL queries to insert at least 10 rows of data in each of the tables.

E. Write SQL queries to create a ‘View’ providing the following information:

1. the total number of residents in all buildings
2. the average age of residents in each building/story
3. the total number of residents in each building ordered by building number
4. Total number of bedrooms, bathrooms, kitchens, living rooms, and total unit area in each building
5. Create a Stored procedure that receives the Person ID as input and shows its residence history chronologically.
6. Create a Stored procedure for moving a person from one unit to another unit. The Stored procedure should receive the Person ID and destination building/unit as input. Make sure to perform transaction management (using Commit/Rollback commands) to ensure data integrity during and after update execution.

F. Database security and ethical issues

1. Produce a report for the head of the Public Housing Agency and discuss the ethical, and security issues that need to be considered in storing and maintaining data and make any recommendations that you think are appropriate. Also, propose measures (including specific Azure security tools/features) to protect data against security threats and handle data ethically.

G. Presentation

• Use Zoom to record a short video presentation (3-4 minutes). In your presentation turn on your webcam (to include your face in the presentation) and share your screen (to show your SQL code in Azure. Run all queries you’ve written for questions in section E in order. Also, explain the commands/functions used for question in section E and how the query works).

This IT Computer Science Assignment has been solved by our IT Computer Science Expert at Exam Question Bank. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well-trained to follow all marking rubrics & referencing Styles. Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turn tin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.