程序代写 - PowCoder代写

The Relational Data Model and Relational Database Constraints
Slide 1- *

Slide 5- *
Informal Definitions

Informally, a relation looks like a table of values.

A relation typically contains a set of rows.

The data elements in each row represent certain facts
that correspond to a real-world entity or relationship
In the formal model, rows are called tuples

Each column has a column header that gives an indication of the meaning of the data items in that column
In the formal model, the column header is called an
attribute name (or just attribute)

Slide 5- *
Example of a Relation

Slide 5- *
Informal Definitions
Key of a Relation:
Each row has a value of a data item (or set of items) that uniquely identifies that row in the table
Called the key
In the STUDENT table, SSN is the key

Slide 5- *
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

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.
For example, the domain of Cust-id is 6 digit numbers.

Slide 5- *
Formal Definitions – Tuple
A tuple is an ordered set of values (enclosed in angled brackets ‘< … >’)
Each value is derived from an appropriate domain.
A row in the CUSTOMER relation is a 4-tuple and would consist of four values, for example:
<632895, " ", "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.
A relation is a set of such tuples (rows)

Slide 5- *
Definition Summary
Informal Terms Formal Terms
Table Relation
Column Header Attribute
All possible Column Values Domain

Table Definition Schema of a Relation
Populated Table State of the Relation

Slide 5- *
Example – A relation STUDENT

Slide 5- *
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

Slide 5- *
Key Constraints
If a relation has several candidate keys, one is chosen to be the primary key.
The primary key attributes are underlined.
Example: Consider the CAR relation schema:
CAR(State, Reg#, SerialNo, Make, Model, Year)
We chose SerialNo as the primary key
The primary key value is used to uniquely identify each tuple in a relation
Provides the tuple identity
Also used to reference the tuple from another tuple
General rule: Choose as primary key the smallest of the candidate keys (in terms of size)
Not always applicable – choice is sometimes subjective

Slide 5- *
CAR table with two candidate keys – LicenseNumber chosen as Primary Key

Slide 5- *
Entity Integrity
Entity Integrity:
The primary key attributes PK of each relation schema R in S cannot have null values in any tuple of r(R).
This is because primary key values are used to identify the individual tuples.
t[PK]  null for any tuple t in r(R)
If PK has several attributes, null is not allowed in any of these attributes

Slide 5- *
Referential Integrity
A constraint involving two relations
The previous constraints involve a single relation.
Used to specify a relationship among tuples in two relations:
The referencing relation and the referenced relation.

程序代写 CS代考加微信: powcoder QQ: 1823890830 Email: powcoder@163.com

Related Posts