Conversation with Merlin [email protected] · Mon Dec 04 2023

COMM051 Database Systems Assignment 2023/24 1 COMM051 Database Systems Assignment – Sem 1, 2023/24 The purpose of this assignment is to give you practical experience of designing database systems and business intelligence (BI) solutions in the context of a realistic case study where you work with real data. You will work in groups of 6. The Assignment carries 100% of the mark for the module.

  1. Assignment scenario and Deliverables Assignment Scenario

MumsNet was founded in 2003 and has grown over the last years to become a UK leading specialist brand for mothers-to-be and mothers of babies and pre-school children. They offer a collection of maternity clothes, nursery equipment, accessories and toys for babies and toddlers, as well as baby clothes from new-born to 3 years old. MumsNet markets and sells its products thro ugh its own retail stores and online via its own website MumsNet.com. The company launched its online store at the start of 2005 and has done significantly well as online sales have grown every year since then. The management team at MumsNet would like to make improvements to its existing IT system to ensure that it meets the increasing volume of sales transactions and customer registrations. You are part of a team of database designers which has been contracted by Mumsnet to carry out a review of the SQL Server database tables supporting the online sales application and implement improvements where you see fit (more in Deliverable D1 on page 3 of this document). In addition, the management team at MumsNet would like to introduce a new business intelligence platform to their organisation. They would like to start analysing the data collected from the online store as they are increasingly convinced that this analysis would provide an immediate benefit to their business. Your team has also been contracted to work on the new business intelligence platform for MumsNet (more in Deliverable D2 on page 5 of this document). COMM051 Database Systems Assignment 2023/24 2 Deliverables

