3612ICT database systems

1. A) Submit the artifacts of the physical design for your project; in particular submit the following items.

• A complete Entity- Relational diagram (you can use the tool

(public domain) Eclipse to edit and draw such diagram). It is NO LONGER acceptable to use Extended Entity-

Relationship Modeling. You can still present your diagrams using a tool for UML class diagrams, but now everything has to conform to the capabilities of a relational mapping ready for implementation in a Relational Database Management System (RDBMS). In particular, semantic relations (like inheritance and aggregation) should have been removed. Also, many to many relations should also have been resolved.

• Provide drafts of SQL for each query in your list of typical queries of the earlier milestone. Make sure you can make some estimates of workload and consider which indexes and of which type you will be suggesting.
• Analyze the level of normalization and discuss if it offers an appropriate trade-off between efficiency and guarantees against update anomalies.
• Describe if you consider formulating any views and what type would they be (materialized, updatable, etc).
• Detail the transaction structure of your list of functions from Milestone 1. Consider the impact of indexes, updatable views and the race for locks with respect to your level of concurrency.
• Investigate what capacity to administer privileges your RBDMS supports and indicate the structure of users of your database.
• Give a plan for the implementation of the list of constrains that will NOT be possible to ensure by the mechanisms of the Data Definition Language of RDBMS and are not documented in the E-R diagram.