1 / 198

Mastering Relational Database Concepts and SQL Fundamentals

Dive into relational data models, constraints, algebra, SQL schema, and design principles. Learn SQL basics, constraints, querying, and advanced techniques for efficient database management.

jstrouth
Download Presentation

Mastering Relational Database Concepts and SQL Fundamentals

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. Chapter 5 The Relational Data Model and Relational Database Constraints

  2. Relational Model Concepts • Domains, Attributes, Tuples, and Relations • Characteristics of Relations • Relational Model Notation

  3. Relational Model Constraints and Relational Database Schemas • Domain Constraints • Key Constraints and Constraints on Null Values • Relational Databases and Relational Database Schemas • Entity Integrity, Referential Integrity, and Foreign Keys • Other Types of Constraints

  4. Update Operations and Dealing with Constraint Violations • The Insert Operation • The Delete Operation • The Update Operation

  5. Summary

  6. Chapter 6 The Relational Algebra and Relational Calculus

  7. Unary Relational Operations: SELECT and PROJECT • The SELECT Operation • The PROJECT Operation • Sequences of Operations and the RENAME Operation

  8. Relational Algebra Operations from Set Theory • The UNION, INTERSECTION, and MINUS Operations • The CARTESIAN PRODUCT (or CROSS PRODUCT) Operation

  9. Binary Relational Operations: JOIN and DIVISION • The JOIN Operation • The EQUIJOIN and NATURAL JOIN Variations of JOIN • A Complete Set of Relational Algebra Operations • The DIVISION Operation

  10. Additional Relational Operations • Aggregate Functions and Grouping • Recursive Closure Operations • OUTER JOIN Operations • The OUTER JOIN Operation

  11. Examples of Queries in Relational Algebra

  12. The Tuple Relational Calculus • Tuple Variables and Range Relations • Expressions and Formulas in Tuple Relational Calculus • The Existential and Universal Quantifiers • Example Queries Using the Existential Quantifier • Transforming the Universal and Existential Quantifiers • Using the Universal Quantifier • Safe Expressions

  13. The Domain Relational Calculus

  14. Summary

  15. Chapter 7 Relational Database Design by ER- and EER-to-Relational Mapping

  16. Relational Database Design Using ER-to-Relational Mapping • ER-to-Relational Mapping Algorithm • Discussion and Summary of Mapping for Model Constructs

  17. Mapping EER Model Constructs to Relations • Mapping of Specialization of Generalization • Mapping of Shared Subclasses (Multiple Inheritance) • Mapping of Categories (Union Types)

  18. Summary

  19. Chapter 8 SQL-99: Schema Definition, Basic Constraints, and Queries

  20. SQL Data Definition and Data Types • Schema and Catalog Concepts in SQL • The CREATE TABLE Command in SQL • Attribute Data Types and Domains in SQL

  21. Specifying Basic Constraints in SQL • Specifying Attribute Constraints and Attribute Defaults • Specifying Key and Referential Integrity Constraints • Giving Names to Constraints • Specifying Constraints on Tuples Using CHECK

  22. Schema Change Statements in SQL • The DROP Command • The ALTER Command

  23. Basic Queries in SQL • The SELECT-FROM-WHERE Structure of Basic SQL Queries • Ambiguous Attribute Names, Aliasing, and Tuple Variables • Unspecified WHERE Clause and Use of the Asterisk • Tables as Sets in SQL • Substring Pattern Matching and Arithmetic Operators • Ordering of Query Results

  24. More Complex SQL Queries • Comparisons Involving NULL and Three-Valued Logic • Nested Queries, Tuples, and Set/Multiset Comparisions • Correlated Nested Queries • The EXISTS and UNIQUE Functions in SQL • Explicit Sets and Renaming of Attributes in SQL • Joined Tables in SQL • Aggregate Functions in SQL • Grouping: The GROUP BY and HAVING Clauses • Discussion and Summary of SQL Queries

  25. Insert, Delete, and Update Statements in SQL • The INSERT Command • The DELETE Command • The UPDATE Command

  26. Additional Features of SQL

  27. Summary

  28. Chapter 9 More SQL: Assertions, Views, and Programming Techniques

  29. Specifying General Constraints as Assertions

  30. Views (Virtual Tables) in SQL • Concept of a View in SQL • Specification of Views in SQL • View Implementation and View Update

  31. Database Programming: Issues and Techniques • Approaches to Database Programming • Impedence Mismatch • Typical Sequence of Interaction in Database Programming

  32. Embedded SQL, Dynamic SQL, and SQLJ • Retrieving Single Tuples with Embedded SQL • Retrieving Multiple Tuples with Embedded SQL Using Cursors • Specifying Queries at Runtime Using Dynamic SQL • SQLJ: Embedding SQL Commands in JAVA • Retrieving Multiple Tuples in SQLJ Using Iterators

  33. Database Programming with Function Calls: SQL/CLI and JDBC • Database Programming with SQL/CLI Using C as the Host Language • JDBC: SQL Function Calls for JAVA Programming

  34. Database Stored Procedures and SQL/PSM • Database Stored Procedures and Functions • SQL/PSM: Extending SQL for Specifying Persistent Stored Modules

  35. Summary

  36. Chapter 10 Functional Dependencies and Normalization for Relational Databases

  37. Informal Design Guidelines for Relation Schema • Semantics of the Relation Attributes • Redundant Information in Tuples and Update Anomalies • Null Values in Tuples • Generation of Spurious Tuples • Summary and Discussion of Design Guidelines

  38. Functional Dependencies • Definition of Functional Dependency • Inference Rules for Functional Dependencies • Equivalence of Sets of Functional Dependencies • Minimal Sets of Functional Dependencies

  39. Normal Forms Based on Primary Keys • Normalization of Relations • Practical Use of Normal Forms • Definition of Keys and Attributes Participating in Keys • First Normal Form • Second Normal Form • Third Normal Form

  40. General Definitions of Second and Third Normal Forms • General Definition of Second Normal Form • General Definition of Third Normal Form • Interpreting the General Definition of Third Normal Form

  41. Boyce-Codd Normal

  42. Summary

  43. Chapter 11 Relational Database Design Algorithms and Further Dependencies

  44. Properties of Relational Decompositions • Relation Decomposition and Insufficiency of Normal Forms • Dependency Preservation Property of a Decomposition • Lossless (Nonadditive) Join Property of a Decomposition • Testing Binary Decomposition for the Nonadditive Join Property • Successive Lossless (Nonadditive) Join Decompositions

  45. Algorithms for Relational Database Schema Design • Dependency-Preserving Decomposition into 3NF Schemas • Lossless (Nonadditive) Join Decomposition into BCNF Schemas • Dependency-Preserving and Nonadditive (Lossless) Join Decomposition into 3NF Schemas • Problems with Null Values and Dangling Tuples • Discussion of Normalization Algorithms

  46. Multivalued Dependencies and Fourth Normal Form • Formal Definition of Multivalued Dependency • Inference Rules for Functional and Multivalued Dependencies • Fourth Normal Form • Lossless (Nonadditive) Join Decomposition into 4NF Relations

  47. Join Dependencies and Fifth Normal Form

  48. Inclusion Dependencies

  49. Other Dependencies and Normal Forms • Template Dependencies • Domain-Key Normal Form

More Related