1 / 20

The Relational Models and Their Underlying Concepts

The Relational Models and Their Underlying Concepts. Relational Database is a collection of normalized relations with distinct relation names. A relation is a table with columns and rows. Only applies to logical structure of the database, not the physical structure. Relational Databases.

sheena
Download Presentation

The Relational Models and Their Underlying Concepts

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. The Relational Models and Their Underlying Concepts

  2. Relational Database is a collection of normalized relations with distinct relation names. A relation is a table with columns and rows. Only applies to logical structure of the database, not the physical structure. Relational Databases

  3. Attribute is a named column of a relation. Domain is the set of allowable values for one or more attributes. Tuple is a row of a relation. Values in a tuple are related to each other ENCM 339 is taught in Fall Degree is the number of attributes in a relation. Cardinality is the number of tuples in a relation. Relation R can be thought of as predicate R R(x,y,z) is true iff tuple (x,y,z) is in R Relational Model Terminology

  4. Set of rows (no duplicates) Each row describes a different entity Each column states a particular fact about each entity Each column has an associated domain Domain of Semester = {Fall, Winter, Spring} Table

  5. Operations on relations are precisely defined Take relation(s) as argument, produce new relation as result Unary (e.g., delete certain rows) Binary (e.g., union, Cartesian product) Operations

  6. Consider three sets D1, D2, D3 with Cartesian Product D1´D2´D3: e.g. D1 = {1, 3} D2 = {2, 4} D3 = {5, 6} D1´D2´D3 = {(1,2,5), (1,2,6), (1,4,5), (1,4,6), (3,2,5), (3,2,6), (3,4,5), (3,4,6)} Any subset of Cartesian product is a relation; e.g. R = {(2, 1), (4, 1)} Mathematical Definition of Relation

  7. May specify which pairs are in relation using some condition for selection; e.g. second element is 1: R = {(x, y) | x ÎD1, y ÎD2, and y = 1} first element is always twice the second: S = {(x, y) | x ÎD1, y ÎD2, and x = 2y} Mathematical Definition of Relation

  8. Relation name Attribute names and domains Integrity constraints - e.g.,: The values of a particular attribute in all tuples are unique The values of a particular attribute in all tuples are greater than 0 Default values Relation Schema

  9. A1, A2, …, Anare attributes R = (A1, A2, …, An ) is a relation schema E.g: Customer-schema =(customer-name, customer-street, customer-city) r(R) is a relation on the relation schema R E.g. customer (Customer-schema) Relation Schema

  10. Finite set of relations Each relation consists of a schema and an instance Database schema = set of relation schemas (and other things) Database instance = set of (corresponding) relation instances Relational Database

  11. Student (Id: INT, Name: STRING, Address: STRING, Status: STRING) Professor (Id: INT, Name: STRING, DeptId: STRING) Course (DeptId: STRING, CrsName: STRING, CrsCode: STRING) Transcript (CrsCode: STRING, StudId: INT, Grade: INT, Semester: SEMESTERS) Department(DeptId: STRING, Name: STRING) Database Schema (Example)

  12. Relation name is distinct from all other relation names in relational schema. Each cell of relation contains exactly one atomic (single) value. Each attribute has a distinct name. Values of an attribute are all from the same domain. Properties of Relations (1)

  13. Each tuple is distinct; there are no duplicate tuples. Order of attributes has no significance. Order of tuples has no significance, theoretically. Properties of Relations (2)

  14. Restriction on state (or sequence of states) of database Enforced by DBMS Intra-relational - involve only one relation Part of relation schema e.g., all Ids are unique Inter-relational - involve several relations Part of relation schema or database schema Integrity Constraints

  15. Values in a column (or columns) of a relation are unique: at most one row in a relation instance can contain a particular value(s) Key - set of attributes satisfying key constraint e.g., Id in Student, e.g., (StudId, CrsCode, Semester) in Transcript Relational Keys

  16. Super key: An attribute, or a set of attributes, that uniquely identifies a tuple within a relation Candidate Key: A super key that no proper subset is a super key within the relation. Primary Key: Candidate key selected to identify tuples uniquely within relation. Alternate Keys:Candidate keys that are not selected to be primary key. Foreign Key:Attribute, or set of attributes, within one relation that matches candidate key of some (possibly same) relation. Relational Keys

  17. Entity Integrity In a base relation, no attribute of a primary key can be null. Referential Integrity If foreign key exists in a relation, either foreign key value must match a candidate key value of some tuple in its home relation or foreign key value must be wholly null (which may violate the entity integrity). Semantic/Enterprise Constraints Additional rules specified by users or database administrators. Relational Integrity

  18. Attribute names of need not be the same: Teaching(CrsCode: STRING, Sem: STRING, ProfId: INT) Professor(Id: INT, Name: STRING, DeptId: STRING) ProfId attribute of Teaching references Id attribute of Professor R1 and R2 need not be distinct. Employee(Id:INT, MgrId:INT, ….) Employee(MgrId) references Employee(Id) Every manager is also an employee and hence has a unique row in Employee Foreign Key

  19. Foreign key might consist of several columns (CrsCode, Semester) of Transcript references (CrsCode, Sem) of Teaching R1(a1, …an) references R2(b1, …bn) There exists a 1 to 1 relationship between a1,…an and b1,…bn ai and bi have same domains (although not necessarily the same names) b1,…bn is a candidate key of R2 Foreign Key (cont)

  20. Domain, primary key, and foreign key are examples of structural (syntactic) constraints Semantic constraints express rules of application: e.g., number of registered students  maximum enrollment Semantic Constraints

More Related