The Relational Database model

The below post is notes prepared by me by studying the book "Database Systems Design, Implementation and Management" by Peter Rob and Carlos Coronel

Content, examples and diagrams are taken from that book.

UNIT-I

Chapter-III The Relational Database model

Explain characteristics of relational table?

1. A table is perceived as a two-dimensional structure composed of rows and columns.

2. Each table row (tuple) represents a single entity occurrence within the entity set.

3. Each table column represents an attribute, and each column has a distinct name.

4. Each row/column intersection represent a single data value.

5. All values in a column must conform to the same data format.

6. Each column has a specific range of values known as the domain of that attribute.

Example: The domain for the gender attribute consists of only two possibilities: M or F.

The domain for a company’s date of hire attribute consists of all dates (from start up date to current date)

Attribute may share a domain.

For ex: a student address and a professor address share the same domain of all possible addresses.

7. The order of rows and columns is immaterial to the DBMS

8. Each table must have an attribute or a combination of attributes that uniquely identifies each row. Ex: Roll_No in the STUDENT table

What are data types support by most DBMS?

Ans: The different data types are

1. Numeric: Numeric data are data on which you can perform arithmetic operations.
2. Character: Character data or text data or string data can contain any character, symbol or digit not intended for mathematical manipulations.
3. Date: Date attributes contain calendar dates stored in special format known as the julian date format.

Logical: Logical data can have only a true or false (yes or no) condition.  

What is data dictionary?

Ans: The data dictionary provides detailed descriptions of all tables and so contains all of attributes names,characteristics and structure of each table in the system.

What is system catalog?

Ans: it is a detailed system data dictionary that describes all objects within the database, including data about table names, table’s creator etc..

The system catalog is a system – created database whose tables store the user created database characteristics and content. These tables can be queried just like user-defined table.

Explain about indexes in relational database?

Ans: An index is composed of an index key and a set of pointers. An index can be used to retrieve data more efficiently. When you define a table’s primary key, the DBMS automatically creates a unique index on the primary key columns. What is meant by functional dependence?

Ans: The attribute B is functional dependent on A

if each value in column A determines one and only one value in column B.

Ex:                                                     

What is composite key?

Ans: A key may be composed of more than one attribute. Such a multi- attribute key is known as a composite key.

What is meant by fully functional dependency?

Ans: If 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.

Explain about various keys used in relational database model?

Key Type	Definition	Example
Super key	An attribute (or combination of attributes) that uniquely identifies each row in a table.	In STUDENT table, the super key could be any of the following:   STU_NUM STU_NUM, STU_LNAME
Candidate key	A minimal (irreducible) super key is a candidate key. A super key that does not contain a subset of attributes that is itself a super key.	STU_NUM,STU_LNAME is a super key, but it is not a candidate key because STU_NUM by itself can uniquely identifies each row in the STUDENT table.
Primary key	A candidate key is selected as a primary key. It cannot contain NULL values	If  employee’s PAN number has been included as one of the attribute in the EMPLOYEE table. EMP_NUM and EMP_PAN are both candidate keys because both uniquely identifies each employee. Selection of EMP_NUM as primary key would be designer’s choice.
Secondary key	An attribute or combination of attributes used strictly for data retrieval purposes	Most of the time if I need  city wise customers list from CUSTOMER table, I can place a secondary key on CUS_CITY column to get a speed reply.
Foreign key	An attribute in one table whose values must either match the primary key in another table or be null.

Q: What is a constraint? Write short notes on integrity constraints/ rules with example?

Ans: A constraint is a restriction placed upon the data values that can be stored in a column or columns of a table.

Integrity Constraint are of 2 types

1. Entity integrity constraint
2. Referential integrity constraint

Entity integrity : All primary key entries are unique and no part of a primary key may be null.

Referential integrity: A foreign key may have either a null entry, as long as it is not part of its tables primary key or an entry that matches the primary key value in a table to which it is related. (Every non- null foreign key value must reference an existing primary key value.)

Example: Table name: AGENT

                Primary key: AGENT_CODE    Foreign Key: none

AGENT_CODE	AGENT_FNAME	AGENT_PHONE
A01	ANU	2475258
A02	RAM	2465258

Table Name: CUSTOMER

Primary Key: CUS_CODE and Foreign Key: AGENT_CODE

CUS_CODE	CUS_FNAME	AGENT_CODE
C01	SWATHI	NULL
C02	DOLLY	A01
C03	RAMA	A01

Here the customer swathi is not assigned a agent yet, hence the agent code is NULL.

No entry in agent code column in customer table has invalid entry as they reference a valid entry A01 which is anu’s agent code.

