Relational Model 2nd week of classes.
Go to
Key idea for this page.
Q1. What is a Relational Model?
A1. A Relational Model presents the data as relations,
which are two-dimensional tables. Each row in the table holds data about
some entity. Each column of the table in given row holds a value of an
attribute for that entity.
Q2. You use relation, table, row, record etc. sometimes interchangeably, can you clear it up which is which?
A2. Sure, here is a table that makes it clear.
Relational terminology and their interpretations:
|
Relational Model |
Programmer |
User |
|
Relation |
File |
Table |
|
Tuple |
Record |
Row |
|
Attribute |
Field |
Column |
We will use the either Relational Model or User terminology interchangeably.
Q3. What else can you tell me about relational
models?
A3. The entire information content of the database
is represented in table form and in no other way. The rows in a
table (relation) are in no particular order, the attribute values are atomic,
i.e. hold only the supported data types such as integer, text, date, decimal
number. An attribute value can't be another table or object, only a built in
data type value.
Q4. What else is important in a Relational
Model?
A4. A
relational Model requires that each relation (table) must have a Primary
Key.
Q5. What is a Primary Key?
A5. A primary key is a unique column
(attribute) or a combination of columns (composite key) with the property that,
at any time, no two rows of the table
contain the same value in that column or column combination, i.e. no two rows
have the same key.
Q6. Can you give me an example?
A6. In database DataSpj the table S (Supplier) has sNo column as the Primary Key. In the SPJ (Shipments) table the Primary Key is combination of columns (composite key) namely (sNo, pNo, jNo). Note that no triplet of (sNo, pNo, jNo) ever repeats in the table spj.
Q7. Does relational theory tell you how to pick a
Primary Key?
A7. No! It really depends on the semantics
(meaning) of the problem. We are responsible for picking a Primary Key.
Q8. What would happen if two rows had the
same primary key?
A8. Same kind of problems and confusion if two
different people had the same social security number or the same finger prints.
But if we declare a Primary Key for a table the system will not permit
inserting duplications.
Q9. Is there another reason why Primary Keys are
important?
A9. In a Relational Model we use Primary Keys to
capture associations (relationships) among tables.
Q10. How are relationships among tables captured with Primary Keys?
A10. We insert a Primary Key of a table into another table as a cross reference. For example, in the spj. (shipments) table sNo, pNo, jNo identify the supplier, the part, and the job. In the spj table they are referred to as foreign keys and are cross references to records in other tables.
Q11. Are there situations where a relational
database is inappropriate in keeping track of things?
A11. Lots, for example relational databases are
inappropriate in keeping track of data that are complex, e.g., architectural
drawing with information on all of its components, or a mechanical drawing with
information on all of its parts, or a geographical map with information on all
objects in the map. Even a complex financial instrument such as a derivative
and information on all of its parts is difficult for a relational model to
capture. A relational database is well suited for keeping track of
transactional data like bank transactions, airline reservations, or stock
transactions.
Q12. What are some of the other popular database
data models (abstractions)?
A12. Hierarchical, Object-Relational, and
Object-Oriented are also popular today. To be discussed later.
Q13. What else is used today to keep track of
data?
A13. Of course (flat) files and application
programs (usually written in COBOL) are still heavily used today. There are no
abstractions with flat files, so if a file is moved or reformatted then the
COBOL programs must be modified. You can't query a flat file. You have to write
a program to get any information out of a file.
Q14. Why are people still using flat files
today.
A14. The main reason is that it's too expensive
to convert to databases, yet another reason is that some times it's faster to perform certain operations.