C170 Data Management - Applications

A database model is a conceptual framework for database software The relational model is a database model based on mathematical principles, with three parts: 1. A data structure that prescribes how data is organized. 2. Operations that manipulate data structures. 3. Rules that govern valid relational data. A domain is a named set of possible database values, such as integers, dictionary words, or logical values TRUE and FALSE. A tuple is a finite sequence of values, each drawn from a fixed domain. Ex: (3, apple, TRUE) is a tuple drawn from domains (Integers, DictionaryWords, LogicalValues). A relation is a named set of tuples, all drawn from the same sequence of domains. an attribute Each attribute has a name that is unique within the relation. Since each tuple position has a domain, each attribute also has a domain. Relational tuples differ from mathematical tuples in one respect In mathematics, the order of tuple values is significant. Ex: The tuples (7, 2) and (2, 7) are different. In the relational model, the order of tuple values is not significant, since each value is associated with a different attribute name. The most commonly used terms in relational database Row, column, and table. Occasionally tuple, relation, attribute and record, file, field are also used. In the relational data structure, which components are named? Domain, relation, and attribute Relational operation - Select selects a subset of rows of a table. Relational operation - Project eliminates one or more columns of a table. Relational operation - Product lists all possible combinations of rows of two tables. Relational operation - Join a product operation followed by a select operation. Relational operation - Union combines two tables by selecting all rows of both tables. Relational operation - Intersect combines two tables by selecting only rows common to both tables. Relational operation - Difference combines two tables by selecting rows that appear in one table but not the other. relational operations All relational operations result in a table. Structural rules - Unique primary key all tables should have a column with no repeated values, called the primary key and used to identify individual rows. Structural rules - Unique column names different columns of the same table must have different names. Structural rules - No duplicate rows no two rows of the same table may have identical values in all columns. Business rules - Unique column values in a particular column, values may not be repeated. Business rules - No missing values in a particular column, all rows must have known values. Business rules - Delete cascade when a row is deleted, automatically delete all related rows. Business rules are relational rules specific to a particular database and application. Structural rules are relational rules that govern data in every relational database. Relational rules also known as integrity rules, are logical constraints that ensure data is valid and conforms to business policy. What are the components of a column? name and a data type empty table A table without rows is called an empty table. A binary data type stores data as an exact copy of computer memory. Tables structural rules - Tables are normalized exactly one value exists in each cell. Tables structural rules - No duplicate column names duplicate column names are not allowed in one table. However, the same column name can appear in different tables. Tables structural rules - No duplicate rows no two rows may have identical values in all columns. data independence - relational databases rows and columns of a table have no inherent order. Primary keys rules - Values must be unique within the column This rule ensures that each value identifies at most one row. Primary keys rules - Values may not be NULL This rule ensures that each value identifies at least one row.