Релациска шема (со мапирачка трансформација)

Ознаки

• Примарните клучеви се означени со bold и underline
• Надворешните клучеви се означени со *
• Задолжителни атрибути, односно not null со bold

Табели

• Address (Address_Id, Street, City, Postcode, Street_Number)
• Wine_Type (Wine_Type_Id, Wine_Type_Name, Wine_Type_Description, Wine_Type_Region)
• Wine (Wine_Id, Wine_Type_Id*(Wine_Type), Wine_Name, Base_Price, Year_Produced)
• Customer_Type (Customer_Type_Id, Customer_Type_Name, Customer_Type_Description)
• Customer (Customer_Id, Customer_Type_Id*(Customer_Type), Address_Id*(Address), Customer_Name, Customer_Email, Customer_Phone_Number)
• Vehicle_Type (Vehicle_Type_Id, Vehicle_Type_Name)
• Vehicle_Details (Vehicle_Details_Id, Vehicle_Type_Id*(Vehicle_Type), Make, Model, Capacity)
• Vehicle (Vehicle_Id, Vehicle_Details_Id*(Vehicle_Details), Registration)
• Expense_Type (Expense_Type_Id, Expense_Type_Name, Expense_Type_Description)
• Employee (Employee_Id, Employee_Name, Employee_Surname)
• Warehouse (Warehouse_Id, Address_Id*(Address), Warehouse_Name)
• Shipment (Shipment_Id, Employee_Id*(Employee), Payment_Id*(Payment), Vehicle_Id*(Vehicle), Shipment_Date)
• Payment (Payment_Id, Payment_Status)
• Shipment_Load (Shipment_Id*(Shipment),Customer_Id*(Customer), Wine_Id*(Wine), Quantity, Base_Price, Agreed_Price)
• Shipment_has_expense_of_Expense_Type(Shipment_Id*(Shipment),Expense_Type_Id*(Expense_Type), Amount)
• Employee_Drives_Vehicle(Employee_Id*(Employee),Vehicle_Id_*(Vehicle))

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

V2_Init_DB.sql​

DML скрипта за полнење на табелите со податоци

V2_Polnenje_DB.sql​

Релациски дијаграм изваден од DBeaver