Implementation Choices and Transaction Analysis for a Relational Database System

The assignment requires students to design a relational database for a case study involving two scenarios: a fitness studio and a sports equipment company. The student needs to consider implementation choices, transaction analysis, and indexing strategies to optimize the database's performance. Additionally, they must create five SQL queries that generate business reports, evaluate four security threats, discuss optimization strategies, and consider alternative database solutions like NoSQL. Finally, the student should reflect on their answers and provide a personal reflection.

Advanced Database Concepts DL (ADCON.DL) U22389
Semester 1, 2018-19 assessment
The first few pages of this document set out important guidelines and other
information relating to this assessment. The remaining pages consist of the actual
assessment.
1 Information relating to this assessment
1.1 Introduction
The assessment for this unit
The assessment for Advanced Database Concepts DL consists of a single piece of
individual coursework, which is worth 100% of the marks for this unit. The unit
specification states the following:
Assessment will be via a single coursework, attempted individually, worth 100% of
the marks for the unit. The work will be unsupervised, tutor assessed, and cover all
of the learning outcomes. The deliverable will be a single report submitted at the
end of the unit.
The report will cover analysing a chosen case study, designing and implementing a
relational database based on that case study, answering questions based on topics
from the unit syllabus, and reflecting on their work.
Students will be able to submit draft copies of parts of their assessment to the tutor
at specific times during the unit, for formative feedback.
Learning outcomes
The learning outcomes for this unit, which this assessment relates to, and as per the
unit specification, are as follows:
1 Review the theoretical underpinning of the relational model, and apply that to
building a relational database for a small business application, from given user
requirements.
2 Optimise a database design for commercial operation, taking into account data
independence, transaction scheduling and recovery procedures.
3 Critically discuss and explore the advantages, disadvantages and issues that
relate to DDBMSs, web-based databases, and new database technologies to
accommodate big data (structured and unstructured data).
In tandem with the above, this assessment on the following page therefore consists
of:
● a case study (Students choose ONE from case studies provided)
● questions relating to that case study, which also will include a question asking
you to reflect on your work.
1

1.2 Anonymity of work
All work must be submitted anonymously. You should therefore ensure that you do
not include your name anywhere on your work, either in the main body of your
report, the appendix, or in any headers or footers.
1.3 Plagiarism and poor scholarship
Where relevant, extracting sections from the internet or other sources, including
them in your assignment, and passing them off as your own work is not acceptable,
and will be penalised with a fail result. Copying the work of your fellow students is
also not acceptable.
Use respected websites for your information gathering.
To avoid plagiarism rewrite using your own words, but where you have used the
ideas of others do acknowledge the source within the text and provide the full source
in the references at the end of the document (using Harvard APA 6 format). The
majority of your document will need be referenced, unless the idea is an original one
of your own. If you are not sure how to reference, use the information provided by
the Library (see moodle section).
1.4 Submission Instructions
Dropbox: about a week before the scheduled submission date, a ‘dropbox’; will be
created in the Moodle unit for ADCON.DL, which you will use to upload your
Coursework assignment.
Work handed in after that date and time, will incur a LATE penalty (see 20-day rule
in Unit handbook)
Your work will still be marked, but marks will be capped to 40%.
You should submit a report consisting of:
● Main body of report: your coursework answers
● Appendix to your report: your SQL code for the Create Tables
statement, insert statements and creation of primary and foreign key
statements as well as your SQL queries.
Formatting and presentation of your report:
2

The word limit (depending on case study) is 4000-5000 words +/- 10%, but should
only be used as a guide as your personal interpretation of an in-depth analysis of the
case study will determine the length of your final report.
● Add page numbers to each page
● Adhere to word count specified for a question
● Clearly label each question according to coursework question numbers
● Keep your work anonymous (only student number)
● Check your work for plagiarism
● Reference work used to support theory discussions
1.5 Submitting draft copies for guidance and formative feedback
You can submit draft work during the week(s) 3 December - 12 December 2018, if
you want me to give you some guidance and formative feedback by 14 December
(last day of term).
Please note that the coursework is the only assessment for this unit and it is
therefore longer and more comprehensive than coursework where the unit has
multiple assessments.
2. ADCON DL –Assessment Case study and Questions
Your answers should specifically relate to the case study. Be cautious to not
just provide generic answers!
Choose ONE of the following two case studies for your coursework analysis.
2.1 CASE STUDY A
3

