introducing databases

CSCI N207:CSCI N207: Data Analysis Using SpreadsheetsData Analysis Using Spreadsheets

Copyright Copyright ©2005 ©2005 Department of Computer & Information ScienceDepartment of Computer & Information Science

Introducing DatabasesIntroducing Databases

CSCI N207: Data Analysis Using SpreadsheetsCSCI N207: Data Analysis Using SpreadsheetsCopyright Copyright ©2004 ©2004 Department of Computer & Information ScienceDepartment of Computer & Information Science

Business RulesBusiness Rules

• A database is framed to fit the ways in A database is framed to fit the ways in which an organization runs its which an organization runs its business.business.

• Business rules may affect several Business rules may affect several aspects of database design, including:aspects of database design, including:– Field ranges and valid valuesField ranges and valid values– Types of table relationshipsTypes of table relationships– Degree of relationshipsDegree of relationships– Degree of participationDegree of participation– Synchronization of tablesSynchronization of tables


Importance of RelationshipsImportance of Relationships

• Relationships allow users to establish Relationships allow users to establish views based on multiple base tables.views based on multiple base tables.

• Relationships help to reduce data Relationships help to reduce data redundancy and eliminate duplicate redundancy and eliminate duplicate data, thus reinforcing data integrity.data, thus reinforcing data integrity.


RelationshipsRelationships

• We can build a We can build a relationshiprelationship between between tables if we can relate the records in tables if we can relate the records in one table with the records in the one table with the records in the joining table.joining table.

• Two methods for building a Two methods for building a relationship:relationship:– Linking primary and foreign keysLinking primary and foreign keys– Linking tables via a third table called a Linking tables via a third table called a linking linking

tabletable or or associative tableassociative table


Categorizing RelationshipsCategorizing Relationships

• We categorize relationships between We categorize relationships between tables in three ways:tables in three ways:– The type of relationship between tablesThe type of relationship between tables– The way that each table in relationship The way that each table in relationship

participates in that relationshipparticipates in that relationship– The degree of participation that each table The degree of participation that each table

participates in a relationshipparticipates in a relationship


Relationship ParticipationRelationship Participation

• There are two ways that we There are two ways that we categorize relationships based on categorize relationships based on participation:participation:– Mandatory ParticipationMandatory Participation:: If a user MUST enter If a user MUST enter

at least one record into a parent table before at least one record into a parent table before s/he may enter records in a child table.s/he may enter records in a child table.

– Optional ParticipationOptional Participation: : If a user MAY enter If a user MAY enter records in a child table without entering records in a child table without entering records in the parent table.records in the parent table.


Degrees of ParticipationDegrees of Participation

• We calculate a table's degree of We calculate a table's degree of participation by:participation by:– The minimum number of records it must associate with The minimum number of records it must associate with

a single record in the related table.a single record in the related table.– The maximum number of records that a related table The maximum number of records that a related table

may associate with a single record in the given table.may associate with a single record in the given table.

• Think of the degree of participation as the Think of the degree of participation as the minimum and maximum number of minimum and maximum number of relationships for a single record in a table.relationships for a single record in a table.


Example of Degree of Example of Degree of AssociationAssociation

• Assume that for a Department, advisors Assume that for a Department, advisors are assigned are assigned at least at least 1 student and up to 1 student and up to 50 students, but no more. 50 students, but no more.

• The degree of participation of the The degree of participation of the Advisor Table Advisor Table would be 1,50. That is, an would be 1,50. That is, an advisor must be assigned to at least one advisor must be assigned to at least one student in the student in the Student TableStudent Table, but has a , but has a limit of 50 students in the limit of 50 students in the Student TableStudent Table..


Different Types of Different Types of RelationshipsRelationships

•One-to-One Relationship (1:1)One-to-One Relationship (1:1)•One-to-Many Relationship One-to-Many Relationship

(1:N)(1:N)•Many-to-Many Relationship Many-to-Many Relationship

(N:N)(N:N)


One-To-One Relationships One-To-One Relationships (1:1)(1:1)

