6CC546 Database Fundamentals September 2018

6CC546 Database Fundamentals September 2018

1. 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 utilized. 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.

2. Learning Outcomes

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.

University of Derby Online Learning

6CC546 Database Fundamentals September 2018

3) Assessment types used in this module 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 be an individual task, such as reading some text or watching a video and documenting your reaction to it, responding to some discussion points on the discussion forum or participating in a ‘live online classroom’ session. The main aim of formative activities is that you receive feedback on your contribution which will help you in preparation for your final module assessment(s). There is opportunity during the module to engage in formative assessment; this is related to the summative assessment for the module. As part of this module you will be advised to complete several aspects of self-assessment following individual sessions. This will formulate in part a recommended study guide for your summative assessment.

6CC546 Database Fundamentals September 2018

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

www.derby.ac.uk/online

3 of 11

University of Derby Online Learning

4) Specific Assessment Component Guidelines

Coursework 1

Assessment weighting:
Word count:
Submission Deadline Date/Time: Provisional Feedback Released:

50% of the module grade

2000 words equivalence

Wednesday 31st October 2018 by 9 PM (UK time)

15 Working days after submission

Title: Stepping into History

6CC546 Database Fundamentals September 2018

This is an individual assessment worth 50% of the module. Case Study

‘Stepping into History’ is a fast-growing business that offers the history enthusiast something a little bit different. Through its website and attendance at various events, the company promotes the following services and products:

• Guided tours of famous historical sites, buildings and battlefields – across the world

• Customized expert lectures from world-renowned historians for private functions
• Genealogical research and family tree services – either face-to-face or remotely
• Historical documents and books location service – from across the world
• Historical book sales – from its own on-line book store

• History magazine subscriptions – issued quarterly in hardcopy and e-copy format
• Hands-on training workshops and courses – in historical and archaeological skills

The company needs a database to run the company – your job is to deliver that database. For each aspect of the business you must identify and capture the relevant data – such as speaker details, customer details, lecture/visit dates and locations, book details, workshop details etc. etc.

The above 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.

www.derby.ac.uk/online 4 of 11

6CC546 Database Fundamentals September 2018

University of Derby Online Learning

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.

6CC546 Database Fundamentals September 2018

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)
• T able 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.

6CC546 Database Fundamentals September 2018

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.
• Aim to demonstrate some index suppression techniques.

www.derby.ac.uk/online

5 of 11

University of Derby Online Learning

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 belowDo you need help in your 6CC546 Database Fundamentals September 2018