refdefTroniksCW2021 (1)(1)

Database Administration and Security/ Database Admin and Data Sources

Referral/Deferral Coursework

Academic Year 2020/21

 

Introduction

Troniks is one of the leading electronic companies in Great Britain with dozens of sites throughout the country.  It sells electronic products to small businesses through its trade warehouses.  It does not sell directly to the public; instead, its customers must register with Troniks and be added to the company database before they can place orders with the company.

Troniks‘ product base consists of clocks telephones, audio visual equipment, cameras, associated accessories and the like, which they purchase from appointed suppliers whose details exist in the Troniks database.

To date, the company’s sales and purchasing activities have been conducted primarily by telephone but Troniks is considering alternatives to manage their supply chain more effectively with the introduction of web based customer ordering and supplier invoicing.  This would mean customers could: submit orders directly via a web interface to the database; check current product lists via a product catalogue facility; check product availability at branches.  A web based front-end facility would also exist which would allow suppliers to submit invoices directly to the accounting database which links in with the sales/purchasing database.  Consideration needs to be given as to how this would affect the SQL Server implementation should the company choose to proceed down this line.

The method currently used by the company to store customer details and data concerning sales and purchasing activities is an Access database.

Troniks is looking to expand its business and operations with several possible mergers and acquisitions and are therefore considering transferring their database over to SQL Server.

The invitation to tender has requested that your bid proposal include the following elements:

• Security Mechanisms

 

What facilities are offered by SQL Server and how would these prove useful to Troniks. One of the problems Troniks currently faces is that all departments have access to all data in the existing database.  One specific requirement they have of the new system is that Sales Staff should not be able to access Product Cost Prices.

 

• Backup and Restore Facilities

 

Having recently experienced a huge loss in revenue as a result of a lost database through disk failure, Troniks would like to know what options are available to them in terms of protecting their data, and what kind of response they can expect if they were to experience a similar database failure.

 

• Task Automation

 

Although Troniks have an in-house technical expert who currently supports the database, their knowledge of SQL Server is limited.  The introduction of the new RDBMS coupled with the growth in the size of the company is likely to change the nature and magnitude of their job.  As such they would like to be able to automate many of the day to day tasks that they have previously been required to perform manually and would like to know what facilities SQL Server offers to facilitate this along with demonstrations where appropriate.

 

Troniks have provided you with a sample of their current Access database containing test data to work on and they require that you use this to provide demonstrations of what SQL Server can offer the company.

The Assignment

Your consultancy firm is bidding for this contract and will need to produce a business proposal that will address the company’s requirements and outline the possible business benefits to be gained from implementing an RDBMS such as SQL Server.  You should present your proposal as if you were professional consultants as a report and should address the database administration and structured query language tasks below.

Your report should contain a table of contents, a brief introduction, numbered sections, appropriate annotated illustrations/screenshots and SQL scripts.

 

Once complete you must submit your report on blackboard.

 

 

Marking Scheme Database Administration (total 25)

Feature	Assessment Method	Marks Available
Incorporation of Access Database into SQL Server	Explanation of mechanism used to achieve this	4
Security Features ­   roles / permissions ­   objects / views ­   authentication ­   encryption etc.	Explanation of recommended security features. Relevance in context of Troniks‘ requirements and organisational structures	4
Backup & Restore Procedure ­   recovery models ­   backup strategy etc	Explanation of recommended backup and restore procedures.  Relevance in context of Troniks‘ requirements	4
Task Automation ­   Jobs ­   Alerts	Explanation of appropriate automation of routine admin task Relevance in context of Troniks‘ requirements	4
Business Benefits	Explanation of Business Benefits of SQL Server and the suitability for a medium sized organisation such as Troniks	4
Clarity of report		5
sub total		/25
SQL Appendix- see following section for detailed requirements	Task	Mark
1	Explain outcomes	3
2	Alter the database	2
3	Audit table and trigger	5
4	Stored procedure that will accept a customer ID	5
5	Trigger that will produce an error message	5
6	Branch name and address stored procedure	5
	SQL subtotal	25
	Overall report total	/50

 

 

SQL Troniks detailed requirements

All SQL scripts must be provided in the report appendix for each task.

Basic Tasks

1. Run the following inserts to check primary key and foreign keys have been set up correctly. These must be run against tables that have not had any data modifications since the import. Explain the results.
   1. Insert into tblProduct values (4,3,3,’Card Player’,45, 58,16)
   2. Insert into tblPOLine values (32400,10,1,75,13, null)
   3. Delete from tblCustomer where CustomerID=4 3 marks
