Problem
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 meter).
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, mobile phone number and indicate whether or not they are 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 increases 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:
• Consider the problem, identifying the entities involved, their attributes, and relationships among them.
• 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 a diagramming software.
• Make sure that your schema adheres to third normal form (3NF) requirements.
• For each attribute, identify the appropriate datatype and a constraint. For example, date of birth cannot be a date in the future or a date 150 years ago. As another example, 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).
Create a blank SQL database in Azure.
Write SQL queries to create the database tables and relationships
Write SQL queries to insert at least 10 rows of data in each of the tables.
Write SQL queries to create a 'View' providing following information:
The total number of residents in all buildings
The average age of residents in each building/story
The total number of residents in each building ordered by building number
Total number of bedrooms, bathrooms, kitchens, living rooms, and total unit area in each building
Create a Stored procedure that receives the Person ID as input and shows its residence history chronologically.
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.