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.
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
- Numeric: Numeric data are data on which you can perform arithmetic operations.
- Character: Character data or text data or string data can contain any character, symbol or digit not intended for mathematical manipulations.
- 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
- Entity integrity constraint
- 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.
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:
- Links tables on the basis of equality condition.
- 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
No comments:
Post a Comment