Monday, July 6, 2009

Logical Database Design and the Relational Model in DBMS

What is Relational Model - Basic Definitions
- relational model represent the data in the form of tables
- based on mathematical theory
- consists of three components
Data structure: Data are organized in the form of tables with rows and columns
Data manipulation: powerful operations (using SQL) are used to manipulate data stored in the relations Data integrity: facilities are included to specify business rules

What is Relational Data Structure
- a relation is a named, two-dimensional table of data
- can express the structure of a relation by a shorthand notation in which name of the relation is followed (in parentheses) by the name of the attributes in that relation
- e.g. EMPLOYEE(Emp_ID,Name,Dept_Name,Salary)
- Relational Keys: every relation must have a primary key
- e.g. EMPLOYEE(Emp_ID,Name,Dept_Name,Salary)
- a composite key is a primary key that consists of more than one attributes
-E.g the primary key for a relation DEPENDENT would likely consists of the combination of Emp_Id and Dependent_Name
- A foreign key : is an attributes (possibly composite) in a relation of a database that serve as the primary key of another relation in the same database.
-E.g EMPLOYEE(Emp_ID,Name,Dept_Name,Salary), DEPARTMENT(Dept_Name,Location,Fax)

learn Properties of Relations
- not all the tables are relations
- relations have several properties
- unique name
- an entry at the intersection of each row and column is atomic (or single valued)
- each row is unique
- each attribute within a table has a unique name
- the columns of a relation can be interchanged without changing the meaning or use of the relation
- the sequence of rows (top to bottom) is insignificant (can be interchanged)

Example Database
- A relational database consists of any number of relations
- The structure of the database is described through the use of conceptual Schema
- Two common methods for expressing a (conceptual) schema
- Short text statements: each relation is named and the names of its attributes follow in parentheses
- A graphical representation: each relation is represented by a rectangle containing the attributes for the relation
Text statements have the advantage of simplicity, while the graphical representation provides a better means of expressing referential integrity constraints.




Text description for the relations

CUSTOMER(Customer_ID,Customer_Name,Address,City,State, Zip)
ORDER(Order_ID,Order_Date,Customer_ID)
ORDERLINE(Order_ID,Product_ID,Quantity)
PRODUCT(Product_ID,Product_Description,Product_Finish,Unit_Price,On_H and)
- The primary key for the ORDERLINE is a composite key.
- Customer_ID is a foreign key in the ORDER relation; this allows the user to associate an order with the customer who submitted the order.
- ORDERLINE has two foreign keys; Order_ID and Product_ID; these keys allow the user to associate each line on an order with the relevant order and product

No comments:

Post a Comment