CSE4110 - Database System Project1. E-R design and Relational Schema design

A Real-Estate Ofce

“You are a DBA on this web site”

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.

The queries we list below provide some useful hints as well.

Project Requirements :

1. E-R Model

• Construct an E-R diagram representing the conceptual design of the database.

• Be sure to allow us to store information without unnecessary redundancy.

• At minimum you must include all the entity and relationship sets implied by this description. You may go beyond the minimum. Remember that the manager who defned the specifcations is not computer literate so the specifcations should not be viewed as necessarily being precise and complete.

• Be sure to identify primary keys, relationship cardinalities, foreign keys and so far.

1. Relational Schema Diagram

• Afer creating an E-R model, reducing it into Schema diagram.

• Create the schema diagram in ERwin Data Modeler we discussed in practice session.

• Be sure to allow us to store information without unnecessary redundancy.

Be sure to identify primary keys, foreign keys, relationship cardinalities, relationship type, allowing nulls and so far.

• Every entity should have a name and primary key(s).

1. Queries

The queries listed below are those that you have to fnd later. They may provide further hints about database design, so think about them at the outset of the project.

1. Find address of homes for sale in the district “Mapo” costing between ￦1,000,000,000 and ￦1,500,000,000.

1. Find the address of homes for sale in the 8th *school district with 4 or more bedrooms and 2 bathrooms.

*school district = 학군

1. Find the name of the agent who has sold the most properties in the year 2022 by total won value.

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

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

1. 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.

1. Add a new agent to the database.

What to turn in :

• E-R diagram (not hand drawn) made by any chosen tools (e.g.https://online.visual-paradigm.com/diagrams/features/ erd-tool/ or you can feel free to use any tools such as MS Powerpoints, keynotes etc.)

• Relational Schema diagram ERwin fle (.erwin)

• student_id.erwin (submitted flename) e.g. 20241234.erwin

• Be sure to use the same display options in practice session. ( IE notation, display relationship cardinality)

• Use the title in the schema diagram and description if needed.

• Report fle (.pdf)

• [project1]student_id.pdf (submitted flename)

e.g. [project1]20241234.pdf

• Describe the detail explanation about your E-R model and Schema

diagram that you made.

• MAKE YOUR OWN DESCRIPTION on every entity and relationships you made.

• Feel free to use any template you made.

NOTICE :

• 2024.04.19(Fri) 17:00

• Submit your sof copy with title “[DBproject1]student_id” to Cyber Campus(sofcopy includes erwin 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 E-R model picture 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.

DATABASE SYSTEM PROJECT 6