Introduction
Imagine that an organization uses a data mart to record restaurants’ transactions worldwide, and offer services such as reports and performance analysis to help optimize restaurants’ profitability. To capture the information, the organization provides an application the integrates with the restaurants’ point of sales systems, which then transmits the data back to the organization in near real time. This information is stored in the star schema below.
Schema Diagram
Creating Database Using Oracle
This star schema represents groups of people (parties) eating meals at restaurants. The schema is grained to each individual menu item selection for each meal. The schema is incomplete in that it does not represent all significant information that would be included for a complete picture. The following business rules help capture the restaurants’ workings.
Parties of one or more people eat meals at restaurants.
The restaurants ask each party to give a name associated with the party, in addition to the number of people in the party, when the party arrives (or when the party makes the reservation).
Each party selects one or more items from the menu for their meal; the same item might be selected multiple times at the same meal if different people want to eat the same item.
Every menu item has a category (such as “Entrée”, “Side”, “Dessert”, and so on).
There are many restaurants, and each restaurant has their own name, location, and address.
A waitperson serves a party.
The DDL to create the tables in the schema is listed below.
Run drop command if table already exist in database:
DROP TABLE Menu_item_selection;
DROP TABLE Party;
DROP TABLE Meal_date;
DROP TABLE Menu_item;Creating "Party" Table
CREATE TABLE Party (
party_id DECIMAL(12) NOT NULL PRIMARY KEY,
number_in_party DECIMAL(3) NOT NULL,
party_name VARCHAR(64));Creating "Meal_date" Table
CREATE TABLE Meal_date (
meal_date_id DECIMAL(12) NOT NULL PRIMARY KEY,
meal_date DATE NOT NULL,
year DECIMAL(4) NOT NULL,
month DECIMAL(2) NOT NULL,
day_of_month DECIMAL(2) NOT NULL);Creating "Menu_item" Table
CREATE TABLE Menu_item (
menu_item_id DECIMAL(12) NOT NULL PRIMARY KEY,
item_category VARCHAR(32) NOT NULL,
item_name VARCHAR(32) NOT NULL,
item_price DECIMAL(6,2));Creating "Menu_item_selection" Table
CREATE TABLE Menu_item_selection (
party_id DECIMAL(12) NOT NULL,
meal_date_id DECIMAL(12) NOT NULL,
menu_item_id DECIMAL(12) NOT NULL,
FOREIGN KEY (party_id) REFERENCES Party(party_id),
FOREIGN KEY (meal_date_id) REFERENCES Meal_date(meal_date_id),
FOREIGN KEY (menu_item_id) REFERENCES Menu_item(menu_item_id));What Need to do?
1. First, identify different parts of the star schema by completing the following.
Identify the fact table and explain what event it represents.
Identify the dimension tables and explain what event participant it represents.
Identify a hierarchy that exists in one of the dimension tables and explain what is represents.
2. Next, identify and add in a dimension that is missing by completing the following.
Review the business rules in the section introduction, identify a dimension that is missing, and explain.
Explain what attributes and hierarchies this dimension would reasonably contain.
Add the dimension into the schema by creating the dimension table in SQL along with is attributes, and adding a foreign key to the fact table.
3. Next, identify and make use of a useful measure by completing the following.
As there are no measures in the schema, identify a useful one that could be added, and explain what it measures.
In SQL, add the measure to the fact table.
In SQL, insert 15 rows of data into the fact table, along with the corresponding dimension rows. Make sure the data has some variety.
Write a query that uses the ROLLUP extension to GROUP BY, along with an aggregate function on the measure, to analyze some important aspect of the business. Explain what the results mean.
You might Get Help In
Database Designing Help
Database ER Diagram Help
Database Implementation Help
Database Schema Diagram Help
Database Querying Help
Database homework help
Database Project Help
Database Coursework Help
Database Assignment Help
Contact Us! to get any other help related to Oracle Database or Oracle ER Diagram, Queries, Shema Diagram at:
realcode4you@gmail.com
Comments