1 / 33

Relational Databases

Relational Databases. What Is A Relational Database?. Created by Dr. E.F. Codd A database is a collection of tables and other “objects” that are related and collectively describe an entity. Other objects are forms, queries, reports, modules. . Advantages Of Database.

gay-everett
Download Presentation

Relational Databases

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. Relational Databases

  2. What Is A Relational Database? • Created by Dr. E.F. Codd • A database is a collection of tables and other “objects” that are related and collectively describe an entity. • Other objects are forms, queries, reports, modules.

  3. Advantages Of Database • Store data only once - lower cost • Reduces errors • Eliminate data redundancy • Avoid duplicate processing • Simplify maintenance • Centralize data management & security

  4. Advantages Of Database • Offer greater flexibility • Simplify report modifications and updates • Provide ad-hoc query capabilities • Cross functional data analysis • Permits multiple use and simultaneous data analysis

  5. Disadvantages Of Database Systems • Increased costs (usually offset by savings) • Hardware requirements • The software itself • Database administrator

  6. Disadvantages Of Database Systems • Centralized management and security control • System operation becomes critical • Errors in data entry effects all users • Potential disputes over data ownership

  7. Tables • Fundamental storage structures for data • Tables are sometimes called files • Looks like a spreadsheet • Consist of rows (TUPLES) which are the equivalent of a record and columns (ATTRIBUTES) which are the equivalent of a field

  8. Table Terms • Primary key- An attribute (column) that uniquely identifies a given row so the system can distinguish each record of a table PRIMARY KEYS CAN’T BE NULL! • Foreign key - An attribute (column) in one table that must match the primary key in another table. Used to join tables together. • Composite (concatenated) key - A primary key that consists of more than a single column.

  9. Table Rules • No duplicate tables • No duplicate rows or columns in table • Sequence of rows/columns doesn’t matter • Each table must have a primary key • The primary key CANNOT be null • Each table is about ONE concept

  10. Joining Tables Customer ID Company Name Contact Phone Number Credit Limit Invoice ID Invoice Date Order Date Customer ID Employee ID Customer PO Invoice ID Inventory ID Quantity Unit Price Discount Inventory ID Item ID Caffeinated Price On Hand

  11. One-to-One Relationships • Only one matching record • Uses primary key for both tables • Use to limit access to information

  12. One-to-One Relationships (cont.)

  13. One-to-Many Relationships • Most common type of relationship • Related between primary and foreign keys • Can have many related records • Referential integrity prevents orphaned records

  14. One-to-Many Relationships (cont.)

  15. Many-to-Many Relationships • One order, many products • One product, many orders • Not directly supported between tables • Use an Intersection table to relate

  16. Many-to-Many Relationships (cont.)

  17. Relational Database Design Relationships and Referential Integrity • Create relationships between the tables • Set referential integrity

  18. Queries • Allows you to ask questions about your data examples: • How many employees earn more than $40,000? • Which customer invoices are more than 60 days old ? • SQL (structured query language pronounced seequel) is the underlying “how” of making queries of databases

  19. Queries • Access does Queries by using QBE or SQL. QBE makes it easy to pick up simple queries, but becoming accomplished enough to write more complex queries takes much time and effort.

  20. Queries • Queries are where the real POWER of adatabase lies • Relationships between tables must be properly established in order for queries to work correctly in QBE

  21. Forms • Shows data from a table in a format that is more attractive and easier to understand • Access forms can be made to resemble paper forms that users are already familiar with • Forms are commonly used to input, display, or change data

  22. Reports • Hard copy of output of information contained in the database • We often create reports that are the result of queries we made • Access allows you to create board room quality reports

  23. Modules • Visual Basic for Application code that can executed at the click of a button or when a form is opened. They are useful in developing more sophisticated internal controls into an Access based information system.

  24. REVIEW TABLE RULES • No duplicate tables • No duplicate rows or columns in table • Sequence of rows/columns doesn’t matter • Each table must have a primary key • The primary key CANNOT be null • Each table is about ONE concept

  25. DATABASE NORMALIZATION Database normalization is the process of ensuring that each table contains data about only ONE concept.

  26. REPEATING GROUPS AND NORMALIZATION TO FIRST NORMAL FORM (1NF) SALES-INFORMATION Invoice# Date Customer# Salesperson Region Item# Description Price Quantity 100110021003 7/1/927/1/927/1/92 456329897 JohnMaryAl WestEastWest 121348540 WidgetGearBolt $2.25$3.70$0.40 45105 INVOICE-ITEMS (1NF) Invoice# Item# Description Price Quantity INVOICES (2NF) Invoice# Date Customer# Salesperson Region

  27. WHAT IS THE PROBLEM WITH DESCRIPTION/PRICE? • Insert anomalies • Delete anomalies • Update anomalies

  28. DECOMPOSITION OF A FIRST-NORMAL-FORM (1NF) TABLE INVOICE-ITEMS (1NF) Invoice# Item# Description Price Quantity ITEMS (2NF) INVOICE-ITEMS-QTY (2NF) Item# Description Price Invoice# Item# Quantity You can only have a 2nd Normal Form problem if there is a composite primary Key

  29. DATABASE NORMALIZATION Functional dependency is key in understanding the process of normalization. Functional dependency means that if there is only one possible value of Y for every value of X, then Y is functionally dependent on X.

  30. DATABASE NORMALIZATION Think of an invoice table. Two fields would be invoice # and date. Which field is functionally dependent on the other? INVOICE# DATE Date is functionally dependent on invoice number.

  31. Functional Dependency is “good”. With functional dependency the primary key (Attribute A) determines the value of all the other non-key attributes (Attributes B,C,D,etc.) • Transitive dependency is “bad”. Transitive dependency exists if the primary key (Attribute A) determines non-key Attribute B, and Attribute B determines non-key Attribute C.

  32. DECOMPOSITION OF A SECOND-NORMAL-FORM (2NF) TABLE SALES (2NF) Invoice# Date Customer# Salesperson Region This is a transitive dependency which must be eliminated for 3NF INVOICES (3NF) SALESPERSON-REGION (3NF) Invoice# Date Customer# Salesperson Salesperson Region

  33. SUMMARY OF 3NF RELATIONS FOR SALES DATABASE INVOICES (3NF) SALESPERSON-REGION (3NF) Invoice# Date Customer# Salesperson Salesperson Region 100110021003 7/1/927/1/927/1/92 456329897 JohnMaryAl JohnMaryAl WestEastWest INVOICE-ITEMS-QTY (3NF) ITEMS (3NF) Invoice# Item# Quantity Item# Description Price 100110021003 121348540 45105 121348540 WidgetGearBolt $2.25$3.70$0.40

More Related