Advanced Database Development
1. Produce an Entity-Relationship Model for the scenario
2.Design a set of relations conforming to Boyce-Codd Normal Form
3.Implement your final database design
4.Query your database
5.Optimize your Database
6.Secure your Database

The coursework does not necessarily need to be perfect or excellent. It supposes to have more schematic diagrams rather than plenty of words. Essentially I just want to see how you would structure and write this coursework. The last task #7 Building a Web Interface of Assessment Brief may be omitted.

You may check the module units here
http://www.derbyonlinelearning.co.uk/content1.asp?MenuID=6814&courseId=Adv2299

Advanced Database Development
1. Produce an Entity-Relationship Model for the scenario
2.Design a set of relations conforming to Boyce-Codd Normal Form
3.Implement your final database design
4.Query your database
5.Optimize your Database
6.Secure your Database

The coursework does not necessarily need to be perfect or excellent. It supposes to have more schematic diagrams rather than plenty of words. Essentially I just want to see how you would structure and write this coursework. The last task #7 Building a Web Interface of Assessment Brief may be omitted.

You may check the module units here
http://www.derbyonlinelearning.co.uk/content1.asp?MenuID=6814&courseId=Adv2299

Assessment Brief
Advanced Database Development
6CC519
September 2015
University of Derby Online Learning
www.derby.ac.uk/online 2 of 12
Module Overview:
This module covers the design and deployment of relational databases in multi-user and
web-based environments. It also explores alternative technologies that are available in
the database arena along with scripting languages utilised. The key issues of database
security, legal and ethical issues and security of information and the incorporation of
non-traditional data structures are also introduced.
Learning Outcomes:
On successful completion of this module, you will be able to:
On successful completion of this module, you will be able to:
1. Model, design and implement a relational database with a web-based interface for
a specific given case study
2. Review and critically evaluate database technologies.
Assessment
Formative Activities & Assessment
Formative Activities and Assessments are opportunities for you to apply, practice and make
sense of the learning materials and content that you have encountered. These may take the
form of individual tasks, such as reading some text or watching a video and documenting
your reaction to it, responding to some discussion points on the discussion forum,
considering a case study, or participating in a live online classroom session. The main aim
of formative activities is for you to receive feedback on your contribution or performance that
will help you prepare for and complete your final module assessment.
Summative Assessment
Summative assessments are the pieces of coursework that you complete which contribute
towards your final grade in this module. You should take the feedback that you receive from
the completion of coursework in this module and use it to help you improve your
performance in future assessments.
Summative assessment in this module is two pieces of submitted coursework. You will be
expected to submit your summative assignments via the Turnitin assessment points on the
Assessment page in My Learning section of your module.
University of Derby Online Learning
www.derby.ac.uk/online 3 of 12
Coursework 1
Assessment weighting: 50% of the module grade
Word count: 2000 words equivalence
Submission Deadline Date/Time: Friday 20th November at 9pm GMT.
Title: Animal Tracking Systems (ATS)
This is an individual assessment worth 50% of the module.
Case Study
Animal Tracking Systems (ATS) is a developer and supplier of specialist software products
to individuals, businesses and government organizations working in the wildlife and
veterinary sectors. Their software allows individual animals and birds to be remotely
monitored and tracked. Examples include anti-poaching work in Africa, bird migration pattern
analysis across Europe, canine tracking by law enforcement and the military or simply a
private individual attaching a chip to a pet cat’s collar to avoid it getting lost. As part of their
operations and after-sales package, ATS provides a help desk for clients who have
questions about software purchased from the company. The range of software is extensive
and offers a wide range of functionality at various price points. Some functions offered
include location identification, migration and travel pattern analysis, health data capture,
eating and drinking data capture, sleeping and resting pattern analysis and so on.
When a call comes in, an operator enquires about the nature of the call. For calls that are
not truly help desk functions, the operator redirects the call to another unit of the company
(such as Order Processing or Billing). Since many customer questions require in-depth
knowledge of a product, help desk consultants are organized by product.
The operator directs the call to a consultant skilled on the software that the caller needs help
with. Since a consultant is not always immediately available, some calls must be put into a
queue for the next available consultant. Once a consultant answers the call, he determines if
this is the first call from this customer about this problem. If so, he creates a new call report
to keep track of all information about the problem. If not, he asks the customer for a call
report number, and retrieves the open call report to determine the status of the inquiry. If the
caller does not know the call report number, the consultant collects other identifying
information such as the caller’s name, the software involved, or the name of the consultant
who has handled the previous calls on the problem in order to conduct a search for the
appropriate call report.
If a resolution of the customer’s problem has been found, the consultant informs the client
what that resolution is, indicates on the report that the customer has been notified, and
closes the report. If resolution has not been discovered, the consultant finds out if the
consultant handling this problem is on duty. If so, he transfers the call to the other consultant
(or puts the call into the queue of calls waiting to be handled by that consultant). Once the
proper consultant receives the call, he records any new details the customer may have.
University of Derby Online Learning
www.derby.ac.uk/online 4 of 12
For continuing problems and for new call reports, the consultant tries to discover an answer
to the problem by using the relevant software and looking up information in reference
manuals. If he can now resolve the problem, he tells the customer how to deal with the
problem, and closes the call report. Otherwise, the consultant files the report for continued
research and tells the customer that someone at ATS will get back to him, or if the customer
discovers new information about the problem, to call back identifying the problem with a
specified call report number.
A database is required to manage the data for the above scenario. It should also be
possible to query the data and produce various reports for management as and when
required. Your job is to deliver that database by correctly identifying what data must be
captured and how it is related.
NOTE: The above case study is simply an outline of the company and you will need to make
your own assumptions and interpret or even extend the scenario as you go. Use your
imagination as you see fit but you must clearly document all assumptions and extensions.
University of Derby Online Learning
www.derby.ac.uk/online 5 of 12
Your Tasks
1. Produce an Entity-Relationship Model for the scenario described above (15 marks)
Develop a top-down design of the data in the form of an entity-relationship diagram. You
should note all assumptions you make about the data and the reasoning behind your design
choices. Also include any appropriate constraints and a list of entity types showing their
attributes and identifiers.
2. Design a set of relations conforming to Boyce-Codd Normal Form (BCNF). (15
marks)
Once you are satisfied that the ER diagram is a good representation of the data, produce a
logical design by mapping the E-R diagram to a set of (normalized) relations. Clearly show
all intermediate steps.
3. Implement your final database design. (15 marks)
Take each of the relations from your relational model and implement them as SQL tables.
You must include all primary and foreign keys as well as any other table or column
constraints you feel are appropriate. Then, using appropriate sample data and your own
imagination, populate your finished tables.
4. Query your database. (20 marks)
Using SQL, write a set of realistic sample queries based on the above scenario (use your
imagination for details of each query) but they should include the following SQL query
techniques:
? Joins (using two, three or more tables)
? Set operations (UNION, INTERSECT and MINUS)
? Ordering
? Grouping
? Aggregate functions (MIN, MAX, AVG, COUNT, SUM)
? Table aliases
? Renaming columns
? Sub-queries (nested queries)
You should aim to write at least ten sample queries – ranging from basic
SELECT…FROM…WHERE queries to more advanced ones using the above techniques.
5. Optimize your Database (10 Marks)
You should now optimize your database:
? Apply suitable database optimization techniques to your final set of tables.
? Aim to implement a range of indexes.
? Run a suite of queries that will invoke those indexes.
University of Derby Online Learning
www.derby.ac.uk/online 6 of 12
? Aim to demonstrate some index suppression techniques.
6. Secure your Database (10 Marks)
Your optimized database should now be secured. To demonstrate this:
? Create three new users on your database
? Allocate them different security privileges and roles.
? Create a suite of views over your existing base tables.
? Issue appropriate privileges so that at least one user can use those views.
? Clearly demonstrate what actions each user can/cannot perform.
7. Building a Web Interface (15 Marks)
You now have a fully working, secure and optimized database. Your final task is to design
and build a web-based interface to that database such that it can support the following
operations:
? Insert new rows of data
? Update existing rows of data
? Delete existing rows of data
? Query existing rows of data
Please read the following guidance…
Important Notes and FAQ’s.
? Make sure your full name and student ID are on the front page of the assessment
? Clearly label all tasks and take care to explain and discuss your technical work
? For the modelling and design work you must supply clearly annotated diagrams
? For the SQL work you must supply full code listings of the inputted code and
screenshots of the outputted results – even if no rows were returned. Each
screenshot must include some identifying feature – such as a username or user ID –
that proves beyond doubt that it is your own work.
? If you need to research, then fully reference all such sources using the Harvard
notation
The detailed grading criteria are below…
University of Derby Online Learning
www.derby.ac.uk/online 7 of 12
Grading Criteria – Coursework 1
70-
100%
(Excellent/Outstanding)
? Fully complete and accurate ER Model that captures all/most semantic aspects of
the case study
? A fully normalized set of BCNF relations with all intermediate steps fully annotated
? A fully populated set of tables that encapsulates all primary & foreign keys plus all
other constraints
? A minimum of ten complex SQL queries that employ joins, grouping and other
advanced techniques
? A rich set of database optimization techniques to include both indexing and
clustering
? A rich set of database security techniques to include all privileges, roles, profiles and
views
? A well-designed web interface that incorporates HCI design principles and various
components
? A minimum of two insert, update and delete operations (each) via this web interface
? A minimum of ten queries (of varying complexity) via this web interface
? All work to be fully evidenced proving beyond doubt who the author of that work is
? All work to be fully annotated, well laid out and easy to follow with suitable headings
? Any external sources are fully referenced by strict adherence to the Harvard citation
standards
60-
69%
(Very Good/Commendable)
? Fully complete and accurate ER Model that captures most semantic aspects of the
case study
? A fully normalized set of BCNF relations with most intermediate steps fully annotated
? A fully populated set of tables that encapsulates all primary & foreign keys plus other
constraints
? A minimum of eight complex SQL queries that employ joins, grouping & other
advanced techniques
? A rich set of database optimization techniques to include both indexing and
clustering
? A rich set of database security techniques to include many privileges, roles, profiles
and views
? A well-designed web interface that incorporates HCI design principles and various
components
? A minimum of two insert, update and delete operations (each) via this web interface
? A minimum of eight queries (of varying complexity) via this web interface
? All work to be fully evidenced proving beyond doubt who the author of that work is
? All work to be fully annotated, well laid out and easy to follow with suitable headings
? Any external sources are fully referenced by strict adherence to the Harvard citation
standards
50-
59%
(Good/Highly Competent)
? Mostly complete and accurate ER Model that captures many semantic aspects of
the case study
? A largely normalized set of BCNF relations with many intermediate steps fully
annotated
? A fully populated set of tables that encapsulates some primary & foreign keys and
other constraints
? A minimum of five complex SQL queries that employ joins, grouping and other
advanced techniques
? A rich set of database optimization techniques to include either Indexing or
clustering
? A rich set of database security techniques to include a few privileges, roles, profiles
and views
University of Derby Online Learning
www.derby.ac.uk/online 8 of 12
? A basic web interface that incorporates some HCI design principles & perhaps
various components
? A minimum of one insert, update and delete operations (each) via this web interface
? A minimum of five queries (of varying complexity) via this web interface
? All work to be fully evidenced proving beyond doubt who the author of that work is
? Most work to be fully annotated, well laid out and easy to follow with suitable
headings
? Any external sources are fully referenced by strict adherence to the Harvard citation
standards
40-
49%
(Satisfactory/Competent)
? A very basic ER Model that captures only some semantic aspects of the case study
? A partly normalized set of BCNF relations with no/little annotation
? A barely populated set of tables that encapsulates only primary and/or foreign keys
? A minimum of three simple SQL queries that employ no advanced techniques
? A very basic set of database optimization techniques – perhaps only simple
indexing
? A very basic set of database security techniques to include any aspect
? A very basic web interface that incorporates no serious HCI design principles
? A minimum of one insert, update and delete operations (each) via this web interface
? A minimum of three simple queries via this web interface
? All work to be fully evidenced proving beyond doubt who the author of that work is
? Work is presented in a very basic (but readable) state
? Most external sources are fully referenced by adequate adherence to the Harvard
citation standards
35-
39%
(Unsatisfactory)
? A very basic ER Model that captures only small semantic aspects of the case study
? A very basic set of relations with no/little annotation or serious attempt at
normalization
? A barely populated set of tables that encapsulates only primary and/or foreign keys
? A minimum of three simple SQL queries that employ no advanced techniques
? A very basic set of database optimization techniques – perhaps only simple indexing
? A very basic set of database security techniques to include any aspect
? A very basic web interface that incorporates no serious HCI design principles
? A minimum of one insert, update and delete operations (each) via this web interface
? A minimum of one simple query via this web interface
? Some work to be fully evidenced proving beyond doubt who the author of that work
is
? Work is presented in a very basic (but readable) state
? Many external sources are NOT referenced by any adherence to the Harvard
citation standards
1-34%
(Very Poor/Nothing of Merit)
? A very basic (or absent) ER Model that captures only small semantic aspects of the
case study
? A very basic (or absent) set of relations with no /little annotation or serious attempt
at normalization
? A barely populated set of tables that encapsulates only primary and/or foreign keys
? A minimum of three simple SQL queries that employ no advanced techniques
? No database optimization or security work
? No web interface at all
? No hard evidence proving beyond doubt who the author of that work is
? Work is presented in a very basic (but readable) state
? No attempt is made to reference any cited sources
End of Assessment 1
University of Derby Online Learning
www.derby.ac.uk/online 9 of 12
Coursework 2
Assessment weighting: 50% of the module grade
Word count: 2000 words
Submission Deadline Date/Time: Wednesday 9th December at 9pm GMT.
Assessment
This is an individual assessment worth 50% of the module.
Your Tasks
Write a 2000-word report on alternative and complementary technologies to be used in the
deployment of the database implementation from Assessment 1, in order to fully exploit the
corporate data assets. Security, ethical and legal issues should also be considered.
Specifically, you should address the following technical areas:
1. Data Warehousing and the difference between operational and strategic data sets
2. Data Mining and OLAP (On-Line Analytical Processing) compared with OLTP
Systems
3. The rise of ‘Big Data’ and its applications
4. ‘NoSQL’ databases as compared with ‘ACID-compliant’ databases
5. The impact of the ‘Open Data’ movement
Important Notes and FAQ’s
? Make sure your full name and student ID are on the front page of the assessment
? Clearly label and number all sections and sub-sections of your report
? Have an accurate contents page at the front and a full reference list at the rear
? Place a word count at the end of the report
? Try and incorporate good-quality diagrams into your discussion
? Fully reference all sources using the Harvard notation
? Each technical area carries equal weight – 20 marks each
? For each technical area, cover the following points:
? Describe the essential concepts involved in that technology
? Explain the key advantages and benefits of that technology
? Describe specific real-world applications and users of that technology
? Explain any disadvantages or situations were that technology would be unsuitable
The detailed grading criteria are over the page…
University of Derby Online Learning
www.derby.ac.uk/online 10 of 12
Grading Criteria
70-100% (Excellent/Outstanding)
? All five technology areas are fully explained and analysed with many excellent
examples/applications
? Many high-quality diagrams are employed to support the key concepts discussed
in the report
? The data security, ethical and legal aspects are fully explained, analysed and
placed within context
? The report is presented to a very high standard, logically sequenced with no
spelling or other errors
? At least 15 quality external sources are cited within the report
? All external sources are fully cited and referenced by strict adherence to the
Harvard standard
60-69% (Very Good/Commendable)
? All five technology areas are well explained and analysed with some excellent
examples/applications
? Some high-quality diagrams are employed to support the key concepts discussed
in the report
? The data security, ethical and legal aspects are well explained, analysed and
placed within context
? The report is presented to a high standard, logically sequenced with no spelling
or other errors
? At least 12 quality external sources are cited within the report
? All external sources are fully cited and referenced by strict adherence to the
Harvard standard
50-59% (Good/Highly Competent)
? Most technology areas are well explained and analysed with some excellent
examples/applications
? Some high-quality diagrams are employed to support the key concepts discussed
in the report
? The data security, ethical and legal aspects are adequately explained and
analysed
? The report is presented to a good standard, logically sequenced with few spelling
or other errors
? At least 10 quality external sources are cited within the report
? All external sources are fully cited and referenced by strict adherence to the
Harvard standard
40-49% (Satisfactory/Competent)
? Most technology areas are explained and analysed to some degree
? Possibly one or more diagrams are employed to support the key concepts
discussed in the report
? The data security, ethical and legal aspects are explained and analysed in a very
superficial manner
? The report is presented to a basic (but readable) standard
? At least 7 quality external sources are cited within the report
? All external sources are fully cited and referenced by reasonable adherence to
the Harvard standard
University of Derby Online Learning
www.derby.ac.uk/online 11 of 12
35-39% (Unsatisfactory)
? Most technology areas are NOT explained and analysed to any serious degree
? No diagrams are employed to support the key concepts discussed in the report
? The data security, ethical and legal aspects are very poorly explained and
analysed (if at all)
? The report is presented to a very basic (but readable) standard, possibly with
spelling errors
? Fewer than 5 quality external sources are cited within the report
? Few external sources are cited and referenced by any adherence to the Harvard
standard
1-34% (Very Poor/Nothing of Merit)
? Most technology areas are NOT explained and analysed to any meaningful
degree
? No diagrams are employed to support the key concepts discussed in the report
? The data security, ethical and legal aspects are very poorly explained and
analysed (if at all)
? The report is presented to a very basic (but readable) standard, with many errors
and mistakes
? Fewer than 3 quality external sources are cited within the report
? There is no use of the Harvard system
Assessment Presentation
When marking this assignment the academics will also be looking for the following criteria:
? Clear legible presentation.
? Good use of spelling, grammar and language throughout.
? Appropriate focus, meeting learning outcomes/assignment criteria.
? Logical progression and structure of arguments.
? (Normally) an introduction, a well-developed discussion and a conclusion
summarising the work.
? The introduction will include an exploration of the focus of the assignment and
discuss the way the assignment has been approached.
? Evidence of a range of relevant supporting reading.
? Use of accurate, evidence based information to support the arguments made.
? Follow normal Academic Regulations in terms of Academic Offences, style and
language.
? Use the Harvard system of referencing and may include a bibliography that lists all
resources referenced.
University of Derby Online Learning
www.derby.ac.uk/online 12 of 12
? A declaration statement which says you have checked your Turnitin originality report
and certain that the work is your own (and has never been submitted for marking
before by you, or anyone else)
? Maintain the confidentiality of clients/customers and persons associated with them,
colleagues and organisations
This module uses a grading scale applicable to Levels 4, 5 and 6 in the University Credit
Framework.

