Filed under: Databases
Constraints are used within a database to ensure data integrity. The three main types of integrity constraints are primary key, foreign key, and domain integrity. A primary key is an attribute or field within a table and contains unique data, which is used in relationships to identify the data within rows in a table (Rob & Coronel, 2004, p. 79). Entity integrity constraints require that the value of a primary key in an entity must not be null (Adamski & Finnegan, 2006, AC A11). Although they cannot always be avoided, null values typically indicate poor database design (Rob & Coronel, 2004, p. 83). In an effort to maintain integrity during the design process, many database design applications deny the creation of a primary key if it contains a null value. Entity integrity also requires that the primary key in an entity contain a unique identifier. This ensures the unique identity of each entity itself so that the data can be referenced by foreign keys in other entities (Rob & Coronel, 2004, p. 85).
An example of entity integrity would be a database that contains a table called EMPLOYEES. The table would contain an entity named EMPLOYEE_ID. Each employee would have a unique employee ID to identify them. This ID could be any number, even a social security number for example, as long as it is unique to the table. This field would be required any time a new record is added to the table.
Referential integrity refers to the consistency of the data that is shared between foreign and primary keys in related tables (Rob & Coronel, 2004, p. 84). Referential integrity requires that a foreign key, which does not contain a null value, correspond to data in a primary key within another entity (Adamski & Finnegan, 2006, AC A11). Another benefit of referential integrity is that it does not allow the deletion of primary keys, which are referenced by foreign keys in related entities. Some database management systems allow the deletion of primary keys but offer options on how to handle the relation between keys. For instance, you can specify cascading deletion, which finds all related foreign keys and deletes them when a primary key is deleted so that the data object is not orphaned (DBMS Online, 1996).
An example of referential integrity would be a table called DEPARTMENTS that lists all departments that an employee could belong to where each department is uniquely identified by a primary key value such as DEPARTMENT_ID. Then this entity would be referenced in the EMPLOYEES table under a foreign key entity called DEPARTMENT_ID (Rob & Coronel, 2004, p.110).
Reference:
Rob, P. & Coronel, C. (2004). Database Systems: Design, Implementation, & Management. Boston: Course Technology
Adamski, J. J. & Finnegan, K. T. (2006). New Perspective on Microsoft Office Access 2003, Second Ed. Boston: Course Technology
DBMS Online. (1996). Retrieved April 29, 2006 from http://www.dbmsmag.com/9606d17.html
Filed under: Databases
Relationships between tables in a relational database are much like relationships between people. There are varieties of different types of relationships that exist between people. When working with a relational database, three of the main relationship types are one-to-one, one-to-many, and many-to-many (ONLamp, 2006)
One way to look at relationships would be to look at a family. A one-to-one relationship is much like the relationship that exists between a mother and a father or husband and wife. Each child in the family has only one biological mother and one biological father and in a family, there is typically only one father and one mother. Therefore, the relationship that exists between the mother and father is a one-to-one relationship (Rob & Coronel, 2004, p. 95). However, for the sake of argument, if the father had multiple wives then it could be considered a one-to-many relationship.
Another one-to-many relationship would be the one that exists if a person has more than one child. Using the family as an example again, if a mother and father have multiple children then the relationship that exists between each parent and their children would be a one-to-many relationship. It would be one mother for many children and one father for many children. However, if you were to consider the mother and father to be a single entity set known as “parents” and they had multiple children, then this would be a many-to-many relationship. Of course, each child would never have more than two biological parents. Although, they could have multiple stepparents, or again, the father could have multiple wives. Either way, this would be an example of a many-to-many relationship.
The following is a description of how the relationships between family members could be described in a database. In the one-to-one relationship described above, you would have a table called FATHERS, a table called MOTHERS, and another table called CHILDREN. The FATHERS table would contain a primary key, which uniquely identifies each father. The table might also contain other entities, such as name, age, address, etc… The FATHERS table would contain foreign keys, which refer to unique ID’s that would actually be the primary key in the CHILDREN table. Each record in the CHILDRENS table would contain a unique ID as the primary key as well as other entities, such as name, age, address, etc… Attributes of these entities might include “unique”, “not null”, “data type”, etc… The structure of the MOTHERS table would be nearly identical to the FATHERS table. The relationship between the MOTHERS and FATHERS table would exist by using foreign keys that refer to the primary key of the opposite table.
Using the same table design, the relationship between the mother in the MOTHERS table and her children in the CHILDREN table, assuming she has more than one child, would indicate a one-to-many relationship.We could create another entity set called PARENTS, which would contain a primary key such as PARENTS_ID, as well as two foreign keys that refer to the MOTHER_ID in the MOTHERS table and FATHER_ID in the FATHERS table, both of which are primary keys. The CHILDREN table would contain a foreign key, which refer to the primary key of the PARENTS table, or PARENT_ID. There could be multiple children for each set of parents, but considering that the parents refer to foreign keys in the MOTHERS and FATHERS table this would be considered a many-to-many relationship (Rob & Coronel, 2004, p. 99).
Here is another way the family database might be organized using the common shorthand method (Adamski & Finnegan, 2006, AC A9-10):
Parents (Parent_ID, Mother_ID, Father_ID)
Foreign key: Mother_ID to Mothers table
Foreign key: Father_ID to Fathers table
Mothers (Mother_ID, Name, Age, Address)
Foreign key: Mother_ID to Parents table
Fathers (Father_ID, Name, Age, Address)
Foreign key: Father_ID to Parents table
Children (Child_ID, Name, Age, Address, Parent_ID)
Foreign key: Parent_ID to Parents table
Below is a diagram of how the data in the family database would be related.
Reference:
ONLamp. (2006). Retrieved April 29, 2006 from http://www.onlamp.com/pub/a/onlamp/2001/03/20/aboutSQL.html
Rob, P. & Coronel, C. (2004). Database Systems: Design, Implementation, & Management. Boston: Course Technology
Adamski, J. J. & Finnegan, K. T. (2006). New Perspective on Microsoft Office Access 2003, Second Ed. Boston: Course Technology
Debugging an application involves finding bugs in a piece of software’s code and eliminating them (http://en.wikipedia.org/wiki/Debugging, 2006). A bug is something that causes an application to run improperly or crash when given circumstances are present. Testing is the process used to identify bugs within an application (http://en.wikipedia.org/wiki/Software_testing, 2006). When testing an application, several test cases should be used so that as many bugs as possible are identified (http://en.wikipedia.org/wiki/Test_Case, 2006). This reduces the possibility that the software will generate errors or crash when it is used.
From my experience with testing and debugging software, I would have to say that no matter how simple a program is, you can not necessarily guarantee that all bugs will be discovered during the testing phase. One example might be that of Microsoft’s notoriously buggy software such as Internet Explorer or even Windows (http://www.networkworld.com/news/2005/041105-windows-crash.html, 2006). Certainly a company such as Microsoft would employ as many tactics as possible to uncover all bugs in their own software before releasing it to the public, however every time they release new software, users begin discovering bugs almost immediately. This would indicate to me that even the most seasoned developers have a hard time finding all of the bugs in their software.
Resources:
Wikipedia – Debugging. Retrieved April 17, 2006 from http://en.wikipedia.org/wiki/Debugging
Wikipedia – Software Testing. Retrieved April 17, 2006 from http://en.wikipedia.org/wiki/Software_testing
Wikipedia – Test Case. Retrieved April 17, 2006 from http://en.wikipedia.org/wiki/Test_Case
How to Solve Windows Systems Crashes in Minutes. Retrieved April 17, 2006 from http://www.networkworld.com/news/2005/041105-windows-crash.html
It is never required that you use arrays when programming, however, arrays are useful in that they can significantly reduce the amount of code you must write when working with multiple variables that contain related data. This then reduces the amount of time you must spend writing code (www.sheridanc.on.ca, 2006). The components of a one-dimensional array include the array name, the index, and the data type. A one-dimensional array works by assigning a name to the array, specifying how many elements the array will contain by using an index number and declaring the data type of the array. All elements in an array must be of the same data type. Variables within an array are initialized by assigning values to the elements using the array name and variable index. In VB.NET, array indexes are always zero-based, meaning they always start at 0. Therefore, when referencing an index number, it will always be 1 less the variable number. For instance, the following array: Fruits(4) would represent a one-dimensional array named Fruits that contains 5 elements, from 0 to 4. The third element in the array would be referenced as variable(2).
It is possible to create fixed-size arrays and dynamic arrays within VB.NET. An array is created by using the Dim or Public keyword depending on its scope. To declare a fixed-size array in an event procedure, you would type: Dim ArrayName(x) As DataType where ArrayName is the name, x is the number of elements minus 1, and DataType is the data type of all of the elements. An example would be: Dim Fruits(5) As String. The Fruits array will contain 6 elements of the string data type. In the event where the number of elements is unknown, a dynamic array should be used (en.wikipedia.org, 2006). A dynamic array is declared within an event procedure in VB.NET by typing: Dim ArrayName() As DataType. The only difference in declaring a dynamic array versus a fixed-size array is that you do not specify the number of elements in the declaration. Instead, you ReDim the array using a variable as the index. For instance, Dim Fruits() As String would declare the dynamic array named Fruits. You would then assign a value to a variable such as TotalFruits = 1 and then type: ReDim Fruits(TotalFruits). This way, the number of elements in the array is equal to the value of the TotalFruits variable which can be changed as needed.
To create a two-dimensional array within an event procedure in VB.NET, you would type: Dim ArrayName(x, x) As DataType. The difference is that there are two numbers of elements assigned to the array instead of one. To declare a two-dimensional array that contains 5 elements by 4 elements, you would type: Dim ArrayName(4, 3) As DataType. Two-dimensional arrays are used when you have multiple variables that contain similar or related data and share common elements as well (www-ee.eng.hawaii.edu, 2006). For instance, if you have an array named StudentGrades(4, 3) you could store 5 students and 4 grades for each student using the following structure:
Grade0, Grade1, Grade2, Grade3
Student0
Student1
Student2
Student3
Student4
Although beyond the scope of this article, it is important to note that arrays can contain more than two dimensions. Multi-dimensional arrays are used more frequently in graphics programming (en.wikipedia.org, 2006).
Resources:
Why Are Arrays Useful? Retrieved April 5, 2006 from http://www.sheridanc.on.ca/~jollymor/logic/sess8.html
Wikipedia – Array. Retrieved April 5, 2006 from http://en.wikipedia.org/wiki/Array
Two Dimensional Arrays. Retrieved April 5, 2006 from http://www-ee.eng.hawaii.edu/~tep/EE160/Book/chap9/section2.1.1.html
A For Next loop is beneficial in that it combines several actions into one statement, thereby consolidating code and making it easier to identify its components. Using the For Next loop format allows a programmer to create and initialize a counter variable, set a value range, increment or decrement the value and execute the next step in a loop process (Gambas-Loop, 2006). One of the disadvantages to using For Next loops is that they are less flexible than other looping methods because you must know how many times the loop will be executed. Also, sometimes For Loops can be difficult to read and understand (Object Oriented Software Engineering Knowledge Base, 2006). A situation where you might use a For Next loop would be if you were to display a list of numbers or letters such as 1 through 100 or A through Z. You would specify a variable range, increment the value of the counter variable and loop through each element from the beginning of the range to the end of the range (The FOR Statement, 2006).
For example:
for x = 1 to 100
show x
x = x + 1
next
All of the information I have run across seems to indicate that there are no true advantages or disadvantages to using a Do While vs. a Do Until loop. Both types of loops perform essentially the same action. The main thing to keep in mind when choosing a Do loop type is code clarity. You should use whichever loop type makes the code and logic easiest to read and understand (The Do Until Loop, 2006). There can be advantages to using a Do loop over a For loop in that they can be executed an unknown number of times. So, if you are unsure as to how many times a loop should run, you would definitely use a Do loop. Also, many times Do loops aren’t as difficult to understand as For Loops because the code is split up into separate components.
The main difference between a Do While loop and Do Until loop is that a Do While loop is used when the statements in a loop must be executed at least once. In a Do While loop, all of the statements will be continuously executed as long as a certain condition is true. As soon as the condition is false, the loop exits (Two Loops & Flow Charting, 2006). It is important to remember that when using a Do While loop, the condition is tested at the bottom of the loop. In a Do Until loop however, the condition is tested at the top of the loop and should be used when a condition must be tested before the loop executes. If the condition is false, all of the statements will be continuously executed as long as a certain condition remains false. In this case, as soon as the condition is true, the loop exits (Two Loops & Flow Charting, 2006).
Here are examples of a Do While and a Do Until loop:
While x is less than 100, add 1 to x.
Do While x < 100
x = x + 1
End
Until x is more than 100, add 1 to x
Do Until x > 100
x = x + 1
End
Note: The only difference in the code is that the operator changes from < to >.
References:
Gambas-Loop. Retrieved April 1, 2006 from http://en.wikibooks.org/wiki/Gambas-Loop
For Loops. Retrieved April 1, 2006 from http://www.cs.utah.edu/~hamlet/lib/lessons/uces-13/uces-13/node6.html
Object Oriented Software Engineering Knowledge Base. Retrieved April 1, 2006 from http://www.site.uottawa.ca:4321/oose/index.html#forloop
The FOR Statement. Retrieved April 1, 2006 from http://www.arjay.bc.ca/Modula-2/Text/Ch5/Ch5.4.html
Two Loops & Flow Charting, 2006. Retrieved April 1, 2006 http://www.macdonald.egate.net/CompSci/h2loops.html
The Do Until Loop. Retrieved April 4, 2006 from http://www.informit.com/library/content.asp?b=STY_VB6_24hours&seqNum=89&rl=1
Janea Taylor - Ancient Tech Blog 