OPC (Olympics Preparation Committee)
OPC is challenged with the daunting task to administer all activities related to the
preparation phases for the Olympics 2020.
They need your help to design a centralized database to manage data and generate
reports to support the administrative planning and functions for the “Preparation
Phase” prior to the main Olympics. This database will integrate with a number of
other systems which falls beyond the scope of this task.
OPC members are currently based in Tokyo (full-time) but have representatives in all
major world cities as well as mobile agents travelling between cities and sport
venues.
The Olympics is less than two years away and all over the globe, teams and
managers are starting to prepare. OPC needs to keep track of all preparation phase
activities, the teams (detail on country, athletes, sport…), managers (personal
detail), coaches, type of sport, venues, bookings for training sessions and venues,
media coverage, marketing, health and safety, security, athlete health data,
performance tracking data and more.
OPC accumulated a list of approved venues (indoor and outdoor) that will be used
by teams for preparation.
All athletes will take part in a global health tracking initiative and will wear tracking
devices during the preparation and training phases prior to the main events. OPC is
4

responsible to provide data structures to manage performance and health data
generated via the tracking devices.
The members of OPC are responsible for certain tasks within the administrative
planning and functions. They also need to liaise with the business sector to plan for
providing medical services, transport and security at all venues used for the
preparation phases.
The proposed system should be easy to use and maintain and should generate
management reports, for example:
Teams per training venue
Teams, managers, sponsors per type of sport
Schedule for media coverage per training session
Health statistics per athlete per session
Security contracts per venue
OPC needs to enforce some restrictions to prevent double bookings and ensure
effective administration:
● Only 1 team can book a training venue at a given date/time
● Teams can only book a venue for maximum of 4 sessions during the week
prior to the main Olympics
● Teams need to pre-book with deposit to secure venues
● Media coverage are restricted to max 3 different channels per training session
● Each committee member takes responsibility for three to a max of 6 country
accounts
Should you need more detail, you can arrange a discussion with one of the OPC
members.
OR
CASE STUDY B
5

BICYCLE RENTAL FACILITY- BRF
Hampshire’s Social Welfare Service (HSWS) section has planned to introduce
Bicycle Rental Facility (BRF) to its citizens. BRF not only provides a great way to
provide citizens with a safe, convenient and environmentally friendly transportation
service, but also helps in improving their health as it is physical exercise.
Bicycles are housed in service terminals placed at a number of locations across
some of the main cities in Hampshire, UK.
Each service terminal is given a unique identification number, terminal name, street
address, telephone number and maximum storage capacity. HSWS keeps track of
the bicycles currently available at each service terminal.
Bicycles are identified by the unique ID of each bicycle, along with its make, model,
color, size and type (kid, adult and senior citizen) of bicycle.Each bicycle is also
equipped with a sensor to allow GPS tracking of the bicycle.
While HSWS provides the bicycles and maintains the terminals, private-sector
companies are also invited to sponsor individual bicycles and terminals and, in return
for a fee, their logo will be displayed prominently on the sponsored asset. It is
important for HSWS to keep updated information on these sponsors to record
sponsor name, company, contact person, telephone number, address, sponsorship
period and fees. A sponsor will commit to a certain period, after which they can
renew or cancel sponsorship. The sponsors are responsible for maintaining and/or
removing sponsorship logo’s at terminals or from bicycles in arrangement with
HSWS.
A user interested in using the bicycle facilities must register as member. Information
stored about each member includes full name, email, contact phone numbers,
residential address, membership status and member payment detail. Members may
be children between 14 to 16 ages, adults and senior citizens. Membership status
6

