Database Model
A database model is a conceptual framework for database systems, with three parts:
Data structures that prescribe how data is organized. Operations that manipulate data structures. Rules that govern valid data. Relational Model
Relational Model is a database model based on a tabular data structure.
The model was published in 1970 by E. F. Codd of IBM and released in commercial products around 1980. The data structure, operations, and rules are standardized in SQL, the universal query language of relational databases. Designed for transactional data such as bank transactions and airline reservations. Early relational products were not as fast as more established non-relational products. Big Data was generated from the rise of the internet. Characterized by unprecedented data volumes and rapidly changing data structures. Relational Data Structure
Set
A 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. Tuple
A tuple is an ordered collection of elements enclosed in parentheses.
Ex: (a, b, c) and (c, b, a) are different, since tuples are ordered. Data Structure organizes data in tables:
A table has a name, a fixed tuple of columns, and a varying set of rows. A column has a name and a data type. A row is an unnamed tuple of values. Each value corresponds to a column and belongs to the column's data type. A data type is a named set of values, from which column values are drawn. Data type, table, and column are all named. Rows are not named. Table, Column, row, and data type ≈ Relation, attribute, tuple, and domain. A query selects a specific row by specifying one or more row values. Ex:
SELECT EmployeeName
FROM Employee
WHERE EmployeeID = 2594;
Relational Operations
All relational operations result in a table.
Select selects a subset of rows of a table. Project eliminates one or more columns of a table. Product lists all combinations of rows of two tables. Join combines two tables by comparing related columns. Union selects all rows of two tables. Intersect selects rows common to two tables. Difference selects rows that appear in one table but not another. Rename changes a table name. Aggregate computes functions over multiple table rows, such as sum and count A SQL statement can implement several operations.
SELECT Name
FROM Employee
WHERE Salary > 50000;
This implements both a select and a project operation.
Relational Rules
Relational rules are logical constraints that ensure data is valid.
Relational rules are part of the relational model and govern data in every relational database.
Example of relational rules are:
Relational rules are implemented as SQL constraints and enforced by the database system. Unique primary key. All tables have a primary key column, or group of columns, in which values may not repeat. Unique column names. Different columns of the same table have different names. No duplicate rows. No two rows of the same table have identical values in all columns. Business Rules are based on business policy and specific to a specific to a particular database.
Ex: All rows of the Employee table must have a valid entry in the DepartCode column. Ex: PassportNumber values may not repeat in different Employee rows
Unit 2: Relational Databases
