Flashcards
SCC 201 Lecture 2 - The relational model
Cards in this deck (29)
- 1What is the relational model for database management?The relational model (RM) for database management is an approach to managing data using a structure and language consistent with first-order predicate logic, where all data is represented in terms of tuples, grouped into relations.
- 2Who and when was the relational model described?First described in 1969 by Edgar F. Codd,
- 3What concepts is used in relational model, what is it's theoretical basis and how is it implemented?• Mathematical foundation
– Uses concept of a mathematical relation
– like a table of values as its basic building block
– Theoretical basis in set theory and first order predicate logic
– Implemented in a large number of commercial systems over last
twenty years or so - 4What does Relational model describe?• An attempt to describe data by its natural structure only
– i.e. without imposing any additional structure for machine
representation purposes
See earlier database data models: hierarchic, network - 5What do table names and columns names help to interpret?• Table name and column names are used to help interpret meaning of values in each row
– e.g. a Little Student table with columns
Name, Age, RegNum (Registration Number), Major - 6What do row in a table represent?• Each row in table represents collection of related data values (e.g. an entity or relationship)
- 7What does Atomic mean in relational database?– Atomic means each value in domain is indivisible as far as relational model is
concerned (e.g. values that are strings, integers, etc.) - 8What does a domain have? How can it's format be specified and give examples of standard numeric data types(integers, real numbers)?• A domain has a data type associated with it, e.g.
– Student age must be an Integer
– Student name must be a String
• A format can also be specified for a domain e.g.
– UK post codes must be in the format $$nn n$$
(where $ represents characters and n integer numbers)
• Standard numeric data types
– Integers: short integer, integer, long integer
– Real numbers: float, double-precision float - 9Different types of Data types for domains?• Characters
• Strings: fixed length, variable length
• Date, time, timestamp and money
• Others may be described as:
– a sub range of values from a data type
– an enumerated type where all possible values are explicitly listed - 10What is a relation schema and how is it donoted?• A relation schema is used to describe a relation
• A relation schema R denoted by
R ( A1, A2, …, An )
– A relation name R
– A list of attributes A1, A2, …, An
• Each attribute Ai is name of role played by some domain D in relation schema R
– i.e. subset of integers used as domain for Age; role being played is age of student in question - 11What is a degree?• The degree of a relation is the number of attributes n of its relation schema
– A relation schema for a relation of degree 4
(describing university students) is:
LITTLE STUDENT ( Name, Age, RegNum, Major ) - 12What is the donation of a schema• A relation instance r of the relation schema R ( A1, A2, …, An), also denoted by r(R) is a set of n-tuples: r = { t1, t2, …, tm }
– For example, in LITTLE_STUDENT t1 might be
( John, 23, 123456, CSc ), which is a quadruple - 13Does ordering of tuples matter and what shouldn't you do when using a relational DBMS ?• Ordering of tuples is immaterial
– May be logically ordered
e.g. by Name, Age, RegNum, etc.
– Could be randomly ordered
– In practice, when using a relational DBMS, do not make any assumption about order of tuples in relation
– In mathematical sets, ordering is irrelevant - 14Does ordering of values in tuple (or columns in relation) is significant?• Ordering of values in tuple (or columns in relation) is significant
– Each n-tuple t is an ordered list of n values.
– The ith value in tuple t, which corresponds to attribute Ai,is referred to as t[ Ai ]. - 15What can tuples be considered as?• A tuple could alternatively be considered as a set of (, ) pairs, where each pair gives mapping from an attribute Ai to a value vi from dom(Ai)
– In this case, ordering of attributes within tuples is not important as attribute name
appears with its value in the pair
– Makes sense as there is no reason to prefer having one attribute value appear before another in a tuple - 16How is n-tuple t denoted by?• An n-tuple t in a relation is denoted by
t = < v1, v2, …, vn > where vi is value corresponding to attribute Ai
– t [ Ai ] refers to the value vi
in t for attribute Ai - 17What defines uniqueness in a database and describe it?A Super key guarantees uniqueness.
Super Key is a set of attributes within a table whose values can be used to uniquely identify a tuple.
• Usually there are subsets of attributes of a relation schema R with the property that no two tuples in any relation state
r of R can have the same combination of values for these attributes.
– Such a subset of attributes is called a superkey - 18More formally, Can you describe a Superkey?More formally, a superkey SK of a relation schema R is a subset of attributes so that for any two distinct tuples t1 and t2
in a relation state r of R. - 19What can we do if we know the domains (and their ordering) of a relation and the Domains for a specific tuple?If we know the values of the domains (or fields) for a specific tuple (or set of tuples) then we can examine the relation for any tuples which have those specific values in those domains.
- 20What is a key and minimal superkey?• A key K of a relation schema R is a superkey of R with the additional
property that removing any attribute A from K leaves a set of
attributes K' that is not a superkey of R.
– A key is a minimal superkey - 21What are candidate keys?A candidate key is a column, or set of columns, in a table that can uniquely identify any database record without referring to any other data. Each table may have one or more candidate keys, but one candidate key is special, and it is called the primary key.
- 22What can't primary keys be?No primary key value can be NULL
– The value is used to identify individual tuples in a relation - 23A set of attributes FK in relation schema R1 is a foreign key of R1 referencing relation R2 if it satisfies the following two rules. What two rules?– Rule 1: Attributes in FK have same domain(s) as primary key attributes
PK of R2
– Rule 2: A value of FK in tuple t1 of current state r1 (R1) either occurs as
a value of PK for some tuple t2
in current state r2 (R2) or is NULL
(Referential Integrity)
• In former case t1[ FK ] = t2[ PK ]
The tuple t1 references tuple t2 R1
is referencing relation and R2
is referenced relation - 24What are foreign keys?In the context of relational databases, a foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table or the same table. In simpler words, the foreign key is defined in a second table, but it refers to the primary key in the first table.
- 25What is referential integrity?Referential integrity is a relational database concept, which states that table relationships must always be consistent. In other words, any foreign key field must agree with the primary key that is referenced by the foreign key.
- 26What does referential integrity ensure?– Ensuring a tuple in one relation that refers to another relation must refer to an existing tuple in that relation
- 27State some domain constraints?– Data types
– Data Formats
– Range of values - 28State some constraints implemented to Keys?– Uniqueness
– Non-null primary key - 29State some constraints implemented from Foreign Keys?Referential Integrity
Information
- Description
- Flashcards on SCC 201 Lecture 2 - The relational model, created by Ayo Olutobi on 22/01/2017.
- Created by
- Ayo Olutobi · Jan 2017
- Updated
- Apr 22, 2017
- Views
- 2
- Rating
More on Relational Databases and the Relational Model