and member fee are fixed accordingly. An adult can register their children as the
adult will be responsible for the payment. A senior citizen fall within the age range
65-70). HSWS is not permitted, under health and safety act, to rent out bicycled to
members over the age of 70.
In addition to memberships, visitors or casual users can use BRF by purchasing a £5
one-day pass (valid for 24 hrs) online or via the BRF phone app, using a debit/credit
card. The visitors detail, full name, email, contact phone numbers, residential
address and card detail are recorded while collecting a bicycle. The card has a
number, date and time of purchase.
Each rental transaction is fully automated and records details of the type of user,
user detail, bicycle, terminal, pick-up time, duration of rental use and total rent paid.
The total rental cost is done based on the total rental period. The rental rate depends
upon the type of Bicycle. All registered members will receive a monthly bill, while
visitors and casual users are on pay-as-you-go system. Each bill has a bill number,
date and total amount to be paid. Each bill needs a payment. Payment detail
includes the payment number, payment date and mode of payment (cash or
debit/credit card).
HSWS is responsible and has contracts with a variety of external service companies
to maintain, replace and service the bicycles. They make use of companies located
in the cities hosting the bicycle terminals. They need to keep record of the detail on
these Service companies, address, contact person, contact numbers, contract
number, contract fees…..
Once checked back in, a bicycle is immediately available to another user for rental.
BRF also provides bicycle parking racks in different locations. Each parking rack has
a number, name, location and capacity. Members can park the rented bicycle at any
nearby location on route. A member/user needs to submit the parking rack location
number, via the mobile app when they park the bicycle. In this way the physical
location of each bicycle is known at a specific time.
The ideal is to expand the sensor capability to track the position of bicycles in order
to retain any lost or abandoned bicycles as well as to track whether users returned
the bicycles within the agreed rental period.
Typical reports:
List of membership
7

Transactions from casual users
Service company contracts
Bicycle records
Sponsorship detail
List of terminals and parking racks
Monthly income generated per city
Should you need more detail, you can arrange a discussion with one of the HSWS
members.
2.2 Assessment Questions
********************************
BEFORE you start, make sure to engage in the group discussion where the
respective case study “CLIENT” will be available to answer any question
regarding the case study or consult with the “Client” on a 1-1 basis.
8

********************************
QUESTION 1 ASSUMPTIONS AND CONSTRAINTS [10]
Your Database solution will be based on Case study Information, Assumptions
and Business constraints:
Bearing this in mind, provide a list of:
i) Any Assumptions you have to make due to information not being clear or
detailed enough, as being provided in the Case study.
ii) Business constraints, business rules that the chosen case study
business adhere to, which may be clear from the case study or perhaps
common knowledge about that type of business sector.
Marking scheme for Question 1
i) Assumptions: a maximum of 5 marks, with consideration given to assumptions
that seem logical (reasonable) in regards to the case study.
ii) Business constraints/rules: a maximum of 5 marks, with consideration given
to business constraints that seem logical in regards to the case study.
QUESTION 2 LOGICAL DESIGN (Conceptual Model) [25]
Create an ENHANCED ENTITY RELATION DIAGRAM (EERD) for the chosen
Case Study.
Your EERD should include the following:
● All Entities (including real world entities, weak entities,
super/subtype entities...)
● Primary keys and foreign keys = DO NOT include any other
attributes in the EERD!
● Cardinality for each end of each relationship
● Description of relationships
● Aggregation/ Composition
● Participation and disjoint constraints
● Resolved M:M relations (composite PK’s)
Marking scheme for Question 2
9

Create an Enhanced EERD for Case study:
A Maximum of 25 marks,
15 marks for a logical interpretation of the case study, with modelling of the appropriate
entities, relationship descriptions and cardinality
10 marks for the correct modelling of aggregation, composition, generalization, disjoint
constraints and resolving M:M relations
Mark boundaries:
> 19 excellent, error free conceptual/logical design using a good range of the indicated
documentation. Clear and valid list of assumptions, modelling of super/subtype, aggregation
and participation constraints as well as proper resolving of M:M relations.
< 8 unsatisfactory or incomplete design that would lead to significant problems were it to
be implemented.
QUESTION 3 PHYSICAL DESIGN (Relation Schemas and Tables) [25]
3.1 Map your logical model (EERD) from question 2 into a set of Relation
Schemas: (20)
Each Relation schema should include:
● Schema Name
● Attributes (Choose appropriate attributes to describe each entity)
● Indicate Primary key for each Schema
● Indicate Foreign keys, if applicable
Note:
● Add ANY additional attributes you consider necessary that have not
been stated in the case study
● All the entities should be mapped to relation schemas and should
include intersection entities for M:M relationships
● Ensure that the schemas are in 3rd Normal Form (no need to show the
stages of normalisation)
10

