Data Management - Applications - D427
is a conceptual framework for database systems, with
Database Model
three parts
3 Parts of the Database Model Data structures, operations, rules
Data structures prescribe how data is organized
Operations manipulate data structures
Rules govern valid data
characterized by unprecedented data volumes and rapidly
Big Data
changing data structures.
is an unordered collection of elements enclosed in
braces.
Set
Ex: {a, b, c} and {c, b, a} are the same, since sets are not
ordered.
is an ordered collection of elements enclosed in parenthe-
ses.
Tuple
Ex: (a, b, c) and (c, b, a) are different, since tuples are
ordered.
Table
Column
Database Terminology
Row
Data Type
Relation
Attribute
Math Terminology
Tuple
Domain
File
File Terminology
Field
, Record
Data Type
Select (Relational) selects a subset of rows of a table
Project (Relational) eliminates one or more columns of a table
Product (Relational) lists all combinations of rows of 2 tables
Join (Relational) combines 2 tables by comparing related columns
Union (Relational) selects all rows of 2 tables
Intersect (Relational) selects rows common to 2 tables
Difference (Relational) selects rows that appear in one table but not another
Rename (Relational) changes table name
computes functions over multiple table rows, such as sum
Aggregate (Relational)
and count
Theoretical way of manipulating table contents using re-
lational operators
relational algebra
ex. select, project, product, join...
Relational Rules logical constraints that ensure data is valid
Unique Primary Key - all tables have a primary key column,
or group of columns, in which the values may not repeat
Unique Column Names - different columns of the same
Examples of Rules
table have different names
No Duplicate Rows - no 2 rows of the same table have
identical values in all columns
based on business policy and specific to a particular data-
Business Rule
base
Example of a Business Rule
, all rows of the Employee table must have a valid entry in
the DepartCode column
-or-
PassportNumber values may not repeat in different Em-
ployee rows
relational rules that are implemented and enforced by the
database system.
SQL Constraints
Business rules are discovered during database design
and often implemented as SQL Constraints
is a high-level computer language for storing, manipulat-
Structured Query Language (SQL)
ing and retrieving data
SQL Statement is a complete command composed of one or more clauses
groups SQL keywords like SELECT, FROM, and WHERE
Clause
ex. with tables names like City, column names like Name,
and conditions like Population > 100,000
Data Definition Language (DDL)
Data Query Language (DQL)
SQL Language's 5 Sublanguage Data Manipulation Language (DML)
Data Control Language (DCL)
Data Transaction Language (DTL)
Data Definition Language (DDL) defines the structure of the database
Data Query Language (DQL) retrieves data from the database
Data Manipulation Language (DML) manipulates data stored in a database
Data Control Language (DCL) controls database user access
Data Transaction Language (DTL) manages database transactions
is a conceptual framework for database systems, with
Database Model
three parts
3 Parts of the Database Model Data structures, operations, rules
Data structures prescribe how data is organized
Operations manipulate data structures
Rules govern valid data
characterized by unprecedented data volumes and rapidly
Big Data
changing data structures.
is an unordered collection of elements enclosed in
braces.
Set
Ex: {a, b, c} and {c, b, a} are the same, since sets are not
ordered.
is an ordered collection of elements enclosed in parenthe-
ses.
Tuple
Ex: (a, b, c) and (c, b, a) are different, since tuples are
ordered.
Table
Column
Database Terminology
Row
Data Type
Relation
Attribute
Math Terminology
Tuple
Domain
File
File Terminology
Field
, Record
Data Type
Select (Relational) selects a subset of rows of a table
Project (Relational) eliminates one or more columns of a table
Product (Relational) lists all combinations of rows of 2 tables
Join (Relational) combines 2 tables by comparing related columns
Union (Relational) selects all rows of 2 tables
Intersect (Relational) selects rows common to 2 tables
Difference (Relational) selects rows that appear in one table but not another
Rename (Relational) changes table name
computes functions over multiple table rows, such as sum
Aggregate (Relational)
and count
Theoretical way of manipulating table contents using re-
lational operators
relational algebra
ex. select, project, product, join...
Relational Rules logical constraints that ensure data is valid
Unique Primary Key - all tables have a primary key column,
or group of columns, in which the values may not repeat
Unique Column Names - different columns of the same
Examples of Rules
table have different names
No Duplicate Rows - no 2 rows of the same table have
identical values in all columns
based on business policy and specific to a particular data-
Business Rule
base
Example of a Business Rule
, all rows of the Employee table must have a valid entry in
the DepartCode column
-or-
PassportNumber values may not repeat in different Em-
ployee rows
relational rules that are implemented and enforced by the
database system.
SQL Constraints
Business rules are discovered during database design
and often implemented as SQL Constraints
is a high-level computer language for storing, manipulat-
Structured Query Language (SQL)
ing and retrieving data
SQL Statement is a complete command composed of one or more clauses
groups SQL keywords like SELECT, FROM, and WHERE
Clause
ex. with tables names like City, column names like Name,
and conditions like Population > 100,000
Data Definition Language (DDL)
Data Query Language (DQL)
SQL Language's 5 Sublanguage Data Manipulation Language (DML)
Data Control Language (DCL)
Data Transaction Language (DTL)
Data Definition Language (DDL) defines the structure of the database
Data Query Language (DQL) retrieves data from the database
Data Manipulation Language (DML) manipulates data stored in a database
Data Control Language (DCL) controls database user access
Data Transaction Language (DTL) manages database transactions
