db-integrity.pdf - Databases - Data Integrity Jianxin Li...

This preview shows page 1 - 8 out of 31 pages.

Databases - Data IntegrityJianxin LiSchool of Computer Science & Software EngineeringUniversity of Western AustraliaJianxin Li (UWA)Data Integrity1 / 30
Data IntegrityFor most applications it is imperative that the database remain in a logicallyconsistent state, so there are a variety of mechanisms to help preservedataintegrity.Next week, we will discuss thetransactionsmechanism, which is a verylow-level mechanism robust to genuinely unpredictable events, such assystem crashes and the actions of other users.This helps maintain data integrity in several ways:Transactions protect against system crashes half-way through a sequenceof statements reflecting a single logical operationTransactions permit multiple users to simultaneously use the databasewithout needing to be aware of each otherJianxin Li (UWA)Data Integrity2 / 30
Referential integrityReferential IntegrityReferential integritymeans that whenever a value in one tablerefers toa tuplein another table, then the system should ensure that there is always a matchingtuple in the second table.Consider the following schema:Student (id:integer, name:string)Unit (code:string, name:string)Enrolled (sid:integer, ucode:integer)Here,Enrolled.sidrefers totheidcolumn in theStudenttable, whileEnrolled.ucoderefers tothecodecolumn in theUnittable.(Notice that the names of the columns inEnrolleddo not have to match thecolumns that are being referred to.)Jianxin Li (UWA)Data Integrity3 / 30
Referential integrityCurrent contentsmysql> select*from Student;+------+----------+| id| name|+------+----------+|123 | Jane||456 | Ebenezer ||789 | Martin|+------+----------+mysql> select*from Unit;+----------+---------------------+| ucode| name|+----------+---------------------+| CITS1402 | Databases|| CITS2211 | Discrete Structures |+----------+---------------------+2 rows in set (0.00 sec)Jianxin Li (UWA)Data Integrity4 / 30
Referential integrityLegitimate enrolmentsAny tuple inEnrolledshould should then connect alegitimate studentwithalegitimate unit.Of the four commandsINSERT INTO Enrolled VALUES(123, "CITS1402");INSERT INTO Enrolled VALUES(124, "CITS1402");INSERT INTO Enrolled VALUES(123, "CITS1412");INSERT INTO Enrolled VALUES(124, "CITS1412");only the first should succeed.The tuple(124, "CITS1402")refers to a student with id124, but thereis no such student — this is called adangling pointer.Jianxin Li (UWA)Data Integrity5 / 30
Referential integrityKey constraintsThe table containing the pointers (references) is called thechild table, whilethe tables that the pointers point to (the references refer to) are called theparent tables.(So in this case,Enrolledis the child table, andStudent,Unitare theparent tables.)When the referred-to column (or columns) are a key for the parent table, thenSQL provides a mechanism for ensuring that the references between thetables are always in a consistent state — this is thekey constraintmechanism.Jianxin Li (UWA)Data Integrity6 / 30
Referential integrityPrepare the parent tablesThe column (or columns) in each of parent tables must be a key for that table.

Upload your study docs or become a

Course Hero member to access this document

Upload your study docs or become a

Course Hero member to access this document

End of preview. Want to read all 31 pages?

Upload your study docs or become a

Course Hero member to access this document

Term
One
Professor
N/A
Tags
Foreign key, data integrity, Jianxin Li

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture