cs 377 database systems
play

CS 377 Database Systems Relational Data Model Department of - PowerPoint PPT Presentation

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


  1. CS 377 Database Systems Relational Data Model �������� Department of Mathematics and Computer Science Emory University 1

  2. Outline � Relational Model Concepts � Relational Model Constraints � Relational Database and operations 2

  3. ���������������� � 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

  4. �������������������� � 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

  5. �������������������" ����������� ��� � 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

  6. �������������������" �������� � 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

  7. 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

  8. $%���$�������$�������������� � 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

  9. DEFINITION SUMMARY Informal Terms Formal Terms Table Relation Column Attribute Row Row Tuple Tuple Values in a column Domain Table Definition Relation Schema 9

  10. 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

  11. Outline � Relational Model Concepts � Relational Model Constraints � Relational Database and operations 11

  12. 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

  13. Schema/based constraints � Domain constraints � Key constraints � Entity integrity constraints � Referential integrity constraints � Referential integrity constraints 13

  14. 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

  15. 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

  16. 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

  17. 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

  18. Key Constraints and Constraints on NULL Values (cont’d.) 18

  19. )���$���������� ��!��(�� 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

Download Presentation
Download Policy: The content available on the website is offered to you 'AS IS' for your personal information and use only. It cannot be commercialized, licensed, or distributed on other websites without prior consent from the author. To download a presentation, simply click this link. If you encounter any difficulties during the download process, it's possible that the publisher has removed the file from their server.

Recommend


More recommend