1 / 50

Programming Logic and Design Fifth Edition, Comprehensive

Programming Logic and Design Fifth Edition, Comprehensive. Chapter 14 Using Relational Databases. Objectives. Understand relational database fundamentals Create databases and table descriptions Be able to identify primary keys Understand database structure notation

symona
Download Presentation

Programming Logic and Design Fifth Edition, Comprehensive

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. Programming Logic and DesignFifth Edition, Comprehensive Chapter 14 Using Relational Databases

  2. Objectives • Understand relational database fundamentals • Create databases and table descriptions • Be able to identify primary keys • Understand database structure notation • Understand the principles of adding, deleting, updating, and sorting records within a table Programming Logic and Design, Fifth Edition, Comprehensive

  3. Objectives (continued) • Create queries • Understand relationships between tables • Recognize poor table design • Understand anomalies, normal forms, and the normalization process • Understand database performance and security issues Programming Logic and Design, Fifth Edition, Comprehensive

  4. Understanding Relational Database Fundamentals • Data hierarchy: • Smallest unit of data is the character • Characters form fields • Fields form records • Records form files • Database: • Group of files needed to support an organization • Files in a database are called tables Programming Logic and Design, Fifth Edition, Comprehensive

  5. Understanding Relational Database Fundamentals (continued) • Data in tables can be arranged in rows and columns • Each column represents a field • Each row represents a record Figure 14-1 A telephone book table Programming Logic and Design, Fifth Edition, Comprehensive

  6. Understanding Relational Database Fundamentals (continued) • Primary key (or key): • Uniquely identifies a record • May be composed of one or multiple columns • Compound key: constructed from multiple columns • Also known as composite key Programming Logic and Design, Fifth Edition, Comprehensive

  7. Understanding Relational Database Fundamentals (continued) • Database management software allows you to: • Create table descriptions • Identify keys • Add, delete, and update records within a table • Sort records by different fields • Write questions to select specific records for viewing • Write questions to combine information from multiple tables • Relationaldatabase: tables with related columns • Create reports • Secure the data Programming Logic and Design, Fifth Edition, Comprehensive

  8. Creating Databases and Table Descriptions • Creating a database requires planning and analysis • What data to store • How to divide the data between tables • How the tables will interrelate • Designing a database table: • Required columns • And how they are named • Type of data in each column Programming Logic and Design, Fifth Edition, Comprehensive

  9. Creating Databases and Table Descriptions (continued) Figure 14-2 Customer table description Programming Logic and Design, Fifth Edition, Comprehensive

  10. Identifying Primary Keys • Identify a column or combination of columns to be the primary key • Values of primary keys must be unique, such as: • Student ID number • Inventory part number • Social Security number Programming Logic and Design, Fifth Edition, Comprehensive

  11. Identifying Primary Keys (continued) • Primary key is used for: • Ensuring multiple records with the same values cannot be added • Sorting the records in primary key order • Creating relationships between tables • Normalizing a database • Multicolumn keys ensure unique values Programming Logic and Design, Fifth Edition, Comprehensive

  12. Identifying Primary Keys (continued) Figure 14-3 Table containing residence hall student records Programming Logic and Design, Fifth Edition, Comprehensive

  13. Understanding Database Structure Notation • Table description: • Table name with column names in parentheses • Underline the primary key field(s) tblStudents (idNumber, lastName, firstName, gradePointAverage) • Does not provide information about data types or range limits • Does provide overview of table structure Programming Logic and Design, Fifth Edition, Comprehensive

  14. Adding, Deleting, Updating, and Sorting Records Within Tables • May have to set up table to prevent duplicate data in specific fields • Prevent data entry outside of specified bounds in other fields • Depending on database software • Data typed into rows representing each record • Columns represent each field • Create onscreen forms to make data entry more user-friendly • Some software does not allow any blank fields • Deleting and modifying records is relatively easy Programming Logic and Design, Fifth Edition, Comprehensive

  15. Sorting the Records in a Table • Can sort a table based on any column • After sorting: • Records can be grouped by specific values or ranges • Aggregate values can be calculated (counts, sums, averages, etc.) • Database software provides the means to create displays in various formats Programming Logic and Design, Fifth Edition, Comprehensive

  16. Creating Queries • Query: question that causes database software to extract appropriate records from the table • Query by example: fill in blanks to create queries • Structured Query Language (SQL): most common language used to query a database Programming Logic and Design, Fifth Edition, Comprehensive

  17. Creating Queries (continued) • SELECT-FROM-WHERE is the basic form of a query: • Selects columns to view • From a specific table • Where one or more conditions are met • Wildcard symbol specifies “any” or “all” • Compound conditions using AND, OR, or NOT Programming Logic and Design, Fifth Edition, Comprehensive

  18. Creating Queries (continued) Figure 14-4 The tblInventory table Programming Logic and Design, Fifth Edition, Comprehensive

  19. Creating Queries (continued) Figure 14-5 Sample SQL statements and explanations Programming Logic and Design, Fifth Edition, Comprehensive

  20. Understanding Relationships Between Tables • Relationship: connection between two tables • Relational database: database containing relationships • Join operation (or join): connecting two tables based on values in a common column • Query returns data taken from each joined table • Three types of relationships: • One-to-many • Many-to-many • One-to-one Programming Logic and Design, Fifth Edition, Comprehensive

  21. Understanding Relationships Between Tables (continued) Figure 14-6 Sample customers and orders Programming Logic and Design, Fifth Edition, Comprehensive

  22. Understanding One-to-Many Relationships • One-to-many relationship: • Row in one table related to one or more rows in another table • Most common type of table relationship • Relationship based on one or more columns • “One” side is the base table • Primary key is used for the join • “Many” side is the related table • May be a non-key attribute • Foreign key: field in one table which is primary key in another table Programming Logic and Design, Fifth Edition, Comprehensive

  23. Understanding One-to-Many Relationships (continued) Figure 14-7 Sample items and categories: a one-to-many relationship Programming Logic and Design, Fifth Edition, Comprehensive

  24. Understanding Many-to-Many Relationships • Many-to-many relationship: • Multiple rows in each table can correspond to multiple rows in the other table • Additional table contains pairs of primary keys from each table • New table contains compound primary key • These pairs form unique keys in the new table Programming Logic and Design, Fifth Edition, Comprehensive

  25. Understanding Many-to-Many Relationships (continued) Figure 14-8 Sample items, categories, and item categories: a many-to-many relationship Programming Logic and Design, Fifth Edition, Comprehensive

  26. Understanding One-to-One Relationships • One-to-one relationship: • Row in one table corresponds to exactly one row in another table • Least frequently encountered relationship • Tables could be combined into a single table • Often keep the tables separate for security purposes Programming Logic and Design, Fifth Edition, Comprehensive

  27. Understanding One-to-One Relationships (continued) Figure 14-9 Employees and salaries tables: a one-to-one relationship Programming Logic and Design, Fifth Edition, Comprehensive

  28. Recognizing Poor Table Design • Often table design is inadequate to support needs of the application • Structure may be cumbersome • Prone to data errors • Take time to ensure well-designed table structures initially Programming Logic and Design, Fifth Edition, Comprehensive

  29. Recognizing Poor Table Design (continued) Figure 14-10Students table before normalization process Programming Logic and Design, Fifth Edition, Comprehensive

  30. Understanding Anomalies, Normal Forms, and the Normalization Process • Normalization: • Designing a database structure to satisfy user needs • Reduce duplication of data • Data redundancy: unnecessary repetition of data • Anomaly: irregularity in database design that causes problems • Update anomalies • Delete anomalies • Insert anomalies Programming Logic and Design, Fifth Edition, Comprehensive

  31. Understanding Anomalies, Normal Forms, and the Normalization Process (continued) • Update anomaly: when updating data in one table, you must update the same data in another table • Delete anomaly: deleting a record causes other problems, such as loss of unrelated information • Insert anomaly: inability to add a new record due to lack of related data Programming Logic and Design, Fifth Edition, Comprehensive

  32. Understanding Anomalies, Normal Forms, and the Normalization Process (continued) • Normalization removes redundancies and anomalies • Three normal forms: • First normal form (or 1NF): eliminate repeating groups • Second normal form (or 2NF): eliminate partial key dependencies • Third normal form (3NF): eliminate transitive dependencies Programming Logic and Design, Fifth Edition, Comprehensive

  33. First Normal Form • Unnormalized: table contains repeating groups • Repeating group: subset of rows in a table all depend on the same key • Table in 1NF contains no repeating groups of data • Primary key attributes are defined • Atomic attributes: columns as small as possible • Containing undividable pieces of data • In 1NF, all values for intersecting row and column must be atomic Programming Logic and Design, Fifth Edition, Comprehensive

  34. First Normal Form (continued) Figure 14-11Students table in 1NF Programming Logic and Design, Fifth Edition, Comprehensive

  35. Second Normal Form • Partial key dependencies: column depends on only part of the key • For 2NF: • Database must already be in 1NF • All non-key fields must be dependent on the entire primary key • Eliminate partial key dependencies by creating multiple tables Programming Logic and Design, Fifth Edition, Comprehensive

  36. Second Normal Form (continued) Figure 14-12Students table in 2NF Programming Logic and Design, Fifth Edition, Comprehensive

  37. Second Normal Form (continued) • Improvements over 1NF: • Eliminate update anomalies • Eliminating redundancies reduces anomalies • Eliminate insert anomalies • Eliminate delete anomalies • When breaking a table into multiple tables, consider relationships among tables Programming Logic and Design, Fifth Edition, Comprehensive

  38. Third Normal Form • Transitive dependency: value of a non-key attribute determines value of another non-key attribute • For 3NF: • Database must already be in 2NF • No transitive dependencies • Remove attributes that are functionally dependent on the attribute that causes the transitive dependency Programming Logic and Design, Fifth Edition, Comprehensive

  39. Third Normal Form (continued) Figure 14-13 The complete Students database Programming Logic and Design, Fifth Edition, Comprehensive

  40. Third Normal Form (continued) • All redundancies and anomalies are removed • Determinant is allowed in 3NF if it is a candidate key • Normalization summary: • 1NF: no repeating groups • 2NF: 1NF plus no partial key dependencies • 3NF: 2NF plus no transitive dependencies Programming Logic and Design, Fifth Edition, Comprehensive

  41. Database Performance and Security Issues • A company’s data must be protected • Data security includes: • Providing data integrity • Recovering lost data • Avoiding concurrent update problems • Providing authentication and permissions • Providing encryption Programming Logic and Design, Fifth Edition, Comprehensive

  42. Providing Data Integrity • Data integrity: • Data is accurate and consistent • Database software must enforce data integrity Programming Logic and Design, Fifth Edition, Comprehensive

  43. Recovering Lost Data • Data loss caused by: • User mistakes • Hackers or other malicious users • Hardware problems • Fire, flood, or other natural disasters • Recovery: return the database to a correct form that existed before the problem occurred • Make a backup copy of the database • Record of all transactions to recover a database Programming Logic and Design, Fifth Edition, Comprehensive

  44. Avoiding Concurrent Update Problems • Concurrent update problem: • Two users make changes to the same record • One update will be lost • Lock: mechanism to prevent changes to a record for some period of time • Solving concurrent update problem: • Use record-level locking • Make transactions offline, and process as a batch Programming Logic and Design, Fifth Edition, Comprehensive

  45. Providing Authentication and Permissions • Database software must determine that a user is legitimate and is authorized to use the database • Authentication techniques include: • Storing and verifying passwords • Using biometric data to identify users • Permissions: settings that determine what actions a user is allowed to perform • Authentication determines what permissions a user has Programming Logic and Design, Fifth Edition, Comprehensive

  46. Providing Encryption • Encryption: coding data into a format that humans cannot read • Prevents use of the data by unauthorized users Programming Logic and Design, Fifth Edition, Comprehensive

  47. Summary • Database: collection of tables containing an organization's data • Primary key: value that uniquely identifies a record • Database management software allows you to add, delete, and update records in the database Programming Logic and Design, Fifth Edition, Comprehensive

  48. Summary (continued) • Query: question that selects data from database • Database creation requires planning and analysis • Primary key can consist of one or multiple columns • Most data is in a constant state of change Programming Logic and Design, Fifth Edition, Comprehensive

  49. Summary (continued) • Functions • Can sort a table based on any column • Can do aggregate calculations on data • Normalization: designing a database to meet the needs, yet avoiding redundancies and anomalies • Three forms of normalization are commonly used Programming Logic and Design, Fifth Edition, Comprehensive

  50. Summary (continued) • Database may be one of a company’s most important assets, so it must be secured • Security issues: • Data integrity • Recovery • Avoiding concurrent update problems • Authentication and permissions • Providing encryption Programming Logic and Design, Fifth Edition, Comprehensive

More Related