What Options Are Available For Controlling Data Integrity At The Field Level?
Main Body
Chapter 9 Integrity Rules and Constraints
Adrienne Watt & Nelson Eng
Constraints are a very important feature in a relational model. In fact, the relational model supports the well-defined theory of constraints on attributes or tables. Constraints are useful because they allow a designer to specify the semantics of information in the database. Constraints are the rules that force DBMSs to check that information satisfies the semantics.
Domain Integrity
Domain restricts the values of attributes in the relation and is a constraint of the relational model. However, there are real-earth semantics for information that cannot be specified if used only with domain constraints. We need more specific ways to state what data values are or are non allowed and which format is suitable for an attribute. For instance, the Employee ID (EID) must be unique or the employee Birthdate is in the range [January 1, 1950, Jan 1, 2000]. Such information is provided in logical statements called integrity constraints.
There are several kinds of integrity constraints, described beneath.
Entity integrity
To ensure entity integrity, it is required that every table have a primary cardinal. Neither the PK nor whatsoever part of it can comprise null values. This is because null values for the master key hateful we cannot place some rows. For example, in the EMPLOYEE table, Telephone cannot be a chief key since some people may not have a telephone.
Referential integrity
Referential integrity requires that a foreign primal must have a matching primary key or it must exist null. This constraint is specified between two tables (parent and child); it maintains the correspondence between rows in these tables. It means the reference from a row in ane table to some other tabular array must be valid.
Examples of referential integrity constraint in the Customer/Order database of the Company:
- Client( CustID , CustName)
- Lodge( OrderID , CustID, OrderDate)
To ensure that there are no orphan records, we need to enforce referential integrity. An orphan record is one whose foreign key FK value is not constitute in the corresponding entity – the entity where the PK is located. Recall that a typical join is betwixt a PK and FK.
The referential integrity constraint states that the client ID (CustID) in the Order table must match a valid CustID in the Customer table. Almost relational databases accept declarative referential integrity. In other words, when the tables are created the referential integrity constraints are prepare.
Here is another example from a Class/Class database:
- Course( CrsCode , DeptCode, Description)
- Class( CrsCode, Department , ClassTime)
The referential integrity constraint states that CrsCode in the Class table must match a valid CrsCode in the Course tabular array. In this situation, it'due south not plenty that the CrsCode and Section in the Class table make upward the PK, we must also enforce referential integrity.
When setting up referential integrity it is of import that the PK and FK have the same data types and come from the same domain, otherwise therelational database direction system (RDBMS) volition not permit the bring together. RDBMS is a pop database organisation that is based on the relational model introduced by E. F. Codd of IBM'southward San Jose Research Laboratory. Relational database systems are easier to use and understand than other database systems.
Referential integrity in Microsoft Admission
In Microsoft (MS) Admission, referential integrity is prepare by joining the PK in the Customer table to the CustID in the Order table. See Figure 9.one for a view of how this is done on the Edit Relationships screen in MS Admission.
Referential integrity using Transact-SQL (MS SQL Server)
When using Transact-SQL, the referential integrity is set when creating the Order table with the FK. Listed below are the statements showing the FK in the Order table referencing the PK in the Client table.
CREATE Table Customer
( CustID INTEGER Principal Central,
CustName CHAR(35) )
CREATE TABLE Orders
( OrderID INTEGER PRIMARY Fundamental,
CustID INTEGER REFERENCES Customer(CustID),
OrderDate DATETIME )
Foreign key rules
Boosted foreign primal rules may be added when setting referential integrity, such every bit what to do with the child rows (in the Orders table) when the tape with the PK, office of the parent (Customer), is deleted or changed (updated). For example, the Edit Relationships window in MS Access (see Effigy 9.1) shows two additional options for FK rules: Cascade Update and Cascade Delete. If these are not selected, the organization will prevent the deletion or update of PK values in the parent table (Client table) if a child tape exists. The child record is whatever record with a matching PK.
In some databases, an additional option exists when selecting the Delete option called Ready to Null. In this is chosen, the PK row is deleted, but the FK in the child table is set to NULL. Though this creates an orphan row, it is acceptable.
Enterprise Constraints
Enterprise constraints – sometimes referred to as semantic constraints – are additional rules specified by users or database administrators and tin can exist based on multiple tables.
Hither are some examples.
- A class tin can accept a maximum of 30 students.
- A instructor tin can teach a maximum of four classes per semester.
- An employee cannot take part in more than v projects.
- The salary of an employee cannot exceed the salary of the employee's manager.
Business Rules
Business rules are obtained from users when gathering requirements. The requirements-gathering process is very of import, and its results should be verified by the user before the database blueprint is built. If the business rules are incorrect, the design will be incorrect, and ultimately the application built will not part equally expected by the users.
Some examples of business rules are:
- A teacher can teach many students.
- A class can take a maximum of 35 students.
- A course can be taught many times, but by only i instructor.
- Not all teachers teach classes.
Cardinality and connectivity
Business rules are used to determine cardinality and connectivity. Cardinality describes the relationship between ii data tables by expressing the minimum and maximum number of entity occurrences associated with one occurrence of a related entity. In Effigy nine.2, you tin can encounter that cardinality is represented by the innermost markings on the relationship symbol. In this effigy, the cardinality is 0 (zero) on the correct and 1 (one) on the left.
The outermost symbol of the relationship symbol, on the other hand, represents the connectivity between the two tables. Connectivity is the relationship between two tables, e.g., one to one or ane to many. The only time it is zero is when the FK tin can be nil. When it comes to participation, at that place are three options to the human relationship betwixt these entities: either 0 (zero), 1 (one) or many. In Figure 9.ii, for case, the connectivity is 1 (i) on the outer, left-hand side of this line and many on the outer, correct-hand side.
Effigy nine.3. shows the symbol that represents a one to many relationship.
In Effigy 9.4, both inner (representing cardinality) and outer (representing connectivity) markers are shown. The left side of this symbol is read as minimum one and maximum 1. On the correct side, information technology is read equally: minimum 1 and maximum many.
Human relationship Types
The line that connects two tables, in an ERD, indicates the relationship type between the tables: either identifying or not-identifying. An identifying relationship will accept a solid line (where the PK contains the FK). A non-identifying relationship is indicated by a broken line and does not contain the FK in the PK. See the department in Affiliate 8 that discusses weak and strong relationships for more caption.
Optional relationships
In an optional relationship, the FK can be zero or the parent table does not need to have a respective child tableoccurrence. The symbol, shown in Figure nine.six, illustrates one type with a zero and three prongs (indicating many) which is interpreted as zero OR many.
For example, if you look at the Society table on the correct-manus side of Figure 9.7, you'll find that a client doesn't need to place an guild to be a customer. In other words, the many side is optional.
The relationship symbol in Effigy ix.7 tin also exist read every bit follows:
- Left side: The order entity must incorporate a minimum of i related entity in the Customer table and a maximum of one related entity.
- Right side: A customer can place a minimum of zero orders or a maximum of many orders.
Figure 9.8 shows another blazon of optional relationship symbol with a zero and ane, meaning nil OR one. The one side is optional.
Figure 9.9 gives an case of how a zero to one symbol might be used.
Mandatory relationships
In a mandatory human relationship, one entity occurrence requires a corresponding entity occurrence. The symbol for this relationship shows one and simply ane as shown in Effigy ix.ten. The one side is mandatory.
See Figure 9.11 for an case of how the one and only one mandatory symbol is used.
Figure ix.12 illustrates what a i to many relationship symbol looks like where the many side is mandatory.
Refer to Effigy ix.13 for an example of how the one to many symbol may be used.
So far nosotros have seen that the innermost side of a human relationship symbol (on the left-side of the symbol in Figure 9.fourteen) tin can have a 0 (zero) cardinality and a connectivity of many (shown on the correct-side of the symbol in Figure 9.14), or one (not shown).
Yet, it cannot accept a connectivity of 0 (zero), as displayed in Effigy 9.xv. The connectivity can only be i.
The connectivity symbols show maximums. So if you recollect about it logically, if the connectivity symbol on the left side shows 0 (zilch), then at that place would be no connection betwixt the tables.
The way to read a relationship symbol, such equally the ane in Figure 9.16, is equally follows.
- The CustID in the Social club tabular array must also be institute in the Customer table a minimum of 0 and a maximum of 1 times.
- The 0 ways that the CustID in the Guild table may be zilch.
- The left-near 1 (correct before the 0 representing connectivity) says that if there is a CustID in the Order tabular array, it can just be in the Customer table once.
- When you see the 0 symbol for cardinality, you can assume ii things: T
- the FK in the Order table allows nulls, and
- the FK is non part of the PK since PKs must not contain null values.
business concern rules: obtained from users when gathering requirements and are used to determine cardinality
cardinality: expresses the minimum and maximum number of entity occurrences associated with one occurrence of a related entity
connectivity: the relationship between two tables, east.g., one to 1 or i to many
constraints: the rules that forcefulness DBMSs to check that data satisfies the semantics
entity integrity: requires that every tabular array take a master key; neither the main key, nor any part of it, can contain null values
identifying relationship: where the principal central contains the strange key; indicated in an ERD by a solid line
integrity constraints: logical statements that state what information values are or are non immune and which format is suitable for an aspect
mandatory relationship :one entity occurrence requires a corresponding entity occurrence.
not-identifying relationship: does not contain the foreign fundamental in the chief fundamental; indicated in an ERD by a dotted line
optional human relationship: the FK can be nothing or the parent table does not demand to take a respective child tabular array occurrence
orphan tape: a record whose foreign key value is non found in the corresponding entity – the entity where the primary key is located
referential integrity: requires that a foreign fundamental must take a matching primary key or it must be null
relational database direction system (RDBMS): a popular database system based on the relational model introduced by E. F. Codd of IBM's San Jose Enquiry Laboratory
relationship type: the blazon of relationship between two tables in an ERD (either identifying or non-identifying); this relationship is indicated by a line drawn between the two tables.
Read the following clarification and and then answer questions one-5 at the end.
The swim lodge database in Figure 9.17 has been designed to agree information about students who are enrolled in swim classes. The following data is stored: students, enrollment, swim classes, pools where classes are held, instructors for the classes, and various levels of swim classes. Apply Effigy 9.17 to answer questions 1 to five.
The master keys are identified below. The post-obit data types are defined in the SQL Server.
tblLevels
Level – Identity PK
ClassName – text 20 – nulls are not allowed
tblPool
Pool – Identity PK
PoolName – text xx – nulls are not allowed
Location – text thirty
tblStaff
StaffID – Identity PK
FirstName – text 20
MiddleInitial – text 3
LastName – text xxx
Suffix – text 3
Salaried – Bit
PayAmount – money
tblClasses
LessonIndex – Identity PK
Level – Integer FK
SectionID – Integer
Semester – TinyInt
Days – text xx
Fourth dimension – datetime (formatted for time)
Pool – Integer FK
Instructor – Integer FK
Limit – TinyInt
Enrolled – TinyInt
Price – money
tblEnrollment
LessonIndex – Integer FK
SID – Integer FK (LessonIndex and SID) Primary Key
Status – text thirty
Charged – bit
AmountPaid – money
DateEnrolled – datetime
tblStudents
SID – Identity PK
FirstName – text 20
MiddleInitial – text three
LastName – text 30
Suffix – text three
Altogether – datetime
LocalStreet – text thirty
LocalCity – text 20
LocalPostalCode – text 6
LocalPhone – text 10
Implement this schema in SQL Server or access (you lot will need to pick comparable data types). Submit a screenshot of your ERD in the database.
- Explain the human relationship rules for each human relationship (east.yard., tblEnrollment and tblStudents: A student can enroll in many classes).
- Identify cardinality for each relationship, assuming the following rules:
- A pool may or may not always take a course.
- The levels table must e'er be associated with at least ane grade.
- The staff table may non have ever taught a class.
- All students must be enrolled in at to the lowest degree ane class.
- The class must have students enrolled in information technology.
- The class must have a valid puddle.
- The grade may not have an instructor assigned.
- The class must always be associated with an existing level.
- Which tables are weak and which tables are strong (covered in an earlier affiliate)?
- Which of the tables are non-identifying and which are identifying?
Image Attributions
Figures 9.three, ix.4, 9.6, 9.8, 9.x, 9.12, 9.14 and 9.fifteen past A. Watt.
What Options Are Available For Controlling Data Integrity At The Field Level?,
Source: https://opentextbc.ca/dbdesign01/chapter/chapter-9-integrity-rules-and-constraints/
Posted by: sylvestershent1937.blogspot.com
0 Response to "What Options Are Available For Controlling Data Integrity At The Field Level?"
Post a Comment