As a group you will create and deliver:

  1. Deliverable D1 on database design, optimisation (D1.1) and transaction processing (D1.2) as a backup SQL database file (.bak)
  2. Deliverable D2 on a ‘proof of concept’ business intelligence platform, as a SSAS project (.sln)
  3. Deliverable D4 is a demo in a 5-minute long video where you briefly outline your solutions for both Deliverables D1, D2 and show that they work. As an individual, you will deliver:
  4. Deliverable D3 which is a Final Report on your experience and contribution to the group work on all aspects of the coursework, as a PDF file Further information on each of the 4 deliverables, including how each will be submitted, is provided below. COMM051 Database Systems Assignment 2023/24 3 Deliverable D1 The following three tables have been given to you by the Mumsnet Systems Administrator from the production environment and contain historic order, product and customer information from January 2005 until December 2009. D1.1 – database design, optimisation: Your team has reviewed the MumsNet.com database tables and has made the observation that the OrderItem, Product and Customer tables are not normalised to the third normal form (3NF). a) All three tables need to be normalised to 3NF and existing data migrated to the new data structure. Also ensure primary and foreign keys are defined in the resulting (normalised) database schema to safeguard the data integrity of the database. b) There is a business requirement that an Order Group entity/relation is introduced in the database schema with the following attributes and relationships: i) OrderNumber: A string attribute that contains the order number. You observe that this is an existing attribute in the OrderItem table in the database schema. An order number has the format OR\DDMMYYYY\NN, where NN is a sequential number from 01 to 99, for example OR\01012006\04. ii) OrderStatusCode: A string attribute that contains the status code of the order. You observe that this is an existing attribute in the OrderItem table in the database schema. The OrderStatusCode attribute contains the following order status codes: 0: This is a new order 1: This an abandoned order 2: This is an unfulfilled order due to out of stock item(s) 3: This is an order that have been cancelled by the customer 4: This is a fulfilled order; the goods have been shipped to the customer iii) OrderCreateDate: The order creation date. You observe that this is an existing attribute in the database schema (an attribute of the OrderItem table, in particular). iv) BillingCurrency: The billing currency of the order. You observe that this is an existing attribute in the database schema (an attribute of the OrderItem table, in particular). v) TotalLineItems: The total number of items ordered. You observe that this is a new attribute in the database schema. vi) SavedTotal: The total order value. You observe that this is a new attribute in the database schema. vii) The Order Group entity may contain zero or more order items. <20 marks> COMM051 Database Systems Assignment 2023/24 4 D1.2 – transaction processing: Your team has also been asked to develop new stored procedures to deliver order processing functionality for the new Order Group Entity: a) Develop the stored procedure prCreateOrderGroup; the execution of this program will create a new order based on the parameter values supplied below: -OrderNumber: nvarchar(32) -OrderCreateDate: datetime -CustomerCityId: int b) Develop the stored procedure prCreateOrderItem; the execution of this program will create a new order line based on the parameter values supplied below: -OrderNumber nvarchar(32) -OrderItemNumber nvarchar(32) -ProductGroup nvarchar(128) -ProductCode nvarchar(255) -VariantCode nvarchar(255) -Quantity int -UnitPrice money The new stored procedures must contain error handling and transactional support to ensure that the data consistency is maintained during successful and unsuccessful program execution. Implement indexes to improve order processing performance. <15 marks> COMM051 Database Systems Assignment 2023/24 5 Deliverable D2 Your team has been asked to deliver a ‘proof of concept’ business intelligence platform using Microsoft SQL Server 2018 Analysis Services (SSAS/SSDT) to satisfy the following data requirements: • Number of cancelled orders • Percentage of orders cancelled by the customer • Sales value of cancelled orders • Number of unfulfilled basket orders due to out of stock item(s) • Percentage of all unfulfilled basket orders • Percentage of abandoned basket orders • Percentage of fulfilled orders • All percentage calculations are against total orders placed • All order calculations should be broken down, by product, by customer, by day • Sales and quantity sold by product, by customer, by day • Ordered stock quantity by product, by ordered day • Customer rolls into city, which rolls into region, which rolls into country • Product rolls into product group • Day rolls into month, which rolls into quarter, which rolls into year • All product calculations must be broken down to product variant level You will need to create an appropriate Data Source View (DSV) and design corresponding Cube measures and dimensions with the above characteristics. Your BI solution should be delivered as an SSDT project (.sln), in the same way as the projects you will build for Lab 5 and Lab 6 in the course. <25 marks> COMM051 Database Systems Assignment 2023/24 6 Deliverable D3 Each member of the group will need to produce a concise final report that should read as an executive summary on the process of collaboration, your contribution and lessons learned. A template of the report, with recommended sections, is provided in a separate document titled “COMM051_Final_Report_Template.doc” found on SurreyLearn. Please make sure you follow this template in producing your report. The report should not exceed 1,000 words in total. It should be submitted individually as a PDF document via SurreyLearn. So each member of the group submits their own Deliverable D3. <20 marks> COMM051 Database Systems Assignment 2023/24 7 Deliverable D4 Your group will produce a short video (up to 5minutes long) where you will present your approach to database design, including optimisation and transactions for Deliverable D1, as well as the BI solution developed for Deliverable D2. This will be submitted via SurreyLearn. <20 marks> COMM051 Database Systems Assignment 2023/24 8
  5. Assessment Criteria 2.1 Group marks: The technical deliverables D1, D2 and D4 of this project concern the creation and delivery of “the product” or solution, which is shared and therefore the whole group gets the same mark for these parts of the assignment. More specifically, Deliverables D1 and D2 will be assessed based on the following criteria: Concept Assessment Normalisation (D1.1) Appropriate creation of normalised table structures Relational Model Constraints (D1.1) Appropriate use of primary and foreign keys SQL Optimisation (D1.1) Appropriate use of indexes based on SQL query usage to improve database performance Error Handling (D1.2) Appropriate use of TRY/CATCH statements Concurrency Control (D1.2) Appropriate use of BEGIN TRAN / COMMIT / ROLLBACK statements Data Source View design (D2) Appropriate use of Data Source Views Cube Measures and Dimensions design (D2) Appropriate use of measures and dimensions to produce required calculations Deliverable D4 will be assessed based on the following criteria: Deliverable D4 – Demo / video 70-100 A compelling attempt at demonstrating all aspects of the solutions and how these meet all the business requirements. Excellent demo, giving a convincing case, the solution of which can realistically be used. All topics and contributions were crystal clear. 60-70 A compelling attempt at demonstrating most aspects of the solutions and how these meet the corresponding business requirements. Well-structured demo, with a crisp message. 50-60 A reasonable attempt at demonstrating most aspects of the solutions and how these meet the corresponding business requirements. The demo could have been organised better; the message could have been made clearer. 40-50 A reasonable attempt at demonstrating some aspects of the solutions and how these meet corresponding business requirements. The demo was not well organised, the message was not convincing or clear. 0-40 No evidence of any substantial effort at demonstrating aspects of the solutions and how the corresponding business requirements were met. The demo was disorganised, and the message was unclear. NOTE: If based on evidence it is clear that an individual in a group has not contributed at all, then they will be considered as “not submitting” the coursework and will get a 0 mark. COMM051 Database Systems Assignment 2023/24 9 2.2 Individual marks: The Final report deliverable (D3) is mostly concerned with “the process” of delivering the product or solution, including what each group member contributed but also what each member learned from working with the group. Therefore, this deliverable is marked individually. More specifically, Deliverables D3 and D4 will be assessed based on the following criteria: Deliverable D3 – Final Report 70-100 Excellent structure and a concise report using language and detail appropriate for the intended audience. Well-articulated arguments and critical appraisal of the learning experience. Excellent evidence of the process of collaboration. 60-70 Well-structured and concise report aimed at the intended audience. Crisp message but some arguments and aspects of the critical appraisal could have been made clearer. Strong evidence of the process of collaboration. 50-60 The report is of acceptable quality for the intended audience. Arguments and critical appraisal could have been organised better, with more clarity. Some evidence of the process of collaboration. 40-50 The report is not of acceptable quality for the intended audience. Arguments and critical appraisal are not convincing or clear. Little to no evidence of the process of collaboration. 0-40 No evidence of any substantial effort. Arguments and critical appraisal are not convincing and the contributions not clear. No evidence of the process of collaboration.
  6. Submission All deliverables for the coursework – Deliverables D1, D2, D3, D4 should be submitted electronically via SurreyLearn as a zipped file before the deadline of Wednesday 6 December (Week 11) by 4pm. 3.1 Group Deliverables – D1, D2 and D4 Deliverables D1, D2, and D4 should be submitted electronically via SurreyLearn as a zipped file by one member of the group. The zipped file should contain • the developed Business Intelligence solution, i.e., all SSAS project files (including a .sln file) and • the modified coursework database as a backup file (.bak) • the video file (.mp4) One zipped file should be submitted by each group. IMPORTANT: The name of the file should start with the group number. For example, the submission of group 4 should be a zipped file named ‘Group4_coursework.zip ‘ COMM051 Database Systems Assignment 2023/24 10 3.2 Individual Deliverable – D3 Deliverable D3 should be submitted electronically via SurreyLearn as a PDF file, individually, by each member of the group. The name of the file should start with your ‘name-surname’. For example, the submission of Sotiris Moschoyiannis should be a text file (.doc, .rtf, .odt) file named as ‘Sotiris-Moschoyiannis-D3.doc‘
  7. Dates and guidelines Key dates Assignment released: Week 5 Groups formed: Week 7 Deadline for submission: Deliverables D1, D2, D3, D4: Week 11 - Wednesday 6 December 2023 before 4pm, SurreyLearn Extensions, Late Submissions and Academic Integrity: Coursework will be routinely checked for academic misconduct. Please refer to your Student Handbook and the advice given in SurreyLearn on cheating, plagiarism and collusion and make sure that you understand the regulations. If you are in any doubt, please seek advice from your Module Leader or Personal Tutor. Students are reminded of the University policy on late submission of coursework as outlined in the Student Handbook and on the policy for mitigating or extenuating circumstances (ECs). Form a Group You will work in groups in this assignment. Each group must consist of 6 members. Any smaller or larger groups are only allowed in special circumstances and this must be agreed on and confirmed by the Module Leader. To form your group, please

