D427 DATA MANAGEMENT (APPLICATIONS) NEW GUIDE 2024
updated
1. Database Model: is a conceptual framework for database systems, with three
parts
2. 3 Parts of the Database Model: Data structures, operations, rules
3. Data structures: prescribe how data is organized
4. Operations: manipulate data structures
5. Rules: govern valid data
6. Big Data: characterized by unprecedented data volumes and rapidly changing
data structures.
7. Set: is an unordered collection of elements enclosed in braces.
Ex: {a, b, c} and {c, b, a} are the same, since sets are not ordered.
8. Tuple: is an ordered collection of elements enclosed in parentheses.
Ex: (a, b, c) and (c, b, a) are different, since tuples are ordered.
9. Database Terminology: Table
Column
Row
Data Type
10. Math Terminology: Relation
Attribute
Tuple
Domain
11. File Terminology: File
Field
Record
Data Type
12. Select (Relational): selects a subset of rows of a table
13. Project (Relational): eliminates one or more columns of a table
14. Product (Relational): lists all combinations of rows of 2 tables
15. Join (Relational): combines 2 tables by comparing related columns
16. Union (Relational): selects all rows of 2 tables
17. Intersect (Relational): selects rows common to 2 tables
18. Difference (Relational): selects rows that appear in one table but not another
19. Rename (Relational): changes table name
20. Aggregate (Relational): computes functions over multiple table rows, such as
sum and count
21. relational algebra: Theoretical way of manipulating table contents using rela-
tional operators
, D427 DATA MANAGEMENT (APPLICATIONS) NEW GUIDE 2024
updated
ex. select, project, product, join...
22. Relational Rules: logical constraints that ensure data is valid
23. Examples of Rules: 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 table have different names
No Duplicate Rows - no 2 rows of the same table have identical values in all columns
24. Business Rule: based on business policy and specific to a particular database
25. 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 Employee rows
26. SQL Constraints: relational rules that are implemented and enforced by the
database system.
Business rules are discovered during database design and often implemented as
SQL Constraints
27. Structured Query Language (SQL): is a high-level computer language for
storing, manipulating and retrieving data
28. SQL Statement: is a complete command composed of one or more clauses
29. Clause: groups SQL keywords like SELECT, FROM, and WHERE
ex. with tables names like City, column names like Name, and conditions like
Population > 100,000
30. SQL Language's 5 Sublanguage: Data Definition Language (DDL)
Data Query Language (DQL)
Data Manipulation Language (DML)
Data Control Language (DCL)
Data Transaction Language (DTL)
31. Data Definition Language (DDL): defines the structure of the database
32. Data Query Language (DQL): retrieves data from the database
33. Data Manipulation Language (DML): manipulates data stored in a database
34. Data Control Language (DCL): controls database user access
35. Data Transaction Language (DTL): manages database transactions
36. Database System Instance: is a single executing copy of a database system
updated
1. Database Model: is a conceptual framework for database systems, with three
parts
2. 3 Parts of the Database Model: Data structures, operations, rules
3. Data structures: prescribe how data is organized
4. Operations: manipulate data structures
5. Rules: govern valid data
6. Big Data: characterized by unprecedented data volumes and rapidly changing
data structures.
7. Set: is an unordered collection of elements enclosed in braces.
Ex: {a, b, c} and {c, b, a} are the same, since sets are not ordered.
8. Tuple: is an ordered collection of elements enclosed in parentheses.
Ex: (a, b, c) and (c, b, a) are different, since tuples are ordered.
9. Database Terminology: Table
Column
Row
Data Type
10. Math Terminology: Relation
Attribute
Tuple
Domain
11. File Terminology: File
Field
Record
Data Type
12. Select (Relational): selects a subset of rows of a table
13. Project (Relational): eliminates one or more columns of a table
14. Product (Relational): lists all combinations of rows of 2 tables
15. Join (Relational): combines 2 tables by comparing related columns
16. Union (Relational): selects all rows of 2 tables
17. Intersect (Relational): selects rows common to 2 tables
18. Difference (Relational): selects rows that appear in one table but not another
19. Rename (Relational): changes table name
20. Aggregate (Relational): computes functions over multiple table rows, such as
sum and count
21. relational algebra: Theoretical way of manipulating table contents using rela-
tional operators
, D427 DATA MANAGEMENT (APPLICATIONS) NEW GUIDE 2024
updated
ex. select, project, product, join...
22. Relational Rules: logical constraints that ensure data is valid
23. Examples of Rules: 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 table have different names
No Duplicate Rows - no 2 rows of the same table have identical values in all columns
24. Business Rule: based on business policy and specific to a particular database
25. 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 Employee rows
26. SQL Constraints: relational rules that are implemented and enforced by the
database system.
Business rules are discovered during database design and often implemented as
SQL Constraints
27. Structured Query Language (SQL): is a high-level computer language for
storing, manipulating and retrieving data
28. SQL Statement: is a complete command composed of one or more clauses
29. Clause: groups SQL keywords like SELECT, FROM, and WHERE
ex. with tables names like City, column names like Name, and conditions like
Population > 100,000
30. SQL Language's 5 Sublanguage: Data Definition Language (DDL)
Data Query Language (DQL)
Data Manipulation Language (DML)
Data Control Language (DCL)
Data Transaction Language (DTL)
31. Data Definition Language (DDL): defines the structure of the database
32. Data Query Language (DQL): retrieves data from the database
33. Data Manipulation Language (DML): manipulates data stored in a database
34. Data Control Language (DCL): controls database user access
35. Data Transaction Language (DTL): manages database transactions
36. Database System Instance: is a single executing copy of a database system
