Information System in Business - Coursework Example

 ‘Information Systems in Business’ PART 1 Question 1 (i) DATABASE The databases in which data about me may exist may be: Banks. Grocery Stores. Billing Companies. The data in these databases was initially captured by making me fill out the membership forms that required entry of personal information into them.Later on every transaction carried out that involved entry of information into these databases also resulted in data being entered and updated into them. For example, entering or taking out money from bank account, purchasing grocery, paying out the monthly bills. The privacy concerns associated with these databases is the leaking out of personal information due to any security breach or hacking of database that may occur. Moreover the misuse of personal information by the concerned authorities is also a privacy concern. (ii) The figure below demonstrates few of the possible data sources of a data warehouse. Figure 1: using a warehouse to consolidate heterogeneous data. (Sheldon, 2008) Assuming that I am part of a project team building a data warehouse for an inventory store. With reference to Figure 1 it may well be seen that the sources of a data warehouse may be: SQL Server Relational Database Excel Files or CSV Files Active Directory Services Oracle Relational Database etc. The assumption that would be made considering the above possible data sources s thatdata has initially been entered into the respective databases individually. These databases then act as sources to the data warehouse center. The means by which the implementation of a data warehouse would assist in decision support is by decidingto which department some specific type of data would belong without the need for individuals to specifically sort the data out into sections.Data Mining would help in providing analytical reports with respect to the type of data addressed. Question 2 Enterprise Content management encompasses a number of capabilities in it.These include the efficient management of unstructured information, such as in Microsoft Word and also the management of structured information such as application data as done in modelingsoftware like Microsoft Excel. Another example is to integrate varied amounts of data and stack them into modules something done extremely efficiently by Microsoft Access. SAP is the first product that has been chosen for review. SAP is an Enterprise Content Management Solution provided to encapsulate all the content management requirements within it.The main components of SAP provided by the company for the management of content are: “SAP DMS (Document Management System) SAP KM (Knowledge Management) SAP ArchiveLink SAP RMS (Records Management System) Collaboration (Cprojects/Cfolders) CAD Desktop SAP Enterprise Search ASCM (Area Specific Content Management) products” (Hill, 2007) The ECM components of SAP are supported by one core common content layer. Typically there are a number of content stores. Their integration is the basic motive of the common content storage layer. Open TEXT ECM solutions are one of the most prominent members of the vendor community that gives ECM solutions to its customers. It claims that it enhances the comprehensiveness of the People, the Processes and the Content. The comprehensive ECM suite provided by OpenText enables the controlling of the Risk and cost that is with respect to the content. It fosters decision making empowering the personnel within the organization and thus the organization as a whole. User and team productivity are improved by the integration of Open TEXT ECM. Benefits of OPEN TEXT ECM are: Minimization of Risk, Improved Productivity and Efficiency Lowering in the Total Cost of Ownership Consolidated Investments Establishment of Best Practices. (OpenTEXT, 2012) Question 3 To formalize the data storage and management process a relational database model is recommended. The relational database model is the basis of Relational Database Management System (RDBMS). At the core of the relational model lies the process of normalization. There are more than 5 levels of normalization but usually a 3 levels normalized database is considered as a relational database. The tabular structures once normalized and properly related provide a strong basis for setting data archives. The description of how normalization is achieved step by step (i.e. through a gradual process) is appended in the text below. The data can easily be extracted through the manually prepared documents and can be chipped into a single table as flat file. The columns of that table can be considered as initial attributes of main entity. The process of normalization starts with a flat file. The FIRST NORMAL FORM requires identification and removal of repeating groups of attributes. This ends up in the creation of a new entity which enables a user to establish a primary – foreign key based relationship. The SECOND NORMAL FORM asks for identification and removal of part-key dependencies. As a result of this new entity is created with its significant primary key. This entity can easily be related to other entities in term of primary – foreign key relationship. THIRD NORMAL FORM finally asks for continual of the same process of splitting entries (wherever required) on the basis of data redundancy and storage optimization. To sum up it would be right to say that the process of normalization comprise of three parts that are identification of attribute with redundancy, splitting main table into entities and establishing relationships. Normalisation case study- CUSTOMER ORDER FORM: Name: Mary Poole Order Date: 5/3/2008 Address: 1 Kedleston Rd Tel No: 01332 622 222 Derby Post Code: DE22 1GB Order no: 4 Customer No: 1 Line No. Product No. Product Description Quantity Price 1 2 Shirt 1 £12.00 Considering the given customer order form the data if arranged as a flat file would look like this. Order No. Order Date Customer Number Customer Name Address Customer Phone Product No. Product Description Quantity Price 4 2-3-2008 1 Mary Poole 1 Kedleston Rd, Derby, DE22 1GB 01332622222 2 Shirt 1 £12.00 This shows that if the order lines increase the order number, customer name and required details would remain the same. To proceed with the normalization process it is needed to perform the required steps accordingly. Following is the procedure. 1st Normal Form: In this step the redundant groups of attribute are to be removed to create new entity with respective relationship. In the above mentioned flat file the Order No, Order Date, Customer Number, Name, Address and Phone are the attributes that hold redundancy for each order line. Following figure and respective table highlight the process. Figure 1. 1st NF Order Order Number Order Date Customer Number Customer Name Customer Address Customer Phone 4 2-3-2008 1 Mary Poole 1 Kedleston Rd, Derby, DE22 1GB 01332622222 Order Details Order Number Product No. Product Description Quantity Price 4 2 Shirt 1 £12.00 4 3 Pants 2 £32.00 2nd Normal Form: The key dependencies are to be found and new entities with primary foreign keys are to be created. Figures and Table below highlight the process. Product No acts as a primary key in Product entity, and as foreign key in Order Details entity. Figure 2. 2nd NF Order Details Order Number Product No. Quantity 4 2 1 4 3 2 Product Product No. Product Description Price 1 Tie £5.00 2 Shirt £12.00 3 Pants £32.00 3rd Normal Form: Remove rest of the dependencies. Figures and Table below highlight the process. Customer Number acts as a primary key in Customer entity, and as foreign key in Order entity. Figure 3. 3rd NF Customer Customer Number Customer Name Customer Address Customer Phone 1 Mary Poole 1 Kedleston Rd, Derby, DE22 1GB 01332622222 Order Order Number Order Date Customer Number 4 2-3-2008 1 Order Details Order Number Product No. Quantity 4 2 1 4 3 2 Product Product No. Product Description Price 1 Tie £5.00 2 Shirt £12.00 3 Pants £32.00 Question 4 To answer this question the famous social networking site namely facebook is selected as a case study. There may be several entities that may be part of the main facebook database. However to exhibit the understanding few of them are identified here. These are, PersonalProfile, Groups, Membership, WorkplaceProfiles, Friends and Location. The structure of few in terms of attributes is as follows. Groups(GroupID,CreatorID, Dateofcreation, Timeofcreation, Description, Type) PersonalProfile(PersonalID, Name, Gender, Dateofbirth, Profession, Activities, usertype) Membership(PersonalID, GroupID, Datejoined, Timejoined) Location(PersonalID, Street, City, Country, Zipcode) Following is the probable entity relationship diagram. The anticipated entity relationship diagram for facebook database Part II (i & ii): Un-normalised data: SalesDetail(SalespersonID, Salesperson Name, Sales Area, Customer #, Customer Name, Customer Address, Warehouse Number, Warehouse Location, Sales Amount) 1NF In order to remove repeating group of attributes and to create new entity with proper relation the given data is analysed and following split is identified. The first normal form relates sales person as basic entity to the sales done by him/her. Primary key is underlined in the basic entity. Salesperson (Sales person ID, Salesperson Name, Sales Area) SalesDetail (Sales person ID#, Customer #, Customer Name, Customer Address, Warehouse, Warehouse Number, Warehouse Location, Sales Amount) 2NF The dependencies are further identified after achieving first normal form, in order to establish and relate new entities. The newly identified entity is Customer with Customer# as its primary key. This entity holds all the attributes related to a customer. Customer (Customer#, Customer Name, Customer Address) Sales person (Sales person ID, Salesperson Name,Sales Area) SalesDetail (Salesperson ID, Customer#, Warehouse, Warehouse Number, Warehouse Location, Sales Amount) 3NF Finally in the third normal form further dependencies are analysed and new entity namely Warehouse with primary key as Warehouse# is established. Accordingly as the warehouse detail that is location is included in the Warehouse entity the SalesDetail contain a new foreign key for warehouse reference. The SalesDetail would finally be narrated as the sales done by sales person to the respective customer from a corresponding warehouse with a particular sales amount. Warehouse (Warehouse#, Warehouse Location) Customer (Customer#, Customer Name, Customer Address) Sales person (Salesperson ID, Salesperson Name,Sales Area) SalesDetail (Salesperson ID, Customer#, Warehouse#, Sales Amount) Part II (iii) The role of Entity Relationship Diagram and the process of normalization in a database design. The entity relationship diagram represents relational database model. It is originated during and after the process of normalization. The representation of a relational database is necessary to be represented through ER Diagram due to the following reasons. Better Visual Representation. Standardization. Analysis of possible new entities and errors. Representation of relationships in a proper manner. Highlights cardinality as well. The process of normalization lies at core of a relational database model. The raw organization of data can only be converted to a Relational Model via process of normalization. Normalization focuses of better data handling and storage space optimization. The 3 stage process consists of first, second and third normal form. These steps are narrated as identification of attributes and redundancies, formation of new entities on the basis of key based dependencies and establishment of relationships among different entities. REFERENCES: Sheldon, R 2008, Using a Data warehouse to consolidate Heterogeneous Data, digital image, SQL Server Data Warehouse Cribsheet, accessed April 14, 2012, Hill, A. 2007. What is SAP ECM? SAP Community Network. Online. Available at OPEN TEXT. 2012. Enterprise Content Management. Online. Available at Read More