Create a discussion thread (with your name) and answer the following question(s):

Discussion 1 (Chapter 5): Discuss the characteristics of relations that make them different from ordinary tables and files.

Instructions: Your response to the initial question should be 250-300 words. Next respond to two postings provided by your classmates. The first post should be made by Wednesday 11:59 p.m., EST. I am looking for active engagement in the discussion. Please engage early and often. You are require to create your initial thread in order to view and respond to the threads posted by other students. There must be at least one APA formatted reference (and APA in-text citation) to support the thoughts in the post as needed. Do not use direct quotes, rather rephrase the author’s words and continue to use in-text citations.

The Relational Data Model and

Relational Database Constraints

Dr. Buleje

Slide 1- 1

Slide 5- 2

Outline

Relational Model Concepts

Relational Model Constraints and Relational

Database Schemas

Update Operations

Slide 5- 3

Relational Model Concepts

The relational Model of Data is based on the concept of a

Relation

We will review the essentials of the formal relational

model

In practice, there is a standard model based on SQL

Slide 5- 4

Relational Model Concepts

A Relation is a mathematical concept based on

the ideas of sets

The model was first proposed by Dr. E.F. Codd of

IBM Research in 1970 in the following paper:

“A Relational Model for Large Shared Data

Banks,” Communications of the ACM, June 1970

Slide 5- 5

Informal Definitions

Relation looks like a table of values.

Contains a set of rows.

The data elements in each row represent certain facts that correspond to a real-world entity or relationship

Each column has a column header

Slide 5- 6

Example of a Relation

Slide 5- 7

Informal Definitions

Key of a Relation

The key

In the STUDENT table, SSN is the key

Slide 5- 8

Formal Definitions – Schema

The Schema (or description) of a Relation:

Denoted by R(A1, A2, …..An)

R is the name of the relation

The attributes of the relation are A1, A2, …, An

Example:

CUSTOMER (Cust-id, Cust-name, Address, Phone#)

CUSTOMER is the relation name

Defined over the four attributes: Cust-id, Cust-name,

Address, Phone#

Each attribute has a domain or a set of valid values.

Slide 5- 9

Formal Definitions – Tuple

A tuple

Domain

A row in the CUSTOMER relation is a 4-tuple and would

consist of four values, for example:

<632895, “John Smith”, “101 Main St. Atlanta, GA 30332”,

“(404) 894-2000”>

This is called a 4-tuple as it has 4 values

A tuple (row) in the CUSTOMER relation.

Slide 5- 10

Formal Definitions – Domain

A domain: definition

Example

A domain also has a data-type or a format defined for it.

The attribute name designates the role played by a domain in a relation

Slide 5- 11

Formal Definitions – State

The relation state

Example: attribute Cust-name is defined over the domain of character strings of maximum length 25

dom(Cust-name) is varchar(25)

Slide 5- 12

Definition Summary

Informal Terms Formal Terms

Table Relation

Column Header Attribute

All possible Column

Values

Domain

Row Tuple

Table Definition Schema of a Relation

Populated Table State of the Relation

Slide 5- 13

Example – A relation STUDENT

Slide 5- 14

Characteristics Of Relations

Ordering of tuples in a relation r(R):

Tuples are not considered to be ordered

Appear to be in the tabular form.

Ordering of attributes in a relation schema R (and of values within each tuple):

We will consider the attributes in R(A1, A2, …, An) and the values in t=<v1, v2, …, vn> to be ordered .

Slide 5- 15

Same state as previous Figure (but

with different order of tuples)

CONSTRAINTS

DEFINITION: Constraints

They are of three main types:

1. Inherent or Implicit Constraints

2. Schema-based or Explicit Constraints

3. Application based or semantic constraints

Slide 5- 16

Slide 5- 17

Relational Integrity Constraints

Constraints are conditions that must hold on all valid

relation states.

There are three main types of (explicit schema-based)

constraints that can be expressed in the relational model:

Key constraints

Entity integrity constraints

Referential integrity constraints

Another schema-based constraint is the domain

constraint

Every value in a tuple must be from the domain of its

attribute (or it could be null, if allowed for that attribute)

Slide 5- 18

Relational Database Schema

Relational Database Schema:

A set S of relation schemas that belong to the

same database.

S is the name of the whole database schema

S = {R1, R2, …, Rn} and a set IC of integrity

constraints.

R1, R2, …, Rn are the names of the individual

relation schemas within the database S

Slide 5- 19

COMPANY Database Schema

Relational Database State

A relational database state DB of S is a set of

relation states DB = {r1, r2, …, rm} such that each ri is

a state of Ri and such that the ri relation states satisfy

the integrity constraints specified in IC.

A relational database snapshot or instance.

Slide 5- 20

Slide 5- 21

Populated database state

Each relation: many tuples in its current relation state

The relational database state is a union of all the

individual relation states

Basic operations for changing the database:

INSERT

DELETE

MODIFY

Slide 5- 22

Populated database state for COMPANY

Slide 5- 23

Summary Presented Relational Model Concepts

Definitions

Characteristics of relations

Discussed Relational Model Constraints and Relational Database

Schemas

Domain constraints

Key constraints

Entity integrity

Referential integrity

Described the Relational Update Operations

Insert

Delete

Modify