Introduction to Management Information Systems Chapter 4 Database ProcessingLearning ObjectivesPurpose of a DatabaseOne Theme Spread SheetMulti-theme DatabaseDatabase HierarchyComponents of DatabaseStudent TablePoorly Designed TableRelationshipsExamples of RelationshipsReview ComponentsMetadataExercise: Use Access to setup a data tableDB Application Systems ComponentsDB Application System ComponentsEnterprise DBMSPersonal DBMSLanguage used in DBMSExercise:Slide 21Multiple Database ApplicationsForm – A structured way to edit and view dataExample Student ReportQueryExercise:Slide 27Database Development ProcessE-R ModelingComponents in E-R ModelEntities (cont)Student Data Model EntitiesSlide 33Three basic types of relationshipsExerciseSlide 36Opposing Forces Guide:Database SecurityDatabase Security (cont)Slide 40Ethic Guide: Nobody Said I Shouldn’tClass 2: E-R Modeling and Database Lab5 minutes free writeMore exerciseTranslating E-R diagram into Tables (1:N)Slide 46Step 3: edit relationshipMany-to-Many RelationshipForeign Key doesn’t work for N:M relationshipSolutionSlide 511:1 RelationshipSlide 53Importance of User ReviewHow should you review as a user? (Cont)Key Terms and Concepts (Cont.)Introduction to Management Information SystemsChapter 4 Database ProcessingHTM 304 Fall 072Learning ObjectivesKnow purpose of database processingList components of a database systemUnderstand important database termsKnow elements of the entity-relationship modelUnderstand general nature of database design3Purpose of a DatabaseKeep track of things with more than one theme4One Theme Spread Sheet5Multi-theme DatabaseStudent ScoreStudent ScoreEmail recordsEmail recordsofficevisitsofficevisitsAll three records for one student(student is the link of different themes)All three records for one student(student is the link of different themes)6Database Hierarchy Bytes: the basic elements which compose of databaseFields (Columns). Name, number or characters that describe an aspect of a business object or activity. e.g. Student Numbers, Student Names, Midterm Scores, etc.Record (Rows). Collection of related data fields. e.g. 000054323 Jenny White 89.File (or Table). Collection of related records.e.g. Student Score Table, Student Office Visit Table, Student Email Record TableDatabase. Collection of integrated and related files + Relations between (among) tables + structure of data. The relations and structures are called metadata7Components of DatabaseDatabaseTables or Files+Relationships among Rows in Table+Metadata=How to effectively organize and process raw data is the key to a successful database design and an Information System design.8Student TableRecords/RowsColumns/Fields9Poorly Designed TableIn Database Design, the record will be split into 2 records:Jones Feb 1, 2002 [email protected] 100 FinanceJones Feb 1, 2002 [email protected] 100 AccountingIn Database Design, the record will be split into 2 records:Jones Feb 1, 2002 [email protected] 100 FinanceJones Feb 1, 2002 [email protected] 100 AccountingCommon Practice in ExcelCommon Practice in ExcelPros and cons?10RelationshipsThe records in different tables are connected by certain relationships – relational databasesKey – column or group of columns that identifies a unique row in a tableFor example: Student Number is key of Student tableThe key insures no repeated recordsEvery table MUST have a keyForeign key – non-key column or field in one table that links to a primary key in another tableStudent Number in Email and Office_Visit tablesForeign keys link records from different tables together as one compound record11Examples of RelationshipsKeyForeign KeyForeign Key12Review ComponentsDatabaseTables or Files+Relationships among Rows+Metadata=13MetadataMetadata are data that describe data (Format, Field Properties, etc.)Makes databases much more usefulNo one needs to guess, remember, or record what is in databaseMake databases easy to use for authorized and unauthorized purposes14Exercise: Use Access to setup a data tableCreate a database file (*.mdb)Create table in design view All tables are in one database fileFirst create the table in design viewSpecify the fields (column headers)Specify the types of the fields, and other propertiesSpecify which field is the key!Finish design view -- Close the design viewFill in records in the table Double click the table to enter editing viewEnter recordsTry enter repeated keys, it won’t let you! Why?15DB Application Systems ComponentsPure database data correct, but raw form not pertinent or usefulDB applications make DB data accessible and usefulUsers employ DB applicationsFormsFormatted reportsQueries (SQL language)Application programsEach calls on DBMS to process DB tables16DB Application System ComponentsDBMS (Database Management System)A program used to create, process, and administer a database. Database developers use the DBMS to create tables, relationships, and otherstructures in the database.DBMS (Database Management System)A program used to create, process, and administer a database. Database developers use the DBMS to create tables, relationships, and otherstructures in the database.17Enterprise DBMSProcess large organizational and workgroup databasesSupport many, perhaps thousands, of users and many different database applicationsSupport 24/7 operations and can manage dozens of different magnetic disks with hundreds of gigabytes or more dataIBM’s DB2, Microsoft’s SQL Server, and Oracle are examples of enterprise DBMS products18Personal DBMSDesigned for smaller, simpler database applications.Used for personal or small workgroup applications that involve <100 users, normally fewer than 15Bulk of personal databases have only a single user Microsoft Access only available personal DBMS19Language used in DBMSSQL: Structured Query Language An international standard language for processing a database: Data Manipulation/Definition and Data QueriesData ManipulationData Queries: See later.INSERT INTO StudentTable ([Student Number], [Student Name], Hw1, Hw2, MidTerm) VALUES (1000, ‘Joe Black’, 90, 85, 88)DELETE FROM StudentTable WHERE MidTerm = NULL UPDATE StudentTable SET MidTerm = ’90’ WHERE Student Number = ‘1000’20Exercise: Below shows the original table. Write down the resulting table after executing the following SQL command: UPDATE StudentTable SET LetterScore = ’A’ WHERE Score >= ‘90’.Original Table: StudentTableStudentNumber StudentName Score
View Full Document
Unlocking...