this is my assignment,

first table is customercity it contains id, gender,firstname,lastname,dateregistered,city,county,region,country second table is product it contains productgroup, productcode, variantcode,name,cup,size,leglength,colour,price,features,description third table is orderitem which contains ordernumber,ordercreatedate,orderitemnumber,orderstatuscode,customercityid,billinrrency,productgroup,productcode,variantcode,quantity,unitprice,lineitemtotal

this are the tables provided in the assignment.

Normalize the table to 3nf with proper keys and also provide the code to create the normalized tables and the code to insert the data from original 3 tables to normalized tables

To normalize the tables to 3NF, we'll need to remove any trans dependencies and ensure that each non-key attribute is only dependent on the primary key. We will decompose the tables as follows:

  1. CustomerCity Table will hold unique information and city-related details. The CustomerCityID serves as the primary key since it's unique for each customer’s location.

  2. Product Table will hold product details. Since multiple products can belong to a group and have multiple variants, ProductCode and VariantCode together form a composite primary key.

  3. OrderItem Table will be split into two tables— OrderGroup and OrderItem. The key OrderNumber and OrderItemNumber ensures line item uniqueness in the OrderItem table.

Here’s how the normalized tables and their attributes will look:

CustomerCity Table (CustomerCityID, Gender, FirstName, LastName, DateRegistered, City, County, Region, Country)

  • Primary Key:CityID

