Home / Summaries / Class notes - Interactive Data Transformation / attribute-keys-domain

DBMS - SQL environment

Q: What is a relational schema and what does a relation consist of?

A database consists of a finite set of relations Often referred to as tables Each relation has a name and a set of attributes Each attribute has a name and a domain A relation contains a set of instance Often referred to as rows Or tuples Or records

Q: What is a domain of attribute values and what domains are there?

Domain is the collection of values an attribute may take NULL: represent not having a value / unknown value Numeric: integer, float, etc. E.g., FLOAT(7,4) is 999.9999 Boolean: True or False values Date , time, timestamp, year, etc. E.g., TIME is '00:00:00’ Text : string, char, set, etc. E.g., SET('one', 'two’) is ‘’, ‘one’, ‘two’, ‘one,two’

Q: What are keys in a RDB and which keys are there?

Keys are special fields that serve two main purposes: 1. Primary keys are unique identifiers of the relation Examples: id, social security number, etc. This guarantees that all rows are unique Must contain data values 2. Foreign keys are identifiers that enable a dependent relation (on the many side of a relationship) to refer to its parent relation (on the one side of the relationship) Example: registration of students to courses Keys can be simple , i.e., one attribute, or composite , i.e., more than one attribute Usually used as indexes to speed up query processing

4 important questions on DBMS - SQL environment

What at the notation of a relational schema and attributes relational instance?

R = (A1, A2, …, An ) is a relation schema

E.g., instructor = (id, name, dept_name, salary)
A1, A2, …, An are attributes

A relation instance r defined over a relation schema R is denoted by r( R )

E.g., r(id, name, dept_name, salary) = 12121, “Wu”, “Finance”, 90000

All the data is stored in various tables
Relations are unsorted, i.e., instances don’t have a particular order

What is a relational schema and what does a relation consist of?

A database consists of a finite set of relations

Often referred to as tables

Each relation has a name and a set of attributes
Each attribute has a name and a domain
A relation contains a set of instance

Often referred to as rows
Or tuples
Or records

What is a domain of attribute values and what domains are there?

Domain is the collection of values an attribute may take
NULL: represent not having a value / unknown value
Numeric: integer, float, etc.

E.g., FLOAT(7,4) is 999.9999

Boolean: True or False values
Date, time, timestamp, year, etc.

E.g., TIME is '00:00:00’

Text: string, char, set, etc.

E.g., SET('one', 'two’) is ‘’, ‘one’, ‘two’, ‘one,two’

What are keys in a RDB and which keys are there?

Keys are special fields that serve two main purposes:
1. Primary keys are unique identifiers of the relation

Examples: id, social security number, etc.
This guarantees that all rows are unique
Must contain data values

2. Foreign keys are identifiers that enable a dependent relation (on the many side of a relationship) to refer to its parent relation (on the one side of the relationship)