Database Design and Implementation - Coursework Example

? Database Design and Implementation -For Assignment and Relocation Office [Pick the Contents Introduction: 3 Analysis and Design3 2.Physical Design 7 3.Queries 10 4.Critical Review 13 5.Security Issues 14 Conclusion: 16 Appendix A 18 Reference 26 Introduction: This Report presents a detailed overview of analysis, design and implementation of database for Assignment and Relocation Office (ARO) using SQL SERVER. The report starts with the detailed analysis of the requirement, suitable solution for the design and all the assumptions made while designing the model. The ER diagram which visualises the overall operation of ARO is also presented along with a detailed Data dictionary for all the entities defined in the model. Followed by the data dictionary, 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. In addition, the report also discusses various security issues that can be considered while creating very large databases. 1. Analysis and Design a. Assumptions: i. A company is registered in the ARO’s system as an affiliate before they issue any contract. In other words, affiliated company’s details are recorded in the database irrespective of whether they make contracts or not. ii. A company issues contract which are unique to each of its employees. In other words, no two clients share the same contract. iii. A client may or may not have any dependants. In other words, cases may exist in which there is no entry for clients in the dependant table. iv. A company may issue any number of contracts based on its requirements. v. Each contract has at least one service. In other words, no contract exists without any services. vi. Each contract is assigned to an ARO centre where it is transferred into CRPs (Client Relocation Plan) and assigned to a representative (employee) who takes care of client’s relocation services and updates the status of each service at each stage. vii. Each ARO centre has its own set of employees. In other words, no two centres share a same employee. viii. Employees are either employed full time or part time. 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 CENTRE_EMP_NOS EMP_ID EMP_NAME EMP_CONTACT_NO EMP_EMAIL EMP_TYPE EMP_DOJ EMP_CENTRE_ID EMP_SAL COMPANY_ID COMPANY_NAME COMPANY_ADDRESS COMPANY_CONTACT_NO COMPANY_EMAIL CRP_ID SERVICE_ID CLIENT_ID COMPANY_ID CRP_TYPE CRP_SERVICE_DESC CRP_SERVICE_COST CRP_CRT_DATE CRP_DESTN_COUNTRY CRP_INCHARGE_REP CRP_SERVICE_STATUS CRP_UPD_DATE SERVICE_ID SERVICE_NAME SERVICE_CHARGE CLIENT_ID CLIENT_NAME CLIENT_ADDRESS CLIENT_CONTACT_NO CLIENT_EMAIL CLIENT_COUNTRY CLIENT_PASSPORT_NO CLIENT_VISA_NO CLIENT_DEP_NOS CLIENT_ID DEP_NO DEP_REL DEP_NAME DEP_CONTACT_NO DEP_ADDRESS DEP_PASSPORT_NO DEP_VISA_NO CONTRACT_ID COMPANY_ID CENTRE_ID Type INTEGER VARCHAR VARCHAR INTEGER INTEGER VARCHAR INTEGER VARCHAR CHAR DATE INTEGER INTEGER INTEGER VARCHAR VARCHAR INTEGER VARCHAR INTEGER INTEGER INTEGER INTEGER CHAR VARCHAR INTEGER DATE VARCHAR INTEGER CHAR DATE INTEGER VARCHAR INTEGER INTEGER VARCHAR VARCHAR INTEGER VARCHAR VARCHAR VARCHAR VARCHAR INTEGER INTEGER INTEGER VARCHAR VARCHAR INTEGER VARCHAR VARCHAR VARCHAR INTEGER INTEGER INTEGER Size 25 20 25 25 1 20 40 25 1 50 20 1 20 25 40 25 20 10 10 10 25 40 10 10 Keys PK PK PK PK PK FK FK FK PK PK PK PK PK Derived CRP_ TOTAL_COST Assumptions Auto incremented Auto incremented Auto incremented Auto incremented Auto incremented Auto incremented Domain F or P R or A N, P, Y Abbreviation Explanations: F or P: Full time or Part Time R or A: Relocation or Assignment N, P, Y: Not started, in Progress, Yes completed 2. Physical Design The DDL statements used for creating the above tables and relationships in SQL SERVER database are given below: 3. Queries i. List number of clients to be billed for a specific company Output: ii. List the status of relocation services per country Output: iii. Name of the staff working on a specific CRP Output: Creating View: INVOICE The view INVOICE lists the detailed Client Relocation Plan along with the description, cost and current status for each service listed in the contract. The SQL statement used to create the view and the report obtained by querying the view is shown below: Fig: Report created by querying Invoice View 4. Critical Review The overall business operation of ARO is captured using 6 major entities and a weak entity to hold details of the dependants of each client. The relationships among each entity are also clearly defined in the ER model. The entity CLIENT_DEP which was designed to hold information about the dependants of each client is identified as a weak entity as it does not hold any primary key for itself but can only be identified by the primary key of its owner (CLIENT) client_id and its own discriminator dep_no. During requirement analysis, the ARO was found to have two types of employee: part time and full time and hence two sub-types ARO_FT_EMPLOYEE and ARO_PT_EMPLOYEES were defined under ARO_EMPLOYEE. Further each employee in ARO_EMPLOYEE employed in a centre in ARO_CENTRES is assigned a CRP, in other words a employee assigned to CRP must be a employee of any ARO centre and hence there exist an aggregation between ARO_EMPLOYEE and ARO_CENTRES with relationship ‘employ’ which acts an entity and forms a relationship ‘assigned’ with CRP. Each entity is implemented in the physical database of SQL SERVER 2008. The 7 entities are transferred to physical tables and the relationships among them are implemented as foreign keys on the corresponding tables. A relationship table named CONTRACT is implemented in addition to hold the relationship between ARO centres and the COMPANY for each contract. This table is assumed to be updated each time a new contract is received. This new table has been employed in order to keep both ARO_CENTRES and COMPANY tables tied to each of its one and only primary key. If this relationship table is not in place, then there would be multiple rows for each primary key and a new primary key has to be combined to the existing one. This would not only increase the table size but also affects performance. Most of the situations given under report requirements in the case study are covered in the Queries section. A view is also created for INVOICE. As invoice data can be easily derived from other tables. A separate table for invoice was not created. Instead, a view is created which extracts the required data from multiple tables and presents in a report format for each client within each company. Thus, the design and implementation of ARO database is complete and the reports are well designed. The design is also test with sample data. The database is now ready for data loading and extraction. The performance of the database is also improved by introducing relation table between ARO_CENTRES and COMPANY which reduces the size of the table and avoids multi row redundancies. Similarly, the overall design can be further improved by implementing intermediate relation tables for the relationships CRP/ARO_ EMPLOYEE and CRP/COMPANY. 5. Security Issues In the current design, the possibility for database threats is very high. There are a wide range of threats available in the outside world, few of which are discussed below along with the possible prevention measures. Unauthorized access: The information in the database is high confidential and hence unauthorized access of any data will impose a serious threat to the overall business of the ARO. To prevent this, separate login and user accounts are created for ARO database and the login details are confidentially shared only to the intended persons. Users are created using the below syntax: CREATE LOGIN ARO_EMP WITH PASSWORD = 'mypassword'; USE ARO; CREATE USER ARO_EMP for LOGIN ARO_EMP; Excessive privilege on database: Users who are given access to entire database can sometimes try to access the data beyond their role. For example, an employee in ARO (created in the above step) whose role is to enter/view details of services in CRP and client information may try to access the COMPANY details and try to retrieve confidential information. This can be prevented by implementing query level access control which grants privilege only to minimum required SQL operations. GRANT SELECT ON CLIENT TO ARO_EMP; GRANT SELECT ON CLIENT_DEP TO ARO_EMP; GRANT SELECT, UPDATE ON CRP TO ARO_EMP; Excessive privilege on table: In the above scenario, where the user is granted privilege on selected tables, unrestricted access to all columns may still impose threats. For example, the employee who is given update privilege for CRP table can try to update the CRP created date to adjust his deadline issues in completing a service. This sort of issues can be prevented by granting column level privileges to users. In this case, the employee is granted update privilege only for columns he is authorized to update. The SQL syntax is given below: GRANT UPDATE ON CRP (CRP_SERVICE_STATUS, CRP_SERVICE_COST, CRP_UPD_DATE) TO ARO_EMP; Excessive privilege on data: In the above example, the employee though restricted to update columns only he is intended to, he has privilege to update data that is beyond his limits. In other words, he can update the data for CRPs that are not assigned to him. This is a serious threat and should be addressed. However, there is no row level access control in any database. Hence the concept of view should be used here. A view is created for each table which contains data that are relevant only to the particular user. CREATE VIEW CRP_VIEW AS SELECT * FROM CRP WHERE CRP_ID =@CRP_ID; @CRP_ID should be replaced by the CRP_ID the employee is working with, at run time in the application program. Conclusion: Thus 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 queries 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. Appendix A INSERT statements used to populate table: INSERT INTO ARO_CENTRES(CENTRE_NAME,CENTRE_LOCATION, CENTRE_EMP_NOS) VALUES ('Centre1','Location1',6), ('Centre2','Location2',8), ('Centre3','Location3',9), ('Centre4','Location4',5), ('Centre5','Location5',10), ('Centre6','Location6',6), ('Centre7','Location7',8), ('Centre8','Location8',9), ('Centre9','Location9',5), ('Centre10','Location10',10); INSERT INTO ARO_EMPLOYEE(EMP_NAME,EMP_CONTACT_NO, EMP_EMAIL, EMP_TYPE,EMP_CENTRE_ID,EMP_SAL) VALUES ('Employee1',787987099,'employee1@domain.com','P',1,2000), ('Employee2',808908000,'employee2@domain.com','P',2,2200), ('Employee3',797097808,'employee3@domain.com','F',3,1900), ('Employee4',797979079,'employee4@domain.com','F',4,2400), ('Employee5',797898998,'employee5@domain.com','F',5,2300), ('Employee6',787111099,'employee6@domain.com','P',1,2500), ('Employee7',808222000,'employee7@domain.com','P',2,1900), ('Employee8',797333808,'employee8@domain.com','F',3,2100), ('Employee9',797444079,'employee9@domain.com','F',4,1900), ('Employee10',797555998,'employee10@domain.com','F',5,2300); INSERT INTO CLIENT(CLIENT_NAME,CLIENT_CONTACT_NO,CLIENT_ADDRESS, CLIENT_EMAIL,CLIENT_COUNTRY,CLIENT_PASSPORT_NO, CLIEN T_VISA_NO, CLIENT_DEP_NOS) VALUES ('Client1',898988989,'Address for Client1','client1@domain.com', 'Location1' , 'P1232424', 'A1213132',2), ('Client2',898988987,'Address for Client2','client2@domain.com','Location2', 'P1232425','B1213132',1), ('Client3',898988985,'Address for Client3','client3@domain.com','Location3', 'P12333424','C1213132',0), ('Client4',894488987,'Address for Client4','client4@domain.com','Location4', 'P12324444','D1213132',1), ('Client5',778988987,'Address for Client5','client5@domain.com','Location5' , 'P12325525','E1213132',1), ('Client6',898111989,'Address for Client6','client6@domain.com', 'Location6' , 'P1211124', 'G1213132',2), ('Client7',898222987,'Address for Client7','client7@domain.com','Location7', 'P1222225','H1213132',1), ('Client8',898333985,'Address for Client8','client8@domain.com','Location8', 'P12334424','I1213132',0), ('Client9',894444487,'Address for Client9','client9@domain.com','Location9', 'P12324444','J1213132',1), ('Client10',77555987,'Address for Client10','client10@domain.com','Location10' , 'P12355525','K1213132',1); INSERT INTO CLIENT_DEP(CLIENT_ID,DEP_NO,DEP_REL,DEP_NAME, DEP_CONTACT_NO, DEP_ADDRESS,DEP_PASSPORT_NO,DEP_VISA_NO) VALUES (1,1,'wife','Dependant1',89898989,'Address for Client1','A13242414', 'A121344'), (1,2,'child','Dependant2',78798989,'Address for Client1','A42749782','VISA234'), (2,1,'wife','Dependant3',78979899,'Address for Client2','B3209898','VISA422'), (4,1,'wife','Dependant4',8798798,'Address for Client4','A6867879','C23u1u97'), (5,1,'wife','Dependant5',89898989,'Address for Client5','H79798099','V797909879'), (6,1,'wife','Dependant6',89111989,'Address for Client6','I13242414', 'B121344'), (6,2,'child','Dependant7',78222989,'Address for Client7','J42749782','GHSA234'), (7,1,'wife','Dependant8',78333499,'Address for Client8','K3209898','HKISA422'), (9,1,'wife','Dependant9',87944448,'Address for Client9','L6867879','F23CLA97'), (10,1,'wife','Dependant10',89334599,'Address for Client10','M22298099', 'ZFG909879'); INSERT INTO COMPANY(COMPANY_NAME,COMPANY_ADDRESS, COMPANY_CONTACT_NO, COMPANY_EMAIL) VALUES ('Company1','Address for company1',977329899,'company1@domain.com'), ('Company2','Address for company2',989829989,'company2@domain.com'), ('Company3','Address for company3',897978790,'company3@domain.com'), ('Company4','Address for company4',879798797,'company4@domain.com'), ('Company5','Address for company5',986893478,'company5@domain.com'), ('Company6','Address for company6',977111899,'company6@domain.com'), ('Company7','Address for company7',989222989,'company7@domain.com'), ('Company8','Address for company8',897233790,'company8@domain.com'), ('Company9','Address for company9',879442797,'company9@domain.com'), ('Company10','Address for company10',986232478,'company10@domain.com'); INSERT INTO CRP(CRP_ID,SERVICE_ID,CLIENT_ID,COMPANY_ID,CRP_TYPE, CRP_SERVICE_DESC, CRP_SERVICE_COST,CRP_CRT_DATE, CRP_DESTN_COUNTRY,CRP_INCHARGE_REP,CRP_SERVICE_STATUS, CRP_UPD_DATE) VALUES (1,1,1,1,'P','Client Travel and boarding',680,'2012-06-03','DLocation1',2,'Y', '2012-06-03'), (1,2,1,1,'P','Dependants Travel',0,'2012-06-03','DLocation1',2,'N','2012-06-03'), (1,5,1,1,'P','Bank account opening',0,'2012-06-03','DLocation1',2,'P','2012-06-03'), (2,1,2,1,'P','Client Travel and boarding',800,'2012-06-03','DLocation2',3,'Y', '2012-06-03'), (2,2,2,1,'P','Dependants Travel',900,'2012-06-03','DLocation2',3,'Y','2012-06-03'), (2,5,2,1,'P','Bank account opening',0,'2012-06-03','DLocation2',3,'P','2012-06-03'), (2,3,2,1,'P','School finding',0,'2012-06-03','DLocation2',3,'P','2012-06-03'), (3,1,3,2,'P','Client Travel and boarding',800,'2012-06-03','DLocation3',4,'Y', '2012-06-03'), (3,2,3,2,'P','Dependants Travel',900,'2012-06-03','DLocation3',4,'Y','2012-06-03'), (3,3,3,2,'P','School finding',0,'2012-06-03','DLocation3',4,'P','2012-06-03'), (4,1,4,2,'T','Client Travel and boarding',800,'2012-06-03','DLocation4',5,'Y', '2012-06-03'), (4,5,4,2,'T','Bank account opening',30,'2012-06-03','DLocation4',5,'Y','2012-06-03'), (5,1,5,2,'T','Client Travel and boarding',800,'2012-06-03','DLocation5',6,'Y', '2012-06-03'), (5,6,5,2,'T','Health care insurance paper submission',30,'2012-06-03','DLocation5', 6,'Y','2012-06-03'), (6,1,6,3,'P','Client Travel and boarding',680,'2012-06-03','DLocation6',7,'Y', '2012-06-03'), (6,2,6,3,'P','Dependants Travel',0,'2012-06-03','DLocation6',7,'N','2012-06-03'), (6,5,6,3,'P','Bank account opening',0,'2012-06-03','DLocation6',7,'P','2012-06-03'), (7,1,7,3,'P','Client Travel and boarding',800,'2012-06-03','DLocation7',8,'Y', '2012-06-03'), (7,2,7,3,'P','Dependants Travel',900,'2012-06-03','DLocation7',8,'Y','2012-06-03'), (7,5,7,3,'P','Bank account opening',0,'2012-06-03','DLocation7', 8,'P','2012-06-03'), (7,3,7,3,'P','School finding',0,'2012-06-03','DLocation7',8,'P','2012-06-03'), (8,1,8,4,'P','Client Travel and boarding',800,'2012-06-03','DLocation8',9,'Y', '2012-06-03'), (8,2,8,4,'P','Dependants Travel',900,'2012-06-03','DLocation8',9,'Y','2012-06-03'), (8,3,8,4,'P','School finding',0,'2012-06-03','DLocation8',9,'P','2012-06-03'), (9,1,9,4,'T','Client Travel and boarding',800,'2012-06-03','DLocation9',10,'Y', '2012-06-03'), (9,5,9,4,'T','Bank account opening',30,'2012-06-03','DLocation9',10,'Y','2012-06-03'), (10,1,10,4,'T','Client Travel and boarding',800,'2012-06-03','DLocation10',11,'Y', '2012-06-03'), (10,6,10,4,'T','Health care insurance paper submission',30,'2012-06-03','DLocation10', 11,'Y','2012-06-03'); INSERT INTO CONTRACT(COMPANY_ID,CENTRE_ID) VALUES (1,1), (2,2), (3,3), (4,4), (5,5), (6,1), (7,2), (8,3), (9,4), (10,5); INSERT INTO SERVICES(SERVICE_NAME,SERVICE_CHARGE) VALUES ('Client Travel',100), ('Dependant Travel',100), ('School',100), ('Bank',100), ('Health care',100), ('Removal Companies',100), ('Pets',100), ('Insurance',100), ('Vehicle Purchase',100), ('House Hunt',100), ('Miscelaneous',100); UPDATE Statements: UPDATE CRP SET CRP_SERVICE_COST = 220, CRP_SERVICE_STATUS='Y', CRP_UPD_DATE='05-03-2012' WHERE CRP_ID =1 and SERVICE_ID=2; UPDATE CRP SET CRP_SERVICE_COST = 260, CRP_SERVICE_STATUS='Y', CRP_UPD_DATE='05-03-2012' WHERE CRP_ID =1 and SERVICE_ID=5; UPDATE CRP SET CRP_SERVICE_COST = 270, CRP_SERVICE_STATUS='Y', CRP_UPD_DATE='05-03-2012' WHERE CRP_ID =2 and SERVICE_ID=3; UPDATE CRP SET CRP_SERVICE_COST = 280, CRP_SERVICE_STATUS='Y', CRP_UPD_DATE='05-03-2012' WHERE CRP_ID =2 and SERVICE_ID=5; UPDATE CRP SET CRP_SERVICE_COST = 210, CRP_SERVICE_STATUS='Y', CRP_UPD_DATE='05-03-2012' WHERE CRP_ID =3 and SERVICE_ID=3; UPDATE CRP SET CRP_SERVICE_COST = 200, CRP_SERVICE_STATUS='Y', CRP_UPD_DATE='05-03-2012' WHERE CRP_ID =6 and SERVICE_ID=2; UPDATE CRP SET CRP_SERVICE_COST = 120, CRP_SERVICE_STATUS='Y', CRP_UPD_DATE='05-03-2012' WHERE CRP_ID =6 and SERVICE_ID=5; UPDATE CRP SET CRP_SERVICE_COST = 320, CRP_SERVICE_STATUS='Y', CRP_UPD_DATE='05-03-2012' WHERE CRP_ID =7 and SERVICE_ID=3; UPDATE CRP SET CRP_SERVICE_COST = 120, CRP_SERVICE_STATUS='Y', CRP_UPD_DATE='05-03-2012' WHERE CRP_ID =7 and SERVICE_ID=5; UPDATE CRP SET CRP_SERVICE_COST = 200, CRP_SERVICE_STATUS='Y', CRP_UPD_DATE='05-03-2012' WHERE CRP_ID =8 and SERVICE_ID=3; UPDATE CRP SET CRP_SERVICE_COST = 120, CRP_SERVICE_STATUS='Y', CRP_UPD_DATE='05-03-2012' WHERE CRP_ID =3 and SERVICE_ID=2; UPDATE CRP SET CRP_SERVICE_COST = 240, CRP_SERVICE_STATUS='Y', CRP_UPD_DATE='05-03-2012' WHERE CRP_ID =3 and SERVICE_ID=2; Reference Natan, Ron Ben., 2005. Implementing Database Security and Auditing. Oxford: Elsevier Digital Press. Read More