Chapter 1 Overview of Database Concepts

1. Chapter 1Overview of Database Concepts Jason C.H. Chen, Ph.D. Professor of MIS School of Business, Gonzaga University Spokane, WA 99258 USA chen@jepson.gonzaga.edu

2. Objectives • Define database terms • Identify the purpose of a database management system (DBMS) • Explain database design using entity-relationship models and normalization • Explain the purpose of a Structured Query Language (SQL) • Understand how this textbook’s topics are sequenced and how the two sample databases are used

3. DATA What is Information ? INFORMATION Information is refined data.

4. Data vs. Information • Users really want is • Information • What users can learn from the data • how to satisfy their best customers • how to allocate their resources most efficiently, • how to minimize losses

5. Objectives of the MIS who has whatinformation about whom and when,where, and how will all be decided in the process of building an information system. Deliver the right information to the right people, at the right time, with the right form. Ultimately, MIS should improve the workers’ productivity.

6. A Relationship Between the Organization and the DataBase Organizational IS Resources: 1. Hardware 2. Software 3. Data 4. Procedures 5. People Picture Or Model Old State Activities Events New State DataBase Management Systems Transactions (Add, modify, delete, …) D.B. Correspond with the processing of the events Information must be: 1. Time: Timely, 2. Content: Accurate, relevant, and verifiable. 3. Form: Presented in a useable form. User TM 0-6

7. Questions • Is MS/Access or Oracle (SQL/Server or IBM DB2) a data base? • Why don’t we just create a “huge” data file containing all fields (columns)? • Have you ever created a database (with many applications) with only one “huge” data file containing all fields (columns)?

8. Traditional File Processing Approach • Traditional File Processing • An application uses one specialized file. For example, purchase order processing application uses data about supplier and products, while an order-taking application uses data about customer, products and orders. • Problems: • Data dependence: data structures are tightly coupled with applications. In other words, we prefer data independence. • Data redundancy: same data are repeatedly saved for different applications. • Other problems

9. Types of Data Processing • Two types of data processing • File-based (traditional) data processing • applications developed by Java • Data-based data processing • applications developed by Oracle or MS/Access)

10. Duplicate Data Three file processing systems at a Furniture Company What is the main problem in the company’s processing systems?

11. Disadvantages of File Processing • Program-Data Dependence • All programs maintain metadata for each file they use • Data Redundancy (Duplication of data) • Different systems/programs have separate copies of the same data • Limited Data Sharing • No centralized control of data • Lengthy Development Times • Programmers must design their own file formats • Excessive Program Maintenance • 80% of of information systems budget

12. Application #1 Application #2 Application #3 Database containing centralized shared data Database Management System DBMS DBMS manages data resources like an operating system manages hardware resources What are the advantages of employing Data-based Data Processing?

13. Traditional File Processing

14. Database Management Systems

15. Database Terminology • Database – an organized collection of logically related data files • Database management system (DBMS) – software used to create and interact with the database

16. Database Components • Character • Basic unit of data • Can be a letter, number, or special symbol • Field • A group of related characters • Represents an attribute or characteristic of an entity • Corresponds to a column in the physical database • Record • A collection of fields for one specific entity • Corresponds to a row in the physical database • File • A group of records about the same type of entity

17. Relational Databases • Data is organized in tables • Columns (fields) represent different data categories • Rows (records) contain actual data values

18. Database Example

19. Components Example

20. Relational Database Terms • Entity:an object about which you want to store data • Table, column, row • Flat file, attribute (or field), record • Relationships:links that show how different records are related • Key Fields: establish relationships among records in different tables • Five main types of key fields: • primary keys • candidate keys • surrogate keys • foreign keys • composite keys

21. Primary Keys vs. Candidate Keys • Primary key • Value must be unique for each record • Serves to identify the record • Present in every record • Can’t be NULL • Should be numeric • Candidate key • Any field that could be used as the primary key • Should be a unique, unchanging numeric field

22. Surrogate Keys • Surrogate key: created to be the record’s primary key identifier when no suitable primary key exists • Surrogate key has no real relationship to the record to which it is assigned, other than to identify the record uniquely • Developers configure the database to generate surrogate key values automatically • In an Oracle database, you can automatically generate surrogate key values using a sequence • Surrogate keys are always numerical fields, because the database generates surrogate key values automatically by incrementing the previous value by one

23. Foreign Keys • Foreign key:a field in a table that is a primary key in another table • Foreign key creates a relationship between the two tables • Foreign key value must exist in the table where it is a primary key

24. Composite Key • Composite key: a unique key that you create by combining two or more fields • Usually comprised of fields that are primary keys in other tables Composite Key

25. Database Management System • Data storage: manage the physical structure of the database • Security: control user access and privileges • Multiuser access: manage concurrent data access • Backup: enable recovery options for database failures • Data access language: provide a language that allows database access • Data integrity: enable constraints or checks on data • Data dictionary: maintain information about database structure

26. Client/Server Database Management Systems • Client/server database • Takes advantage of distributed processing and networked computers by distributing processing across multiple computers • DBMS server process runs on one workstation, and the database applications run on separate client workstations across the network • Preferred for database applications that retrieve and manipulate small amounts of data from databases containing large numbers of records because they minimize network traffic and improve response times • Organizations generally use a client/server database if the database will have more than 10 simultaneous users and if the database is mission critical

