in sql for course storage data of mangment Q.creat tables then fill , relationsh

Question

in sql for course storage data of mangment

Q.creat tables then fill , relationships
also do its Index and Cube

A Retail Dimensional Model Ret Calendar PK ti me period id day number Mall shot Campaign hour number PK mailshot id day date product category code malahot name malahot 8tart dates maishot end date mailshat target ulation Retail Dimensional Model maishot objectives other malahot detalla PK fact Udl invoice status code Products in Warehouses FK product id FK promotioLid PF product Id FK payment method code FAX warehouse id FR slam id parent product. d er FAT store id product name product price totals product calo averages, counts, balas product size her figures product description other product datala Ref Invoice Status Codes Product Promotion PK invoice status code PK Promotio invoice Btatue deecnpton promotion start date promotion end date promotion name promotion description other promotion details Data Base Answers Ltd. 2013 Statt PK staff id FK job id FK reporta to atar FAT store id staff name staff address other staff details Stores PK store id store name store adress other stone details. Ref Payment Methods Ph payment method code payment method description

Explanation / Answer

Ans

CREATE TABLE Mailshot_Campaigns (
mailshot_id int NOT NULL,
product_category_code varchar2(45),
mailshot_name varchar2(45),
mailshot_start_date datetime,
mailshot_end_date datetime,
mailshot_target_population longtext ,
mailshot_objectives varchar2(250),
other_mailshot_details varchar2(250),
PRIMARY KEY (mailshot_id)
);

CREATE TABLE Products_in_Warehouses (
product_id int NOT NULL,
warehouse_id int,
parent_product_id int ,
product_name varchar2(45),
product_price decimal(10,2),
product_color varchar2(45),
product_size varchar2(45),
product_description varchar2(250),
other_product_detail varchar2(250),
PRIMARY KEY (product_id),
FOREIGN KEY (warehouse_id) REFERENCES Retail_Dimension_Model(product_id)
);

CREATE TABLE Product_Promotions (
promotion_id int NOT NULL,
promotion_start_date datetime,
promotion_end_date datetime,
promotion_name varchar2(45),
promotion_description varchar2(250),
other_promotion_detail varchar2(250),
PRIMARY KEY (promotion_id)
);

CREATE TABLE Ref_Calender (
time_period_id int NOT NULL,
day_number datetime,
hour_number datetime,
day_date datetime,
PRIMARY KEY (time_period_id)
);

CREATE TABLE Ref_Invoice_Status_Codes (
invoice_status_code int NOT NULL,
invoice_status_description varchar2(250),
PRIMARY KEY (invoice_status_code)
);

CREATE TABLE Ref_Payment_Methods(
payment_method_code int NOT NULL,
payment_method_description varchar2(250),
PRIMARY KEY (payment_method_code)

);

CREATE TABLE Stores(
store_id int NOT NULL,
store_name varchar2(45),
store_address varchar2(250),
other_store_detail varchar2(250),
PRIMARY KEY (store_id)
);

CREATE TABLE Staff(
staff_id int NOT NULL,
job_title_id int,
report_to_staff_id int,
store_id int,
staff_name varchar2(45),
staff_address varchar2(250),
other_staff_details varchar2(250),
PRIMARY KEY (staff_id),
FOREIGN KEY (job_title_id) REFERENCES Job_Details(job_id),//if Job_details table exist or join with the table where job_id is exist
FOREIGN KEY (report_to_staff_id) REFERENCES Staff(staff_id),
FOREIGN KEY (store_id) REFERENCES Stores(store_id)
  
);

CREATE TABLE Retail_Dimensional_Model(
fact_id int NOT NULL,
invoice_status_code int,
mailshot_id int,
product_id int,
promotion_id int,
payment_method_code int,
staff_id int,
store_id int,
time_period_id int,
totals decimal(10,2),
average_counts_total decimal(10,2),
other_derived_figures decimal(10,2),
PRIMARY KEY (fact_id),
FOREIGN KEY (invoice_status_code) REFERENCES Ref_Invoice_Staus_Codes(invoice_status_code),
FOREIGN KEY (mailshot_id) REFERENCES Mailshot_Campaigns(mailshot_id),
FOREIGN KEY (product_id) REFERENCES Products_In_Warehouses(product_id),
FOREIGN KEY (promotion_id) REFERENCES Product_Promotions(promotion_id),
FOREIGN KEY (payment_method_code) REFERENCES Ref_Payment_Mehtods(payment_method_code),
FOREIGN KEY (staff_id) REFERENCES Staff(staff_id),
FOREIGN KEY (store_id) REFERENCES Stores(store_id),
FOREIGN KEY (time_period_id) REFERENCES Ref_Calender(time_period_id)
);

CREATE UNIQUE INDEX staff_report_index ON staff( report_to_staff_id);

-->cube example

select product_id,product_name,product_color,sum(product_price) as total from Products_In_warehouses group by cube(product_name,product_color);

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.