1 / 20

Chapter 5:

Chapter 5:. The Relational Model and Relational Database Constraints. Outline. Relational Model Concepts Relational Model Constraints and Relational Database Schemas. Database Design. Steps in building a database for an application:. Real-world domain. Conceptual Model (EER).

colman
Download Presentation

Chapter 5:

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. Chapter 5: The Relational Model and Relational Database Constraints

  2. Outline • Relational Model Concepts • Relational Model Constraints and Relational Database Schemas Introduction to Databases

  3. Database Design Steps in building a database for an application: Real-world domain Conceptual Model (EER) DBMS data model Create Schema (DDL) Load data (DML) Introduction to Databases

  4. Relational Data Structure In the relational model, all data is logically structured within relations (tables). • Relation is a table with columns & rows. Holds information about entities. • Attribute is a named column of a relation. • Domain is the set of allowable values for one or more attributes. Every attribute in a relation is defined on a domain. • Tuple is a row of a relation. • Degree of a relation is the number of attributes it contains. • Cardinality of a relation is the number of tuples it contains. • Relational database is a collection of normalized relations with distinct relation names. Introduction to Databases

  5. Relational Data Structure Relation Name Attributes STUDENT StudentNo LName FName Initial DOB GPA 4170010 Al-Saleh Amal M. 04-06-78 3.91 4182000 Al-Ghanem Nora A. 4.20 02-12-79 Relation Tuples 4182034 Al-Fahad Cardinality 01-11-74 Laila A. 4.01 Saod 4188134 Amal F. 22-04-73 3.01 4189860 Rashed Rana I. 2.31 30-01-78 Al-Fahad 4192134 19-03-79 Rania M. 3.50 Degree Introduction to Databases

  6. Formal Definitions • A Relation may be defined in multiple ways. • The Schema of a Relation: R (A1, A2, .....An) Relation schema R is defined over attributes A1, A2, .....An For Example CUSTOMER (Cust-id, Cust-name, Address, Phone#) Here, CUSTOMER is a relation name. CUSTOMER relation defined over the four attributes Cust-id, Cust-name, Address, Phone#, each of which has a domain or a set of valid values. For Example, the domain of Cust-id is 6 digit numbers. Introduction to Databases

  7. Formal Definitions • A tuple is an ordered set of values • Each value is derived from an appropriate domain. For Example Each row in the CUSTOMER table may be referred to as a tuple in the table and would consist of four values. <632895, "John Smith", "101 Main St. Atlanta, GA 30332", "(404) 894-2000"> is a tuple belonging to the CUSTOMER relation. • A relation may be regarded as a set of tuples (rows). Introduction to Databases

  8. Domains STUDENT StudentNo LName FName Initial DOB GPA 4170010 Al-Saleh Amal M. 04-06-78 3.91 4182000 Al-Ghanem Nora A. 4.20 02-12-79 4182034 Al-Fahad 01-11-74 Laila A. 4.01 Attribute Domain Name Definition StudentNo Digits: size 7 Student Number LName Character: size 15 Last Name FName Character: size 15 First Name Character: size 3 Initial Middle Initial Date: range 01-01-20, format dd-mm-yy DOB Date of Birth Real: size 3, decimal 2, range 0-5 GPA Great Point Average Introduction to Databases

  9. Properties of Relations • The relation has a name that is distinct from all other relation names in the relational DB • Each cell of the relation contains exactly single value • Each attribute has a distinct name • The values of an attribute are all of the same domain • Each tuple is distinct. There are no duplicate tuples • The order of attributes has no significance • The order of tuples has no significance; theoretically. Introduction to Databases

  10. The relation STUDENT with a different order of tuples. Introduction to Databases

  11. Relational Integrity Constraints • Constraints are conditions that must hold on all valid relation instances. There are four main types of constraints: • Domain constraints • Key constraints • Entity integrity constraints • Referential integrity constraints Introduction to Databases

  12. Key Constraints • Candidate key (CK) is an attribute, or set of attributes, that uniquely identifies a tuple, and no proper subset is a CK within the relation • Primary Key (PK) is the CK that is selected to identify tuples uniquely within the relation • Foreign Key (FK) is an attribute, or set of attributes, within one relation that matches the CK of some relation. Used to represent relationship between tuples of two relations. Introduction to Databases

  13. Relational Keys STUDENT GPA DeptNo StudentNo LName FName Initial DOB 4170010 Al-Saleh Amal M. 3.91 04-06-78 D001 4182000 Al-Ghanem Nora A. 4.20 D001 02-12-79 4182034 Al-Fahad Laila A. 4.01 01-11-74 D002 Saod 4188134 Amal F. 3.01 22-04-73 D003 4189860 Rashed Rana I. 2.31 D001 30-01-78 Foreign Key Primary Key DEPARTMENT Location Department _Name DeptNo Build # 20 D001 Computer Science Build # 45 D002 Business Administration Build # 6 D003 Science Introduction to Databases

  14. Key Constraints • Primary key of R: A set of attributes PK of R such that no two tuples in any valid relation instance will have the same value for PK. Example: The STUDENT relation schema: STUDENT( Ssn, Name, Dob, Age, Address) The attribute set {Ssn} ia a key of STUDENT because no two student tuples can have the same value for Ssn. Introduction to Databases

  15. Entity Integrity Constraints • Entity Integrity: The primary key attributes PK of each relation schema R in S cannot have null values in any tuple. This is because primary key values are used to identify the individual tuples. PK  null for any tuple • Note: Other attributes of R may be similarly constrained to disallow null values, even though they are not members of the primary key. Introduction to Databases

  16. Schema diagram for the COMPANY relational database schema. Introduction to Databases

  17. One possible database state for the COMPANY database schema. Introduction to Databases

  18. Referential Integrity Constraints • A constraint involving two relations (the previous constraints involve a single relation). • Used to specify a relationship among tuples in two relations: the referencing relation and the referenced relation. • Tuples in the referencing relation R1 have attributes FK (called foreign key attributes) that reference the primary key attributes PK of the referenced relation R2. A tuple t1 in R1 is said to reference a tuple t2 in R2 if t1[FK] = t2[PK]. • A referential integrity constraint can be displayed in a relational database schema as a directed arc from R1.FK to R2. Introduction to Databases

  19. Referential integrity constraints displayed on the COMPANY relational database schema. Introduction to Databases

  20. Summary Introduction to Databases

More Related