Part 1- Introduction The report contains the...
ISYS5000 Designing Database Systems Proof Reading Services
This unit explains principles of relational database modeling and design, containing creation of a relational model from a conceptual entity-relationship model, integrity of data and databases, tables, indexes, and use of structured query language SQL as a database interface language.
1. Apply what you have learned in this unit by designing a database and implementing it and provide some functionality in the form of SQL scripts to support the business processing requirements specified in this assignment. An assignment is a learning tool as it will take you through the stages that required designing and implementing a database.
2. To get experience working in a team as it would be in a real-life project.
1. Show the ability to normalize given data to a proper form
2. Design and create a secure database
3. Create a relational database schema based on a conceptual model and any other data storage requirements otherwise identified
4. You need to use SQL to construct, secure and access the database.
You will be given a case study of a real estate firm. Based on the given ER Model and Entity and Relationship Descriptions, design and implement a database to:
a. Store the information specified in the ER model
b. Support the business processing requirements specified in this document.
Business Processing Requirements
You need to write SQL scripts to as part of application design to provide the functionality required to fulfill the below-given processing requirements. Use SELECT statements for the reporting scripts and DML statements for the data maintenance scripts.
To check the reporting scripts, create as many records as needed.
1. List the details of agents who have been employed for more than 3 months but who have not sold any property they have been assigned to sell. The following details are required:
2. Display details of the employee who has sold the highest number of listed properties. Display the employee's name, office suburb and a number of properties sold.
3. Display the total income of all employees in each sales office. The income for real estate agents is calculated as salary plus salary multiplied by the sales commission. Sort your output by total income. Display the following details:
4. Find the breakdown of employees for each office. Display the office suburb, total number of employees in the office, total number of agents in the office and total number of non-agents in the office. Sort your output by a number of employees.
5. List offices where the total number of the properties currently listed by the office is 4 times more than the number of agents in that office. Display the office number and the total number of employees in the office.
6. List real estate agents who are assigned properties that are not listed with their office. Also, provide details of the manager of the office that assigned the property from their office to an agent outside the office. Sort your output by the name of the agent.
7. List the name of clients who are the sole owners of more than one property. Sort your output by the number of properties.
8. Display details of properties that have been on the market for more than 6 months and have not been sold. Sort the list in descending order by the period the property has been available within sales office. The information required is:
9. Display the names of mentors and the number of employees they mentor.
DATA MAINTENANCE SCRIPTS
You’re required to write the SQL data maintenance scripts mentioned below to provide some basic functionality to the user.
Note:if a question needs more than one DML statement, save each statement in a separate script and execute the scripts in the suitable sequence when testing.
1. A properly owned by a new client has to be listed. Write scripts that will:
a. Record the property and allocate the property to the office in the property’s suburb.
b. Record the owner details.
Note:The only data values specified in the scripts should be the details of the client and property.
2. An agent has left the company. Write scripts for that will
a. Re-assign the properties allocated to the agent to the manager of the agent’s office
b. Remove details of the agent
Note:the only data values specified in the scripts should be the ID of the employee that has left the company.
OVERVIEW OF ASSIGNMENT REQUIREMENTS
Provide the below-mentioned deliverables in your assignment in the following order:
1. A set of relations derived from the ER model provided.
2. A physical data model of the database Assignment.
3. A set of table instance charts and tables.
4. The SQL data manipulation scripts.
5. Data in each table.
6. The SQL reporting scripts.
Challenges students may face in this assignment
In completing ISYS5000 Assignment, students may face several feedbacks and problems like designing database, using SQL, etc. Explore ISYS5000 in depth with OZ Assignments’ technical experts and secure best grades in your exam for this assignment. We provide help and academic guidelines to resolve students’ problems for this assignment. We are aimed to help students in the best way to help them in living stress-free student lives.