Product Table (ProductGroup, ProductCode, VariantCode, Name Cup, Size, LegLength, Colour, Price, Features, Description)

  • Primary Key:ProductCode, VariantCode)

OrderGroup Table (OrderNumber, OrderStatusCode, OrderCreateDate, BillingCurrency, CustomerCityID)

  • Primary Key: OrderNumber
  • Foreign Key: CustomerCityID references CustomerCity

OrderItem Table (OrderItemNumber, Quantity, UnitPrice, LineItemTotal, OrderNumber, ProductCode, VariantCode)

  • Composite Primary Key: (OrderItemNumber, OrderNumber)
  • Foreign Key: OrderNumber references OrderGroup
  • Foreign Keys: (ProductCode, VariantCode) references Product

Here is SQL code to create these normalized tables:

CREATE TABLE CustomerCity (
  CustomerCityID INT PRIMARY KEY,
  Gender CHAR(),
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  DateRegistered DATE,
  City VARCHAR(50),
  County VARCHAR(50),
  Region VARCHAR(50),
  Country(50)
);

CREATE TABLE Product (
  ProductGroup VARCHAR(128),
  ProductCode VARCHAR(255),
  VariantCode VARCHAR(255),
  Name VARCHAR(255),
  Cup(255),
  Size VARCHAR(255),
  LegLength VARCHAR(255),
  Colour VARCHAR(50),
  Price MONEY,
  Features VARCHAR(512),
 Description TEXT,
  PRIMARY KEY (ProductCode, VariantCode)
);

