Assignment Task
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:
Technical Report: The report must include the following:
- Details of the chosen business case. Scope of the database. Seven business requirements gathered/assumed during the analysis stage. Screen shots must be included to demonstrate the execution of each business requirement (through stored procedures).
- 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.
- Relational Schema for the hybrid database system (must be developed in Visio or some similar system using crow’s foot notation or UML notations). Relational schema must fulfil the requirements of 3 NF, you must provide the justification in report. Data type of each field must be shown in the diagram. 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.
- Report must be well-structured with Conclusions and Bibliography. Screenshots and explanation must be provided for all requirements (at a later stage) and business rules.
Note: All code must be included in the appendix. In addition to above all, your report must include data diagram (as well as visio diagram), comment on the referential integrity constraints and write a paragraph on innovation.
Completed Database System
Develop a SQL Server database system based on the schema developed as part of the assessment. Your database must include the following:
- 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.
- 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 custom SQL functions
- Developing XML with appropriate elements using relational 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.
- Two triggers to demonstrate the implementation of business rules.
- Two Views to demonstrate the development of virtual tables. How Views would be helpful to generate customised View of your data? What type of clients would need that data?