EXAMPLE of Relation Schema for Entity STUDENT:
STUDENT (StudNnr, StudSurname, StudName, Age……, CourseId)
….where STUDENT = schema name
….Use bold/underline for PK
….Use italic for FK
3.2 Choose ANY ONE of the table schema from 3.1 (with at least 5
attributes) and provide the Data dictionary for that table schema, using
the following template: (5)
EXAMPLE:
Entity Name: STUDENT
Attribute
Name
Data
type
Data
size
Primary
key
Foreign
key
Domain
(allowable
value)
Derived
data
St_nr
DOB
Int
Date
8 Yes
Age
Marking scheme for Question 3
Map your EERD from Question 2 into a set of relation schemas: a maximum of 20 marks
for question 3.1, with consideration given to an accurate mapping of the schemas to the
entities shown on the EERD in question 2; with appropriate and reasonable keys and
attributes.
Marks specified are for the complete schema design-
Primary keys: 5 marks
Foreign keys: 5 marks
Relevant attributes: 7 marks
Schemas in 3NF: 3 marks
> 15 excellent, error free and relation schemas with clear and well-argued attributes and
correct primary and foreign keys
11

< 7 unsatisfactory or incomplete. Relation schemas incomplete and no attempt at
justifying design decisions/validation of models etc …
Data dictionary for ONE chosen schema with at least 5 attributes: a maximum of 5 marks
for question 3.2, with consideration given to an accurate Data dictionary showing Attribute
names, data type, data size keys and derived or domain values.
QUESTION 4 PHYSICAL DESIGN (Features) [16]
Discuss the design decisions you will have to make to ensure successful
implementation of the database on ONE CENTRALISED location.
Your decisions should consider and explain choice of RDBMS, file systems,
indexes and underlying hardware decisions.
Many of these decisions would be based on doing a transaction analysis,
which you need to show for the key tables.
Factors to consider when deciding for example on which index would be most
suitable for a given column in a given table include:
● How many users will access the column?
● Frequency of updates?
● Indexing single or multiple column(s)?
● Choice of RDBMS (Allows for different type indexing)
To answer this question you will need to do some research, it is suggested that you
look at Database concepts textbooks and search for Transaction analysis for
database implementation decision making. Apply your findings to your database
design.
Marking scheme for Question 4
Implementation choices and transaction analysis: a maximum of 16 marks , identified
and discussed; with consideration the most appropriate choice for RDBMS, file system and
indexing to maximise implementation and performance of the proposed conceptual database
design.
QUESTION 5 QUERIES (Reports) [30]
12

Now that you have designed your relational model, you need to think about 5
good Business reports you could generate for the Case study business.
You will need to create ONLY the tables you need for the SQL queries and
insert test data, to be able to run your queries. You need to add 5-8 rows of
data to each table you create, to show that the query works.
Create 5 SQL queries (Business Reports):
For each query:
● State the purpose of the query (who would use the report and why?)
● Show the SQL syntax of the query
● Show the equivalent Relational Algebra notation for the query(for at
least 3 of the 5 queries)
● Run the query against your database and show the output
You should use screenshots for the SQL syntax and output and copy and
paste to your report.
Use a wide range of good SQL syntax and avoid repetition of the same syntax
patterns in each query.
An example of how you could answer this question to show a variety of SQL
syntax could be to include:
Query: use join, like, order by…
Query: use subqueries
Query: use group by, aggregate functions
Query: use set operators eg UNION
Query: use format functions eg. Date, round, substr…
Marking scheme for Question 5
A maximum of 6 marks for each query, with consideration given to which users will be
using the query and also the reasons given to why the query would be needed. (2 marks).
Display a good range and high standard of SQL syntax, correct relational algebra notation
reflecting the business needs of the case study and correct output. (4 marks).
QUESTION 6 Database SECURITY [16]
13