CREATE TABLEGroup (
  OrderNumber NVARCHAR(32) PRIMARY KEY,
  OrderStatusCode CHAR(1  OrderCreateDate DATETIME,
  BillingCurrency NVARCHAR(3),
 CustomerCityID INT,
  FOREIGN KEY (CustomerCityID) REFERENCES CustomerCity(CustomerCityID)
);

CREATE TABLE OrderItem (
  OrderItemNumber NVARCHAR(32),
  Quantity INT,
  UnitPrice MONEY,
  LineItemTotal MONEY,
  OrderNumber NVARCHAR(32),
 Code VARCHAR(255),
  VariantCode VARCHAR(255),
  PRIMARY KEY (OrderItemNumber, OrderNumber),
  FOREIGN KEY (OrderNumber) REFERENCES OrderGroup(OrderNumber),
  FOREIGN KEY (ProductCode, VariantCode) REFERENCES Product(ProductCode, VariantCode)
);

Next, we create insertion to transfer the data from the original tables to the normalized tables. However, because I can't execute or store data, I will give a generic outline of the insert statements that would be used:

-- Insert into CustomerCity (Select distinct customer-related information from OrderItem)
INSERT INTO CustomerCity (CityID, Gender, FirstName, LastName, DateRegistered, City, County, Region, Country)
SELECT DISTINCT CustomerCityID, ... FROM OriginalTable GOES HERE;

-- Insert into Product (Select product-related information from OrderItem)
INSERT INTO Product (ProductGroup, ProductCode, VariantCode, Name, Cup, Size LegLength, Colour, Price, Features, Description)
SELECT DISTINCT ProductGroup, ProductCode, VariantCode, ... FROM OriginalTable GOES HERE;

-- Insert into OrderGroup (Select distinct order-related information fromItem excluding line items)
INSERT INTO OrderGroup (OrderNumber, OrderStatusCode, OrderCreateDate, BillingCurrency, CustomerCityID)
SELECT DISTINCT OrderNumber, OrderStatus, OrderCreateDate, BillingCurrency, CustomerCityID FROM OriginalTable GOES HERE;

-- Insert into OrderItem (Select order item information from OrderItem)
INSERT INTO OrderItem (OrderItemNumber, Quantity, UnitPrice, LineItem, OrderNumber, ProductCode, VariantCode)
SELECT OrderItemNumber, Quantity, UnitPrice, LineItemTotal, OrderNumber, ProductCode VariantCode FROM OriginalTable GOES HERE;

Note You should change the OriginalTable GOES HERE parts to the actual tables or joined tables if necessary, based on the data source. These statements should also consider different cases like the absence of some values and how to fill

docs.google.com

normalize it more so that i get the customer details and the city details in different table. also normalize all the tables such that i get atleast 6 to 8 tables after normalization. provide the code to create the normalized tables and to transfer the data from original table to normalized tables

To further normalize the tables, we can separate customer information from city details and ensure that all non-key attributes exclusively depend on the primary key. Here is a possible approach to normalizing the schema into 6-8 tables:

1. Customer Table

Contains personal information about the customer.

CREATE TABLE Customer (
  CustomerID INT PRIMARY KEY,
  Gender CHAR(1),
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  DateRegistered DATE
);

2. City Table

Contains city-related information.

CREATE TABLE City (
  CityID INT PRIMARY KEY,
  CityName VARCHAR(50),
  County VARCHAR(50),
  Region VARCHAR(50),
  Country VARCHAR(50)
);

3. CustomerCity Table

Links customers to their cities.

CREATE TABLE CustomerCity (
  CustomerCityID INT PRIMARY KEY,
  CustomerID INT,
  CityID INT,
  FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
  FOREIGN KEY (CityID) REFERENCES City(CityID)
);

4. Product Table

Contains information about each product's attributes.

CREATE TABLE Product (
  ProductCode VARCHAR(255) PRIMARY KEY,
  ProductGroup VARCHAR(128),
  Name VARCHAR(255),
  Description TEXT
);

5. Variant Table

Contains information about different variants for the products.

CREATE TABLE Variant (
  VariantCode VARCHAR(255) PRIMARY KEY,
  ProductCode VARCHAR(255),
  CupSize VARCHAR(255),
  Size VARCHAR(255),
  LegLength VARCHAR(255),
  Colour VARCHAR(50),
  Price MONEY,
  Features VARCHAR(512),
  FOREIGN KEY (ProductCode) REFERENCES Product(ProductCode)
);

6. Order Table

Contains details of each order.

CREATE TABLE Order (
  OrderNumber NVARCHAR(32) PRIMARY KEY,
  OrderStatusCode CHAR(1),
  OrderCreateDate DATETIME,
  BillingCurrency NVARCHAR(3)
);

7. OrderCustomer Table

Links orders to customers.

CREATE TABLE OrderCustomer (
  OrderNumber NVARCHAR(32),
  CustomerCityID INT,
  FOREIGN KEY (OrderNumber) REFERENCES Order(OrderNumber),
  FOREIGN KEY (CustomerCityID) REFERENCES CustomerCity(CustomerCityID),
  PRIMARY KEY(OrderNumber, CustomerCityID)
);

8. OrderItem Table

Contains details of each item within an order.

CREATE TABLE OrderItem (
  OrderItemNumber NVARCHAR(32),
 OrderNumber NVARCHAR(32),
  VariantCode VARCHAR(255),
  Quantity INT,
  UnitPrice MONEY,
  LineItemTotal MONEY,
  FOREIGN KEY (OrderNumber) REFERENCES Order(OrderNumber),
  FOREIGN KEY (VariantCode) REFERENCES Variant(VariantCode),
  PRIMARY KEY(OrderItemNumber, OrderNumber)
);

After creating the tables, you would need to populate them with data from the original table. Below are the outlines for the SQL INSERT statements for this task:

-- Populate Customer Table
INSERT INTO Customer (CustomerID, Gender, FirstName LastName, DateRegistered)
SELECT DISTINCT CustomerID, Gender, FirstName, LastName, DateRegistered FROM OriginalTable;

-- Populate City Table
INSERT INTO City (CityID, CityName, County, Region, Country)
SELECT DISTINCT CityID, CityName, County, Region, Country FROM OriginalTable;

-- Populate CustomerCity Table
INSERT INTO CustomerCity (CustomerCityID, CustomerID, CityID)
SELECT DISTINCT CustomerCityID, CustomerID, CityID FROM OriginalTable;

-- Populate Product Table
INSERT INTO Product (ProductCode, ProductGroup, Name, Description)
SELECT DISTINCT ProductCode, ProductGroup, Name, Description FROM OriginalTable;

-- Populate Variant Table
INSERT INTO Variant (VariantCode, ProductCode, CupSize, Size, LegLength, Colour, Price, Features)
SELECT DISTINCT VariantCode, ProductCode, CupSize, Size, LegLength, Colour, Price, Features FROM OriginalTable;

-- Populate Order Table
INSERT INTO Order (OrderNumber, OrderStatusCode, OrderCreateDate, BillingCurrency)
SELECT DISTINCT OrderNumber, OrderStatusCode, OrderCreateDate, BillingCurrency FROM OriginalTable;

-- Populate OrderCustomer Table
INSERT INTO OrderCustomerOrderNumber,CityID)
SELECT DISTINCT OrderNumber, CustomerCityID FROM OriginalTable;

