1 / 21

Relational Data Model

Relational Data Model. Relational Database Terminologies. Relation is a table with columns and rows Attribute  named column of a relation Domain set of allowable values for one or more attributes . Each attribute has values taken from a domain .

drew
Télécharger la présentation

Relational Data Model

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. Relational Data Model

  2. Relational Database Terminologies • Relation is a table with columns and rows • Attribute  named column of a relation • Domain set of allowable values for one or more attributes. • Each attribute has values taken from a domain. • For example, the domain of Name is string and that for salary is real • Tuple a row of the relation • Degree:- number of columns of the relation. Ex: the above table has degree 4 • Cardinality:- Number of tuples in a relation. Ex: the above table has cardinality=1 • Remark: often data type is taken for the domain; like char for the attribute gender, string(Text) for the attributes stID & stName attributes Tuple

  3. Cont’d…

  4. Formal Definition of a relation • Given a collection of n types of domain Ti (i=1,2, …, n) not necessarily all distinct, r is a relation on those types if it consists of two parts, a heading & a body, where: • Theheading is a set of n attributes of the form Ai: Ti, where the Ai (which must be distinct) are the attributes of r and the Ti are the corresponding type names ( i=1,2,..,n) • The body is a set of m tuples t, where t in turn is aset of components of the form Ai:Vi in which Vi is a value of type Ti

  5. Cont’d… • Example: Consider the following • Heading= {StID: string, stName: string, score:Number} • Body= { {stID: 001, stName:Abebe, score:90}stID: 002, stName:Taye, score:50 }} • Q: the above relation has cardinality _______ and degree ____

  6. Property of a relation • A relation should have a unique name • A relation should not contain duplicate tuples • Tuples are unordered (may not be ordered) • Similar to the set: {a, 2}={2,a} • Attributes are unordered • Each tuple contains exactly one value for each attribute

  7. Relational Keys • Keys are used to enforce integrity rules • The 1st type of key is called super key • Super key:- an attribute, or set of attributes that uniquely identifies a tuple with in a relation Possible supper keys 1. {stID} 2. {stID, stName} 3. {stID, score} 4. {stID, stName, score} • Candidate key:- is a super key such that no proper subset of it is a super key with in the relation • Ex: {stID}

  8. Cont’d… • Candidate Key (Key Attribute) • An attribute or set of attributes that uniquely identifies individual occurrences of an entity type. • Example: ID. No of a student, SSN of a person • Key attributes are very important in databases • A candidate key, k, for a relation R has two properties: • Uniqueness:- in each tuple of R, the values of k uniquely identify that tuple • Irreducibility:- no proper subset of k has the uniqueness property • Primary key:- it is a candidate key that is selected to identify tuples uniquely with in a relation • Ex: primary key: Region# or RegionName

  9. Cont’d… • Primary Key • An entity type may have one or more possible candidate keys, one of which is selected to be the primary key. • Foreign Key:-A foreign key is a field in a relational table that matches the primary key column of another table. • The foreign key can be used to cross-reference tables. • Alternative terminologies • Relation table file • Tuple Row Record • Attribute Column Field

  10. Cont’d… • Setting primary key will improve performance, for example one can search records by primary key • Foreign key:- is a set of attributes whose values are required to match values of some candidate key of the home or base relation • stID in the Grade relation is a foreign key and stID in the student relation is a candidate key Referencing relation Home relation

  11. Cont’d… • A foreign key can contain null value • A foreign key can be simple or composite (Composite involves many attributes) • If a foreign key is composite then the home relation should contain composite candidate key • The foreign – to- candidate key match – “glue”

  12. Relational Data Integrity • Data integrity rules ensures that the data is accurate and consistent • Three integrity rules • Domain Constraint • Entity integrity rule:- says a primary key can not contain a null value • Referential integrity rule:- if a freign key exists in a relation, either the foreign key value must match a candidate key value of some tuple in its home relation (referenced relation) or the foreign key value must be wholly null • Null value:- is not a value. It is not zero or it is not the null string (“”)

  13. Cont’d… • Causes for null value: • The value is not known at the time of data entry • The value for that attribute is not applicable for the current tuple • Example: Null value

  14. Referential Action • Consider update and Delete operations • There are three rules: • CASCADE:- The update or Delete on the home relation (Referenced relation) cascades to update or delete the matching tuples in the referencing relation • If we delete B01 in Branch relation the 1st two records of Employee will be deleted • Updates if we update the home relation ( For example if B01 is changed to B05 in the Branch relation, then all B01’s in the Employee relation will be updated to B05)

  15. Cont’d… • RESTRICTIVE (Enforce Referential Integrity) • The UPDATE or DELETE on the home relation (Referenced relation) will be restricted to the case where there are no such matching in the referencing relation • We can delete the 3rd record of the Branch relation • NO ACTION • The delete or update are performed exactly as requested, no more and no less • Can potentially cause referential integrity violation

  16. Views • Base Relation: • A named relation corresponding to an entity in the conceptual schemas whose tuples are physically stored in the database • View: • The dynamic result of one or more relational operations operating on the base relations to produce another relation • A view is a virtual relation • Its definition is stored in the database but the records are not

  17. SELECTstName, Address FROM student WHERE gender = “F” • is a definition stored in the database and becomes a table during runtime

  18. Cont’d… • Views are important for:- • Security • For efficient use of memory • Easiness • Base relation is much faster than the views (low accessing speed) • A view is a relation that appears to the user as if it exists, manipulated as if it is a base relation • When we modify a view that modification will be propagated to the base relation and • Updating the base relation will propagate to the view

  19. Advantages of Views • Provide security • To provide customized data • It simplifies complex operations • Views can provide logical data independence

  20. Cont’d… • A view should be defined using simple query on a single or multiple relations SELECTstName, DeptName FROM student, Department WHEREstudent.DeptNo=Department.DeptNoAND gender = “F” Q. What is the result of the above operation (SQL statement)?

  21. Next: Conceptual Data Model (ER Model)

More Related