Connecting with Computer Science, 2e Chapter 6 Database FundamentalsConnecting with Computer Science, 2e 2 Objectives • In this chapter you will: – Consider the widespread use of databases – Take a brief tour of database development history – Learn basic database concepts – Be introduced to popular database management software – See how normalization makes your data more organizedConnecting with Computer Science, 2e 3 Objectives (cont’d.) • In this chapter you will (cont’d.): – Explore the database design process – Understand data relationships – Gain an understanding of Structured Query Language (SQL) – Learn some common SQL commandsConnecting with Computer Science, 2e 4 Why You Need to Know About... Databases • Data must be organized • Effective computer professionals know correct database design – Normalization • Ensures an accurate and reliable database – Structured Query Language (SQL) • Describes how information is retrieved from relational databaseDatabase Applications • Database – Data logically related and organized into a file or set of files to allow access and use • Database applications – Student grading and library inventory – Genealogy studies and Social Security payments – Real estate sales, video store rentals, and retail sales – Space shuttle missions • Database development – Essential part of computer professional’s daily life Connecting with Computer Science, 2e 5Brief History of Database Management Systems • 1970 to 1975 – Work of IBM employees E. F. Codd and C. J. Date • Created theoretical model for designing data structures • Model became foundation for database design – Software for organizing and sorting data • System R by IBM and Ingres by UC-Berkeley • Structured Query Language (SQL) • SQL: database standard • Database management system (DBMS) for PCs • 1978 – C. Wayne Ratliff of Martin Marietta develops Vulcan Connecting with Computer Science, 2e 6Connecting with Computer Science, 2e 7 Brief History of Database Management Systems (cont’d.) • 1980 to the present – Vulcan renamed dBASE II (no dBase I) – Popularity of dBASE II inspires other companies • Paradox, Microsoft Access, and FoxPro – Databases become essential for business • Corporate decision making • Systems: inventory management to customer supportConnecting with Computer Science, 2e 8 Table 6-1, Popular database management systems Brief History of Database Management Systems (cont’d.)Connecting with Computer Science, 2e 9 Database Management System Fundamentals • Six main DBMS functions – Manage database security – Manage multiple users’ access to the database – Manage database backup and recovery – Ensure data integrity – Provide an end-user interface to the database – Provide a query language allowing users to modify and view database information easilyConnecting with Computer Science, 2e 10 Database Concepts • Basic database elements – Database: collection of one or more tables (entities) – Table or entity: divided into rows and columns – Row (record or tuple): collection of columns – Column (field or attribute): represents specific information – Domain: set of possible column valuesConnecting with Computer Science, 2e 11 Figure 6-1, A database table consists of rows and columns Database Concepts (cont’d.)Connecting with Computer Science, 2e 12 Indexes • Special files occupying their own space – Specify columns determining how information stored in a table can be accessed more efficiently • Examples: music database and the telephone book • Advantages – Flexibility: many different columns to sort against – Searching and retrieval speeds up • Disadvantages – Extra storage space – Updating takes longerConnecting with Computer Science, 2e 13 An Example of Indexing Figure 6-2, You use database concepts in your everyday lifeConnecting with Computer Science, 2e 14 An Example of Indexing (cont’d.) • Each database row has similar attributes • Sort key: one or more columns used to determine the data’s sort order – One key or a combination of keys determines sort order • Database information is stored in natural or sequential order – Order of records displayed equals the order records are enteredConnecting with Computer Science, 2e 15 Figure 6-3, Database records sorted by using the UPC column as a key An Example of Indexing (cont’d.)Connecting with Computer Science, 2e 16 Figure 6-4, Database records sorted by Brand_Name and Description An Example of Indexing (cont’d.)Connecting with Computer Science, 2e 17 Normalization • Set of rules dictating database design – Eliminates duplication and inconsistencies – Process: sequence of stages called normal forms • Five normal forms • Third normal form provides sufficient structure • Three database design problems solved – Representation of certain real-world items – Redundancies (repetitions) in data – Excluded and inconsistent informationConnecting with Computer Science, 2e 18 Preparing for Normalization: Gathering Columns • Make a list of all pertinent fields (columns or attributes) – Source of fields: end-user reports or song inventory – Write fields on the column list • Review user-specified input forms – Convert each field from the report to column in tableConnecting with Computer Science, 2e 19 Figure 6-5, End-user report with table columns highlighted Preparing for Normalization: Gathering Columns (cont’d.)Connecting with Computer Science, 2e 20 Figure 6-6, Additional table columns can be gleaned from input forms Preparing for Normalization: Gathering Columns (cont’d.)Connecting with Computer Science, 2e 21 Preparing for Normalization: Gathering Columns (cont’d.) • Reconcile fields in report to column list • Create tables of columns by combining associated fields – Logically group related information • Example: information on artist and song files • Gather data to create physical music databaseConnecting with Computer Science, 2e 22 First Normal Form • Unnormalized table – Row-column intersection with two or more values • First normal
View Full Document
Unlocking...