• In a one-to-one relationship (1:1), we relate In a one-to-one relationship (1:1), we relate one and only oneone and only one record from a parent table record from a parent table to to one and only one one and only one record in a second record in a second table (a table (a child tablechild table). ).

• To create a 1:1 relationship, we copy the To create a 1:1 relationship, we copy the primary key of a parent table into a child primary key of a parent table into a child table, where it becomes a foreign key.table, where it becomes a foreign key.

• This type of relationship is unique because This type of relationship is unique because both tables share the same primary key. both tables share the same primary key. The primary key in the child table serves The primary key in the child table serves both as that table's primary key and a both as that table's primary key and a foreign key .foreign key .


Example of a 1:1 Example of a 1:1 RelationshipRelationship

Employee Employee IDID

Employee First Employee First NameName Employee Last NameEmployee Last Name

100100 ZacharyZachary ErlichErlich

101101 SusanSusan McClainMcClain

102102 JoeJoe RosalesRosalesEmployee Employee IDID

Hourly Hourly RateRate

Commission Commission RateRate

100100 25.0025.00 5.0%5.0%

101101 19.7519.75 3.5%3.5%

102102 22.5022.50 5.0%5.0%

- Adapted from Figure 3.13 from Herenandez

EmployeeEmployeeTableTable

Compensation TableCompensation TableEmployee IDEmployee ID is the is the Primary KeyPrimary Key for for both both tablestablesand also a and also a Foreign KeyForeign Key in the Compensation Table. in the Compensation Table.


One-To-Many Relationships One-To-Many Relationships (1:N)(1:N)

• In a one-to-many (1:N) relationship, we relate In a one-to-many (1:N) relationship, we relate a record in one table (a a record in one table (a parent tableparent table) to ) to many many recordsrecords in a second table (a in a second table (a child tablechild table). ).

• To create a 1:N relationship, we copy the To create a 1:N relationship, we copy the primary key of a parent table into a child primary key of a parent table into a child table, where it becomes a foreign key.table, where it becomes a foreign key.

• This type of relationship is the most common This type of relationship is the most common type of relationship in the relational database type of relationship in the relational database model.model.


Example of a 1:N Example of a 1:N RelationshipRelationship

Agent Agent IDID

Agent First NameAgent First Name Agent Last Agent Last NameName

Hire Hire DateDate

100100 MikeMike HernandezHernandez 05/16/9505/16/95

101101 GregGreg PiercyPiercy 10/15/9510/15/95

102102 KatherineKatherine EhrlichEhrlich 03/01/9603/01/96Client Client

IDIDAgent IDAgent ID Client First NameClient First Name Client Last Client Last

NameName

90019001 100100 StewartStewart JamesonJameson

90029002 100100 ShannonShannon McLainMcLain

90039003 102102 EstellaEstella PundtPundt


AgentsAgentsTableTable

ClientsClientsTableTable

Agent IDAgent ID is the is the Primary KeyPrimary Key in the Agents Table in the Agents Tableand a and a Foreign KeyForeign Key in the Clients Table. in the Clients Table.


Many-To-Many Relationships Many-To-Many Relationships (N:N)(N:N)

• In a many-to-many relationship, we relate In a many-to-many relationship, we relate many recordsmany records in one table to in one table to many records many records in a second table. in a second table.

• We cannot inherently create a N:N We cannot inherently create a N:N relationship. Instead, we can resolve a N:N relationship. Instead, we can resolve a N:N relationship by copying the primary keys of relationship by copying the primary keys of each table into a third table, called a each table into a third table, called a linking linking (associative) table(associative) table. Together, the copied . Together, the copied keys form a keys form a composite primary keycomposite primary key. . Individually, they serve as foreign keys for Individually, they serve as foreign keys for the other table.the other table.


Example of Resolving an Example of Resolving an N:N RelationshipN:N Relationship


Primary KeysPrimary Keys

• A A primary keyprimary key is a field or group of fields is a field or group of fields that uniquely identifies a record. A primary that uniquely identifies a record. A primary key comprised of two or more fields is key comprised of two or more fields is called a called a composite primary keycomposite primary key. . Every Every table must have a primary keytable must have a primary key!!

