The Relational Data Model: Understanding Relations, Schemas, and States, Study Guides, Projects, Research of Design

Download The Relational Data Model: Understanding Relations, Schemas, and States and more Study Guides, Projects, Research Design in PDF only on Docsity!

The Relational Data Model

(ALL the Vocabulary)

Lecture 2

The Relational Data Model

A Quick Reminder

• One of the key features of a DBMS is use of data

models to support “data independence”

• The conceptual representation is independent of

underlying storage and/or operation implementation

The Relational Data Model

ER Diagrams

Relations

The Relational Model

Codd, Edgar F. "A relational model of data for large shared data banks." Communications of the ACM 13.6 (1970): 377-387. The Relational Data Model “Future users of large data banks must be protected from having to know how the data is organized in the machine (the internal representation)… Activities of users at terminals and most application programs should remain unaffected when the internal representation of data is changed and even when some aspects of the external representation are changed…”

Motivation

• A formal mathematical basis for databases
  • Set theory and first-order predicate logic
  • Allows scientists to advance theoretically
• A foundation for efficient and usable database management systems - Allows companies/developers to advance end- user products
• Note: some aspects of the model are not adhered to by modern RDBMSs The Relational Data Model

A Relation

A relation consists of… i. its schema , describing structure ii. its state , or current populated data The Relational Data Model

Name SSN Phone Address Age GPA

Ben Bayer 305 - 61 - 2435 555 - 1234 1 Foo Lane 19 3.

Chung-cha Kim 422 - 11 - 2320 555 - 9876 2 Bar Court 25 3.

Barbara Benson 533 - 69 - 1238 555 - 6758 3 Baz Blvd 19 3.

STUDENT

Schema

State

Relational Schema

• Relation name STUDENT
• Ordered list of n attributes (columns; degree n or n - ary) Each with a corresponding domain (set of valid atomic values)
  • dom(SSN) = “###-##-####”
  • dom(GPA) = [0, 4]
• Notation: NAME(A 1 , A 2 , … An)

STUDENT(Name, SSN, Phone, Address, Age, GPA)

The Relational Data Model

Name SSN Phone Address Age GPA

STUDENT

What is the degree

of STUDENT?

Exercise

Diagrammatically produce a relation HAT according

to the following schema; the relation state should

have at least three tuples

HAT(Team, Size, Color)

• dom(Team) = { RedSox, Bruins, Celtics, Patriots,

Revolution }

• dom(Size) = { S, M, L, XL }
• dom(Color) = { Black, Blue, White, Red, Green,

Yellow }

How many tuples are possible in this relation?

The Relational Data Model

Answer

|dom(T eam)| ⇥ |dom(Size)| ⇥ |dom(Color)| 5 ⇥ 4 ⇥ 6 120 The Relational Data Model

Team Size Color

RedSox M Red

Revolution S White

Bruins XL Yellow

HAT

NULL

• NULL is a special value that may be in the attribute domain
• Several possible meanings - E.g. unknown, not available, does not apply, undefined, …
• Best to avoid
  • Else deal with caution The Relational Data Model

Value Structure in Tuples

• Each value should be atomic – no composite or multi-valued attributes - Composite: “one column, many parts” - Multi-valued: “one column, multiple values”
• Convention called 1NF ( first normal form )
  • More on this later in the course The Relational Data Model

Violation of 1NF: Multi-Valued

The Relational Data Model

SSN Class

305 - 61 - 2435 COMP

422 - 11 - 2320 COMP

533 - 69 - 1238 MATH

305 - 61 - 2435 MATH

422 - 11 - 2320 BIOL

CLASS

SSN Class

305 - 61 - 2435 COMP355,

MATH 650

422 - 11 - 2320 COMP355,

BIOL

533 - 69 - 1238 MATH

CLASS

vs.

Model Constraints

Categories of restrictions on data in a relational database

1. Inherent in the data model (implicit)
2. Schema-based (explicit)
3. Application-based (or triggers/assertions)
4. Data dependencies Relates to “goodness” of database design; we will revisit in normalization The Relational Data Model

Domain Constraints

Within each tuple, the value of each attribute A must be an atomic value from the domain dom(A) Schema must dictate whether or not a NULL value is allowed for each attribute More later on standard data types in SQL The Relational Data Model N U LL ? 2 dom(A)

Key Constraints

A key is a set of attribute(s) satisfying two properties:

1. Two distinct tuples in any state of the relation cannot have identical values for all the attributes of the key ( superkey )
2. No attribute can be removed from the key and still have #1 hold ( minimal superkey ) A relation may have multiple keys (each is a candidate key ). Relations commonly have a primary key (underlined, PK; typically small number of attributes, used to identify tuples), and may also have some number of additional unique key(s). The Relational Data Model