Description
Business Use Case Scenario
This case involves a Database creation for a small real estate agency owned by John Wick. In
fact, his agency is so small that he is the only agent in the company along with a part-time data
entry assistant. John wants to have a database that will keep track of key information for his
agency. John has asked you to develop the database that will support a Website that is under
development by another contractor.
Firstly, he would like to store information about listed properties like: the address (street, city,
province and postal code), # of bedrooms, # of bathrooms, square area (ft2
) and listing (selling)
price. Some properties are single-family homes and John wants to store the lot (land) size;
other properties are condos and he needs to display the monthly maintenance fee.
Information about the area in which each property is located is also important. John wants to
track the name of the area, along with the names of the schools. Those include the high
schools, elementary schools and middle schools. He wants to also store general comments
about the area in paragraph form.
John uses a variety of advertising outlets such as newspapers, magazines and Web sites to
advertise properties he is selling. He wants to track which advertising outlet is being used to
promote each property and that advertising may repeat several times (in the same outlet).
John also wants to know when each ad was placed and how much does it cost. And of course,
he will store the name and main phone number of the advertising outlet.
He wants to store information about each client as well like: first and last name, main contact
number and type along with an e-mail address. Client’s must be categorized such that he will
know who is selling a house and who is looking to purchase a house. It is possible that each
property may be shared by two or more people, and therefore John needs to know who owns
what percentage of the ownership. Finally, he will record the actual selling price for each
property sold. John also pays past clients for referring other people to his agency. When such a
referral results in a sale, he pays the referring client a small cash fee.
Finally, John sometimes sells properties that are listed by other agencies. For this case he wants
to know which agency listed the property by tracking its name and main phone number.
Tasks
Each group will produce:
• An Entity Relationship Diagram (ERD) of their design using DRAW.io or other electronic
software. Include both the save file for the ERD and a PDF of the final ERD (in case I can
not load it).
• An .sql file for the creation script for creating all tables, fields and relationships in the
database
DBS301 – Database Design II and SQL using Oracle Assignment 2
3 | P a g e
• a second .sql file that contains insertion scripts for inserting a minimum of 10 records
per table where appropriate. This will be fake made up data, but it must make sense.
• In a third .sql file, produce Views to create a minimum of 4 reports that will enable John
to see the state of his business, the properties currently for sale, a client list and a
report of his total sales for a year of his choosing. (i.e. parameter input). Note: You do
not need to create the reports, just the views which will be used to feed the data to the
report.
• You will actually create the database tables and views in one group members database
area and grant me permissions (user id:
Note:
• Design to 3NF
• All tables and views should use the prefix “a2”. Example: “a2Properties”
• Use the documented style guide for all naming, sql scripts and ERD formatting.
• Create meaningful names for all attributes and use PK for Primary Key and use FK for
Foreign Key.
• Show all table names and column names
Marking Schema
Marks will be given in a top down format. i.e. you start with a perfect mark and marks are
removed for incorrect, incomplete and insensible work. Bonus marks will be given for work
that goes beyond the scope of the requirements, but only for work that is exceptional, creative
and beneficial to John Wick (the client).