2. Explain how to alter the database so that if the SupplierID in tblSupplier changes, all other references to that supplier are also updated automatically. 2 marks
3. There have been some problems with the total charge to customers when the VAT field has been updated and changed. Provide the scripts to create an audit table to track users (username) who update the tblSOLINE table and the time that they did the update. You do not need to track inserts or deletes. You need to provide the create table code; the create trigger code; the update a record in tblSOLine code; the ‘display the records in the audit table’ code to show that the trigger has worked; the ‘Insert a new record to tblSOLine’ code;

Include appropriate evidence to show that the update fires the trigger but the insert has not fired the trigger  5 marks

 

1. Create a stored procedure that will accept a customer ID and display all the sales made by that customer and explain how to run the stored procedure. 5 marks

Intermediate

1. On tblProduct create a trigger that will produce an error message if you try to reduce the number of items in stock below the reorder value of 10 items. Provide the script and evidence of it working. 5 marks

Advanced

1. Create a stored procedure that will display the Branch name and address (concatenated so that they appear in the same column) in the grids section of the query pane when the user provides a Branch ID. The code should check that the Branch ID given is within the valid range of Ids and if the ID given is above or below the valid range a message needs to be displayed to the user alerting them of the error. The procedure must execute correctly to gain the marks. Ensure that you have included scripts that will show all three possible outcomes. 5 marks

 

Professional Computing Project

Work for refers/defers – web portfolio

 

Your task is to write a WordPress website and a report.

 

The report should refer to the website through a URL. Where needed, you should provide test usernames and passwords for site use or administration. If the site cannot be publicly hosted, an alternative is to record a video presenting it, to share as an alternative to a site URL.

 

Whether you share a site URL or a recording of the site, it is your responsibility to make sure that key information to understand the site contents and its technology, and to show that the site is not limited to a client only system.

 

The website

