1 / 49

INTRO TO ENTERPRISE DATABASES - I

INTRO TO ENTERPRISE DATABASES - I. Dr. Chandra Amaravadi Western Illinois University. IN THIS PRESENTATION. Basic concepts: data and info Need for databases Basic concepts: entity, attr , pkey , file, record Historical approach to data Definitions Database organization

lflores
Download Presentation

INTRO TO ENTERPRISE DATABASES - I

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. INTRO TO ENTERPRISE DATABASES - I Dr. Chandra Amaravadi Western Illinois University

  2. IN THIS PRESENTATION.. • Basic concepts: data and info • Need for databases • Basic concepts: entity, attr, pkey, file, record • Historical approach to data • Definitions • Database organization • DBMS and DBMS activities and functions • Importance of databases/Organizational importance • Course features

  3. WHAT IS INFORMATION?

  4. DEFINING DATA AND INFORMATION Data: Data consists of raw facts Examples: 1. 2982034 2. USA 30 32 31 93 3. Red car 4. Tall man

  5. THE NATURE OF INFORMATION Raw facts are not useful by themselves. Blue car is not very descriptive!

  6. THE NATURE OF INFORMATION.. Vin#: 8399933V9833 Manufacturer: Lamborgini Model: diablo Year: 2014 Price: $219,500 Collection of facts are useful!  information!

  7. DEFINING INFORMATION • raw facts not useful by themselves • collection of facts are useful • describe an object or an event • these are called attributes • Attributes are properties that describe Information is a collection of facts.

  8. DEFINING INFORMATION.. Information can be defined as: • Collection of facts • Organization of facts • Result of processing data

  9. BASIC ORGANIZATION CONCEPTS (Entity, attribute, primary key, file, record)

  10. INTRODUCING ENTITIES, ECLASSES AND ATTRIBUTES Information describes ______ or ________ What does it describe here? How? Name: Nash Tanner DOB: 2-7-1980 Profession: Engineer Address: 415 Walnut street.

  11. COLLECTION OF DATA... Following is another example of information as a collection of facts describing a transaction. Acct#: 4555950 Date: 12/5/16 Time: 4:48 pm Merchant: WIU Union Card type: Master Card Issuer: First Bank These are called _________.

  12. ONE ATTRIBUTE HAS A UNIQUE VALUE One of these attributes always has a unique value which one is it? Acct#: 4555950 Date: 12/5/16 Time: 4:48 pm Merchant: WIU Union Card type: Master Card Issuer: First Bank The unique attribute is Called a ______________

  13. ORGANIZATION CONCEPTS What can we say about these two sets of data? Transaction#: 55643 Date: 12/5/16 Time: 4:09 pm Merchant: WIU Union Card type: Master Card Issuer: First Bank Transaction#: 55644 Date: 12/5/16 Time: 4:20 pm Merchant: Vitales Card type: Master Card Issuer: Midwest Bank ____________________________

  14. ORGANIZATION CONCEPTS.. Transaction#: 55643 Date: 12/5/16 Time: 4:09 pm Merchant: WIU Union Card type: Master Card Issuer: First Bank ________ Data can be organized into a _____ with ________

  15. NEED FOR DATABASES We live in an information age • cannot function without information • can be a life or death issue • finding acct balance • print transaction history • order spare parts for MRI machine • find patient records • needs to be a click away • otherwise, lost sale, lost profits or lost career! How can we find information when we need it?

  16. DEFINITION OF A FILE/TABLE.. CBT QUALITY MANAGEMENT COMMITTEE Meeting Minutes Meeting Date/time: December 2nd, 2:00 p.m. Present: Brad Burke, Ken Dien, Chin Yi, John Smith 1215 Oak Drive $515.00 11/5/16 Mary Ash 415 Ponds $ 56.00 5/9/16 A database ______ is a collection of _______ .

  17. DATABASE

  18. DATABASE Database - A shared collection of logically related data designed to meet the needs of multiple users in an organization. • Example, cars listed in auto-trader • Matches played in the olympics • Properties owned by a company • Books available from Amazon.com DBMS is the software program.

  19. DATABASE ORGANIZATION Structure/schema Attributes/field names Primary key Record Record A database has a structure or schema that organizes the data into attributes. Collections of attribute values become records. A primary key guarantees unique access. Attr. values

  20. DATABASE ORGANIZATION Database Files/tables Records Attr. values

  21. HISTORICAL APPROACH TO DATA The historical method of handling data is called file processing 55643 3/02/16 4:09pm WIU Union 55644 5/08/16 4:20pm Vitales 55644 7/05/16 4:20pm Vitales 55646 12/11/16 8:05am Pizza Hut 55643 $25.60 MC WIU Union 55644 $38.00 Visa Vitales 55644 $63.00 Visa Vitales 55646 $55.00 Amex Pizza Hut File 1 File 2 Instead of storing data centrally in a database, in the file processing approach, the same data was often duplicated in multiple files.

  22. FILE PROCESSING PROBLEMS What problems did the file processing approach result in?

  23. DATABASE MANAGEMENT SYSTEMS (DBMS)

  24. DBMS DBMS - software program to create, manage and provide controlled access to the data • Example Access, Oracle, DB2 • What can we do with a DBMS?

  25. DBMS ACTIVITIES & FUNCTIONS The operations that can be carried out with a DBMS include: • Define structure / schema • Enter data • modify data • query data • get reports

  26. DBMS ACTIVITIES & FUNCTIONS Define structure/schema This is called data definition

  27. DATA ENTRY A data entry form allows data to be entered easily. Title EMPLOYEEDATA ENTRY Label EMP ID: Field NAME: Data entry form

  28. ENTERING DATA EMPLOYEEDATA ENTRY 1117 EMP ID: NAME: John The data is entered into the data entry form This is called data entry. Where does the data go to?

  29. RETRIEVING DATA Data can be obtained from the database in the following ways: • By the user • SQL commands • QBE (Query by Example) • Reports • Via an application program • SQL commands only

  30. RETRIEVING DATA THE STRUCTURED QUERY LANGUAGE Each SQL statement has three parts as shown below: SELECT is used to select attributes needed FROM is used to specify the tables from which data is obtained. WHERE is used for including criteria (filter) Select car_id, car_color, car_price, car_mileage From cars Where car_mileage < 100,000

  31. RETRIEVING DATA.. Write an SQL statement for listing names of employees in finance department. Employee SELECT ?? FROM ?? WHERE ?? This is called query/retrieval.

  32. QUERY BY EXAMPLE List names of employees in finance department. Employee A query by example form (QBE) This is also another form of query/retrieval.

  33. RETRIEVING DATA: REPORTS.. We can get data out through queries or reports • Reporting resembles COBOL reports • Report specification needs to be created • Consists of RH, PH, Detail, RF, PF • Report is generated from specification

  34. SPECIFYING REPORTS.. RH Title PRODUCT LISTING PH Column Headings Detail PRODUCT # DESCR. PRICE Product # PF Descr. Price Fields Average Price Footer RF A report specification

  35. SUMMARY OF DBMS ACTIVITIES Activities with DBMS define structure/schema enter data (into table/tables) modify data (not discussed) query data (from table/tables) get reports (from table/tables)

  36. USAGE OF DATABASES Databases are used (operational): • To store and record information e.g. bal, price, grades etc. • To retrieve information e.g. check#432 cashed? • To report information e.g. daily sales • To answer queries e.g. how many shoes were sold? Advanced uses (Strategic): • To analyze trends • Identify sales prospects

  37. OPERATIONAL & STRATEGIC USAGE What if a customer wants to return a tie purchased in a store? What if a manager wants to know what products were sold on a particular day? Suppose we have detailed information on each and every transaction in a store, what can we do with that?

  38. THE DEVELOPMENT CYCLE

  39. THE DATABASE DEVELOPMENT CYCLE How do we know what information we need for the database?

  40. THE DATABASE DEVELOPMENT CYCLE.. The database development cycle parallels the information systems development cycle. • database planning • database analysis • database design • implementation • support/maintenance

  41. THE DATABASE DEVELOPMENT CYCLE..

  42. THE DEVELOPMENT CYCLE AND THIS COURSE..

  43. ABOUT THE COURSE

  44. COURSE OBJECTIVES • Thorough understanding of database concepts • Ability to develop ER models • Ability to develop database designs • Ability to implement databases in PC based systems • Ability to write SQL queries • Familiarity with database administration issues Ability to analyze database requirements and implement systems for a small organization

  45. COURSE FEATURES • Very concept oriented • Database concepts are abstract • Participation required! • Digressions welcome! • Guidelines are very subjective • Numerous in-class exercises • Reinforcing assignments Need to have a good “schema”!

  46. COURSE EVALUATION *Lowest will be dropped, but -20 point penalty for non-submission Note: Please refer to your course syllabus for up to date information on the evaluation schedule.

  47. CLASS PARTICIPATION • Answer questions • Raise an issue relevant to the topic at hand • Solve an in-class problem • Participation quizzes • Max, two “credits” per session • Quality and quantity of participation • Disruptive activities negatively assessed Review powerpoints before coming to class!

  48. GENERAL COMMENTS • Database practice is an application of theory • Get theories/frameworks and definitions into your system; • Need to have good conceptual understanding • Need to review frequently (before class, after class and everyday) • Participate in class • Exams cover all topics (don’t strategize) • Most important course in the IS curriculum

  49. Questions?

More Related