Relational Database Design for Aisle Perfect

The assignment requires the creation of a relational database for Aisle Perfect, a company that provides romantic inspiration to brides. The project involves four phases: planning, conceptual model, logical database model, and physical database model. The database should include tables for clients (with different types such as advertising services, website listing, and consulting services), vendors, and other relevant information. Queries will be needed to answer questions about client accounts, product types, inventory, and more.

PROJECT OVERVIEW
Your task is to analyze, design and create a relational database for an actual existing
business. The business we are using is Aisle Perfect. (www.aisleperfect.com)
Information about the business is below.
The submission will consist of two parts:
1. Your project report, with all diagrams embedded within the document.
2. The Oracle Tables resulting from your design efforts, and containing the
items specified below in ‘Project Detail’.
Your project should utilize and present the four phases of DBMS development:
 Planning: Some background information on the business; define purposes
and needs of the database, required data, etc.
 Conceptual Model: Entity-Relationship Model, Enhanced E-R Model,
Business Rules
 Logical Database Model: Mapping diagrams into relations, Normalization
 Physical Database Model: Denormalization, Set indexes for data file,
Queries as described below.
The following queries may need to be altered to make sense in the context of
your business. It is your responsibility to modify the queries accordingly without
trivializing them, and they should reasonable be about the same level of
complexity.
a) Number of product types stocked in the business?
b) Total volume of products stocked in the business?
c) Inventory on-hand for a specific product? (e.g. how many copies of a
particular video)
d) Status of customer accounts? (e.g. overdue, active, not active)
e) Status of a particular product? (e.g. in stock, not in stock, ordered,
discontinued, etc.)
f) Search by product name or type.
g) Display product detail that is relevant to your business (e.g. for video rental:
producer, length, and rental price, one night, two nights, or one week)
(include at least three such detail items in your query). You must utilize and
demonstrate the use of multi-table joins (either left or right outer join).

PROJECT DETAIL
You can use the database-modeling tool, SQL Developer for diagram drawing. You
must use the Oracle 12c database to create the actual tables and queries. All
diagrams must be embedded into your document.
Fist, visit a local business, retail store or your companies business unit, in order to
understand what is required for a database platform and to uncover the necessary
information. Then carry out the following tasks with enough detail to be able to
create working queries as shown in below in step 8:
1. Draw an enterprise diagram to present the business unit’s business data.
2. Draw an enhanced entity relationship diagram to show the business unit’s
database and business rules for its daily operation.
3. Convert the diagrams into relations.
4. Normalize the relations.
5. Present the relations for the whole database using SQL Developer.
6. Use Oracle to create at least five basic tables for the unit’s database. The
normalized tables should be well defined with keys, referential integrity, data
type, index, and relationships with other tables.
7. Store actual data, or reasonable mock data that you create, into tables with at
least 10 records for each table.
8. Write queries against the database you created to answer the following
questions: (Choose 6 queries from this list – but you must include mandatory
query “g.”). Queries should be included in your document and they should
work – i.e. give the desired results.
9. Write a short report containing the following:
a) Name, address, and a description of the business that is sufficient to
understand your model and tables.
b) Briefly describe the DBMS platform that the business is currently
using, if any.
c) Describe your new database including purpose, data, organization,
etc.
d) Describe the challenges you have experienced, and how you met these
challenges.
CLIENT MANAGEMENT SYSTEM DATABASE FOR AISLE PERFECT

Aisle Perfect (AP) is a company dedicated to providing romantic inspiration to the
discerning bride. As a go-to wedding planning resource for brides, they cover
everything from real weddings to planning tips, bridal fashion and much more. They
also provide advertising services to wedding professionals through their Pretty
Perfect Partner program - a carefully curated exclusive listing of trusted wedding
professionals in the USA and abroad.
They have 3 types of clients known as ‘vendors’. They are as follows:
1. Clients that pay for advertising services on social media. They let AP know
what they’re trying to sell and what kind of bride they are trying to reach.
Based on that information, AP tailors the posts they advertise for them.
2. Clients that pay to be listed on the Aisle Perfect’s website.
3. Clients that pay for consulting services.
Aisle Perfect needs a database that keeps a well-organized record of every client and
their requirements. They want a system that provides them with a better way to
track clients ROI, track the amount of clicks each client is getting from their ads on
Aisle Perfect, how many times their content is pushed on social media since they are
guaranteed a certain amount of shares, and also track when their ad partnership
with Aisle Perfect ends and is due for renewal.