Database Design and Implementation Coursework Example | Topics and Well Written Essays - 3250 words

Database Design and Implementation - Coursework Example In other words, though they share common attributes, full time employment may require extra attributes to be recorded. ix. Invoices are prepared per client and can be generated as a report at any time. b. Entity Relationship Model: From the above assumptions, the following business rules are identified: Business Rules: i. Each ARO centre can receive contracts from one or more companies and each company can send contracts to one or more ARO centres. ii. Each company can request for one or more CRPs but each CRP must be requested by one and only one company. iii. No two clients have a same CRP and no two CRPs exist for a same client. iv. Each CRP must have at least one service but each service can exist in one or many CRPs. v. Each client can have one or many dependant buts each dependant must have one and only one client. vi. Each ARO centre must employ one or more employees and each employee must be employed by one and only one centre. vii. Each employee can be assigned only one CRP and each CRP must be assigned to one and only one employee. The above assumptions and business rules are transferred into an enhanced ER data model as shown in below figure: Fig: ER model for ARO database schema c. Data Dictionary Entity ARO_CENTRES Holds details about each ARO centres ARO_EMPLOYEE Holds details about the full time and part time employees employed at each ARO centres COMPANY Holds details about all affiliated companies of ARO CRP Holds details about each Client Relocation Plan created by ARO after receiving Contract from the company SERVICES Relocation services offered by ARO CLIENT Holds details about the client associated with the CRP CLIENT_DEP Holds details about all dependants of each client CONTRACT Attribute CENTRE_ID CENTRE_NAME CENTRE_LOCATION... According to the research findings it can therefore be said that all SQL statements built for creating the tables and relationship constraints are discussed. SQL queries that can used to build the reports given in the requirement were also presented. Further a critical review on the proposed model is discussed in detail. The database for ARO was designed and implemented in SQL SERVER 2008. Initially, an ER diagram was designed that best describes the overall operation of ARO in terms of entities (tables) and the relationship between them. Later, the ER model was transformed to SQL statements for creating the tables and defining relationships. The tables were then populated with sample data. In order to recreate the real business scenario of handling CRP, few rows in the table were initially populated with cost as zero CRP and status of CRP as ‘N’ or ‘P’. The status was later updated to ‘Y’ and the CRP service cost with the actual cost. SQL queri es were also created and executed to produce some of the reports given in the requirement. Further, a VIEW that combines multiple tables to produce invoice report was built and executed. In addition, a brief overview of the model and various security issues that can be considered while creating very large databases along with possible solutions were discussed. Though the overall database design serves the purpose of the requirement, the design can be further enhanced by using appropriate optimisation techniques for maximum efficiency and overall throughput.