CS241 - Project Solved

    • INSTRUCTIONS

        (1) Read the entire description before you begin your work.

        (2) The specifications given are from end-users, so they may not be precise and complete. State all your assumptions.

    • PROJECT DESCRIPTION

A DBMS needs to be implemented for a real estate office, that sells houses and apartments within a city. The office needs to store a lot of data, specifically:

    • agents: An agent is responsible for interacting with buyers and sellers, to show properties to them and facilitate sales. An agent may also interact with people to give properties on rent.

    • details of homes/apartments: The address, selling price, rent, details of the house (example: size: 1500 sq. ft,

number of bedrooms: 2), year of construction

    • DATA GENERATION

You can see magicbricks.com, 99acres.com, makaan.com etc. for good examples of data.

    • PROJECT REQUIREMENTS

        (1) E-R Model

            (a) Construct an E-R diagram representing the conceptual design of the database.

            (b) Be sure to identify primary keys, relationship cardinalities, etc.

        (2) Relational Model

            (a) After creating an initial relational design from your E-R design, refine it based on the principles of relational design.

            (b) Create the relations in MySQL.

            (c) Create indices (indices will be taught later) and constraints as appropriate.

            (d) If, as you refine your design, you discover flaws in the E-R design, go back and change it (even if the earlier design passed the checkpoint.) Your final E-R design must be consistent with your relational design.
        (3) Populate Relations

            (a) Include enough data to make answers to your queries interesting and nontrivial for test purposes.

            (b) You may find it helpful to write a program to generate test data.

        (4) Queries: Run enough queries to ensure that your database is populated the way you intended it to be. Given below are the queries that you must demonstrate. You may be asked to demonstrate additional queries.
            (a) List the houses in your city (for example Guwahati) that are built later than 2020 and are available for rent.

            (b) Find the addresses of the houses in your city costing between Rs.30,00,000 and Rs. 50,00,000.

            (c) Find the addresses of the houses for rent in G.S.Road (you can use the name of another locality if your city is different) with at least 2 bedrooms and costing less than Rs.15,000 per month.

            (d) Find the name of the agent who has sold the most property in the year 2023 by total amount in rupees.


.

1
2

        (e) For each agent, compute the average selling price of properties sold in 2018, and the average time the property was on the market. Note that this suggests use of date attributes in your design.

        (f) List the details of the most expensive houses and the houses with the highest rent, in the database.

    (5) Interfaces: There are three types of users who access the database. Each may need a different interface:

        (a) The database administrator (you) may use SQL via the command line.

        (b) The real estage agent’s office, to get 1) sales reports for each agent, consisting of the sale dates, property details and selling price and 2) how many properties have been given on rent by each agent for what amount, in which area, when.

        (c) Agents, to update the database when a property is rented or sold.

These interfaces can be built as 1) Web applications. 2) A standalone Java application with a GUI (for e.g with Swing) 3) A standalone Java application with a command line interface 4) Any other GUI tool you may know

    • DELIVERABLES

The last date for each item for CS1(CS2) are as given below.

    (1) ER diagram, with assumptions, in a document - 24 March 2023 (21 March 2023) (maximum two pages). You can draw this with any tool or this can be hand-written.

    (2) Relational design, with data populated - 31 March 2023 (28 March 2023) (Provide the SQL tables and their primary and foreign keys).

    (3) Interface for the database administrator with all the queries working, as demo - 17 April 2023 1 (11 April 2023).

    (4) Interface for real estate office and agents, as demo -21 April 2023 (April 18, 2023)

For items 3 and 4 above, you will be asked to explain parts of your code and your data.



























    • In lieu of the lab on 25 March