Assessment Handouts Dbfn Assessment 3 T1 2020 Kent Instituti ✓ Solved
Assessment Handouts Dbfn Assessment 3 T1 2020pdfkent Institute Austra
Develop an ERD, convert it to relational tables, build a database in MS Access, create forms, input data, write queries, and generate a report based on a rental property management scenario involving properties, customers, staff, maintenance, and other related entities. The task involves designing a relational database with proper constraints, normalization to 3NF, and demonstrating understanding through data entry, query creation, and reporting.
Sample Paper For Above instruction
The management of rental properties requires a comprehensive and efficient database system to handle information about properties, customers, staff, maintenance activities, and contractual agreements. This paper details the design and implementation of such a system, including the development of an Entity-Relationship Diagram (ERD), normalization of relational tables, physical database construction using Microsoft Access, and the creation of user interfaces, queries, and reports. This holistic approach aims to facilitate smooth data management and support decision-making processes effectively.
Introduction
Managing rental properties involves multiple interconnected entities, including property details, customer applications, contractual agreements, staff involved, and maintenance activities. An effective relational database can streamline this management process by organizing data systematically, ensuring data integrity, and providing easy access for analysis and reporting. The primary goal of this project is to design such a database tailored to an Australian property rental business, incorporating the essential attributes and relationships outlined in the scenario.
Analysis and ERD Development
The first step involves analyzing the requirements and defining core entities: Property, Customer, Application, Contract, Property Manager, External Employee, and Maintenance. Each entity has specific attributes; for example, Property includes type, address, bedrooms, bathrooms, rental price, and availability date. Customers have contact details, and applications link customers with properties, capturing application status and approval details. Property Managers are assigned to properties, and external employees perform maintenance tasks, which are recorded with descriptions and completion dates.
Relationships between these entities are critical. A property is managed by one property manager, but one manager can oversee many properties (one-to-many). Customers can submit multiple applications; only one will be approved, linked via the application’s status. Maintenance tasks are linked to external employees and specific properties, with each task representing a one-to-many relationship. Participation constraints are identified—for example, mandatory involvement of property managers for all properties, and optional maintenance activities.
The ERD was created using diagrammatic tools, illustrating entities, attributes, and relationships, with cardinality and participation constraints explicitly marked. The ERD ensures clarity in understanding entity interactions and supports accurate translation into relational tables.
Normalization of Tables
Following ERD development, the next step was converting entities into tables in third normal form (3NF). This process involved identifying primary keys, foreign keys, and dependencies among attributes. For instance, the Property table includes primary key PropertyID, with dependent attributes like property type, address, and rental details. The Application table references PropertyID and CustomerID as foreign keys, linking applications to properties and customers respectively.
The normalization process removed redundancy and update anomalies by ensuring each table depended solely on its primary key and all non-key attributes were functionally dependent on the primary key. Dependency diagrams were created to visualize these relationships, confirming adherence to 3NF.
Implementation in Microsoft Access
The database was built in Microsoft Access, establishing tables with appropriate primary and foreign keys, enforcing referential integrity constraints, and setting data types to match attribute requirements. Indexes and validation rules were added to maintain data accuracy. Data entry forms were designed for easy input and updating of records. Sample data was entered into each table, reflecting real-world scenarios such as multiple properties, tenants, and maintenance tasks.
User Interface and Data Management
Forms were created to allow users to input and update information conveniently. The forms facilitated data entry for properties, customers, applications, contracts, property managers, external employees, and maintenance activities. These forms incorporated dropdowns, date pickers, and validation checks to reduce input errors.
Queries and Reporting
Three queries were developed:
1. A list of properties currently available for rent, including properties' attributes.
2. A report identifying maintenance tasks scheduled within a specific period.
3. A list of tenants with current active contracts, including contract durations and rental terms.
These queries demonstrate data retrieval capabilities aligned with management needs. Additionally, a comprehensive report was generated summarizing property details, active contracts, and maintenance schedules, assisting managers in decision-making.
Conclusion
This project successfully modeled a rental property management system through detailed ERD design, normalization to 3NF, and practical implementation in MS Access. The database supports efficient data entry, updates, and retrieval, which are essential for operational efficiency in real estate management. The combination of forms, queries, and reports ensures that property managers can oversee their business effectively while maintaining data integrity and supporting strategic planning.