CSE4110 - Database System A Real-Estate Office

Goal: The goal of the project is to provide a realistic experience in the conceptual design, logical design, implementation, operation, and maintenance of a small relational database.

Application Description :

The application consists of the operations of a real-estate ofce. The ofce needs to keep track of agents, buyers, sellers, properties on the market, and recently sold properties. This ofce focuses on homes rather than business real estate. The management of this real-estate ofce is not very computer literate. You are being asked to design the database, populate it with sample data (the management won’t allow you to test with live data because of privacy concerns), and to write several SQL queries to demonstrate the system. The ofce deals only on properties in Seoul.

To learn more about the application domain, look at the below real-estate web site named “zigbang”. It will serve as a useful guideline when you create sample data.

• zigbang: https://www.zigbang.com/home/apt/subways/375

Refer to actual listings on “zigbang” for detailed information and prices of properties, and create any data not available on “zigbang”, such as sellers and buyers, arbitrarily.

According to the client, similar to the data from “zigbang”, for studio or one-bedroom apartments, there must be at least one interior photo. For multi-bedroom apartments or detached houses, there should be at least one exterior photo and one foor plan each.

Project Requirements :

1. BCNF Decomposition

• Decompose your Relation Schema into BCNF form if they are not.

• Same process learned in lecture.

1. Physical Schema Diagram

• Afer creating an decomposed logical schema diagram, also creating Physical Schema diagram.

• Create the “physical” schema diagram in Erwin Data Modeler.

• Be sure to identify data types, domain, constraints, relationship type, allowing nulls.

1. Queries

The queries listed below are those that you have to fnd. We now call these query categories as TYPE. (total 13 diference query types)

• (TYPE1) Find address of homes for sale in the district “Mapo”. (1)

• (TYPE 1-1) Then fnd the costing between ￦ 1,000,000,000 and ￦1,500,000,000. (2)

(TYPE 2) Find the address of homes for sale in the 8th school district. (3)

• (TYPE 2-1) Then fnd properties with 4 or more bedrooms and 2 bathrooms. (4)

• (TYPE 3) Find the name of the agent who has sold the most properties in the year 2022 by total won value. (5)

• (TYPE 3-1) Then fnd the top k agents in the year 2023 by total won value. (6)

• (TYPE 3-2) And then fnd the bottom 10% agents in the year 2021 by total won value. (7)

• (TYPE 4) For each agent, compute the average selling price of properties sold in 2022, and the average time the property was on the market. (8)

• (TYPE 4-1) Then compute the maximum selling price of properties sold in 2023 for each agent. (9)

• (TYPE 4-2) And then compute the longest time the property was on the market for each agent. (10)

• (TYPE 5) Show photos of the most expensive studio, one-bedroom, multi-bedroom apartment(s), and detached house(s), respectively, from the database. (11)

• (TYPE 6) Record the sale of a property that had been listed as being available. This entails storing the sales price, the buyer, the selling agent, the buyer’s agent(if any), and the date. (12)

• (TYPE 7) Add a new agent to the database. (13)

4. Code Implementation

We will use Visual Studio 2019 and MySQL connector(ODBC). Implement C code to execute your queries in MySQL DBMS you built. The program must use fle input from text fles which have CRUD queries as text format.

e.g. CREATE, INSERT, UPDATE, DELETE.

The program has a user interface to handle each TYPE.

Program runs until the user puts ‘quit’ instruction.

Afer selecting one of types in menu, the user puts values of query as stdin (if necessary, see below) and then the program prints the results of query execution as stdout.

TYPE 1 & 2 & 3 & 4 has a submenu for subtypes.

Keep running each type of query until the user puts “0” in stdin (for all types of query). Afer exit from the query menu, go back to the select menu.

What to turn in :

• Decomposed Logical Schema Diagram (.png)

• Student_id.png (submitted flename) e.g. 20241234.png

• “Physical” Schema Diagram ERwin fle (.erwin)

• Student_id.erwin (submitted flename) e.g.

20241234.erwin

• C code (.c or .cpp) with README

• Student_id.c (submitted flename) e.g. 20241234.c

• Describe your code with comments.

• It should be able to build and execute in Visual Studio 2019 with MySQL C APL

• Plus, write and submit a README fle for your program.

• Report fle (.pdf)

• [project2]student_id.pdf (submitted flename) e.g. [project2]20241234.pdf

• Describe the detailed explanation about your Physical Schema diagram and ODBC implementation within MySQL that you made.

• MAKE YOUR OWN DESCRIPTION on physical schema and ODBC C language codes.

• Describe the detailed explanation how you decompose each relationship to BCNF, see testing one of your relationship results by simplifed test in lecture notes.

• Feel free to use any template you made.

NOTICE :

• 2024.06.13 (Thu) 17:00

• Submit your sof copy with title “[DBproject1]student_id” to Cyber Campus (sofcopy includes png, erwin, c code and pdf fle you wrote)

• Submit your hardcopy to the box in front of AS809 (1st class section) / AS909 (2nd class section) before the deadline. (hardcopy includes one decomposed logical schema diagram, one physical schema diagram and one report you wrote)

• DON’T COPY ANYTHING FROM YOUR FRIENDS AND WEB SOURCES. IF YOU VIOLATE THIS, YOU WILL GET F FOR THIS COURSE.