Introducing Databases CSCI N 207 Data Analysis Using

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 participation Synchronization of tables CSCI N 207: Data Analysis Using Spreadsheets Copyright © 2004 Department of Computer & Information Science

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. CSCI N 207: Data Analysis Using Spreadsheets Copyright © 2004 Department of Computer & Information Science

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 CSCI N 207: Data Analysis Using Spreadsheets Copyright © 2004 Department of Computer & Information Science

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 CSCI N 207: Data Analysis Using Spreadsheets Copyright © 2004 Department of Computer & Information Science

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. CSCI N 207: Data Analysis Using Spreadsheets Copyright © 2004 Department of Computer & Information Science

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. CSCI N 207: Data Analysis Using Spreadsheets Copyright © 2004 Department of Computer & Information Science

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. CSCI N 207: Data Analysis Using Spreadsheets Copyright © 2004 Department of Computer & Information Science

Different Types of Relationships • One-to-One Relationship (1: 1) • One-to-Many Relationship (1: N) • Many-to-Many Relationship (N: N) CSCI N 207: Data Analysis Using Spreadsheets Copyright © 2004 Department of Computer & Information Science

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. CSCI N 207: Data Analysis Using Spreadsheets Copyright © 2004 Department of Computer & Information Science

Example of a 1: 1 Relationship Employee ID Employee First Name Employee Last Name 100 Zachary Erlich 101 Susan Mc. Clain 102 Joe Rosales Employee ID Hourly Rate Commission Rate 100 25. 00 5. 0% 101 19. 75 3. 5% 102 22. 50 Employee Table 5. 0% Compensation Table Employee ID is the Primary Key for both tables and also a Foreign Key in the Compensation Table. - Adapted from Figure 3. 13 from Herenandez CSCI N 207: Data Analysis Using Spreadsheets Copyright © 2004 Department of Computer & Information Science

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. CSCI N 207: Data Analysis Using Spreadsheets Copyright © 2004 Department of Computer & Information Science

Example of a 1: N Relationship Agent ID Agent First Name Agent Last Name 100 Mike Hernandez 05/16/95 101 Greg Piercy 10/15/95 102 Katherine Ehrlich 03/01/96 Client ID Agent ID Clients Table Hire Date Agents Table Client First Name Client Last Name 9001 100 Stewart Jameson 9002 100 Shannon Mc. Lain 9003 102 Estella Pundt Agent ID is the Primary Key in the Agents Table and a Foreign Key in the Clients Table. - Adapted from Figure 3. 14 from Herenandez CSCI N 207: Data Analysis Using Spreadsheets Copyright © 2004 Department of Computer & Information Science

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. CSCI N 207: Data Analysis Using Spreadsheets Copyright © 2004 Department of Computer & Information Science

Example of Resolving an N: N Relationship CSCI N 207: Data Analysis Using Spreadsheets

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 CSCI N 207: Data Analysis Using Spreadsheets Copyright © 2004 Department of Computer & Information Science

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. CSCI N 207: Data Analysis Using Spreadsheets Copyright © 2004 Department of Computer & Information Science

Example of Primary & Foreign Keys Agent ID Agent First Name Agent Last Name 100 Mike Hernandez 05/16/95 101 Greg Piercy 10/15/95 102 Katherine Ehrlich 03/01/96 Client ID Agent ID Clients Table Hire Date Agents Table Client First Name Client Last Name 9001 100 Stewart Jameson 9002 100 Shannon Mc. Lain 9003 102 Estella Pundt Agent ID is the Primary Key in the Agents Table and a Foreign Key in the Clients Table. - Adapted from Figure 3. 11 from Herenandez CSCI N 207: Data Analysis Using Spreadsheets Copyright © 2004 Department of Computer & Information Science

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 CSCI N 207: Data Analysis Using Spreadsheets Copyright © 2004 Department of Computer & Information Science

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 CSCI N 207: Data Analysis Using Spreadsheets Copyright © 2004 Department of Computer & Information Science

Field-Level Integrity • Also known as domain integrity • Guarantees 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). CSCI N 207: Data Analysis Using Spreadsheets Copyright © 2004 Department of Computer & Information Science

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. CSCI N 207: Data Analysis Using Spreadsheets Copyright © 2004 Department of Computer & Information Science

Questions? CSCI N 207: Data Analysis Using Spreadsheets Copyright © 2004 Department of Computer

References • geekgirl's plain-english computing (website): http: //www. geekgirls. com/menu_databases. htm • Database Design for Mere Mortals, 2 nd Edition by Michael Hernandez (Addison. Wesley, 2004) CSCI N 207: Data Analysis Using Spreadsheets Copyright © 2004 Department of Computer & Information Science