Advanced Database Development
1. Produce an Entity-Relationship Model for the scenario
2.Design a set of relations conforming to Boyce-Codd Normal Form
3.Implement your final database design
4.Query your database
5.Optimize your Database
6.Secure your Database

The coursework does not necessarily need to be perfect or excellent. It supposes to have more schematic diagrams rather than plenty of words. Essentially I just want to see how you would structure and write this coursework. The last task #7 Building a Web Interface of Assessment Brief may be omitted.

You may check the module units here
http://www.derbyonlinelearning.co.uk/content1.asp?MenuID=6814&courseId=Adv2299

Assessment Brief
Advanced Database Development
6CC519
September 2015
University of Derby Online Learning
www.derby.ac.uk/online 2 of 12
Module Overview:
This module covers the design and deployment of relational databases in multi-user and
web-based environments. It also explores alternative technologies that are available in
the database arena along with scripting languages utilised. The key issues of database
security, legal and ethical issues and security of information and the incorporation of
non-traditional data structures are also introduced.
Learning Outcomes:
On successful completion of this module, you will be able to:
On successful completion of this module, you will be able to:
1. Model, design and implement a relational database with a web-based interface for
a specific given case study
2. Review and critically evaluate database technologies.
Assessment
Formative Activities & Assessment
Formative Activities and Assessments are opportunities for you to apply, practice and make
sense of the learning materials and content that you have encountered. These may take the
form of individual tasks, such as reading some text or watching a video and documenting
your reaction to it, responding to some discussion points on the discussion forum,
considering a case study, or participating in a live online classroom session. The main aim
of formative activities is for you to receive feedback on your contribution or performance that
will help you prepare for and complete your final module assessment.
Summative Assessment
Summative assessments are the pieces of coursework that you complete which contribute
towards your final grade in this module. You should take the feedback that you receive from
the completion of coursework in this module and use it to help you improve your
performance in future assessments.
Summative assessment in this module is two pieces of submitted coursework. You will be
expected to submit your summative assignments via the Turnitin assessment points on the
Assessment page in My Learning section of your module.
University of Derby Online Learning
www.derby.ac.uk/online 3 of 12
Coursework 1
Assessment weighting: 50% of the module grade
Word count: 2000 words equivalence
Submission Deadline Date/Time: Friday 20th November at 9pm GMT.
Title: Animal Tracking Systems (ATS)
This is an individual assessment worth 50% of the module.
Case Study
Animal Tracking Systems (ATS) is a developer and supplier of specialist software products
to individuals, businesses and government organizations working in the wildlife and
veterinary sectors. Their software allows individual animals and birds to be remotely
monitored and tracked. Examples include anti-poaching work in Africa, bird migration pattern
analysis across Europe, canine tracking by law enforcement and the military or simply a
private individual attaching a chip to a pet cat’s collar to avoid it getting lost. As part of their
operations and after-sales package, ATS provides a help desk for clients who have
questions about software purchased from the company. The range of software is extensive
and offers a wide range of functionality at various price points. Some functions offered
include location identification, migration and travel pattern analysis, health data capture,
eating and drinking data capture, sleeping and resting pattern analysis and so on.
When a call comes in, an operator enquires about the nature of the call. For calls that are
not truly help desk functions, the operator redirects the call to another unit of the company
(such as Order Processing or Billing). Since many customer questions require in-depth
knowledge of a product, help desk consultants are organized by product.
The operator directs the call to a consultant skilled on the software that the caller needs help
with. Since a consultant is not always immediately available, some calls must be put into a
queue for the next available consultant. Once a consultant answers the call, he determines if
this is the first call from this customer about this problem. If so, he creates a new call report
to keep track of all information about the problem. If not, he asks the customer for a call
report number, and retrieves the open call report to determine the status of the inquiry. If the
caller does not know the call report number, the consultant collects other identifying
information such as the caller’s name, the software involved, or the name of the consultant
who has handled the previous calls on the problem in order to conduct a search for the
appropriate call report.
If a resolution of the customer’s problem has been found, the consultant informs the client
what that resolution is, indicates on the report that the customer has been notified, and
closes the report. If resolution has not been discovered, the consultant finds out if the
consultant handling this problem is on duty. If so, he transfers the call to the other consultant
(or puts the call into the queue of calls waiting to be handled by that consultant). Once the
proper consultant receives the call, he records any new details the customer may have.
University of Derby Online Learning
www.derby.ac.uk/online 4 of 12
For continuing problems and for new call reports, the consultant tries to discover an answer
to the problem by using the relevant software and looking up information in reference
manuals. If he can now resolve the problem, he tells the customer how to deal with the
problem, and closes the call report. Otherwise, the consultant files the report for continued
research and tells the customer that someone at ATS will get back to him, or if the customer
discovers new information about the problem, to call back identifying the problem with a
specified call report number.
A database is required to manage the data for the above scenario. It should also be
possible to query the data and produce various reports for management as and when
required. Your job is to deliver that database by correctly identifying what data must be
captured and how it is related.
NOTE: The above case study is simply an outline of the company and you will need to make
your own assumptions and interpret or even extend the scenario as you go. Use your
imagination as you see fit but you must clearly document all assumptions and extensions.
University of Derby Online Learning
www.derby.ac.uk/online 5 of 12
Your Tasks
1. Produce an Entity-Relationship Model for the scenario described above (15 marks)
Develop a top-down design of the data in the form of an entity-relationship diagram. You
should note all assumptions you make about the data and the reasoning behind your design
choices. Also include any appropriate constraints and a list of entity types showing their
attributes and identifiers.
2. Design a set of relations conforming to Boyce-Codd Normal Form (BCNF). (15
marks)
Once you are satisfied that the ER diagram is a good representation of the data, produce a
logical design by mapping the E-R diagram to a set of (normalized) relations. Clearly show
all intermediate steps.
3. Implement your final database design. (15 marks)
Take each of the relations from your relational model and implement them as SQL tables.
You must include all primary and foreign keys as well as any other table or column
constraints you feel are appropriate. Then, using appropriate sample data and your own
imagination, populate your finished tables.
4. Query your database. (20 marks)
Using SQL, write a set of realistic sample queries based on the above scenario (use your
imagination for details of each query) but they should include the following SQL query
techniques:
? Joins (using two, three or more tables)
? Set operations (UNION, INTERSECT and MINUS)
? Ordering
? Grouping
? Aggregate functions (MIN, MAX, AVG, COUNT, SUM)
? Table aliases
? Renaming columns
? Sub-queries (nested queries)
You should aim to write at least ten sample queries – ranging from basic
SELECT…FROM…WHERE queries to more advanced ones using the above techniques.
5. Optimize your Database (10 Marks)
You should now optimize your database:
? Apply suitable database optimization techniques to your final set of tables.
? Aim to implement a range of indexes.
? Run a suite of queries that will invoke those indexes.
University of Derby Online Learning
www.derby.ac.uk/online 6 of 12
? Aim to demonstrate some index suppression techniques.
6. Secure your Database (10 Marks)
Your optimized database should now be secured. To demonstrate this:
? Create three new users on your database
? Allocate them different security privileges and roles.
? Create a suite of views over your existing base tables.
? Issue appropriate privileges so that at least one user can use those views.
? Clearly demonstrate what actions each user can/cannot perform.
7. Building a Web Interface (15 Marks)
You now have a fully working, secure and optimized database. Your final task is to design
and build a web-based interface to that database such that it can support the following
operations:
? Insert new rows of data
? Update existing rows of data
? Delete existing rows of data
? Query existing rows of data
Please read the following guidance…
Important Notes and FAQ’s.
? Make sure your full name and student ID are on the front page of the assessment
? Clearly label all tasks and take care to explain and discuss your technical work
? For the modelling and design work you must supply clearly annotated diagrams
? For the SQL work you must supply full code listings of the inputted code and
screenshots of the outputted results – even if no rows were returned. Each
screenshot must include some identifying feature – such as a username or user ID –
that proves beyond doubt that it is your own work.
? If you need to research, then fully reference all such sources using the Harvard
notation
The detailed grading criteria are below…
University of Derby Online Learning
www.derby.ac.uk/online 7 of 12
Grading Criteria – Coursework 1
70-
100%
(Excellent/Outstanding)
? Fully complete and accurate ER Model that captures all/most semantic aspects of
the case study
? A fully normalized set of BCNF relations with all intermediate steps fully annotated
? A fully populated set of tables that encapsulates all primary & foreign keys plus all
other constraints
? A minimum of ten complex SQL queries that employ joins, grouping and other
advanced techniques
? A rich set of database optimization techniques to include both indexing and
clustering
? A rich set of database security techniques to include all privileges, roles, profiles and
views
? A well-designed web interface that incorporates HCI design principles and various
components
? A minimum of two insert, update and delete operations (each) via this web interface
? A minimum of ten queries (of varying complexity) via this web interface
? All work to be fully evidenced proving beyond doubt who the author of that work is
? All work to be fully annotated, well laid out and easy to follow with suitable headings
? Any external sources are fully referenced by strict adherence to the Harvard citation
standards
60-
69%
(Very Good/Commendable)
? Fully complete and accurate ER Model that captures most semantic aspects of the
case study
? A fully normalized set of BCNF relations with most intermediate steps fully annotated
? A fully populated set of tables that encapsulates all primary & foreign keys plus other
constraints
? A minimum of eight complex SQL queries that employ joins, grouping & other
advanced techniques
? A rich set of database optimization techniques to include both indexing and
clustering
? A rich set of database security techniques to include many privileges, roles, profiles
and views
? A well-designed web interface that incorporates HCI design principles and various
components
? A minimum of two insert, update and delete operations (each) via this web interface
? A minimum of eight queries (of varying complexity) via this web interface
? All work to be fully evidenced proving beyond doubt who the author of that work is
? All work to be fully annotated, well laid out and easy to follow with suitable headings
? Any external sources are fully referenced by strict adherence to the Harvard citation
standards
50-
59%
(Good/Highly Competent)
? Mostly complete and accurate ER Model that captures many semantic aspects of
the case study
? A largely normalized set of BCNF relations with many intermediate steps fully
annotated
? A fully populated set of tables that encapsulates some primary & foreign keys and
other constraints
? A minimum of five complex SQL queries that employ joins, grouping and other
advanced techniques
? A rich set of database optimization techniques to include either Indexing or
clustering
? A rich set of database security techniques to include a few privileges, roles, profiles
and views
University of Derby Online Learning
www.derby.ac.uk/online 8 of 12
? A basic web interface that incorporates some HCI design principles & perhaps
various components
? A minimum of one insert, update and delete operations (each) via this web interface
? A minimum of five queries (of varying complexity) via this web interface
? All work to be fully evidenced proving beyond doubt who the author of that work is
? Most work to be fully annotated, well laid out and easy to follow with suitable
headings
? Any external sources are fully referenced by strict adherence to the Harvard citation
standards
40-
49%
(Satisfactory/Competent)
? A very basic ER Model that captures only some semantic aspects of the case study
? A partly normalized set of BCNF relations with no/little annotation
? A barely populated set of tables that encapsulates only primary and/or foreign keys
? A minimum of three simple SQL queries that employ no advanced techniques
? A very basic set of database optimization techniques – perhaps only simple
indexing
? A very basic set of database security techniques to include any aspect
? A very basic web interface that incorporates no serious HCI design principles
? A minimum of one insert, update and delete operations (each) via this web interface
? A minimum of three simple queries via this web interface
? All work to be fully evidenced proving beyond doubt who the author of that work is
? Work is presented in a very basic (but readable) state
? Most external sources are fully referenced by adequate adherence to the Harvard
citation standards
35-
39%
(Unsatisfactory)
? A very basic ER Model that captures only small semantic aspects of the case study
? A very basic set of relations with no/little annotation or serious attempt at
normalization
? A barely populated set of tables that encapsulates only primary and/or foreign keys
? A minimum of three simple SQL queries that employ no advanced techniques
? A very basic set of database optimization techniques – perhaps only simple indexing
? A very basic set of database security techniques to include any aspect
? A very basic web interface that incorporates no serious HCI design principles
? A minimum of one insert, update and delete operations (each) via this web interface
? A minimum of one simple query via this web interface
? Some work to be fully evidenced proving beyond doubt who the author of that work
is
? Work is presented in a very basic (but readable) state
? Many external sources are NOT referenced by any adherence to the Harvard
citation standards
1-34%
(Very Poor/Nothing of Merit)
? A very basic (or absent) ER Model that captures only small semantic aspects of the
case study
? A very basic (or absent) set of relations with no /little annotation or serious attempt
at normalization
? A barely populated set of tables that encapsulates only primary and/or foreign keys
? A minimum of three simple SQL queries that employ no advanced techniques
? No database optimization or security work
? No web interface at all
? No hard evidence proving beyond doubt who the author of that work is
? Work is presented in a very basic (but readable) state
? No attempt is made to reference any cited sources
End of Assessment 1
University of Derby Online Learning
www.derby.ac.uk/online 9 of 12
Coursework 2
Assessment weighting: 50% of the module grade
Word count: 2000 words
Submission Deadline Date/Time: Wednesday 9th December at 9pm GMT.
Assessment
This is an individual assessment worth 50% of the module.
Your Tasks
Write a 2000-word report on alternative and complementary technologies to be used in the
deployment of the database implementation from Assessment 1, in order to fully exploit the
corporate data assets. Security, ethical and legal issues should also be considered.
Specifically, you should address the following technical areas:
1. Data Warehousing and the difference between operational and strategic data sets
2. Data Mining and OLAP (On-Line Analytical Processing) compared with OLTP
Systems
3. The rise of ‘Big Data’ and its applications
4. ‘NoSQL’ databases as compared with ‘ACID-compliant’ databases
5. The impact of the ‘Open Data’ movement
Important Notes and FAQ’s
? Make sure your full name and student ID are on the front page of the assessment
? Clearly label and number all sections and sub-sections of your report
? Have an accurate contents page at the front and a full reference list at the rear
? Place a word count at the end of the report
? Try and incorporate good-quality diagrams into your discussion
? Fully reference all sources using the Harvard notation
? Each technical area carries equal weight – 20 marks each
? For each technical area, cover the following points:
? Describe the essential concepts involved in that technology
? Explain the key advantages and benefits of that technology
? Describe specific real-world applications and users of that technology
? Explain any disadvantages or situations were that technology would be unsuitable
The detailed grading criteria are over the page…
University of Derby Online Learning
www.derby.ac.uk/online 10 of 12
Grading Criteria
70-100% (Excellent/Outstanding)
? All five technology areas are fully explained and analysed with many excellent
examples/applications
? Many high-quality diagrams are employed to support the key concepts discussed
in the report
? The data security, ethical and legal aspects are fully explained, analysed and
placed within context
? The report is presented to a very high standard, logically sequenced with no
spelling or other errors
? At least 15 quality external sources are cited within the report
? All external sources are fully cited and referenced by strict adherence to the
Harvard standard
60-69% (Very Good/Commendable)
? All five technology areas are well explained and analysed with some excellent
examples/applications
? Some high-quality diagrams are employed to support the key concepts discussed
in the report
? The data security, ethical and legal aspects are well explained, analysed and
placed within context
? The report is presented to a high standard, logically sequenced with no spelling
or other errors
? At least 12 quality external sources are cited within the report
? All external sources are fully cited and referenced by strict adherence to the
Harvard standard
50-59% (Good/Highly Competent)
? Most technology areas are well explained and analysed with some excellent
examples/applications
? Some high-quality diagrams are employed to support the key concepts discussed
in the report
? The data security, ethical and legal aspects are adequately explained and
analysed
? The report is presented to a good standard, logically sequenced with few spelling
or other errors
? At least 10 quality external sources are cited within the report
? All external sources are fully cited and referenced by strict adherence to the
Harvard standard
40-49% (Satisfactory/Competent)
? Most technology areas are explained and analysed to some degree
? Possibly one or more diagrams are employed to support the key concepts
discussed in the report
? The data security, ethical and legal aspects are explained and analysed in a very
superficial manner
? The report is presented to a basic (but readable) standard
? At least 7 quality external sources are cited within the report
? All external sources are fully cited and referenced by reasonable adherence to
the Harvard standard
University of Derby Online Learning
www.derby.ac.uk/online 11 of 12
35-39% (Unsatisfactory)
? Most technology areas are NOT explained and analysed to any serious degree
? No diagrams are employed to support the key concepts discussed in the report
? The data security, ethical and legal aspects are very poorly explained and
analysed (if at all)
? The report is presented to a very basic (but readable) standard, possibly with
spelling errors
? Fewer than 5 quality external sources are cited within the report
? Few external sources are cited and referenced by any adherence to the Harvard
standard
1-34% (Very Poor/Nothing of Merit)
? Most technology areas are NOT explained and analysed to any meaningful
degree
? No diagrams are employed to support the key concepts discussed in the report
? The data security, ethical and legal aspects are very poorly explained and
analysed (if at all)
? The report is presented to a very basic (but readable) standard, with many errors
and mistakes
? Fewer than 3 quality external sources are cited within the report
? There is no use of the Harvard system
Assessment Presentation
When marking this assignment the academics will also be looking for the following criteria:
? Clear legible presentation.
? Good use of spelling, grammar and language throughout.
? Appropriate focus, meeting learning outcomes/assignment criteria.
? Logical progression and structure of arguments.
? (Normally) an introduction, a well-developed discussion and a conclusion
summarising the work.
? The introduction will include an exploration of the focus of the assignment and
discuss the way the assignment has been approached.
? Evidence of a range of relevant supporting reading.
? Use of accurate, evidence based information to support the arguments made.
? Follow normal Academic Regulations in terms of Academic Offences, style and
language.
? Use the Harvard system of referencing and may include a bibliography that lists all
resources referenced.
University of Derby Online Learning
www.derby.ac.uk/online 12 of 12
? A declaration statement which says you have checked your Turnitin originality report
and certain that the work is your own (and has never been submitted for marking
before by you, or anyone else)
? Maintain the confidentiality of clients/customers and persons associated with them,
colleagues and organisations
This module uses a grading scale applicable to Levels 4, 5 and 6 in the University Credit
Framework.