Property Listings DB

Purpose:

This project will provide an occasion for students to employ all of the skills they have learned over the course of the term in a single integrated effort that closely mirrors the data management challenges they are likely to face in their professional careers.

Description:

Students will develop a database system designed to meet the needs of an organization. This includes:

• defining a problem domain (e.g., used car sales, auctions, service providers, etc.)
• specifying the data management needs associated with that domain (e.g., capturing sales, maintaining customer information, managing inventory levels)
• creating an ER model in accordance with the requirements developed
• translating this ER model into a normalized (3NF) relational schema
• implementing the relational schema using SQL
• manipulating the data in this schema to meet the everyday needs of the website (i.e., developing SQL commands to insert, update, delete, and query business data)

Constraints:

This project will be regulated by the following constraints:

• Whatever problem domain you choose and however you bound the functionality of your system, the database you ultimately implement must contain at least eight tables. (Your database can have more than eight tables, but you don't need to have more than eight to get an excellent grade.)
• You must use Oracle to implement your database.

Deliverables:

Your final project report shall contain (at minimum) the following sections, but you are free to add others if you feel ambitious.

• Table of Contents
• Requirements: In this section you must clearly define the information that your database needs to capture, along with any associated business rules (i.e., constraints). Every entity, attribute, and relationship reflected in your ER model must be directly traceable to the requirements you describe in this section. (As such, "record sales" is not an adequate requirement - something like for each item purchased, record the item number, quantity, and customer would be much better.) You should try to make these requirements somewhat realistic, but ultimately you have great latitude in determining the capabilities that you want to build into your system. If your requirements section is longer than one or two typewritten pages (single-spaced), then you are probably making the project harder than it needs to be.
• ER Model: Draw an ER diagram that reflects the requirements you documented in the previous section. Again, be sure that every part of this diagram can be traced back to your requirements. Be sure to use our course modeling notation.
• Database Schema: Translate your ER diagram into a set of relational schemas.
• Data Dictionary: Define the meanings of each of the tables and columns in your relational schema.
• DDL: SQL statements needed to create the objects in your database, including any views and constraints. Make sure that I can copy and paste code from the document you turn in, in case I need to run the code - screenshots of your code are unacceptable, as the will require me to retype your code if I need to run it.
• DML: SQL statements needed to manipulate the data in your database. For each table, include at least three example inserts. Please make sure that these statements can actually be executed without errors (e.g., the values in your example statements should not violate referential integrity constraints when executed in the order in which they appear in your documentation). Again, make sure that I can copy and paste code from the document you turn in - screenshots of your code are unacceptable
• Queries: SQL statements needed to create the reports defined in your requirements (e.g., show all items purchased by customer 12345 in May of 2018). You need to have at least five reports (different in syntax, not just parameters) with some obvious business value. For each query, show its output. As before, make sure that I can copy and paste your query statements from the document you turn in - screenshots of your code are unacceptable