B9IS100 Design and implementation of a hybrid database system

Advanced Databases

MSc Information Systems with Computing

Assessment Brief

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

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 .