Task 3
CREATE TABLE `dimension_clients` (
`ClientID` int(10) NOT NULL,
`CompanyName` varchar(20) NOT NULL,
`NumberOfEmployees` int(5) NOT NULL,
`BankAccount` varchar(20) NOT NULL,
`Address` varchar(20) NOT NULL,
`Telephone` int(15) NOT NULL,
`ABN` int(15) NOT NULL,
`Manager` varchar(20) NOT NULL,
PRIMARY KEY (`ClientID`)
)
CREATE TABLE `dimension_contract` (
`ContactID` int(10) NOT NULL,
`ContactSignedDate` date NOT NULL,
`LeasingStartDate` date NOT NULL,
`LeasingEndDate` date NOT NULL,
PRIMARY KEY (`ContactID`)
)
CREATE TABLE `dimension_invoice` (
`InvoiceID` int(11) NOT NULL,
`InvoicePremise` varchar(20) NOT NULL,
`InvoiceDate` date NOT NULL,
`PaymentType` varchar(20) NOT NULL,
`TotalPrice` int(11) NOT NULL,
PRIMARY KEY (`InvoiceID`)
)
CREATE TABLE `dimension_leased_premises` (
`LeasedID` int(10) NOT NULL,
`LeasedSize` int(10) NOT NULL,
`LeasedUnitPrice` int(10) NOT NULL,
PRIMARY KEY (`LeasedID`)
)
CREATE TABLE `dimension_premises` (
`PremisesID` int(10) NOT NULL,
`PremisesType` varchar(10) NOT NULL,
`Size` int(10) NOT NULL,
PRIMARY KEY (`PremisesID`)
)
CREATE TABLE `dimension_utilities` (
`UtilitiesID` int(10) NOT NULL,
`Description` varchar(100) NOT NULL,
`Capacity` int(10) NOT NULL,
`GovernmentChargePerUnit` int(10) NOT NULL,
PRIMARY KEY (`UtilitiesID`)
)
CREATE TABLE `dimension_utilities_used` (
`ConsumptionID` int(11) NOT NULL,
`Description` varchar(100) NOT NULL,
`UnitPrice` int(11) NOT NULL,
`ConsumptionAmount` int(11) NOT NULL,
`TotalPrice` int(11) NOT NULL,
`ConsumptionStartDate` date NOT NULL,
`ConsumptionEndDate` date NOT NULL,
PRIMARY KEY (`ConsumptionID`)
)
CREATE TABLE `dimension_warehouse` (
`WarehouseID` int(11) NOT NULL,
`EstablishedYear` int(11) NOT NULL,
`Size` int(11) NOT NULL,
`ConstructionCost` int(11) NOT NULL,
`MantananceAnnualCost` int(11) NOT NULL,
PRIMARY KEY (`WarehouseID`)
)
CREATE TABLE `fact_sales` (
`ClientID` int(11) NOT NULL,
`ContractID` int(11) NOT NULL,
`UtilitiesID` int(11) NOT NULL,
`InvoiceID` int(11) NOT NULL,
`PremisesID` int(11) NOT NULL,
`ConsumptionID` int(11) NOT NULL,
`LeasedID` int(11) NOT NULL,
`WarehouseID` int(11) NOT NULL,
KEY `ClientID` (`ClientID`),
KEY `UtilitiesID` (`UtilitiesID`),
KEY `ContractID` (`ContractID`),
KEY `InvoiceID` (`InvoiceID`),
KEY `PremisesID` (`PremisesID`),
KEY `ConsumptionID` (`ConsumptionID`),
KEY `LeasedID` (`LeasedID`),
KEY `WarehouseID` (`WarehouseID`)
)
ALTER TABLE `fact_sales`
ADD CONSTRAINT `ClientID_FK` FOREIGN KEY (`ClientID`) REFERENCES `dimension_clients` (`ClientID`),
ADD CONSTRAINT `ContractID_FK` FOREIGN KEY (`ContractID`) REFERENCES `dimension_contract` (`ContactID`);
ADD CONSTRAINT `UtilitiesID_FK` FOREIGN KEY (`UtilitiesID`) REFERENCES `dimension_Utilities` (`UtilitiesID`),
ADD CONSTRAINT `InvoiceID_FK` FOREIGN KEY (`InvoiceID`) REFERENCES `dimension_invoice` (`InvoiceID`);
ADD CONSTRAINT `PremisesID_FK` FOREIGN KEY (`PremisesID`) REFERENCES `dimension_premises` (`PremisesID`),
ADD CONSTRAINT `ConsumptionID_FK` FOREIGN KEY (`ConsumptionID`) REFERENCES `dimension_ConsumptionID` (`Consumption`);
ADD CONSTRAINT `LeasedID_FK` FOREIGN KEY (`LeasedID`) REFERENCES `dimension_lease_premises` (`LeasedID`),
ADD CONSTRAINT `WarehouseID_FK` FOREIGN KEY (`WarehouseID`) REFERENCES `dimension_ WarehouseID` (`WarehouseID`);
Task 4
- SELECT SUM(dimension_invoice.TotalPrice) ORDER BY INNER JOIN dimension_client.size
- SELECT SUM(dimension_utilities_used.TotalPricet)WHERE INNER JOIN dimension_contract.leasingStartDate =2016
- SELECT COUNT(dimension_contracts.ContractID) from dimension_contract INNER JOIN (SELECT * FROM dimension_company WHERE size>100
- SELECT COUNT(contracts) GROUP BY (dimension_contract.ContracteSignedDate INNER JOIN dimension_client.NumberOfEmployees) order DESC
