Assignment Prompt & Instructions
– ## AdvancedDatabasesFinal.docx
**## AdvancedDatabasesFinal.docx**
2. Final Assignment – equivalent to 4,000 words
The final module mark is based on two deliverables focused on the CarNow case study described below.
– 50% of the final mark
a. An advisory report – 50 % of the final mark
Includes 5% (of the module grade) given for the quality of expression in English (written + oral)
The presentation must address the following questions:
Presentation of the database design (around 45% of the presentation)
This section describes your proposed database design. It should include a diagram of how the tables relate to each other and a detailed discussion of the design choices and how they match / follow the business needs. There should be at least 2 examples of how the business needs are translated in design choices (ex: international company, rental options, multiple drivers, invoicing with multiple payments, etc.)
The best presentation is to start with the overall diagram and then break it down into pieces for the discussion of the design choices and the business needs.
A database with 5-6 tables is not enough. It will not get a passing grade.
Reporting solution (around 35% of the presentation)
This section will discuss how data can be extracted / presented from the database. Management reports which are using aggregation by
region and
time are ideal for this.
Discuss briefly the business needs and then present your solution.
Here are a few examples to get you started, but you may create your own scenarios:
how much of the fleet is being currently used (per branch / etc.);
monitoring the incidents by region;
car breakdowns; repeating customers; comparing the month performance to the average performance in the past months. You may also expand the data model to capture more scenarios (ex: costs -> profitability).
The SQL scripts must be included in the presentation. Example output data from the scripts is highly recommended.
Include the SQL scripts directly on the slide as you discuss each report.
Brief discussion of the performance considerations (around 20% of the presentation)
Starting from the numbers in the case study description, discuss briefly some of the performance aspects. For example, concurrent requests, duration of queries, volume of data, evolution in time, etc.
An estimation of the expected data volume per year/ month would be useful, for example.
It is recommended that you practice before making the final recording; see this link for help:
[https://support.microsoft.com/en-gb/office/record-a-slide-show-with-narration-and-slide-timings-0b9502c6-5f6c-40ae-b1e7-e47d8741161c](https://support.microsoft.com/en-gb/office/record-a-slide-show-with-narration-and-slide-timings-0b9502c6-5f6c-40ae-b1e7-e47d8741161c) while the following includes tips for developing a good presentation
[https://support.microsoft.com/en-gb/office/tips-for-creating-and-delivering-an-effective-presentation-f43156b0-20d2-4c51-8345-0c337cefb88b](https://support.microsoft.com/en-gb/office/tips-for-creating-and-delivering-an-effective-presentation-f43156b0-20d2-4c51-8345-0c337cefb88b)
2.b. Written report –
**50% of the module mark**
The written report, maximum 1500 words, completes the video presentation with an analysis of:
Cloud vs. inhouse (around 50% of the report)
Until recently, the question of cloud or in-house hosting, was almost automatically answered with: the cloud, and the argument was: lower costs. However, more and more, the news reports are about data being stolen and / or exposed, which has either a huge impact on a company’s reputation, or an associated cost (or both).
As such, you are requested to revisit the main arguments and make a justified recommendation for the case study. A critical analysis of the literature on the topic should be conducted including topics such as backup & restore, disaster recover, high-availability, performance. A rough estimation of the costs could be included.
Given the limited space available, the use of tables and diagrams is highly recommended.
Use
**short** paragraphs. This will ensure a clear and concise presentation.
Be sure to have a clear recommendation.
Implementation guidelines (around 50% of the report)
The report will end with some recommendations for the implementation of the new database (and application). Topics for discussion could be: a possible roadmap, best practices from the literature (development vs production environments), possible risks / pitfalls to avoid, maintenance and deployment of updates, etc.
Quality of expression in English (5% of the overall module grade)
The overall quality of the presentation in English is evaluated (grammar, vocabulary). The video presentation will be examined first and considered for this part of the grade.
Formatting requirements
The video presentation should have a clear title slide, similar to the cover page for the written report. References should be included on the image of the recording and should not be read during the recording.
The list of references should be on the last slide. No need to read them.
The written report must have a clear structure and must include:
· Cover page (an example is available to you in Induction/Unit 4)
· Abstract (no more than 150 words, a single paragraph)
· Table of contents (Table of tables/figures if necessary) – numbered sections, page numbers
· A subsection for each of the questions of the assessment –
**no more than 1’500 words**
· A list of references – at Master level you must use in-text citations to support your arguments and any work cited must appear in the References list at the end of the work. Use the APA formatting.
Avoid Plagiarism.
Please ask any questions about the assignment in the class discussion forums.
**Case study: CarNow – Rent-a-car company, database transformation**
CarNow is a rent-a-car business that now has offices in 40 countries and manages a fleet of more than 20 000 cars. You have been hired as a Database Consultant to support their transition to a new web application that would replace their current outdated solution based on Microsoft Access.
The current solution uses a local database in each branch which is accessed by a Microsoft Access application opened by each of the employees in that branch. End of day, week and month reports are being sent per email to headquarters. Despite this operation being automated via scripts, the process is cumbersome and error prone. It is becoming almost impossible to deploy updates to the software solution across the many branches.
There is also the fact that a new account has to be opened for the customers if they visit a different branch of CarNow, which is leading to additional time being spent entering the customer’s data in the forms.
For the headquarters business reporting there is currently a different database being used where all the end of day/week/month reports are being loaded from each branch. This database works well except when there are issues and data is not being received in time from one or the other branch which causes errors in the reports.
To address all these issues the company has decided to create a new application using the web browser for the user interface and a centralized database. However, not having the necessary knowledge in-house, they are concerned about how to structure the database, and more than that, about the performance of the database when all the branches will connect simultaneously to the same database.
There is also the issue of the geographical distribution of the branches which raises questions on the accessibility of the central database. What are the available options here? The company’s management has heard about “the cloud” and would like to know more about this option. What does that mean for the database development, security, backup, access time, etc? Does the cloud mean that they have to host the database in several locations? How will the data be synchronized?
Until the application is finalized, which is only expected to happen in one year, there will be one SQL developer available at headquarters to provide management with the necessary reporting data.
The database must contain all the necessary data to support the business operations:
· Record detailed customer data (unique account across all branches)
· Keep track of the fleet
· Including the maintenance operations (both scheduled and unexpected breakdowns)
· Detailed information about the rentals (with options)
· Information about the invoices and payments
## 
## image1.emf
## image2.emf
**## AdvancedDatabasesFinal.docx**
2. Final Assignment – equivalent to 4,000 words
The final module mark is based on two deliverables focused on the CarNow case study described below.
– 50% of the final mark
a. An advisory report – 50 % of the final mark
Includes 5% (of the module grade) given for the quality of expression in English (written + oral)
The presentation must address the following questions:
Presentation of the database design (around 45% of the presentation)
This section describes your proposed database design. It should include a diagram of how the tables relate to each other and a detailed discussion of the design choices and how they match / follow the business needs. There should be at least 2 examples of how the business needs are translated in design choices (ex: international company, rental options, multiple drivers, invoicing with multiple payments, etc.)
The best presentation is to start with the overall diagram and then break it down into pieces for the discussion of the design choices and the business needs.
A database with 5-6 tables is not enough. It will not get a passing grade.
Reporting solution (around 35% of the presentation)
This section will discuss how data can be extracted / presented from the database. Management reports which are using aggregation by
region and
time are ideal for this.
Discuss briefly the business needs and then present your solution.
Here are a few examples to get you started, but you may create your own scenarios:
how much of the fleet is being currently used (per branch / etc.);
monitoring the incidents by region;
car breakdowns; repeating customers; comparing the month performance to the average performance in the past months. You may also expand the data model to capture more scenarios (ex: costs -> profitability).
The SQL scripts must be included in the presentation. Example output data from the scripts is highly recommended.
Include the SQL scripts directly on the slide as you discuss each report.
Brief discussion of the performance considerations (around 20% of the presentation)
Starting from the numbers in the case study description, discuss briefly some of the performance aspects. For example, concurrent requests, duration of queries, volume of data, evolution in time, etc.
An estimation of the expected data volume per year/ month would be useful, for example.
It is recommended that you practice before making the final recording; see this link for help:
[https://support.microsoft.com/en-gb/office/record-a-slide-show-with-narration-and-slide-timings-0b9502c6-5f6c-40ae-b1e7-e47d8741161c](https://support.microsoft.com/en-gb/office/record-a-slide-show-with-narration-and-slide-timings-0b9502c6-5f6c-40ae-b1e7-e47d8741161c) while the following includes tips for developing a good presentation
[https://support.microsoft.com/en-gb/office/tips-for-creating-and-delivering-an-effective-presentation-f43156b0-20d2-4c51-8345-0c337cefb88b](https://support.microsoft.com/en-gb/office/tips-for-creating-and-delivering-an-effective-presentation-f43156b0-20d2-4c51-8345-0c337cefb88b)
2.b. Written report –
**50% of the module mark**
The written report, maximum 1500 words, completes the video presentation with an analysis of:
Cloud vs. inhouse (around 50% of the report)
Until recently, the question of cloud or in-house hosting, was almost automatically answered with: the cloud, and the argument was: lower costs. However, more and more, the news reports are about data being stolen and / or exposed, which has either a huge impact on a company’s reputation, or an associated cost (or both).
As such, you are requested to revisit the main arguments and make a justified recommendation for the case study. A critical analysis of the literature on the topic should be conducted including topics such as backup & restore, disaster recover, high-availability, performance. A rough estimation of the costs could be included.
Given the limited space available, the use of tables and diagrams is highly recommended.
Use
**short** paragraphs. This will ensure a clear and concise presentation.
Be sure to have a clear recommendation.
Implementation guidelines (around 50% of the report)
The report will end with some recommendations for the implementation of the new database (and application). Topics for discussion could be: a possible roadmap, best practices from the literature (development vs production environments), possible risks / pitfalls to avoid, maintenance and deployment of updates, etc.
Quality of expression in English (5% of the overall module grade)
The overall quality of the presentation in English is evaluated (grammar, vocabulary). The video presentation will be examined first and considered for this part of the grade.
Formatting requirements
The video presentation should have a clear title slide, similar to the cover page for the written report. References should be included on the image of the recording and should not be read during the recording.
The list of references should be on the last slide. No need to read them.
The written report must have a clear structure and must include:
· Cover page (an example is available to you in Induction/Unit 4)
· Abstract (no more than 150 words, a single paragraph)
· Table of contents (Table of tables/figures if necessary) – numbered sections, page numbers
· A subsection for each of the questions of the assessment –
**no more than 1’500 words**
· A list of references – at Master level you must use in-text citations to support your arguments and any work cited must appear in the References list at the end of the work. Use the APA formatting.
Avoid Plagiarism.
Please ask any questions about the assignment in the class discussion forums.
**Case study: CarNow – Rent-a-car company, database transformation**
CarNow is a rent-a-car business that now has offices in 40 countries and manages a fleet of more than 20 000 cars. You have been hired as a Database Consultant to support their transition to a new web application that would replace their current outdated solution based on Microsoft Access.
The current solution uses a local database in each branch which is accessed by a Microsoft Access application opened by each of the employees in that branch. End of day, week and month reports are being sent per email to headquarters. Despite this operation being automated via scripts, the process is cumbersome and error prone. It is becoming almost impossible to deploy updates to the software solution across the many branches.
There is also the fact that a new account has to be opened for the customers if they visit a different branch of CarNow, which is leading to additional time being spent entering the customer’s data in the forms.
For the headquarters business reporting there is currently a different database being used where all the end of day/week/month reports are being loaded from each branch. This database works well except when there are issues and data is not being received in time from one or the other branch which causes errors in the reports.
To address all these issues the company has decided to create a new application using the web browser for the user interface and a centralized database. However, not having the necessary knowledge in-house, they are concerned about how to structure the database, and more than that, about the performance of the database when all the branches will connect simultaneously to the same database.
There is also the issue of the geographical distribution of the branches which raises questions on the accessibility of the central database. What are the available options here? The company’s management has heard about “the cloud” and would like to know more about this option. What does that mean for the database development, security, backup, access time, etc? Does the cloud mean that they have to host the database in several locations? How will the data be synchronized?
Until the application is finalized, which is only expected to happen in one year, there will be one SQL developer available at headquarters to provide management with the necessary reporting data.
The database must contain all the necessary data to support the business operations:
· Record detailed customer data (unique account across all branches)
· Keep track of the fleet
· Including the maintenance operations (both scheduled and unexpected breakdowns)
· Detailed information about the rentals (with options)
· Information about the invoices and payments
## 
## image1.emf
## image2.emf