Логички и физички дизајн - Креирање база податоци (со SQL DDL)

Фаза 2 - DDL, податоци, погледи

Оваа фаза почнувате да ја работите откако финалниот ЕР дијаграм ќе ви биде одобрен и од асистент и од професор.

2А: DDL за изработениот ЕР дијаграм

Генериран е DDL за изработениот ЕР дијаграм. Задолжително се проверени сите податочни типови, ограничувања и клучеви.

Додадени се дополнителни ограничувања, проверки и дефолтни вредности секаде каде што е потребно, со цел да се обезбеди интегритет и конзистентност на податоците.

Ознаки

• Со * се означени надворешни клучеви
• Со ** се означени втори надворешни клучеви
• Со *** се означени трети надворешни клучеви
• Примарните клучеви се подвлечени и болдирани

Табели

Ентитети

ADMIN(admin_id, admin_name, admin_email, admin_password)

API_USER(api_user_id, username, password, role)

COMPANY(company_id, company_name, company_email, company_phone, company_address)

CONTRACT_STATUS(contract_status_id, status_name)

ORDER_STATUS(order_status_id, status_name)

DELIVERY_STATUS(delivery_status_id, status_name)

LOYALTY_TIER(loyalty_tier_id, tier_name, min_points, discount_percent)

CUSTOMER_LOYALTY_STATUS(customer_loyalty_status_id, status_name)

INGREDIENT(ingredient_id, ingredient_name)

ALERGEN(alergen_id, alergen_name)

CATEGORY(category_id, category_name)

LUNCH_TIME(lunch_time_id, lunch_name, start_time, end_time)

REVIEW(review_id, customer_id*, review_text, rating)

• референцира до CUSTOMER (customer_id)

RESTAURANT(rest_id, rest_name, rest_location, rest_website, rest_phone, rest_email, company_id*)

• референцира до COMPANY (company_id)

CUSTOMER(customer_id, first_name, last_name, email, phone, address, password)

DRIVER(driver_id, first_name, last_name, phone, vehicle_info, company_id*)

• референцира до COMPANY (company_id)

CONTRACT(contract_id, company_id*, contract_status_id, contract_start_date, contract_end_date, contract_value)

• референцира до COMPANY (company_id)

референцира до CONTRACT_STATUS (contract_status_id)

CUSTOMER_LOYALTY(customer_loyalty_id, points, customer_id*, loyalty_tier_id, customer_loyalty_status_id*)

• референцира до CUSTOMER (customer_id)

референцира до LOYALTY_TIER (loyalty_tier_id)

* референцира до CUSTOMER_LOYALTY_STATUS (customer_loyalty_status_id)

MEAL(meal_id, meal_name, meal_price, meal_description, rest_id*, category_id, lunch_time_id*)

• референцира до RESTAURANT (rest_id)

референцира до CATEGORY (category_id)

* референцира до LUNCH_TIME (lunch_time_id)

DRINK(drink_id, drink_name, drink_price, rest_id*)

• референцира до RESTAURANT (rest_id)

CUSTOMER_ORDER(order_id, order_date, total_amount, customer_id*, rest_id, order_status_id*)

• референцира до CUSTOMER (customer_id)

референцира до RESTAURANT (rest_id)

* референцира до ORDER_STATUS (order_status_id)

DELIVERY(delivery_id, delivered_at, order_id*, driver_id, delivery_status_id*)

• референцира до CUSTOMER_ORDER (order_id)

референцира до DRIVER (driver_id)

* референцира до DELIVERY_STATUS (delivery_status_id)

ORDER_REVIEW(order_review_id, order_id*, review_id)

• референцира до CUSTOMER_ORDER (order_id)

референцира до REVIEW (review_id)

DELIVERY_REVIEW(delivery_review_id, delivery_id*, review_id)

• референцира до DELIVERY (delivery_id)

референцира до REVIEW (review_id)

