Research and Data Modelling

Snug

lecture3.pdf

Home >Information Systems homework help >Research and Data Modelling

Quick Review and

Chapter 3

The Relational Database Model

Learning Objectives

• In this chapter, one will learn:

• That the relational database model offers a logical view of data

• About the relational model’s basic component: relations

• That relations are logical constructs composed of rows (tuples) and columns (attributes)

• That relations are implemented as tables in a relational DBMS

• In this chapter, one will learn:

• About relational database operators, the data dictionary, and the system catalog

• How data redundancy is handled in the relational database model

• Why indexing is important

Learning Objectives

• In this chapter, one will learn:

• About relational database operators, the data dictionary, and the system catalog

• How data redundancy is handled in the relational database model

• Why indexing is important

Quick R E V I E W - from last week’s topic

Prepared By: Antoinette Cevenini

1. What are business rules, and why are they important for the designer?

2. Describe the following three views with reference to database modelling and degrees of abstraction: External - Conceptual - Internal - Physical

3. What is an entity?

4. List 3 types of data constraints, and provide an example

5. What is the differences between an object and a class in the object oriented data model (OODM)?

A Logical View of Data

• Relational database model enables logical representation of the data and its relationships

• Logical simplicity yields simple and effective database design methodologies

• Facilitated by the creation of data relationships based on a logical construct called a relation

Table 3.1 - Characteristics of a Relational Table

Keys

• Consist of one or more attributes that determine other attributes

• Used to:

• Ensure that each row in a table is uniquely identifiable

• Establish relationships among tables and to ensure the integrity of the data

• Primary key (PK): Attribute or combination of attributes that uniquely identifies any given row

- PK cannot contain Nulls and cannot be duplicated

- Null: Absence of any data value that could represent: • An unknown attribute value

• A known, but missing, attribute value

• A inapplicable condition

Determination

• State in which knowing the value of one attribute makes it possible to determine the value of another

• Is the basis for establishing the role of a key

• Based on the relationships among the attributes

Example of student table

STD_IDSTD_NAME, STD_DOB, STD_ADDRESS, STD_CONTACT

Dependencies

• Functional dependence: Value of one or more attributes determines the value of one or more other attributes

• Determinant: Attribute whose value determines another

• Dependent: Attribute whose value is determined by the other attribute

• Full functional dependence: Entire collection of attributes in the determinant is necessary for the relationship

Example of student table : each of these attributes are dependents on STD_ID STD_IDSTD_NAME, STD_DOB, STD_ADDRESS, STD_CONTACT

Types of Keys :

• Key attribute: Attribute that is a part of a key

• Composite key: Key that is composed of more than one attribute

• Superkey: key that can uniquely identify any row in the table

• Candidate key: minimal superkey

• Foreign key: primary key of one table that has been placed into another table to create a common attribute

• Secondary key: key used strictly for data retrieval purposes

15 © 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

Examples

Integrity Rules & Constraints

•Entity integrity: Condition in which each row in the table has its own unique identity

• All of the values in the primary key must be unique

• No key attribute in the primary key can contain a null

Example:

No invoice can have a duplicate number, nor it can be null

Example: -- Referential Integrity Rules

Entity Integrity Description

Requirement A foreign key may have either a null entry or a entry that matches a primary key value in a table to which it is related

Purpose It is possible for an attribute not to have a corresponding value but it is impossible to have an invalid entry

It is impossible to delete row in a table whose primary keys has mandatory matching foreign key values in another table

Example It is impossible to have invalid sales representative number

Every reference to an entity instance by another entity instance is valid.

Figure 3.2 - An Example of a Simple Relational Database

Figure 3.3 - An Illustration of Integrity Rules

Prepared By: Antoinette Cevenini 21

Review Integrity Rules

Ways to Handle Nulls

•Flags: Special codes used to indicate the absence of some value

•NOT NULL constraint - Placed on a column to ensure that every row in the table has a value for that column

•UNIQUE constraint - Restriction placed on a column to ensure that no duplicate values exist for that column

Relationships within the Relational Database

•1:M relationship - Norm for relational databases

•1:1 relationship - One entity can be related to only one other entity and vice versa

•Many-to-many (M:N) relationship - Implemented by creating a new entity in 1:M relationships with the original entities

•Composite entity (Bridge or associative entity): Helps avoid problems inherent to M:N relationships, includes the primary keys of tables to be linked

Prepared By: Antoinette Cevenini 24

Assignment item 2 on Modelling

Figure 3.26 - Changing the M:N Relationship to Two 1:M Relationships

Prepared By: Antoinette Cevenini

28Prepared By: Antoinette Cevenini 28

More on Relationships

The 1:1 Relationship - considerations

• One entity related to only one other entity, and vice versa

• Sometimes means that entity components were not defined properly

• Could indicate that two entities actually belong in the same table

• Certain conditions absolutely require their use

Data Redundancy Revisited

•The relational database facilitates control of data redundancies through use of foreign keys

•Common attributes that are shared by tables

•To be controlled except the following circumstances:

•Sometimes data redundancy must be increased to make the database serve crucial information purposes

