Fundamentals of database system - Relational data model and relational database constraints

Fundamentals of Database systems Ministry of Higher Education Bamyan University Computer Science Department 1 Presented by : Mustafa Kamel Mohammadi Email : bamian.cs@gmail.com Relational data model and relational database constraints Fundamentals of database system 6th edition

Learning objective  In his chapter you will learn  Relational data model concepts  Relational data model constraints  Characteristics of relation 2

Relational Model Concepts The Relational model represents the database as a collection of relations. Each relation resembles a table of values Each row in the table represents a collection of related data values. A row represents a  Real-world entity  Relationship Table name and column names are used to help to interpret the meaning of the values in each row. In the formal relational model terminology  A row is called a tuple  A column header is called an attribute  The table is called a relation  The data type describing the types of values that can appear in each column is represented by a domain of possible values 3

Cont. Domain A domain D is a set of atomic values. By atomic we mean that each value in the domain is indivisible Method of specifying a domain is to specify a data type  Usa_phone_numbers. The set of ten-digit phone numbers valid in the United States.  Social_security_numbers. The set of valid nine-digit Social Security numbers.  (This is a unique identifier assigned to each person in the United States for employment, tax, and benefits purposes.)  Names. The set of character strings that represent names of persons. The preceding are called logical definitions of domains. A domain is thus given a name, data type, and format  ISBN = xxx-xxxx-xxxx-xxxx , x can contain (0-9, A-Z) 4

Cont. Relation schema A relation schema R, denoted by R(A1, A2, ...,An) is made up of a relation name R and a list of attributes, A1, A2, ..., An Each attribute Ai is identified by dom (Ai) The degree of a relation is the number of attributes of its relation schema A relation of degree seven  STUDENT(Name, Ssn, Home_phone, Address, Office_phone, Age, Gpa) Using the data type of each attribute, the definition is sometimes written as:  STUDENT(Name: string, Ssn: string, Home_phone: string, Address: string,Office_phone: string, Age: integer, Gpa: real) Attribute A can be qualified with the relation name R to which it belongs by using the dot notation R.A , for example, STUDENT.name 5

Cont. Relation state Denoted by r(R), is a set of n-tuples r = {t1, t2, ..., tn}. Denoted by lower case letter (r) Each tuple (t) is an ordered list of n values t=<v1,v2, ..., vn> each value vi, 1 ≤ i ≤ n, is an element of dom (Ai) or is a special NULL value It is possible for several attributes to have the same domain. The attribute names indicate different roles  USA_phone_numbers plays the role of Home_phone  USA_phone_numbers plays the role of office_phone 6

Characteristics of Relations Ordering of Tuples in a Relation  Logically because relation is defined as a set of tuples. Mathematically, elements of a set have no order among them.  Physically there always is an order among the records. This ordering indicates first, second, ith, and last records in the file  When we display a relation as a table, the rows are displayed in a certain order by different attributes. Values and NULLs in the Tuples  Each value in a tuple is an atomic value  Composite and multivalued attributes are not allowed  Null is used to represent values that are  value unknown  value exists but is not available  Attribute does not apply 8

Cont. Interpretation (Meaning) of a Relation The relation schema can be interpreted as a declaration. Each tuple in the relation can then be interpreted as a fact or a particular instance of the assertion  Some relations may represent facts about entities  Whereas other relations may represent facts about relationships. 9

Relational Model Constraints There are generally many restrictions or constraints on the actual values in a database state Constraints on databases can generally be divided into three main categories:  Constraints that are inherent in the data model. We call these inherent model-based constraints or implicit constraints  Constraints that can be directly expressed in schemas of the data model, typically by specifying them in the DDL ,We call these schema-based constraints or explicit constraints.  Constraints that cannot be directly expressed in the schemas of the data model, and hence must be enforced by the application programs. We call these application-based or semantic constraints or business rules. 10

Explicit or Schema Based constraints Domain constraints Specify that within each tuple, the value of each attribute A must be an atomic value. Key Constraints Means that no two tuples can have the same combination of values for all their attributes. Subsets of attributes of a relation schema R with the property that no two tuples in any relation state r of R is called a super key. A super key can have redundant attributes so removing those attributes makes the concept of key.  Key is a minimal super key that is, a super key from which we cannot remove any attributes and still have the uniqueness constraint 11

Cont.  A key is also a super key but not vice versa  Example: {Ssn, Name, Age} is a super key for R (STUDENT) but not a key, because removing Name and Age still leaves us with a super key.  The value of a key attribute can be used to identify uniquely each tuple in the relation.  A relation schema may have more than one key. In this case, each of the keys is called a candidate key  For example, the CAR relation in Figure 3.4 has two candidate keys: License_number and Engine_serial_number  Primary key of the relation is the candidate key whose values are used to identify tuples in the relation. Primary keys are underlined in schema diagram.  The choice of one to become the primary key is arbitrary.  The other candidate keys are designated as unique keys. 13

