Advanced Database Concepts DL (ADCON.DL) U22389
Semester 1, 2018-19 assessment
Submission date: Wednesday 30th January 2019, 22:30 UK time (GMT)
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.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
SUBMISSION DATE: WEDNESDAY 16 JANUARY 2019, 22:30 UK time(GMT)
Feedback date: Wednesday 13th February 2019
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:
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
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 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
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 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
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.
********************************
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
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)
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
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]
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]
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.
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
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]