Question 1

Orders Database

Based on the visible difference between shipping prediction and the actual arrival time, the client organisation wants to better understand shipment performance. They need a data warehouse (data mart) to identify the vital information about shipping that was acquired from the Orders Database Orders Architecture in order to execute analytics on this data.

Question 2

You will be guided through the process of building a straightforward dimensional model, or "star schema," for a section of the Hallux Entertainment database in this assignment. Following the creation of your tables in SQL Server, you'll go through a series of setup tasks to create an SSIS package that will load data into your databases from various sources. The star architecture that you will be constructing and uploading is listed below. The SQL CREATE statements are listed after that.

You will adhere to instructions in this stage to build up a procedure to load data from just an Excel file. There are two distinct approaches to doing this. To load data from pre-existing SQL Server database tables, one method is to create a control flow and data flow, which we will do later in this exercise. Here, we're going to use the "SSIS Import and Export Wizard," which is a different strategy. Right-click SSIS Packages in your Solution Explorer window and select SSIS Import and Export Wizard. Any getting started screen that appears should be exited.

Question 3

A relational model's limitations are a key component. The relational model actually backs up the well-established theory of constraints on attributes or tables. Because they let a designer define the semantics of original database, limitations are useful. Rules known as constraints compel DBMSs to verify that data complies with semantic requirements. We must impose referential integrity in order to guarantee that there are no orphan records. A record is considered orphan if its foreign key FK value cannot be identified in the organization where the primary key (PK) is stored. Remember that a PK and FK are the usual partners in a join. According to the referential integrity requirement, a valid Customer table CustID must match the customer ID (CustID) in the Order table. Declarative referential integrity is a feature of most relational databases. In other words, referential integrity restrictions are established whenever the tables are formed.

Referential integrity is set when the Order table is created with the FK in Transact-SQL. The instructions mentioning the PK in the Customer table and the FK in the Order table are given below.

Question 4

4) Sample Data

Make sure [dbo] is selected as the source in the Select Source Table and Views window. Click Next after entering [Date Dimension] from the preceding step. The "Review Data Type Mapping" window will now appear. You might encounter caution signs in this window. By moving your mouse over the indications, you can view the warning's specific details. In this instance, we can click Next while ignoring the alerts.

CREATE TABLE [Sales].[Orders]

(

[OrderID] [int] NOT NULL CONSTRAINT [DF_Sales_Orders_OrderID] DEFAULT (NEXT VALUE FOR [Sequences].[OrderID]),

[CustomerID] [int] NOT NULL,

[SalespersonPersonID] [int] NOT NULL,

[PickedByPersonID] [int] NULL,

[ContactPersonID] [int] NOT NULL,

[BackorderOrderID] [int] NULL,

[OrderDate] [date] NOT NULL,

[ExpectedDeliveryDate] [date] NOT NULL,

[CustomerPurchaseOrderNumber] [nvarchar] (20) NULL,

[IsUndersupplyBackordered] [bit] NOT NULL,

[Comments] [nvarchar] (max) NULL,

[DeliveryInstructions] [nvarchar] (max) NULL,

[InternalComments] [nvarchar] (max) NULL,

[PickingCompletedWhen] [datetime2] NULL,

[LastEditedBy] [int] NOT NULL,

[LastEditedWhen] [datetime2] NOT NULL CONSTRAINT [DF_Sales_Orders_LastEditedWhen] DEFAULT (sysdatetime())

)

GO

ALTER TABLE [Sales].[Orders] ADD CONSTRAINT [PK_Sales_Orders] PRIMARY KEY CLUSTERED  ([OrderID])

GO

CREATE NONCLUSTERED INDEX [FK_Sales_Orders_ContactPersonID] ON [Sales].[Orders] ([ContactPersonID])

GO

CREATE NONCLUSTERED INDEX [FK_Sales_Orders_CustomerID] ON [Sales].[Orders] ([CustomerID])

GO

CREATE NONCLUSTERED INDEX [FK_Sales_Orders_PickedByPersonID] ON [Sales].[Orders] ([PickedByPersonID])

GO

CREATE NONCLUSTERED INDEX [FK_Sales_Orders_SalespersonPersonID] ON [Sales].[Orders] ([SalespersonPersonID])

GO

ALTER TABLE [Sales].[Orders] ADD CONSTRAINT [FK_Sales_Orders_Application_People] FOREIGN KEY ([LastEditedBy]) REFERENCES [Application].[People] ([PersonID])

GO

ALTER TABLE [Sales].[Orders] ADD CONSTRAINT [FK_Sales_Orders_BackorderOrderID_Sales_Orders] FOREIGN KEY ([BackorderOrderID]) REFERENCES [Sales].[Orders] ([OrderID])

GO

ALTER TABLE [Sales].[Orders] ADD CONSTRAINT [FK_Sales_Orders_ContactPersonID_Application_People] FOREIGN KEY ([ContactPersonID]) REFERENCES [Application].[People] ([PersonID])

GO

ALTER TABLE [Sales].[Orders] ADD CONSTRAINT [FK_Sales_Orders_CustomerID_Sales_Customers] FOREIGN KEY ([CustomerID]) REFERENCES [Sales].[Customers] ([CustomerID])

GO

ALTER TABLE [Sales].[Orders] ADD CONSTRAINT [FK_Sales_Orders_PickedByPersonID_Application_People] FOREIGN KEY ([PickedByPersonID]) REFERENCES [Application]