Introducing Databases

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)