Cont. Null value constraints Another constraint on attributes specifies whether NULL values are or are not permitted. NULL  Can have or have not value NOT NULL  Must have value, it can not be empty 14

Relational Databases and Relational Database Schema A relational database usually contains many relations, with tuples in relations that are related in various ways. A relational database schema S is a set of relation schemas S = {R1, R2, ..., Rm }. A relational database state DB of relational schema S is a set of relation states DB = {r1, r2, ..., rm} such that each ri is a state of Ri Example:  COMPANY = {EMPLOYEE, DEPARTMENT,DEPT_LOCATIONS, PROJECT, WORKS_ON, DEPENDENT} When we refer to a relational database, we implicitly include both its schema and its current state. 15

Cont. Attributes that represent different concepts may have the same name in different relations For example  we could have used the attribute name Name for both PROJECT and DEPARTMENT. The same real-world concept is used in different roles (meanings) in the same relation  Ssn and super_ssn …. Must have different names 16

Integrity constraint Integrity constraint states that no primary key value can be NULL  because the primary key value is used to identify individual tuples in a relation. Having NULL values for the primary key implies that we cannot identify some tuples Key constraints and entity integrity constraints are specified on individual relations. Referential integrity constraint is specified between two relations and is used to maintain the consistency and relationship among tuples in the two relations. the referential integrity constraint states that a tuple in one relation that refers to another relation must refer to an existing tuple in that relation  Dno of EMPLOYEE gives the department number for which each employee works,hence, its value in every EMPLOYEE tuple must match the Dnumber value of some tuple in the DEPARTMENT relation Referential integrity constraint is maintained by foreign keys. 19

20 Referential integrity constraints

Foreign key Specify a referential integrity constraint between the two relation schemas R1 and R2. A set of attributes FK in relation schema R1 is a foreign key of R1 that references relation R2 if it satisfies the following rules.  The attributes in FK have the same domain(s) as the primary key attributes PK of R2.  A value of FK in a tuple t1 of the current state r1(R1) either occurs as a value of PK for some tuple t2 in the current state r2(R2) or is NULL. In the former case, we have t1[FK] = t2[PK] R1 is called the referencing relation and R2 is the referenced relation. A foreign key could have NULL values  when doesn’t related to other relation  when it is not being used as primary key a foreign key can refer to its own relation 21

Other types of constraints Semantic integrity constraints  the salary of an employee should not exceed the salary of the employee’s supervisor  maximum number of hours an employee can work on all projects per week is 56.  specified and enforced within the application programs that update the database General-purpose constraint specification language.  Use Mechanisms called triggers and assertions 22

Operations and Constraint violation The operations of the relational model can be categorized into retrievals and updates  retrievals operations  database modification or update operations three basic operations that can change the states of relations in the database: Insert, Delete, and Update Whenever these operations are applied, the integrity constraints specified on the relational database schema should not be violated 23

Insert operation The Insert operation provides a list of attribute values for a new tuple t that is to be inserted into a relation R insert can violate any of the four types of constraints discussed  Domain constraints can be violated if an attribute value is given that does not appear in the corresponding domain  Key constraints can be violated if a key value in the new tuple t already exists in another tuple in the relation r(R)  Entity integrity can be violated if any part of the primary key of the new tuple t is NULL  Referential integrity can be violated if the value of any foreign key in t refers to a tuple that does not exist in the referenced relation If an insertion violates one or more constraints, the default option is to reject the insertion 24

Delete operation The Delete operation can violate only referential integrity. This occurs if the tuple being deleted is referenced by foreign keys from other tuples in the database. Several options are available if a deletion operation causes a violation  restrict rejects the deletion  Cascade attempts to cascade (or propagate) the deletion by deleting tuples that reference the tuple that is being deleted  set null  set default 25

The Update operation  The Update (or Modify) operation is used to change the values of one or more attributes in a tuple (or tuples) of some relation R.  Updating an attribute that is neither part of a primary key nor of a foreign key usually causes no problems.  check to confirm that the new value is of the correct data type and domain  Modifying a primary key value is similar to deleting one tuple and inserting another in its place  If a foreign key attribute is modified, the DBMS must make sure that the new value refers to an existing tuple in the referenced relation 26

The Transaction Concept A database application program running against a relational database typically executes one or more transactions A transaction is an executing program that includes some database operations, such as  reading from the database  applying insertions, deletions, or updates to the database. At the end of the transaction, it must leave the database in a valid or consistent state that satisfies all the constraints specified on the database schema 27

Fundamentals of database system - Relational data model and relational database constraints

More Related Content

What's hot

Viewers also liked

Similar to Fundamentals of database system - Relational data model and relational database constraints

More from Mustafa Kamel Mohammadi

Recently uploaded

Fundamentals of database system - Relational data model and relational database constraints