1 / 46

INTRO TO ENTERPRISE DATABASES - I

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

xylia
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.. • Need for databases • Basic concepts: data and info, entity, attr, pkey, file, record • Historical approach to data • Definitions • Database organization • Importance of databases/Organizational importance • DBMS and DBMS activities and functions • Course features

  3. WHAT IS INFORMATION?

  4. DEFINING DATA AND INFORMATION Data: Data consists of raw facts e.g. 2982034 Information: collection of data; data presented in context; elaboration. USA 46 29 29 104 China 38 27 23 88 Russia 24 26 32 82 Great Britain 29 17 19 65 Germany 11 019 14 44 Japan 7 14 17 38 Australia 7 16 12 34 France 11 11 12 34

  5. DATA VS INFORMATION Data becomes information when we ________________ it.

  6. 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! What is information? How can we find it when we need it?

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

  8. INTRODUCING ENTITIES, ECLASSES AND ATTRIBUTES Information is a collection of data usually DESCRIBING something. What does it describe here? How? Name: Steve Nash DOB: 2-7-1980 Profession: Basket ball player Address: 415 Walnut street.

  9. COLLECTION OF DATA... Following is another example of information as a collection of data describing a transaction. Acct#: 4555950 Date: 12/5/13 Time: 4:48 pm Merchant: WIU Union Card type: Master Card Issuer: First Bank ______describe a transaction more.

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

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

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

  13. DEFINITION OF A FILE.. 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 $514.00 11/5/13 Mary Ash 415 Ponds $ 56.00 5/9/13 A database ______ is a collection of _______ .

  14. DATABASE

  15. 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.

  16. 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

  17. DATABASE ORGANIZATION Database – A group of related files File/table – A group of related records Record – a grouping of related fields Attr. value – value of an attr. e.g. hair color Schema -- This is the structure or logical view of the database (tables and attr.) Primary key – An attribute whose values are unique within a file Secondary key – Any other attribute Database Files/tables Records Attr. values

  18. HISTORICAL APPROACH TO DATA The historical method of handling data is called file processing 55643 3/02/13 4:09pm WIU Union 55644 5/08/13 4:20pm Vitales 55644 7/05/13 4:20pm Vitales 55646 12/11/13 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.

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

  20. DATABASE MANAGEMENT SYSTEMS (DBMS)

  21. 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?

  22. 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

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

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

  25. 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?

  26. RETRIEVING DATA Data can be obtained from the database in the following ways: • SQL commands • QBE (Query by Example) • Reports

  27. 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

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

  29. 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.

  30. 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

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

  32. 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)

  33. 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

  34. 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?

  35. THE DEVELOPMENT CYCLE

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

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

  38. THE DATABASE DEVELOPMENT CYCLE.. Planning – is concerned with identifying what information will be in the database and the resources that will be required. Analysis – this is concerned with the detailed requirements such as attributes, entity classes as well as with their relationships. Design -- is concerned with creating a database structure that will fulfill the requirements Implementation – this is the phase that is concerned with actually defining the structures, entering data and creating reports. Maintenance – this is concerned with ensuring that the schema, reports etc are up to date and the database is performing well.

  39. THE DEVELOPMENT CYCLE AND THIS COURSE.. Database environment -- Evolution & environment Database planning -- Enterprise analysis Analysis -- Database analysis Design -- Logical design Design -- Physical design Implementation -- Access, MySQL assignments Support/maintenance -- Database adminstration.

  40. ABOUT THE COURSE

  41. 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

  42. 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”!

  43. 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.

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

  45. 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

More Related