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

infobase Design and Implementation - Coursework ExampleIn other words, though they share normal attributes, full era leasement may require extra attributes to be recorded. ix. Invoices are prepared per lymph node and can be generated as a report at any time. b. Entity Relationship Model From the higher up assumptions, the following business rules are identified Business Rules i. sever tout ensembley ARO centre can receive contracts from cardinal or more companies and all(prenominal) company can send contracts to one or more ARO centres. ii. Each company can request for one or more CRPs but distributively CRP must be requested by one and solely one company. iii. No two clients have a corresponding CRP and no two CRPs exist for a same client. iv. Each CRP must have at to the lowest degree 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 em ploy 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 pattern as shown in below figure Fig ER model for ARO database schema c. Data Dictionary Entity ARO_CENTRES Holds details closely 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 motion 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 s tatements built for creating the tables and kin constraints are discussed. SQL queries that can used to build the reports given in the requirement were withal 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 stray to recreate the real business scenario of handling CRP, few rows in the table were initially populated with personify 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 queries were also created and executed to produce some of the reports given in the requirement. Further, a VIEW that comb ines 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 utilise appropriate optimisation techniques for maximum efficiency and overall throughput.