Assignment -
You are required to develop a hybrid database system for a Hospital. As part of your assessment, you are required to submit the following:
Part 1: Report
The report must include the following:
- Background of your chosen system. Scope including business requirements.
- A list of business rules for the system (at least 5 implemented must be implemented).
- A database model for the hybrid database system. The model (relational schema) of your database must fulfill the requirements of 3NF. It must be based on an Entity Relationship Diagram (ERD) for the database application using the ER/EER modelling techniques (include a minimum of 4 entities displaying all the relationships with cardinalities and all constraints. At least one entity must contain an attribute with XML data type.
- Critical evaluation of your database design, highlighting the strengths and weaknesses.
- Report must be well-structured with Conclusions and Bibliography. Screenshots and explanation must be provided where required.
Part 2: 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 4 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?
- At least one table with an appropriate field with the proper use of XML data type.
- Queries to meet the business requirements. The queries must include:
i. A query that retrieves data from one or more tables.
ii. A query that retrieves data from two or more tables.
iii. A query that retrieves aggregate data based on certain criteria.
iv. A query that retrieves data based on certain pattern
v. A query that generates XML document from SQL records.
vi. A query that retrieves data logically from a field with XML data type as well as data from fields from other data types.
vii. A query to update data in a field of XML data type.
viii. Any other queries to fulfill the business requirements.
Note: All these queries must be developed as "Stored Procedures". Four stored procedures must use parameters.
- Two triggers to demonstrate the implementation of business rules.
- Two Views to demonstrate the development of virtual tables and their advantages.