•Sometimes data redundancy exists to preserve the historical accuracy of data

Figure 3.30 - The Relational Diagram for the Invoicing System

Index

•Orderly arrangement to logically access rows in a table

•Index key: Index’s reference point that leads to data location identified by the key

•Unique index: Index key can have only one pointer value associated with it

•Each index is associated with only one table

Data Dictionary and the System Catalog

•Data dictionary: Description of all tables in the database created by the user and designer

•System catalog: System data dictionary that describes all objects within the database

•Homonyms and synonyms must be avoided to lessen confusion

-Homonym: Same name is used to label different attributes

-Synonym: Different names are used to describe the same attribute

Relational Algebra

•Theoretical way of manipulating table contents using relational operators

•Relvar: Variable that holds a relation

•Heading contains the names of the attributes and the body contains the relation

•Relational operators have the property of closure

•Closure: Use of relational algebra operators on existing relations produces new relations

Relational Set Operators

•Unary operator that yields a horizontal subset of a table

Select (Restrict)

•Unary operator that yields a vertical subset of a table

Project

•Combines all rows from two tables, excluding duplicate rows

•Union-compatible: Tables share the same number of columns, and their corresponding columns share compatible domains

Union

•Yields only the rows that appear in both tables

•Tables must be union-compatible to yield valid results

Intersect

Figure 3.4 - Select

Figure 3.5 - Project

Figure 3.6 - Union

Figure 3.7 - Intersect

Figure 3.8 - Difference

• Difference

• Yields all rows in one table that are not found in the other table

• Tables must be union-compatible to yield valid results

Figure 3.9 - Product

• Product

• Yields all possible pairs of rows from two tables

Relational Set Operators

• Join

• Allows information to be intelligently combined from two or more tables

• Divide

• Uses one 2-column table as the dividend and one single- column table as the divisor

• Output is a single column that contains all values from the second column of the dividend that are associated with every row in the divisor

Types of Joins

• Natural join: Links tables by selecting only the rows with common values in their common attributes

• Join columns: Common columns

• Equijoin: Links tables on the basis of an equality condition that compares specified columns of each table

• Theta join: Extension of natural join, denoted by adding a theta subscript after the JOIN symbol

Types of Joins

Join

Allows information to be intelligently combined from two or more tables

• Natural join: Links tables by selecting only the rows with common values in their common attributes

• Inner join: Only returns matched records from the tables that are being joined

• Outer join: Matched pairs are retained and unmatched values in the other table are left null

• Left outer join: Yields all of the rows in the first table, including those that do not have a matching value in the second table

• Right outer join: Yields all of the rows in the second table, including those that do not have matching values in the first table

Figure 3.10 - Two Tables That Will Be Used in JOIN Illustrations

Figure 3.16 - Divide

Codd’s Relational Database Rules (1 of 2)

Table 13.8

Dr. Codd’s 12 Relational Database Rules

Rule Rule Name Description

1 Information All information in a relational database must be logically represented as column values in rows within tables.

2 Guaranteed access Every value in a table is guaranteed to be accessible through a combination of table name, primary key value, and column name.

3 Systematic treatment of nulls Nulls must be represented and treated in a systematic way, independent of data type.

4 Dynamic online catalog based on the relational model

The metadata must be stored and managed as ordinary data—that is, in tables within the database; such data must be available to authorized users using the standard database relational language.

5 Comprehensive data sublanguage

The relational database may support many languages; however, it must support one well-defined, declarative language as well as data definition, view definition, data manipulation (interactive and by program), integrity constraints, authorization, and transaction management (begin, commit, and rollback).

6 View updating Any view that is theoretically updatable must be updatable through the system.

7 High-level insert, update, and delete The database must support set-level inserts, updates, and deletes.

© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

Codd’s Relational Database Rules (2 of 2)

Table 13.8

Dr. Codd’s 12 Relational Database Rules

Rule Rule Name Description

8 Physical data independence Application programs and ad hoc facilities are logically unaffected when physical access methods or storage structures are changed.

9 Logical data independence Application programs and ad hoc facilities are logically unaffected when changes are made to the table structures that preserve the original table values (changing order of columns or inserting columns).

10 Integrity independence All relational integrity constraints must be definable in the relational language and stored in the system catalog, not at the application level.

11 Distribution independence The end users and application programs are unaware of and unaffected by the data location (distributed vs. local databases).

12 Nonsubversion If the system supports low-level access to the data, users must not be allowed to bypass the integrity rules of the database.

13 Rule zero All preceding rules are based on the notion that to be considered relational, a database must use its relational facilities exclusively for management.

What’s Next?

Read Topic 3 Relational Data Models

Complete Tutorial 3

Read chapters 2 & 3 in your textbook to understand relationships better

This will help you to start working on your modelling assignment

Added by Antoinette Cevenini

Summary

• Tables are the basic building blocks of a relational database

• Keys are central to the use of relational tables

-Each table row must have a primary key

• Although tables are independent, they can be linked by common attributes

• The relational model supports several relational algebra functions

• A relational database performs much of the data manipulation work behind the scenes

• Once you know the basics of relational databases, you can concentrate on design