CompSci FINAL.docx

28 Pages
Unlock Document

Western University
Computer Science
Computer Science 1032A/B
Diane Goldstein

Database Design and Entity-Relationship Models Database Design  Databases store data  Design reflects the organization that exists within the data  Model: representation of reality that retains only selected details o Powerful tool for validating necessary details and eliminating irrelevant ones o Associate or map elements in reality to elements in the model  Data model: captures organization of the data in a logical representation of the data  Database development steps: o Developers analyze forms, reports, queries to judge requirements for system o Requirements summarized in a data model, containing descriptions of data and relationships o Database design implemented o Database created and filled with user data  To build a database, must first start with an entity-relationship model (ER diagram): o Visualization/diagram of the data o Describes the logical database o Graphical method of mapping the real world o Once ER diagram is built it is used as a blueprint for the real database Entity Relationship Model  Most popular for data modeling, introduced by Peter Chen in 1976 o Now has many variants  Consists of o Entities (with attributes) o Relationships (with attributes, cardinality, participation)  Entity: represents a discrete object (person, place, thing, event) in rectangle o I.e. Western: Student, Course, Program, Professors, etc.  Attributes: describe properties of the entity in oval o I.e. Student attributes: student number, name, address, phone, email, etc.  Instances: set of data items that exist under the entities o I.e. instance of Student entity: 250615954, Tamara Grunberger, 695, etc.  Key attributes: necessary to uniquely identify the instance of each entity underlined o I.e. SIN, student number, employee number, course number  Relationship: association between two or more entities that captures how they are related in a diamond o I.e. person WORKS FOR company, student TAKES course o Can have attributes (i.e. hire date describes relationship, not person or company)  Cardinality: indicates the number of instances involved in the relationship o 1:1 relationship: single entity to single entity  I.e. 1 faculty member is chair of 1 department o 1:M (M:1) relationship: one to many entities  I.e. MANY students registered to 1 faculty o N:M relationship: many to many entities o I.e. MANY students take MANY courses  Participation: indicates whether all or only some of the instances of an entity are involved in the relationship o Total participation: all instances involved (marked with double line) o Partial participation: only some instances involved (marked with single line)  Recursive relationship: relationship among the same entity o I.e. Supervision relationship (supervisor and supervisee) solely among employees ER Diagrams—Crow’s Foot  Rectangles represent entities, relationships showing with lines  Cardinality: crow’s foot o Forks at the end of lines indicate many instances involved in relationship  Participation: vertical line marks at least 1 entity of that type involved; oval marks that entities are optional Normalization  Process: converts table into two or more tables, changing from poorly to well structured  Data integrity problems: different names for same entity produces incorrect/inconsistent data  Normalized tables… o Eliminate duplicate data o Are slower to process o Have a single theme for each table Database Elements  Entity: anything about which an organization wishes to store data (i.e. Student info)  Fields: information about attributes stored in fields (i.e. Student names stored under Name field)  Record: consists of all the fields containing data about one entity instance  File: a set of all related records  Tables: consists of rows and columns, should always have a key attribute (primary key) Relational Database  Data model based on a relation represented in a 2D table  Entities and relationships in ER diagram become tables in relational model  Rows consist of instances of entity or relationship  Columns consist of attributes of entity or relationship  The table that stores a relationship inherits the key attributes from all entities involved in the relationship o I.e. TAKES relationship—Student TAKES Course  Key from Student table (student number) and key from Course table (course number) become information to be stored in Takes table Converting ER Diagrams to Relational Databases  Each entity becomes its own table with key attribute as primary key and other attributes as subsequent columns  For each 1:1 relationship: choose one entity involved and make its key the foreign key in the other entity’s table, and the relationship’s attributes get moved as well  For each 1:M relationship: take the key from the 1 side and make it the foreign key in the MANY table  For each N:M relationship: create a new table that combines the relationship attributes, and the key attributes of both entities Chapter 5: Database and Content Management Content Organization and Management  Content is related to intellectual property o A form of creative endeavor that can be protected through a trademark, patent, copyright, industrial design, or integrated circuit topography  Content management challenge: processing and storing the right content, getting the right content to the right person in the right format and the right time  Management of content data: Database Management System (DBMS)—effectively and efficiently stores and processes data  Presentation of content: Content Management System (CMS)—organize documents and seek out documents to organize access  Spreadsheet VS database: both keep track of things o Spreadsheet keeps lists of a single concept/theme o Database keeps lists involving multiple themes Components of a Database  Database= table/files + relationships among rows + metadata  Relationships among records: values in one table relate to rows in other tables o Primary keys: columns that identify unique row in table, each table has a key o Foreign keys: primary keys from other tables  Metadata: databases are self-describing—metadata is data describing the data  Components of a database application system: o User o Database application: forms, reports, queries, application programs o Database Management System (DBMS): i.e. Microsoft Access o Database: tables, relationships, metadata Database Applications  Collection of forms, reports, queries, application programs  Applications can have multiple users, databases can have more than one application  Forms: read, insert, modify, delete data  Reports: shows data in structured context, able to compute values  Queries: comprehensive and robust method to ask/answer questions about data o Structured Query Language (SQL): international standard for processing databases  Application programs: process logic specific to a business’ needs o Enables database processing over the Internet o Intermediary between web server and database o Responds to events (reads, inserts, modifies, deletes data) Multi-User Processing  Lost update problem: locking used to coordinate activities of multiple users o Each change/modification/deletion of content overrides the past changes done and the initial value is lost with each new activity Database Management Systems (DBMS)  Functions: o Create tables and relationships in database o Process database o Administer security levels to access database  Enterprise DBMS: process organizational and work group databases o Large databases that support many users o I.e. IBM’s DB2, SQL Server, Oracle  Personal DBMS: designed for smaller, simpler database applications o Supports fewer than 100 users o I.e. Access, dBase, FoxPro, Paradox, R: Base Extension 5B: Access and SQL Access Tables  Primary key: makes each record unique o Default is the first AutoNumber data type added  Properties: define characteristics of object  Datasheet view: used to add, modify, delete, view records  Design view: used to create and modify fields in a table  Store data in smallest parts—i.e. instead of 1 field for full name make 2 for first/last  Date arithmetic: data type date/time follows specific structure  Normalization: use of multiple tables to reduce redundancy  Field names have no spaces Forms  Form view: displays completed form, used to enter/modify data  Layout view: visual representation used to create/modify form  Design view: used to create/modify form with more options for controlling it Reports  Printed documents displaying information from database  Layout data in useful and attractive format  Can be based on one or more tables/queries, not all fields need to be included, allows data to be summarized  Default graphic is Binder, but can be changed  Report headers/footers: printed only once at beginning and end of report  Page headers/footers: printed at the top/bottom of every page  Group headers/footers: start/end of each grouping  Unbound controls: no data source, displays titles, labels, lines, rectangles, graphics  Bound controls: data source is a field in the table or query  Calculated controls: data source is an expression usually consisting of values in fields, tables, queries (i.e. avg, sum)  Report wizard: uses predefined report styles to create a report based on answers users provide o Choose fields, grouping level and order, sort and summarize, chose layout, style, name  Establishing relationships: between primary and foreign keys from different tables/queries o Referential integrity: ensures data is relational database maintains consistency when data is changed (marked by infinity symbol)  Cascade update related fields OR cascade delete related fields Queries  Processed using SQL, provide a subset of a table based on specific criteria o Subset becomes an Access object  Dataset: allow us to ask questions about data, the subset that answers the question o Questions formed using criteria, which restricts dataset to match parameters  Select query: searches associated tables and returns a dataset that matches parameters  Specifying criteria: use operands (i.e. ,=), wild cards (*, ?), null values, AND (must match all specified criteria), OR (must match only some of specified criteria)  Run command (!): click this to run the query SQL  Structured Query Language statements retrieve and update data in a database  ANSI: American National Standards Institute created standard computer language for accessing and manipulating database systems  SQL works with database programs (Access, DB2, SQL Server, Oracle)  Statement format: SELECT FROM WHERE o I.e. SELECT, s-number,, c-number FROM (Student INNER JOIN Takes ON Student. s-number=Takes. s-number) INNER JOIN Course ON Takes. c-number=Course. c-number WHERE s-number=”250615954”  ORDER BY keyword: sorts results in ascending (“Asc”) or descending (“Desc”) order  Nested queries: retrievals involving more than one query, make SQL statement for on then use that for the final SQL statement  INSERT INTO statement: adds new instance of an entity or relationship to database o INSERT INTO (, , etc.) VALUES (, , etc.)  DELETE FROM statement: deletes instances o DELETE FROM WHERE  UPDATE statement: modifies attribute values o UPDATE SET = WHERE Chapter 7: Information Systems for Competitive Advantage Information Systems affect Competitive Advantage  To determine competitive strategies, businesses either… o Change the product: new or enhanced o Change business processes: use technology to secure customers, reduce costs, create barriers for competitors  ISs make primary and support activities more productive than competitors o Holds true for commercial, non-profit, and government organizations  Fundamental types of IS within organizations: o Calculation systems o Functional systems o Integrated/cross-functional systems Calculation Systems  Antiquated systems  Relieved workers of repetitive calculations  Labour-saving devices  Produced little information  I.e. computing payroll and writing cheques, inventory tracking Functional Systems  Facilitated work of single department or function  Functions added to calculation system programs providing more value o I.e. payroll expanded to become human resources  Functional silos: problem with system, designed to work independently of one another fostering isolation, BUT functional systems are interrelated which leads to data duplication, disjointed business processes across functions, limited information at any one source, inefficient decisions, increased cost to business  Decisions that are appropriate for only single business function may be inefficient for an entire business process  Reorganize Porter’s Value Chain such that basic types of functional systems include… o Marketing and sales o Operations o Manufacturing o Human resources o Accounting Marketing and Sales Information Systems  Marketing IS: product and brand management that assesses… o Effectiveness of marketing messages o Advertising o Promotions  Sales IS: involves… o Sales forecasting: planning production, managing inventory, financial reporting o Customer management: generate follow-up business, turn prospects into customers, manage customers  Summary of functions of sales and marketing information systems: o Prospect generation o Lead tracking o Customer management o Sales forecasting o Product/brand management Operations Information Systems  Manage finished-goods inventory  Used primarily by non-manufacturers  Processes include… o Order entry o Order management o Finished-goods inventory management o Customer service Manufacturing Information Systems  Process data about inventory—manufacturing, scheduling, operations  Support production and planning  Manufacturing philosophies: o Push production planning: create schedule and push goods through manufacturing and sales o Pull production planning: respond to customer demand such that a reduction in inventory triggers production o “One-off” producers fall in neither category  Inventory, manufacturing planning, manufacturing scheduling, and manufacturing operations facilitate production of goods Accounting Information Systems  General ledger, financial reporting, accounts receivable/payable, cost accounting, cash management, treasury management, budgeting applications  Importance of legislation: creation of internal controls to prevent corporate fraud o Sarbanes-Oxley (SOX) Act (US) o Bill 198—Budget Measures Act (Canada) Business Process Design/Redesign  Does not simply automate or improve existing functional systems  Paving the cow path: process of making what already exists more efficient o Making things easier without necessarily changing them  Considers creation of new, more efficient business processes o Integrates activities of all departments involved in value chain o Cross-departmental business processes: takes advantage of an many activity linkages as possible  Challenges: o Process design projects are expensive, difficult, and time consuming o Employees resist change o Ultimate outcome is uncertain Industry Standard Processes  Early business process design projects were tailor made  1990s software vendors designed integrated applications with built-in industry standard processes, which work to… o Integrate activities across departments o Save costs of tailor-made process design o I.e. Oracle, SAP  Advantages: inherent business processes and use of tried and tested processes  Disadvantages: may differ from pre-existing processes in the organization and may require it to change substantially Integrated-Cross Functional Systems  Operate across departmental boundaries—increased functionality and efficiency  Transition from function systems is difficult, designed to overcome problems in functional systems  Integrated processing requires a clear line of authority and the coordination of departmental activities  Inter-organizational systems: used by 2 or more companies  2 cross functional systems: o Customer Relationship Management (CRM) o Enterprise Resource Planning (ERP)  Most organizations have a mixture of functional and cross-functional systems Customer Relationship Management (CRM) Systems  Organization is customer-centered  Support processes: attracting, selling, managing, delivering, supporting customers  Includes only direct value chain activities involving customer  Single repository for customer data o Eliminates inconsistent data o All departments have access to all customer data  4 customer life cycles: o Marketing sends message to target market o Prospects order and need to be supported o Support and resale increases value to existing customers o Win-back processes categorize customers according to value Enterprise Resource Planning (ERP) Systems  Enterprise wide, cross-departmental  Support processes: primary business processes, human resources, account support  Integrates sales, orders, inventory, manufacturing, customer service activities  Based on documented, tested business models: o Provide software, pre-designed databases, procedures, and job descriptions for organization-wide process integration  Provides cross-functional process view of organization through a formal approach based on formal business models  Maintains data in centralized database  Advantages: o Organizations don’t need to reinvent processes, they are tried and tested o Inventory reduction due to better planning o Lead time reduction o No data inconsistency problems as it creates an integrated database o Lower costs and higher profitability  Disadvantages: o Costly o Change is challenging  Implementation: determine current models and ERP models >> remove inconsistencies >> implement the ERP system Enterprise Application Integration (EAI) Systems  Solves problems of isolated systems  Provides layers of software that connect applications together  Enables existing applications to communicate  Provides integrated information  Leverages existing system  Enables gradual move to ERP  No centralized database—files of metadata describing where the data is Inter-organizational Systems  Systems that cross organizations o Involve selling and purchasing o Integrate multiple company operations  Types of inter-organizational systems: o E-commerce o Supply Chain Management (SCM) E-Commerce and Web 2.0  Systems working across organizations  Provides competitive advantage to both parties—but disparity makes hard to implement  Web 2.0: concept introduced by Tim Reilly describing applications and platforms on web o Focuses on providing services, not simply software applications, that can be accessed by a large number of people o Recognizes the importance of the user as part of the system, providing data and information that makes the service better o Creation of unique and difficult-to-develop data improves when more people use the system (i.e. Facebook) o Natural evolution of e-commerce  E-commerce: buying and selling good/services over private or public computer networks o Merchant type: sell their own goods and services  Business-to-business (B2B)—i.e. Grand and Toy  Business-to-customer (B2C)—i.e. Amazon  Business-to-government (B2G) o Non-merchant type: sell service of others, arrange the sale of goods  E-commerce auctions: online auctions—i.e. eBay  Clearing houses/electronic exchanges: matches buyers with sellers (i.e. Priceline)  Benefits of e-commerce: o Greater market efficiency disintermediation: elimination of layer of supply chain o Improves flow of price information web based price comparisons o For the seller: knowledge of price elasticity  Price elasticity: measures amount demand rises or falls with price changes  Losing-bidder auction prices  Price experimentation  More information obtained directly from customer Supply Chain Management (SCM)  Supply chain: network of organizations and facilities o Transforms raw materials into products o Products delivered to customers  Integrates primary inbound logistics business activity  Involves… o Customers, retailers, distributors, manufacturers, suppliers o Transportation, companies, warehouses, inventories o Method for transmitting messages and information among organizations  Supply chain performance: facilities, inventory, transportation, information  Three factors of information: o Purpose: transactional (orders/returns), or informational (customer order data) o Availability: access and sharing o Means: methods of transmission (i.e. XML)  Information systems involved in supply chain management: o Supplier Relationship Management (SRM) o Inventory o Customer Relationship Management (CRM)  Supplier Relationship Management (SRM): business process for managing contacts between an organization and suppliers o Supplier: any organization that sells something to an organization with an SRM application  I.e. manufacturer supplies to distributor o Supports inbound logistics primary activity, and the procurement support activity o SRM processes: source >> purchase >> settle  Benefits of IS on supply chain performance: o Reduced cost of buying and selling o Expanded supply chain speed o Reduced size and cost of inventory—enables just-in-time inventory o Fix bullwhip effect o Supply chain profitability not optimized  Bullwhip effect: variety in the size and timing of orders increases at each stage up supply chain o Natural dynamic of multistage supply chain  Unrelated to erratic customer demand  Large fluctuations force distributors, manufacturers, suppliers to carry large inventory  Reduce overall profitability o Eliminate effect by giving participants access to consumer-demand information  Inter-organizational information systems share data Chapter 8: Decision Making and Business Intelligence Challenges of Making Decisions  Factors making business decisions challenging: o Uncertainty and complexity o Information overload o Data quality Information Overload  Exabyte= 10^18 bytes  Storage capacity increases while cost decreases o Basically unlimited today  Exponential growth of information o Inside and outside of organizations o Can be used to improve decision making  Ability to store any amount of data pertaining to customers o Allows for better understanding of customers o Data can be used for forecasting  Provides competitive strength when making decisions  Business manager’s challenge: find appropriate data, incorporate data into decision making o Information systems can both help or hinder this process  Data quality: processed data from operational systems can be used for basic reports (i.e. current sales, sales projection) OLTP Support for Decision Making  Online Transaction Processing (OLTP) System: collects data electronically, processes transactions online  Backbone of all the functional, cross-functional, and inter-organizational systems in an organization  OLTP systems support decision making by… o Providing raw information about transactions o Providing information about the status of an organization Basic Methods for Processing Transactions  Real-time processing: transactions are entered and processed immediately upon entry o I.e. airline reservation, personal online banking systems  Batch processing: system waits until it has a batch of transactions before the data is processed and the information is updated o I.e. transfer of all daily branch transactions to central office  Data resource challenge: while data may be collected in OLTP system, the data may not be used to improve decision making  Asset: resource from which future economic benefits may be obtained o Treat data as an important asset Online Analytic Processing—OLAP  Focus on making OLTP-collected data useful for decision making  Provides the ability to sum, count, average, and perform other simple arithmetic operations on groups of data  Final report has measured facts and dimensions Business Intelligence (BI) Systems  Provide information for improving decision making  Primary systems (bolded shit): o Reporting systems o Data-mining systems o Knowledge management systems o Expert systems  Reporting systems: process supplier information to rank quality o Integrate data from multiple sources o Process data by sorting, grouping, summing, averaging, comparing o Results form the report o Improve decision making by providing right information to the right user at the right time  Data mining systems: search patterns to predict delivery delays or quality problems o Process data using statistical techniques:  Regression analysis  Decision tree analysis o Market based analysis: look for patterns and relationships to anticipate events or predict outcomes  Predict donations o Represents convergence of disciplines o Takes advantage of developments in data management to process enormous databases o Unsupervised data mining: analysis run before model created  Technique applied, then results observed  Hypotheses created after analysis to explain results  I.e. cluster analysis: technique to identify groups of entities that have similar characteristics o Supervised data mining: analysis run after model created  Statistical techniques used to estimate parameters  I.e. regression analysis: measures impact of a set of variables on another variable  I.e. neural networks: predict values; make classifications (i.e. good or bad prospect customer?)  I.e. market-based analysis: computes correlations based on past performances  Market-based analysis: technique for determining sales patterns, creates probabilities that two items will be purchased together o Confidence: probability of purchasing 2 items together o Lift: ratio of confidence to the base probability of buying an item o May need to consider multiple-item purchases  Knowledge management systems: process that creates value from intellectual capital by collecting and sharing human knowledge o Rank suppliers and share experiences o Supported by information system technology and the 5 components of information systems with an emphasis on people o Fosters innovation o Improves customer service o Increases organizational responsiveness o Reduces costs  Expert systems: rule-based systems that encode human knowledge gathered from human experts o Contain rules for supplier selection o Improve diagnosis and decision making in non-experts o Expert system shells: program that processes the “if” side of the rules until no value gets returned, and reports the values of all variables o Difficult and expensive to develop  Labour intensive, ties up domain experts o Difficult to maintain, don’t really live up to expectations o I.e. expert systems for pharmacies  MYCIN developed in 70s to diagnose certain infectious diseases  DoseChecker verifies appropriate doses  PharmaADE ensures patients not prescribed drugs with harmful effects RFM Analysis  Way of analyzing and ranking customers according to their purchase patterns  Simple technique that considers… o How recently (R) a customer has ordered o How frequently (F) a customer orders o How much money (M) a customer spends per order Data Warehouse  Extracts and cleans data from operational system  Prepares data for BI processing  Data warehouse DBMS: o Stores data o May also include data from external sources o Contains metadata concerning data stored in the warehouse meta-database o Extracts and provides data to BI tools Data Mart  Maintain information on inbound logistics and manufacturing  Data collection to address particular needs (business function, problem, opportunity)  Smaller than data warehouse  Users may not have data management expertise, so must have knowledgeable analysts for special functions  Complete implementation is costly, and it requires data management expertise and knowledge
More Less

Related notes for Computer Science 1032A/B

Log In


Don't have an account?

Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.