INVOICE(invoice_id, issued_at, amount, order_id*)

• референцира до CUSTOMER_ORDER (order_id)

COMPANY_ORDER(company_order_id, company_id*, order_id)

• референцира до COMPANY (company_id)

референцира до CUSTOMER_ORDER (order_id)

Релациски табели

MEAL_INGREDIENT(meal_id*, ingredient_id)

• референцира до MEAL (meal_id)

референцира до INGREDIENT (ingredient_id)

ALERGEN_INGREDIENT(alergen_id*, ingredient_id)

• референцира до ALERGEN (alergen_id)

референцира до INGREDIENT (ingredient_id)

ORDER_MEAL(order_id*, meal_id, quantity)

• референцира до CUSTOMER_ORDER (order_id)

референцира до MEAL (meal_id)

ORDER_DRINK(order_id*, drink_id, quantity)

• референцира до CUSTOMER_ORDER (order_id)

референцира до DRINK (drink_id)

DDL скрипта за креирање на табелите

Финалната DDL скрипта за креирање на шемата:

• schema_final.sql

Простор за поставување на schema_final.sql

2Б: Полнење на табелите со податоци

Потребно е табелите да бидат наполнети со реалистични податоци. Неколку табели содржат повеќе од еден милион редици, а големината на табелите е избрана така што го одразува реалното користење на апликацијата.

Податоците се генерирани и увезени од реални податочни извори каде што е соодветно.

Број на податоци по табела

[cols="2,1", options="header"] |=== | Табела | Број на записи

| API_USER | 10,001,000 | CUSTOMER | 10,000,000 | CUSTOMER_LOYALTY | 10,000,000 | CUSTOMER_ORDER | 10,000,000 | REVIEW | 1,000,000 | ORDER_MEAL | 1,000,000 | ORDER_DRINK | 1,000,000 | API_ADMIN | 100 | DRIVER | 900 | COMPANY | 500 | COMPANY_ORDER | 1,000,000 | RESTAURANT | до 50 | CATEGORY | реални MealDB категории | INGREDIENT | реален MealDB/CocktailDB сет на состојки | ALERGEN | 14 | MEAL | 200 | DRINK | 100 | MEAL_INGREDIENT | зависно од увезените meal-ingredient врски | ALERGEN_INGREDIENT | зависно од совпаѓањето според имињата на состојките | CONTRACT | 200 | DELIVERY | 1,000,000 | LUNCH_TIME | 1,000 | INVOICE | 6,000 | ORDER_REVIEW | 700,000 | DELIVERY_REVIEW | 300,000 | CONTRACT_STATUS | 5 | CUSTOMER_LOYALTY_STATUS | 3 | DELIVERY_STATUS | 8 | ORDER_STATUS | 6 |===

Извор на податоците

• *RESTAURANT* – до 50 ресторани од реалниот dataset
• *CATEGORY* – реални категории од MealDB
• *INGREDIENT* – реален сет на состојки од MealDB/CocktailDB
• *MEAL_INGREDIENT* – врски добиени од увезените податоци за оброците и состојките
• *ALERGEN_INGREDIENT* – врски добиени преку совпаѓање на имињата на состојките
• Останатите податоци се генерирани со реалистична распределба според очекуваното користење на апликацијата.

Скрипти за полнење на табелите

• data_final.sql

Простор за поставување на data_final.sql

2В: Погледи (Views)

Потребно е да се направат три погледи (views) по член, така што тие подоцна реално ќе бидат користени од апликацијата.

View 1

Простор за првиот view

## [source,sql]

## -- SQL код за View 1

View 2

Простор за вториот view

## [source,sql]

## -- SQL код за View 2

View 3

Простор за третиот view

## [source,sql]

## -- SQL код за View 3

Прилози

• schema_final.sql – финална DDL скрипта
• data_final.sql – скрипта за полнење на табелите
• Скрипти за креирање на views