1 / 24

Introducing Databases

Introducing Databases. Business Rules. A database is framed to fit the ways in which an organization runs its business. Business rules may affect several aspects of database design, including: Field ranges and valid values Types of table relationships Degree of relationships

merrill-bean
Télécharger la présentation

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

  2. Business Rules • A database is framed to fit the ways in which an organization runs its business. • Business rules may affect several aspects of database design, including: • Field ranges and valid values • Types of table relationships • Degree of relationships • Degree of participation • Synchronization of tables

  3. Importance of Relationships • Relationships allow users to establish views based on multiple base tables. • Relationships help to reduce data redundancy and eliminate duplicate data, thus reinforcing data integrity.

  4. Relationships • We can build a relationship between tables if we can relate the records in one table with the records in the joining table. • Two methods for building a relationship: • Linking primary and foreign keys • Linking tables via a third table called a linking table or associative table

  5. Categorizing Relationships • We categorize relationships between tables in three ways: • The type of relationship between tables • The way that each table in relationship participates in that relationship • The degree of participation that each table participates in a relationship

  6. Relationship Participation • There are two ways that we categorize relationships based on participation: • Mandatory Participation: If a user MUST enter at least one record into a parent table before s/he may enter records in a child table. • Optional Participation: If a user MAY enter records in a child table without entering records in the parent table.

  7. Degrees of Participation • We calculate a table's degree of participation by: • The minimum number of records it must associate with a single record in the related table. • The maximum number of records that a related table may associate with a single record in the given table. • Think of the degree of participation as the minimum and maximum number of relationships for a single record in a table.

  8. Example of Degree of Association • Assume that for a Department, advisors are assigned at least 1 student and up to 50 students, but no more. • The degree of participation of the Advisor Table would be 1,50. That is, an advisor must be assigned to at least one student in the Student Table, but has a limit of 50 students in the Student Table.

  9. Different Types of Relationships • One-to-One Relationship (1:1) • One-to-Many Relationship (1:N) • Many-to-Many Relationship (N:N)

  10. One-To-One Relationships (1:1) • In a one-to-one relationship (1:1), we relate one and only one record from a parent table to one and only one record in a second table (a child table). • To create a 1:1 relationship, we copy the primary key of a parent table into a child table, where it becomes a foreign key. • This type of relationship is unique because both tables share the same primary key. The primary key in the child table serves both as that table's primary key and a foreign key .

  11. Example of a 1:1 Relationship Employee Table Employee ID is the Primary Key for both tablesand also a Foreign Key in the Compensation Table. Compensation Table - Adapted from Figure 3.13 from Herenandez

  12. One-To-Many Relationships (1:N) • In a one-to-many (1:N) relationship, we relate a record in one table (a parent table) to many records in a second table (a child table). • To create a 1:N relationship, we copy the primary key of a parent table into a child table, where it becomes a foreign key. • This type of relationship is the most common type of relationship in the relational database model.

  13. Example of a 1:N Relationship Agents Table Clients Table Agent ID is the Primary Key in the Agents Tableand a Foreign Key in the Clients Table. - Adapted from Figure 3.14 from Herenandez

  14. Many-To-Many Relationships (N:N) • In a many-to-many relationship, we relate many records in one table to many records in a second table. • We cannot inherently create a N:N relationship. Instead, we can resolve a N:N relationship by copying the primary keys of each table into a third table, called a linking (associative) table. Together, the copied keys form a composite primary key. Individually, they serve as foreign keys for the other table.

  15. Example of Resolving an N:N Relationship

  16. Primary Keys • A primary key is a field or group of fields that uniquely identifies a record. A primary key comprised of two or more fields is called a composite primary key. Every table must have a primary key! • The most important key in a table: • Uniquely identifies a specific record throughout a database • Identifies a specific table throughout the database • Enforces table-level integrity • Helps to establish relationships between tables

  17. Foreign Keys • A foreign key is important when we establish relationships between tables. • To create a foreign key, you would take a primary key from one table and copy it in a second table. In the second table, the key becomes a foreign key. • Foreign keys enforce relationship-level integrity – values in one table's foreign key field must match exactly with the corresponding values of a second table's primary key field.

  18. Example of Primary & Foreign Keys Agents Table Clients Table Agent ID is the Primary Key in the Agents Tableand a Foreign Key in the Clients Table. - Adapted from Figure 3.11 from Herenandez

  19. Data Integrity • "Data integrity refers to the validity, consistency, and accuracy of the data in a database." (Hernandez, p. 71) • Four Types of Data Integrity: • Table-level integrity • Field-level integrity • Relationship-level integrity • Business rules

  20. Table-Level Integrity • Also known as entity integrity • Ensures there are no duplicate records throughout a database • Makes sure that primary keys with a table are unique never null

  21. Field-Level Integrity • Also known as domain integrity • Guarantees that that structure of each field is sound: • Values are "valid, consistent and accurate" (Hernandez, p. 71) • Values of the same type (for instance, we would define fields related to an academic major in a consistent manner throughout the database).

  22. Relationship-Level Integrity • Also known as referential integrity • Checks to make sure that the relationships between tables are sound. • Also, ensures that records in related tables are synchronized when someone enters data, deletes data or otherwise manipulates it.

  23. Questions?

  24. References • geekgirl's plain-english computing (website): http://www.geekgirls.com/menu_databases.htm • Database Design for Mere Mortals, 2nd Edition by Michael Hernandez (Addison-Wesley, 2004)

More Related