Also primary keys of both tables contain null values and has unique values.

Relational set operators or relational algebra

Relational algebra is set of basic operations used to manipulate the data in relational model. These operations can be classified into two categories:

1. Basic set operations: These are

Binary operations

UNION

INTERSECTION

SET DIFFERENCE

CARTESIAN PRODUCT

Relational operations

SELECT

PROJECT

JOIN

DIVISION

                    

  

When two or more tables share

• the same number of columns and
• the columns have the same names and
• the columns share the same (or compatible) domains

the Two tables are said to be union-compatible.

UNION: union combines all rows from two tables, excluding duplicate rows. The two tables must be union- compatible.

Example:                                                       R3=R1U R2

Fname

A1

A2

A3

A4

A7

R1                                    R2                                                                                                           

Fname

A1

A2

A3

A4

Fname

A1

A7

A2

A4

                                               

                                        yields                                                                                                         

UNION                                                                                     

Intersect: Intersect yields only the rows that appear in both tables.

The tables must be union –compatible to yield valid results.

Example:

Fname

A1

A2

A4

R1                                        R2                    R3=R1 n R2                                                                                   

Fname

A1

A2

A3

A4

Fname

A1

A7

A2

A4

                                               

                                        yields                                                                                                         

INTERSECT                                                                                     

Difference: Difference yields all rows in one table that are not found in the other table. The tables must be union-compatible. Example:                                                       

Fname

A3

R1                                    R2                        R1-R2 =R3                                                                                   

Fname

A1

A2

A3

A4

Fname

A1

A7

A2

A4

                                               

                                        yields                                                                                                         

DIFFERENCE                                                                                    

Cartesian Product: yields all possible pairs of rows from two tables.

                                                                     R3=R1X R2

Course	Fname
C1	A1
C1	A2
C1	A3
C2	A1
C2	A2
C2	A3

R1                                 R2                                                                                                           

Course

C1

C2

Fname

A1

A2

A3

                                               

                                        yields                                                                                                         

                                                                                    

Select: Also known as RESTRICT

Yields values for all rows found in a table that satisfy a given condition.

PRODUCT

Pcode	Pdesc	Price
1	Flash Light	5
2	Lamp	25
3	Battery	7
4	100W Bulb	15
Pcode	Pdesc	Price
1	Flash Light	5
3	Battery	7

  

SELECT only price < $10 yields

Project: yields all values for selected attributes. Project yield  a vertical subset of a table.

Price

5

25

7

15

PRODUCT

Pcode	Pdesc	Price
1	Flash Light	5
2	Lamp	25
3	Battery	7
4	100W Bulb	15

  

PROJECT Price yields

Join: A join is used to combine rows from multiple tables.

Natural Join links tables by selecting only the rows with common values in common columns.  A natural join is a result of a three-stage process.

1.a PRODUCT  of the tables is created.

2. a SELECT is performed on the output to yield only the rows for which Acode = Cus_code
and these common columns Acode, Cus_code are called as join columns.

3. a PROJECT is performed on the result to include only one join column.

Table name: CUSTOMER                                               Table name: AGENT

Cus_code	Name	Agent_code
C01	ANU	A01
C02	RANI	A02
Acode	Name
A01	RAJ
A02	TAJ

         

              

STEP1: product of the above 2 tables yields

Cus_code	Name	Agent_code	Acode	Name
C01	ANU	A01	A01	RAJ
C01	ANU	A01	A02	TAJ
C02	RANI	A02	A01	RAJ
C02	RANI	A02	A02	TAJ

STEP 2: SELECT rows for which Acode = Cus_code

Cus_code	Name	Agent_code	Acode	Name
C01	ANU	A01	A01	RAJ
C02	RANI	A02	A02	TAJ

STEP 3: PROJECT to remove Acode field from the result.

Cus_code	Name	Agent_code	Name
C01	ANU	A01	RAJ
C02	RANI	A02	TAJ

           

The column on which the join occurs only once in new table.

Equi Join:

1. Links tables on the basis of equality condition.
2. Does not eliminate duplicate columns

Theta join : if any other comparison operator other than equality is used, the join is called theta join.

Left outer join: yields all of the rows in CUSTOMER table, including those that do not have a matching value in AGENT table.

Right outer join: yields all of the rows in AGENT table, including those that do not have a matching value in CUSTOMER table.

DIVIDE: This operation uses single column table as the divisor and 2-column table as the dividend. The tables must have a common column.

CODE	LOC
A	5
A	9
B	5
B	3
C	6
CODE
A
B
LOC
5

                                                                        

                                                                 

DIVIDE                                             YIELDS