B9IS100 Design and implementation of a hybrid database system

Advanced Databases

MSc Information Systems with Computing

Assessment Brief

Module Title:

Advanced Databases

Module Code:

B9IS100

Stage (if relevant):

Award

Assessment Title:

Design and implementation of a hybrid (Relational/XML) database system of your choice.

Assessment Number (if relevant):

1

Assessment Type:

Advanced Databases

Restrictions on Time/Length : +/- 10%

N/A

Individual/Group:

Individual

Assessment Weighting:

50% of the Total Grading

Details of assignment brief

(IT SHOULD BE ORIGINAL WORK OF THE STUDENT)

Total: 100 Marks (Weighting: 50%)

You are required to design and implement a hybrid database system for a company/business of your choice (real or virtual). As part of your assessment, you are required to submit the following:

Report: (35 Marks)

The report must include the following:

  1. Details of the chosen system. Scope of the database. Seven business requirements. Screen shot must be included to demonstrate the execution of each business requirement (through stored procedures). (12 marks)
  2. A list of business rules for the system (at least 4 must be implemented using SQL code instead of showing at design stage). The implementation must be shown by the screenshots in the report. (8 marks)
  3. Relational Schema for the hybrid database system (must be developed in Visio or some similar system using crow’s foot notation or UML notation). Relational schema must fulfill the requirements of 3 NF, you must provide the justification in report. It must include five substantial tables using XML data type in at least one table (or more) as appropriate. Justify the use of XML data type that makes it a hybrid database. (10 marks)
  4. Report must be well-structured with Conclusions and Bibliography. Screenshots and explanation must be provided for all requirements and business rules. (5 marks)

Note: All code must be included in the appendix.

Completed Database System (45 Marks)

Develop a SQL Server database system based on the schema developed as part of the assessment. Your database must include the following:

  1. Tables with relationships using some referential integrity constraints. There should be a minimum of five substantial tables and any number of smaller tables that you require to implement your database. Tables should be linked using an appropriate method in SQL Server. Each table must have a minimum of 5 test records per table. In your report explain which referential integrity constraints are used and why? Include Data Diagram that must match relational schema. Code must be included in appendix. (10 marks)
  2. All queries must be written using stored procedures (with parameters) to meet the business requirements. The queries that meet business requirements must include the following features:
    • Use of JOIN between two or more tables as required
    • Use of GROUP BY with HAVING
    • Use of SQL functions
    • Developing XML with appropriate elements using relation fields
    • Retrieving data logically from a field with XML data type as well as data from fields from other data types.
    • Modifying data in a field of XML data type.
    • Searching data in a field of XML data type.(21 marks)
  3. Two triggers to demonstrate the implementation of business rules.(8 marks)
  4. Two Views to demonstrate the development of virtual tables. How Views would be helpful to generate customised View of your data?(6 marks)

Innovation (20 marks)

Use of any other features to enhance the usability of your database system.

For Example, Put Front End and Create formatted reports to enhance usability of the system and mention it in your report.

Assessment criteria

Criteria/Mark

< 40

40 - 49

50 - 59

60 – 69

70 +

Report

Insufficient or incomplete report with poor or incomplete data model, no/poor normalisation, poor explanation, poor formulation of requirements, etc.

Some but insufficient and poorly written report with poorly developed data model, some normalisation and explanation not enough to meet the requirements

Sufficient explanation with data model with normalised schema that solves problem but lack of attention to formulating business requirements and good report writing skills

Well-structured and well-written report with good specifications and data model with normalised schema. Appropriate requirements for the business.

Excellent solution to problem proving originality, creativity and evidence of research – very well written with excellent data model and normalised schema to 3NF

Database System

Less than four tables not properly structured, queries without the required features that do not meet business requirements, no/poor implementation of stored procedures/Views/Triggers

Four substantial tables demonstrating some structure of data, queries with some required features that meet some business requirements, some implementation of stored procedures/Views/Triggers

Four substantial tables with some structure, queries with most required features that meet most business requirements, implementat-ion of stored procedures/Views/Triggers but not as appropriate as required

Four substantial and structured tables with appropriate records queries with all required features that meet the business requirements, good implementation of stored procedures /Views/Triggers,etc.

Excellent database system that meets all the business requirements, required features, etc.

General Assessment Submission Requirements for Students:

  1. Online assignments must be submitted no later than the stated deadline.
  2. All relevant provisions of the Assessment Regulations must be complied with.
  3. Extensions to assignment submission deadlines will be not be granted, other than in exceptional circumstances. To apply for an extension please go to http://www.dbs-students.com/Registrar/ and download the Assignment Extension Request Form.
  4. Students are required to retain a copy of each assignment submitted, and the submission receipt.
  5. Assignments that exceed the word count will be penalised.
  6. Students are required to refer to the assessment regulations in their Student Guides and on the Student Website.
  7. Dublin Business School penalises students who engage in academic impropriety (i.e. plagiarism, collusion and/or copying). Please refer to the attached referencing guidelines for information on correct referencing.

What is referencing and why is it necessary?

Please follow this link to the Harvard Style Referencing Guide - all referencing is required in this format.

http://issuu.com/dbslibrary/docs/harvard-referencing-guide/1?mode=a_p

The School of Arts generally use APA Referencing , information is available under DBS library guides on http://www.library.dbs.ie .