Database and Analytics Principles
- Subject Code :
LD7084
Scenario
Newbury Business School (NBS) provides a new blended course to improve students' English skills in speaking, reading and writing. This course uses interactive multimedia activities and targets people interested in learning English, German, French and Spanish languages around the world. NBS has 10 campuses around the world including London, Hongkong, Paris, Berlin, Rome, Dubai, Copenhagen, Oslo, Ankara and Cairo.
Since the college intends to deliver all classes in a blended model (i.e. online and in campus), hence they are looking to develop an effective Relational Database Management System (RDBMS) and Data Warehouse to cater the needs of their growing educational business.
As a data analyst, you have been asked to develop a DB system for NBS, which should satisfy the following information requirements:
- The database should contain information about campuses, learners, tutors, managers, enrollment, fees, learning resources and related activities. For each learner, the database should at least store the learner id, last name, first name, email, age, gender, learning style (Fulltime or parttime) and phone number.
- For each tutor, the database should at least store their name, salary, type, email address and qualifications.
- Each tutor is assigned a maximum of five classes each term. Each class has a starting date, ending date and size, where minimum class size is 5 students and maximum size should be 20 students.
- Each campus runs four semesters full time each year and two part time semesters each year.
- Student fees are 300$ per semester for Full Time and 150$ for part time. A learner who enrolls into more than one course per year gets a 20?es deduction.
- Each course leader manages at least one tutor and maximum 10 tutors, whereas each tutor supervises minimum 5 students and maximum 20 students in each class.
- The college provides four different types of learning resources including videos, audios, PowerPoint presentations and reading text. Each learning resource should have relevant data such as a unique id, type, duration, author, date of issue etc.
- Each learning resource will be connected with a set of learning activities such as drag and drop, fill in the missing, write full answers, sort order of words or sentences etc. Each activity should have an id, issue date, author, marks etc.
- For each enrollment, it is required to store enrollment data such as learner id, course duration, fees, learner address and contact details etc. Each student can be enrolled only into one course at once.
Relevant assumptions can be made, if required.
Section 1 - Database (35%) (Learning Outcome 1 & 2)
- Using Crows foot notation, design a logical data model (ERD) for Newbury Business School database in a professional modeling tool of your choice such as starUML or draw.io. All entity types, attributes and the relationships between them must be labeled clearly. Your design should show all cardinality and participation constraints accurately. Discuss and justify your design choices in detail.
- Produce a script using appropriate SQL commands (DDL) to create a set of database tables using MySQL relational database. Your tables should show all constraints applied at either the column or the table level. You are expected to implement the database so that it must meet the requirements described in the scenario. Populate all the tables in the database you created with some meaningful data (At least 5 records in each table).
- As a Data Analyst, produce 5 DML statements that will provide valuable insights for Newbury Business School. Your DML statements should contain at least one of the following:
- statement involving a self-join.
- statement involving an equi-join.
- statement involving at least one group function
- statement involving at least one subquery.
- statement involving null values.
Section 2 - Data warehouse (20%) (Learning Outcome 1 & 2)
The management of Newbury Business School wants to gain competitive advantages over competitors and proposed to invest in the latest tools and technologies that will enable them to make strategic as well as management decisions. One of the suggestions is to build a Data Warehouse.
Assessment Brief
- Using Kimballs four step dimensional design process, draw/design either a star schema or a snowflake schema, which will help the management understand the business revenue and recruitments across the different countries in the world.
- Using the concept of a Data Cube, show all possible combinations of revenue insight that can be derived from the Warehouse. Justify your Modeling choice.
Section 3 Business Intelligence (Tableau) (30%) (Learning Outcome 2 & 3)
You are hired as a Data Analyst by one of the leading supermarkets in the US and provided with the Dataset in Appendix 1. You must use Tableau to analyze the data set provided and find answers to the following:
- Using Tableau, explain what data integrity checks you will perform on each of the columns in the Dataset.
- Given the item ordered comes with a 60 days warranty from the date it was ordered. Use Tableau to calculate the Warranty End date.
- Using an appropriate chart, display the total sales made in each City. Order the result by the City with the highest sales.
- Using Tableau, display all cities that generated more than $3,000,000 for the year 2019.
- Using Tableau, display the top 5 selling products for the year 2019. Your chart should also show the quantity of products sold.
- Using Tableau, display the total sales made in each month for the year 2019.
- Given that the profit on each item is set at 30%. Use an appropriate chart to display the total sales and total profit made by each product for the year 2019.
- Using tableau, find the maximum sales generated in Boston, Los Angeles, Seattle and New York.
- Build a dashboard to display the results obtained in Number 3, 4, 5 and 6.
For questions 3 8, each answer should be on a separate worksheet with a chart, graph or table that shows your finding.
Section 4 Critical Reflection (15%) (Learning Outcome 4)
The manager of NBS management system has decided to create a social network dedicated for staff and students to share their experiences in learning new languages.
This social network will present a new digital platform and discussion forum to be developed on Web and smartphone devices. It will be used by the management as well to analyze data in order to improve their services to their students.
You are asked to investigate whether it is better to use a Relational Database or a NOSQL database for the new proposed social network. You are required to write a report to explain the factors that will influence your choice of database and why you will be recommending one type of database over the other.
Grading Guidance
Distinction:
Excellent in-depth application and critical research on the processes and user requirements. Provide in-depth knowledge of how to design and evaluate a complete Database, data warehouse for a given scenario.
Excellent in-depth understanding and demonstration of data analytics using tableau.
Commendation:
Shows good research on the processes and user requirements. Provides good knowledge of how to design and Database, data warehouse for a given scenario. Shows good understanding and evaluation of data warehouse, data mining and Hadoop.
Pass:
Provides basic understanding of the deliverables. Provides end-to-end design and all requirements are met. Report has some errors and lacks adequate explanation. The robustness and correctness of Database, data warehouse and data analytics are not explained thoroughly. Evidence of design is shown but inadequate explanation using tableau.
Fail:
Provides incomplete attempt or lacks substantial parts of the deliverables. Fails to demonstrate understanding of the concepts required to implement deliverables. Work lacks serious clarity and detail. There are several errors in the report.
Academic Integrity Statement: You must adhere to the university regulations on academic conduct. Formal inquiry proceedings will be instigated if there is any suspicion of plagiarism or any other form of misconduct in your work. Refer to the Universitys Assessment Regulations for Northumbria Awards if you are unclear as to the meaning of these terms. The latest copy is available on the University website.
https://northumbria-cdn.azureedge.net/-/media/services/academic-registry/documents/qte/assessment/guidance-for-students/pl,-d-,005-v004-academic-misconduct-policy.pdf?modified=20210212163133
(last accessed on 25th September 2022)