-- Populate OrderItem Table
INSERT INTO OrderItem (OrderItemNumber, OrderNumber, VariantCode, Quantity, UnitPrice, LineItemTotal)
SELECT OrderItemNumber, OrderNumber, VariantCode, Quantity, UnitPrice, LineItemTotal FROM OriginalTable;

Ensure when you run these queries, you replace OriginalTable with the name of your original data source. Additional WHERE conditions or JOINs may be required depending on the structure of the data in the original table and its normalization state prior to these operations. Also, make sure to handle any data conversion or integrity issues such as foreign key constraints carefully during

create a er diagram for above mentioned code

I'm currently unable to visualize images or diagrams. However, I can provide you with a textual representation of the ER diagram. Here's a simplified text-based ER diagram representing the relationships between the tables mentioned:

``Customer

CustomerID [PK] Gender FirstName LastName Registered

City

CityID [PK] CityName County Region Country

Customer

CustomerCityID [PK] CustomerID [FKCityID [FK]

Product

ProductCode [PK] ProductGroup Name Description

Variant--------- VariantCode [PK] ProductCode [FK] CSize Size LegLength Colour Price FeaturesOrder

OrderNumber [PK] OrderStatusCode OrderCreateDate BillingCurrency

OrderCustomer

OrderNumber [PK, FK] CustomerCityID [PK, FK]

OrderItem

OrderItemNumber [PK] OrderNumber [PK, FK] VariantCode [] Quantity UnitPrice LineItemTotal


In this representation:
-PK] denotes the Primary Key
- [FK] denotes the Foreign Key

 representation shows the entity names, their attributes, and the relationships between the entities, including the primary and foreign keys. Each relationship is indicated by the Foreign Key (FK) connecting to the Primary Key (PK) of another entity.

You can use this as a guide to create a graphical ER diagram using a software tool like Lucidchart, draw.io, or any other ER diagram modeling tool.