• The The most important most important key in a table:key in a table:– Uniquely identifies a specific record throughout a Uniquely identifies a specific record throughout a

databasedatabase– Identifies a specific table throughout the databaseIdentifies a specific table throughout the database– Enforces table-level integrityEnforces table-level integrity– Helps to establish relationships between tablesHelps to establish relationships between tables


Foreign KeysForeign Keys

• A A foreign keyforeign key is important when we establish is important when we establish relationships between tables.relationships between tables.

• To create a foreign key, you would take a To create a foreign key, you would take a primary key from one table and copy it in a primary key from one table and copy it in a second table. In the second table, the key second table. In the second table, the key becomes a foreign key.becomes a foreign key.

• Foreign keys enforce relationship-level Foreign keys enforce relationship-level integrity – values in one table's foreign key integrity – values in one table's foreign key field field must match exactlymust match exactly with the with the corresponding values of a second table's corresponding values of a second table's primary key field.primary key field.


Example of Primary & Foreign Example of Primary & Foreign KeysKeys

Agent Agent IDID

Agent First NameAgent First Name Agent Last Agent Last NameName

Hire Hire DateDate

100100 MikeMike HernandezHernandez 05/16/9505/16/95

101101 GregGreg PiercyPiercy 10/15/9510/15/95

102102 KatherineKatherine EhrlichEhrlich 03/01/9603/01/96Client Client

IDIDAgent IDAgent ID Client First NameClient First Name Client Last Client Last

NameName

90019001 100100 StewartStewart JamesonJameson

90029002 100100 ShannonShannon McLainMcLain

90039003 102102 EstellaEstella PundtPundt


AgentsAgentsTableTable

ClientsClientsTableTable

Agent IDAgent ID is the is the Primary KeyPrimary Key in the Agents Table in the Agents Tableand a and a Foreign KeyForeign Key in the Clients Table. in the Clients Table.


Data IntegrityData Integrity

• "Data integrity refers to the validity, "Data integrity refers to the validity, consistency, and accuracy of the data consistency, and accuracy of the data in a database." (in a database." (HernandezHernandez, p. 71), p. 71)

• Four Types of Data Integrity:Four Types of Data Integrity:– Table-level integrityTable-level integrity– Field-level integrityField-level integrity– Relationship-level integrityRelationship-level integrity– Business rulesBusiness rules


Table-Level IntegrityTable-Level Integrity

• Also known as Also known as entity integrityentity integrity• Ensures there are no duplicate Ensures there are no duplicate

records throughout a databaserecords throughout a database• Makes sure that primary keys with a Makes sure that primary keys with a

table are unique never nulltable are unique never null


Field-Level IntegrityField-Level Integrity

• Also known as Also known as domain integritydomain integrity• Guarantees that that structure of Guarantees that that structure of

each field is sound:each field is sound:– Values are "valid, consistent and accurate" Values are "valid, consistent and accurate"

((HernandezHernandez, p. 71), p. 71)– Values of the same type (for instance, we Values of the same type (for instance, we

would define fields related to an academic would define fields related to an academic major in a consistent manner throughout the major in a consistent manner throughout the database).database).


Relationship-Level IntegrityRelationship-Level Integrity

• Also known as Also known as referential integrityreferential integrity• Checks to make sure that the Checks to make sure that the

relationships between tables are relationships between tables are sound.sound.

• Also, ensures that records in related Also, ensures that records in related tables are synchronized when tables are synchronized when someone enters data, deletes data or someone enters data, deletes data or otherwise manipulates it.otherwise manipulates it.


Questions?Questions?


ReferencesReferences

• geekgirl's plain-english computing geekgirl's plain-english computing (website): (website): http://www.geekgirls.com/menu_databases.htm

• Database Design for Mere Mortals, Database Design for Mere Mortals, 22ndnd Edition Edition by Michael Hernandez by Michael Hernandez (Addison-Wesley, 2004)(Addison-Wesley, 2004)

introducing databases

Documents

parent table

child table

student table

related table

advisor table

given table

joining table

tables primary key