Using examples from the case study, critically evaluate FOUR security threats
that your database solution might face. Discuss the security measures that
you could take to protect the data and database.
Your discussion and examples should focus on the four most important
threats to a centralized database and should NOT just be a general discussion
on Database security. (1000 words max)
Marking scheme for Question 6
A Maximum of 16 marks, with consideration being given to:
● Reasonable discussion of security issues and threats to designed Database system
(8 marks)
● Reasonable discussion of measures that could be taken for the specific threats
identified (8 marks)
QUESTION 7 OPTIMISATION [8]
The Database system you have designed needs to be optimised for best
performance and includes proper backup functionality.
Discuss the need for optimisation of a database, with specific discussion on
how you would monitor, tune and optimise the database. (500 words max)
Marking scheme for Question 7
i) A maximum of 8 marks, for reasonable discussion of the need of optimisation
for a database, including discussion of specific strategies applicable to chosen
case study.
QUESTION 8 ALTERNATIVE DATABASE Architecture solutions [15]
Both case study scenarios need to consider future development and the need
to accommodate and manage both structured and unstructured data. Both
scenarios implement sensors to track and monitor the use of equipment
and/or athletes. These sensors will generate real-time data for analysis. Also
the embedding of social media within the business websites and/or mobile
apps will generate numerous unstructured data.
During the course of this unit, you completed the units on Big Data Essentials
ESS 1000, Introduction to Big Data ESS 100 and Apache Hadoop Essentials
ESS 101.
14

You have to write a motivation for the need to consider alternative Database
solutions like NoSQL by including discussion on:
What is Big data (in reference to your case study)? Big data computing,
scaling, clustering, advantages and types of NoSQL databases, HADOOP as a
possible solution, HDFS and how it works and more……(more detail during
chat sessions)
Marking scheme for Question 8
A maximum of 15 marks, for a reasonable discussion on Big data, alternative database
solutions, specifically focusing on Hadoop.
QUESTION 9 PERSONAL REFLECTION [10]
Now that you have completed questions 1-8, spend some time reflecting on
the answers that you have prepared. Discuss your thoughts and challenges
executing this coursework: (1000 words max)
In a personal reflection, include:
● The processes/steps taken and decisions you made in order to complete
your assessment.
● What you have learned/not learned from the assessment.
● What you could have done different or better and why? Show examples.
● What you are happy with or proud of, regarding your answers.
● What final mark do you feel you might obtain?
Marking scheme for Question 9
A maximum of 10 marks for, well-planned discussion, relevant to the answers given,
logical reasons for points made and a degree of self-reflection shown regarding the standard
of work produced.
Note these are general guidelines for essay, reports, projects & dissertations - the emphasis given to
various aspects e.g. the importance of research may vary depending upon the nature of criteria.
80+
As below plus:
● Excellent work - able to express an original reasoned argument in a lucid manner by reviewing & critiquing a
wide range of material. Original, critical thinking based on outstanding insight, knowledge & understanding of
material. Material contributes to current understanding & is of potentially publishable quality in terms of
presentation and content
● Wide reaching research showing breadth & depth of sources
15

70-
79
As below plus:
● Outstanding work - contains accurate, relevant material, demonstrates understanding of complex subject matter
& is able to view it in a wider context. Shows originality & confidence in analysing and criticising assumptions,
is aware of the limits of knowledge
● Evidence of extensive research, uses & presents references effectively
● Excellent in terms of organisation, structure, use & flow of language, grammar, spelling, format, presentation,
diagrams, tables etc
60-
69
As below plus:
● Clear, balanced coherent critical & rigorous analysis of the subject matter. Detailed understanding of knowledge
& theory expressed with clarity
● Extensive use of relevant & current literature to view topic in perspective, analyse context & develop new
explanations and theories
50-
59
As below plus:
● Detailed review and grasp of pertinent issues & a critical contextual overview of the literature. Thorough
knowledge of theory and methods & uses this to underpin arguments and conclusions
● Confidence in understanding and using literature
40-
49
● Demonstrates grasp of key concepts & an ability to develop & support an argument in a predominately
descriptive way with valid conclusions draw from the research
● Familiarity with key literature which is cited and presented according to convention
● Logical & clear structure, well organised with good use of language and supporting material
30-
39
FAIL Some knowledge of relevant concepts & literature but significant gaps in understanding and/or knowledge. Little
attempt at evaluation, conclusions vague, ambiguous & not based on researched material. Limited or inappropriate
research. Deficits in length, structure, presentation &/or prose
0-
29
TOTAL [155]
16