المحاضرة الرابعة

The Relational Database Model

The logical view of the relational database is facilitated by the creation of data relationships based on a logical construct known as a relation. Because a relation is a mathematical construct, end-users find it much easier to think of a relation as a table.
A table is perceived as a two-dimensional structure composed of rows and columns.

Characteristics of a Relational Table:

- A table is perceived as a two-dimensional structure composed of rows and columns.
- Each table row(tuple) represents a single entity occurrence within the entity set.
- Each table column represents attribute, and each column has a distinct name.
- Each row/column intersection represents a single data value.
- All values in a column must conform to the same data format.
- Each column has a specific range of values known as the attribute
domain.
- The order of the rows and columns is immaterial to the DBMS.
- Each table must have an attribute or a combination of attributes that uniquely identities each row.

Keys

In the relational model, keys are important because they are used to ensure that each row in a table is uniquely identifiable. They are also used to establish relationships among tables and to ensure the integrity of the data. Therefore, a proper understanding of the concept and use of keys in the relational model is very important. A key consists of one or more attributes that determine other attributes. For example, an invoice number identifies all of the invoice attributes, such as the invoice data and the customer name
The keys role is based on a concept known as determination, when the statement" A determines B" indicates that if you know the value of attribute A, you can loop up (determine) the value of attribute B, for example as in table student

Num LName FName Init Phone Class Hrs
232 smith William C 2267 So 42
233 Bowser Anne M 2255 Jr 81
234 Katinga John P 2267 So 36
235 Robertson John K 2277 Jr 66









NUM LNAME
NUM LNAME, FNAME, INIT


The principle of determination is very important because it is used in the definition of a central relational database concept known as functional dependence.

The attribute B is functionally dependent on the attribute A if each value in column A determines one and only one value in column B.

For example in student table, phone is functionally dependent on NUM, when NUM value is 233 determines the phone value 2255. On the other hand, NUM is not functionally dependent on phone because the phone value 2267 is associated with two NUM values 232, 234, similarly in NUM and Fname


Composite key: a key when composed of more than one attribute (multi-attribute) for example in table student we can write

Num, L name, F name Hrs, Class


Def : : if the attribute (B) is functionally dependent on a composite key (A) but not on any subset of that composite key, the attribute (B) is fully functionally dependent on (A)


Type of keys

1) Super key: is any key that uniquely identifies each row it is
functionally determines all of a rows attributes in student table
super key could be any of the following:
Num
Num, L name
Num, L name, Init

2) Candidate key: it is a super key without unnecessary attributes, that is a minimal super key such as Num, L name it is a super key but is not a candidate key because Num by it self is a candidate key
L name, F name, Init, Phone
Might also be a candidate key, because that as long that imposable
two students share the same last name, first name, initial, and
Phone number.

3) primary key : A candidate key selected to uniquely identify all
other attribute values in any given row such as
NUM

Null (that is, no data entry) is not permitted in the primary key.

4) Secondary key: is a key that is used strictly for data retrieval
purposes. For example: suppose customer data are stored in a
CUSTOMER table in which the customer number is the primary
key. Do you suppose that most customers will remember their
numbers? Data retrieval for customer can be facilitated when the
customer s last name and phone number are used. Keep in mined
that a secondary key does not necessarily yield a unique outcome.

5) Foreign key: is an attribute whose value match the primary key










Table name: Product
Primary key: Pro-Code
Foreign key: Code


Pro-Code Descript Price Code
232
235
231







Code Contact Areacode Phone
231
232
235
232
Link





Table name: Vendor
Primary key: Code
Foreign key: none



The Code is the primary key in the vendor table, and it occurs as foreign key in the product table.
Because the vendor table is not linked to third table, the vendor table does not contain a foreign key.