27. The Oracle11g Client/Server Database • Oracle11gis the latest release of Oracle Corporation’s relational database management system • All Oracle server- and client-side programs use Oracle Net, a utility that enables the network communication between the client and the server

28. DATABASE TRENDS Linking Internal Databases to the Web N

29. 2. Request for data-based Web page 1. Request for data-based Web page 3. Data query 8. Data-based Web page 6. Retrieved data 7. Data-based Web page Web Browser Web server 4. Data query 5. Retrieved data Legend Communications Between Web browser And Web server Communications Between Web server And database server Database Server Web-Based Client/Server Database Architecture Network

30. System Response TimeDepends on ... • the speed of the network • the size of the database • the way the database is used … • a personal database running on a server might handle 10 users making database transactions at the same time before becoming overloaded.

31. Advantages of Client/Server Database Management Systems • Handling server and client failures • Processing transactions • Handling high data volumes • Providing security • Servicing multiple simultaneous users

32. Language or Tool Description SQL A command language for communication with the Oracle 10/11Server from any tool or application. Oracle SQL contains many extensions. SQL*Plus An Oracle tool that recognizes and submits SQL and PL/SQL statements to the Server for execution and contains its own command language. PL/SQL An Oracle Procedural Language for writing application logic and manipulating data outside the database. SQL, SQL*Plus, and PL/SQL Terminal Server SQL*Plus Buffer SQL and PL/SQL Scripts

33. Design Principles • To avoid creating tables that contain redundant data, group related items that describe a single entity together in a common table • Do not create tables that duplicate values many times in different rows • When creating a database and inserting data values, you must specify the data type for each column • Recall that primary key fields should use a number data type to avoid typographical, punctuation, and case variation errors

34. Database Design • Systems Development Life Cycle (SDLC) • Entity-relationship model (E-R model) • Normalization

35. Systems Development Life Cycle Systems Investigation (Definition) Product: Feasibility Study Understand the Business Problem or Opportunity Systems Analysis Product: Functional Requirements Develop an Information System Solution Systems Design Product: System Specifications Systems Implementation Product: Operational System Implement the Information System Solution Systems Maintenance Product: Improved System

36. Systems Development Life Cycle (SDLC) • Systems investigation – understanding the problem • Systems analysis – understanding the solution • Systems design – creating the logical and physical components • Systems implementation – placing completed system into operation • including integration, testing and deployment • Systems maintenance and review – evaluating the implemented system

37. CUSTOMER ORDER Data Models • A data modelis a collection of concepts for describing data. • Three database models • Hierarchical • Network • Relational

38. Entity-Relationship Model (E-R Model) • Used to depict the relationship that exists among entities • The following relationships can be included in an E-R model: • One-to-one • One-to-many • Many-to-many

39. E-R Model Notation Examples Figure 1-3 E-R Model notation examples Please note that the name of entity should be singular even it contains multiple instances

40. Figure 1-5 JustLee Books’ table structures after normalization

41. One-to-One Relationship • Each occurrence of data in one entity is represented by only one occurrence of data in the other entity • Example: Each order has just one invoice and each invoice is assigned to just one order ORDER INVOICE

42. One-to-Many Relationship • Each occurrence of data in one entity can be represented by many occurrences of the data in the other entity • Example: A class has only one instructor, but each instructor can teach many classes Class Instructor

43. Many-to-Many Relationship • Data can have multiple occurrences in both entities • Example: A student can take many classes, and each class is composed of many students • Can not be included in the physical database Class Student

44. JustLee Example E-R Model

45. Installing Personal Oracle11g • I created a “Discussion Questions” on the Bb for you to share (or post question) your experience on installation of Oracle 11g. Please participate and it will be also your part of class performance. • Please note that if your computer with 64-bit operating system such as Windows 7, you can’t use the DVD to install Oracle since it is for 32-bit OS. Rather you need to download 64-bit Oracle 11g from the Oracle site (www.oracle.com) or type in keyword search of “Oracle 11g download”.

46. Q: Can we create/enter the data base on the following table immediately? BOOKS table

47. Database Normalization • Purposes • design a reliable and stable data bases • increase data integrity (reduce or control data redundancy) • Processes • determines required tables and columns for each table • multistep process

48. ??? Problem Solving for Modeling a Database Project Study and Analyze w/Team Business Problem IMPLEMENTATION

49. User interview & Integrated Model ER or other Model Normalization Normalization (3NF) IMPLEMENTATION Problem Solving for Modeling a Database Project Study and Analyze w/Team Business Problem

50. Well-Structured Relations • A relation that contains minimal data redundancy and allows users to insert, delete, and update rows without causing data inconsistencies • Goal is to avoid (minimize) anomalies • Insertion Anomaly– adding new rows forces user to create duplicate data • Deletion Anomaly– deleting rows may cause a loss of data that would be needed for other future rows • Modification Anomaly– changing data in a row forces changes to other rows because of duplication General rule of thumb: a table should not pertain to more than one entity type