There are 3 terms that are used in relational database models - relations, attributes and domains .A relation is a table with columns and rows. The named columns of the relation are called the attributes and the domain is the set of values the attributes are allowed to take. A row is also called a tuple. There are certain rules that must be followed, rows must not be identical, and each row will contain a single value for each of its attributes. Keys are used to uniquely identify rows and also used to join tables.
Database schemas are a set of related tables and objects. In oracle a schema is always related to a user.
Relational databases are founded on basic mathematical principles (set theory). Relational algebra consists of a set of operations for manipulating one or more relations without changing the originals. The following basic operations that can bed used on a database using relational algebra:
Unary Operations |
|
| Selection | Extracts (or eliminates) a set of rows form a relation based on the values of the attributes of the relation |
| Projection | extracts (or eliminates) a specified set of columns of a relation |
Binary or Set Operations |
|
| Union | Combines two relations to produce a larger relation |
| intersection | creates a new relation that has only the common rows in two relations |
| Difference | creates a new relation that has only the non-common rows in two relations |
| Cartesian product | creates a new relation that concatenates every row in a relation, basically an example of a join operation. |
The problem with Relational algebra is that it is to complex on all but the simplest database queries, Relational Calculus does not have the complexity as relational algebra and only focuses on what the database is being queried for, rather than how to conduct the query. Relational Calculus involves the use of operators such as AND and OR to manipulate relations in logical expressions.
SQL is an english-like language that enables you to manipulate the database. You can divide SQL statements into two categories:
| DDL (data definition language) | used to build and alter the database structures |
| DML (data manipulation language) | used to manipulate the data in the database tables |
The relation database life cycle is as follows:
Requirement gathering and analysis stage should give a clear idea of the expectations of everyone involved with the database design, right from end users to developers, system administration, etc. Data-flow diagrams or process models are commonly used to capture the data required.
Logical database design involves modeling the database around the real world and applying rules and conditions to meet that model. This stage is probably the most critical as any design can be thrown away if it does not work, it is better to tinker with the design at this stage than any other. You can use entity-relationship modeling (ER Modeling) to logically represent and analyze the components of the business. ER modeling involves using ERD's (ER diagrams) which show the relationships within the business, entities and attributes are used to describe the business terms i.e. entity is a employee, attributes is the employee's name, national security number, etc. Also ER modeling shows what relationship entities have with each other.
There are 3 relationships that an entity can have
| one-to-many | Entity A is related to several members of entity B A customer can check out many books from the library but only one customer can borrow each book at a time |
| one-to-one | Entity A has only one relationship with entity B A person can have only one national security number. |
| many-to-many | Entity A has many relationships with entity B and entity B has many relationships with entity A. A movie star can star in several movies and each movie may have several movie stars. |