CS 377 Database Systems Relational Data Model �������� Department of Mathematics and Computer Science Emory University 1
Outline � Relational Model Concepts � Relational Model Constraints � Relational Database and operations 2
���������������� � First formal database model � Introduced by Codd in "A Relational Model for Large Shared Data Banks," Communications of the ACM, June 1970. � First commercial implementations available in � First commercial implementations available in early 1980s � Based on the concept of a mathematical relation and has theoretical basis in set theory and first/ order predicate logic. � Other models: hierarchical model, network model 3
�������������������� � RELATION: A table of values � A relation may be thought of as a ����������� . � A relation may alternately be thought of as a �������������� . � Each row represents a fact that corresponds to a real/world ������ or ��������� �! . � Table name and column names help interpret the meaning of the values 4
�������������������" ����������� ��� � The table is called a relation, a row is a tuple, a column header is an attribute � ����������� ��� ���� � ��� � �������� � �� Made up of a relation name R and a set of �����#���� � � ��� � �������� � � � ������ (or arity) of a relation is the number of attributes � of its relational schema � E.g. STUDENT (Name, SSN, HomePhone, Address, OfficePhone, Age, GPA) � Each �����#��� � � has a ������ ����� � �� that defines the possible values of the attribute by a data/type or a format attribute by a data/type or a format � E.g. The domain of SSN is the set of 9 digit numbers defined as: ddd/dd/dddd where each d is a decimal digit. 5
�������������������" �������� � A �������� (or relation state) r of the relation schema ���� � ��� � �� ������ � ��������� is a set of tuples r = {t 1 , t 2 , ..., t m } � A ��!�� t is an ordered set of n values t =<v 1 , v 2 , ..., v n >, where each value v i , 1 ≤ i ≤ n, is an element of dom(A i ) or a special NULL value � E.g. <“Benjamin Bayer", 305/61/2435, 373/1616, “2918 Bluebonnet Lane”, null, 19, 3.21> is a tuple belonging to the STUDENT relation. 19, 3.21> is a tuple belonging to the STUDENT relation. 6
Mathematical Definitions � A relation � ( � ) is a ��� ������������������ of degree � on the domains dom( � 1 ), dom( � 2 ), ..., dom( � � ), which is a ��#���� of the $��������� !������� of the domains that define R: ����� ⊆ ������ � �� � ����� � �� � ���� � ����� � �� � The Cartesian product is the direct product of the sets of values of all domains: ��� �� � �� � ��� �� � �� � ���� � ����� � � � The total number of tuples in the Cartesian product is: ���� �� ��� � ���� �� ��� � ���� � ������ �� ���� �� � ��� � ���� �� � ��� � ���� � ������ � �� � $��������������������� reflects only the valid tuples that represent a particular state 7
$%���$�������$�������������� � Ordering of tuples in a relation r(R) A relation is a set of tuples which are ��� ordered � � Ordering of attributes The attributes in R(A 1 , A 2 , ..., A n ) and the values in t=<v 1 , v 2 , ..., v n > are an ������� � list in our definition Alternative definition: tuple considered as a set of (<attribute>, <value>) pairs, � where each pair gives the value of the mapping from an attribute � � to a value � �� from dom( � � ) � Values in a tuple Values in a tuple All values are considered ������ (flat relational model with first normal form � assumption) – what about multi/valued attributes and composite attributes? A special ���� value is used to represent values that are unknown or inapplicable to � certain tuples. 8
DEFINITION SUMMARY Informal Terms Formal Terms Table Relation Column Attribute Row Row Tuple Tuple Values in a column Domain Table Definition Relation Schema 9
Relational Model Notation � Relation schema � of degree � : � ( � � , � � , ..., � � � � Relation names: � , � , � � Relations: � , � , �� � Tuples: � , � , �� � ����� �� in a relation � ( � ): ������ , � , ..., � ���� is the � ����� �� in a relation � ( � ): ������ � , � � , ..., � � ���� � is the value corresponding to attribute � � � Component values of tuples: � � [ � � ] and ��� � refer to the value � � in � for attribute � � � � [ � � , � , ..., � ! ] and �� ( � � , � , ..., � ! ) refer to the subtuple of values �� � , � , ..., � ! �� from �� corresponding to the attributes specified in the list 10
Outline � Relational Model Concepts � Relational Model Constraints � Relational Database and operations 11
Relational Model Constraints � Constraints � Restrictions on the actual values in a database state � �� �����������&#���������������� or ��!����� ����������� � Inherent in the data model � E.g. no duplicate tuples � �� ���&#���������������� or �'!����� ����������� � Can be directly expressed in schemas of the data model � �!!��������&#����� or ��������������������� or #�������� ����� � Cannot be directly expressed in schemas, expressed and enforced by application program � E.g. the max. no. of hours per employee for all projects he or she works on is 56 hrs per week 12
Schema/based constraints � Domain constraints � Key constraints � Entity integrity constraints � Referential integrity constraints � Referential integrity constraints 13
Domain Constraints � The value of each attribute A must be an atomic value from the domain dom(A) � Typical data types associated with domains: � Numeric data types for integers and real numbers � Characters Characters � Booleans � Fixed/length strings � Variable/length strings � Date, time, timestamp � Money � Other special data types 14
Key Constraints � No two tuples can have the same combination of values for all their attributes. � ��!��(�� � No two distinct tuples in any state � of � can have the � No two distinct tuples in any state � of � can have the same value for SK � )�� � Superkey of R � Removing any attribute � from " leaves a set of attributes " that is not a superkey of � any more 15
Key Constraints and Constraints on NULL Values (cont’d.) � Key satisfies two properties: � Two distinct tuples in any state of relation cannot have identical values for (all) attributes in key � Minimal superkey � Cannot remove any attributes and still have uniqueness constraint in above condition hold 16
Key Constraints and Constraints on NULL Values (cont’d.) � $���������(�� � Relation schema may have more than one key � *�������(��� of the relation � Designated among candidate keys � Designated among candidate keys � Underline attribute � Other candidate keys are designated as ���+���(��� 17
Key Constraints and Constraints on NULL Values (cont’d.) 18
)���$���������� ��!��(�� of R: A set of attributes SK of R such that no two tuples � �����#����������������������������� will have the same value for SK. For any distinct tuples t1 and t2 in r(R), t1[SK] ≠ ≠ ≠ ≠ t2[SK]. � {Licence_number}, {License_number, Make}, {Engine_serial_number, Make} � )�� of R: A "minimal" superkey; that is, a superkey K such that � removal of any attribute from K results in a set of attributes that is not a superkey. Key1 = {License_number}, Key2 = {Engine_serieal_number} � Is {Engine_serial_number, Make} a key? Is {Engine_serial_number, Make} a key? � � If a relation has ������� keys, each is called a ����������(��, and � one is chosen arbitrarily to be the !�������(�� . The primary key attributes are ���������� . 19
Recommend
More recommend