The aim of your website should be to present in a better way, the videos of a Youtube education channel of your choice (you can use https://www.youtube.com/user/cboisvert2 if you are short of ideas).

Your site should include these three features:

• Some way to show each the videos from the YouTube channel
• Respect obligations with regard to users’ privacy
• A consistent interface, making it easy to access videos and information about the site

The next features, listed below are to choose from. Your site should include 6 or more; you can prioritise which ones you implement, and which ones you discuss in the report without implementing:

• A keyword search facility for videos
• Descriptions for each video
• A thumbnail page (or several) showing videos to choose from
• Ensure the site is accessible on both large and small screens
• making videos easy to manage – e.g. allowing the site owner to
  • add videos to the site
  • remove them
  • change descriptions and keywords / tags
• allow inclusion of other videos (from alternative sources or from different YouTube channels)
• Organise videos in themes (in the manner of YouTube playlists, or copied from the channel’s playlists)
• Some way of rating a video (giving it stars)
• Some way to join in a discussion linked to each video – either by showing YouTube comments on the website, or a separate discussion.

The report

The report should discuss every one of the features – those you implemented, and those you didn’t – and discuss the obligations your web site should respect.

For each feature you implemented, you should explain:

• How it looks and feels to a site user
• How you created it
• How the site owner can adapt the site

For features you did not, you should explain:

• Why in your site, you estimated that feature is not a priority
• A description of techniques that would help to implement it
• Give a clear “definition of done”, as a description or in visual form

For obligations the site should respect, you should explain:

• Work you have implemented (for example, cookie consent, if your site uses any)
• Actions that need to be taken by the site owner as they make the site public and as they maintain it in future

Marking criteria

 

Criteria	Not passable ( < 40%)	Passable to good pass (41 to 69%)	High quality work (over 70%)
Compulsory site features	Features not implemented; testing not possible (e.g. if the site has very few videos)	Features implemented and testable; result visible by trying the site and explained in the report	Implementation technique very clear; management of the site facilitated and documented
Implemented features	Too few features covered, or lack of information about how they are implemented.	More than half the features implemented and explained. Where management by the site owner is needed, the reports makes clear how.	Most of the features implemented. Explanation is very clear. Well-presented administration facilities support the site owner.
Planned (discussed, not implemented) features	Definition of done missing or not clear; no justification of why the feature is a low priority; no suggestion of implem-entation technique	Description makes it clear how features would be implemented and what the aim is; its exclusion from the current implementation is justified.	The plan is precise and gives a good idea both what could be achieved, and how. The reasons given for not implementing it are very clear and appropriate.
Site obligations	Important obligations neither implemented nor discussed	Key issues for a small-scale site – user privacy in particular – are respected.	Web site legal obligations are addressed, and their relevance to this site discussed. Where relevant for this site, technical (implemented) solutions or owner information is given.

55-503965 System Architectures 2020/21
1
Coursework Assignment – Referral
This is an INDIVIDUAL assignment.
ABC Credit Union is a local not-for-profit community co-operative which provides savings and loan
services to local people. Until now, the organisation has had no formal web presence. However, recent
months have seen staff responding to an increasing number of general queries about the purpose and
goals of the organisation. Dealing with these enquiries consumes a great deal of time and the
organisation has decided they need a more formal web presence to try and reduce the load on their
office staff and filter out the most common queries.
The organisationwould like to have a LAMPweb server set up, a Content Management System(CMS)
installed, and some web pages created. However the staffs lack the systems administration skills
needed to install a CMS from the ground up. In line with other local not-for-profits, the organisation
has elected to follow the open source route due to its cost benefits, and their ability to tailor solutions
later on. In order to deliver this brochure site, the organisation would like to have a Linux-based CMS
installed which is not Magento. ABC Credit Union has already decided that Magento does not meet
their requirements. There are a number of such CMS available and ABC Credit Union has requested
that you select the one that best meets their needs and requirements.
You will need to critically evaluate 3 CMS and then select the one you feel best meets the needs and
requirements of ABC Credit Union.
55-503965 System Architectures 2020/21
2
Task 1 – Setting Up Web Server and Installing CMS
1. Create a fresh clean Virtual Machine. The VM should be named abcServer. [10 marks]
No marks are awarded if VM is not loading or if the VM is not named abcServer. Zero marks are
awarded if credentials(username and password) to access VM are not provided
2. Install the required software to set up the VM as a LAMPweb server [10 marks]
Full marks are awarded if all the software that makes the VM a LAMP server is installed and
correctly configured. No marks are awarded if VM is not loading. Zero marks are awarded if
credentials(username and password) to access VM are not provided
3. Install all the required packages to create a working CMS install. You are not to install the
latest version of CMS Magentoas ABC Credit Union has already decided that it does not
meet their requirements
[10 marks]
Complete CMS installation. Full marks are awarded if the latest version of the selected CMS is
installed and is fully functional. No marks are awarded if details on how to access the system
are not provided. 5 marks awarded if version of CMS is not the latest. No marks awarded if CMS
installed is Magento. Zero marks are awarded if credentials(username and password) to access
VM are not provided
4. Create a homepage , a frequently asked questions page and 2 content pages containing
information about 2 exemplar products/services they offer. Set up page headings and use
filler text in place of real content, which will be replaced by staff once the system is running.
The functionality of your system will be assessed based on this content.
[10 marks]
2 marks awarded for each page created. Only 1 mark awarded if the page does not contain
appropriate content. No marks awarded if CMS installed is Magento. Zero marks are awarded if
credentials(username and password) to access VM are not provided
Task 2- Installation Procedure and TechnicalRequirements
Documentation of all the tasks should be done in the same document. Save the document as
Technical_Document. The document has got to be prepared using a word-processing application and
55-503965 System Architectures 2020/21
3
be in format which would enable Tutors to view the document on Sheffield Hallam University
computers. It is strongly advised that the document be in doc/docxor .pdfformat
1. Critically evaluate 3 open source CMS and then select one you feel will meet the needs and
requirements of ABC Credit Union. Justify the selection of the preferred CMS
[15 marks]
4 marks are awarded for providing a detailed evaluation for each CMS. 2 marks are awarded for an
evaluation that lacks detail. 1 mark is awarded for just describing the CMS. 1 mark will be awarded
for stating which CMS you selected. 2 marks are awarded for providing plausible justification for
selection of preferred CMS. No marks are awarded for evaluating CMS which is not open source
2. Clearly document only the CMS install process so that the installation could be repeated by a
competent member of staff at a later date. Use screen captures and or screen-casts to
document your installation process. You are not to document the process of creating VM
and setting it up as LAMP web server. It is assumed the staff at ABC Credit Union already
have these skills.
[30 marks]
Clear, concise documentation which contains appropriate information. Screen casts are
accompanied by clear text to describe or provide more information. The documentation is
organised into headed sections. Marks will be lost for adding details which are not relev ant or
for leaving out important information. If text is not accompanied by screen casts or screen casts
not accompanied by text then only a maximum of 10 marks will be awarded. 10 marks will be
awarded for a detailed justification for choice of CMS. No marks awarded if CMS installed is
Magento or if VM is not loading. Zero marks are awarded if credentials(username and
password) to access VM are not provided
55-503965 System Architectures 2020/21
4
3. Clearly document the process of adding new content to the site
[10 marks]
Steps for adding content to pages are clearly described. 10 marks are awarded for an annotated
description of steps to add content to the pages. No marks awarded if CMS installed is Magento
or if VM is not loading. Zero marks are awarded if credentials(username and password) to
access VM are not provided
4. Clearly how ABC Credit Union could make the web server more secure using the framework
nftables
[5 marks]
Appropriate security measures for web server proposed. Full marks are awarded if measures to
ensure a more secure web server are described accompanied by appropriate technical
information or commands. No marks are awarded if the firewalls being described are not built
using nftables. Zero marks are awarded if credentials(username and password) to access VM
are not provided
Hand-in Instructions
A working VM should be available on lab by 3:00pmon Thursday 1 July, 2021. There should be only
one VM to be submitted as part of coursework assessment in this folder. All the other VMs should
be removed. All the VMs will be transferred from lab to another drive for marking soon after the
deadline. If at the time of transferring the VMs your application files are not in place this will be
taken as a non-submission.
You are required to create a text file named README.txt. In this text file you are to add ALL details
which will help access the VM, the Web Server, Database Server(both for root and normal user) and
the website. The details include the usernames and passwords required to login into the VM the
Web Server, Database Server(both for root and normal user) and information required to access the
website including its URL. The file README.txt is to be uploaded on blackboard.Failure to provide
these details will lead to loss of marks.
Submit the document Technical_Document, clearly marked with your name, course and date
through the link Reassessment -002 portfolio submission point on Blackboard by 3:00pmon
Thursday 1 July, 2021.

 

 

Web Application Design & Implementation

 

BSc Computing

55-504265

LEVEL 5

Reassessment Brief

Individual Assignment

The reassessment work is a re-working of your group project assessment, and is now an individual assignment. You may, if you wish, base your website on the work you have done with your group so far, but the remaining work must be your own.

The details of the case study are set out again below.

The deadline for your reassessment work is 8^th July 2021

 

National Carpool Company Case-study

Design and Implement a complex Web Application.

Design and Implement a Web Application with multiple views.

During the module you have learnt numerous techniques which allow you to design and build Web applications using HTML, JavaScript, and a modern, open-source web framework. This assignment gives you an opportunity to consolidate your skills to investigate, design, and implement the concepts of a working Web application.

Case study

We are a taxi management company. Currently we have a fleet of taxis that are booked using our phone system and we would like to move our bookings system online for use by our staff and manager.

The site should record details of taxis that have been booked. Details should include the customer’s name, their pickup and drop-off locations, and the number of expected passengers.

Staff at the booking office should be able to add or remove passengers to the booking.

Managers should be allowed to edit the route, add or remove passengers, or delete a booking. The customer’s name should not be editable.

All users must log on to the system to ensure that the functionalities are restricted appropriately.

A database has already been set up, and REST services have been written to communicate with that database. Your application should use these REST services:

Login Service – http.post enabled:
http://webteach_net.hallam.shu.ac.uk/cmsds/api/login

Booking Service – CRUD enabled:
http://webteach_net.hallam.shu.ac.uk/cmsds/api/booking

The users that have been set up on the database so far are:

Name	Username	Password	Role
Mark Godson	mark201	notsure	Staff (1)
Emma Smith	emma103	prettysure	Manager (2)

 

Task

Produce a web application that addresses the client requirements. For your web application you must use HTML, CSS, JavaScript, and AngularJS.

Submissions

• You must complete the work individually.
• You must submit all your code files, zipped.
• You MUST include the URL of your website in your submission, either in a text file or in the submission comments.
• Please use the Reassessment – 001 Project – Submission Point in the Reassessment folder to submit your work.

Resources

• Learning Materials covered in the module
• Additional external web-based materials identified by the teaching team
• Additional reading materials indicated throughout the teaching of the module