C1025 SQL Term 3 2007 Assignment – part 3 Final
D. Tuples
In relational database system, we also call rows as tuples. So each row or tuple formed a record for an object. Look at the sample from a part of member table below
|
Member |
||||||
|
MemberID |
FirstName |
LastName |
Gender |
DateOfBirth |
Phone |
|
| 1001 | Ryu | Ben | M |
10-Mar-84 |
6597740710 | ryub@hotmail.com |
| 1002 | Lou | Gandalf | M |
12-May-81 |
6597720520 | gadalou@yahoo.com |
| 1003 | Tana | Armstrong | F |
05-Dec-83 |
6281325590188 | cutie@yahoo.com |
| 1004 | Anthony | Bardack | M |
09-Aug-74 |
6587409230 | bardo98@gmail.com |
| 1005 | Richard | Leonheart | M |
05-May-70 |
6596836283 | leon@yahoo.com |
| 1006 | Melissa | Ciu | F |
01-Oct-80 |
6583729238 | meliciu@yahoo.com |
| 1007 | Gerween | Boween | F |
16-Nov-69 |
6587353626 | puccy2@yahoo.com |
The highlighted row shows a record for the object that is member with ID 1002, from that tuple/row we can see the information for that object, like the member ID is 1002, his first name is Lou, his last name is Gandalf, his gender is male (M), he was born on May 12, 1981, his phone number is 6597720520, and his e-mail is gandalou@yahoo.com. Beside that, each tuple/row should have a primary key column that function as unique identifier for each record and make sure that there is no duplicate record because the primary key column can’t has a duplicate value.
Although in the Member Table above the member ID is ordered from 1001 to 1007, actually the order of the tuples/rows is not significant. We can store tuples/rows in without an order because it is an unordered set.
E. Relational Database
A relational database use relations/tables to store its data. The used table is in the form of two dimensional table, it consist of columns that has the data type, rows that store records of objects, and field or table’s cell that contain single value.
The advantages of relational database are we can access and reassemble the data without having to reorganize the tables and it’s easy to extend the database because we can add another table even after the original database has been created. This relational database concept was invented by E. F. Codd at IBM in 1970. (SearchSQLServer.com Definitions, 2006)
According to the Informatics C1025 Study Guide (2006), there are three components of relational database, they are:
1. Collections of objects or relations that store the data.
2. A set of operators that can act on the relations to produce other relations.
3. Data integrity for accuracy and consistency.
A RDBMS (Relational Database Management System) is software that we can use to make, access, or manipulate a relational database. Example of the RDBMS is like Oracle, DB2, SQL Server, MySQL, Ms. Access, etc.
In a relational database system, we can use query to create, manipulate, control, or retrieve data according to what we need. We produce query by using SQL (Structured Query Language). So, if we issue a SQL command to the RDBMS, the RDBMS will send it to the database, and then the result will be showed to us.
As explained before, one advantage of using the relational database is the easy access to the database data, we can retrieve data according to what we need by issuing a SQL command without having to reorganize the data in the table. For example, assume that we want to retrieve some data from the library database (Library.mdb). The columns that we want to retrieve are BorrowID, FirstName, Title, and BorrowDate for borrowing events that occur between February and March 2007. We know that the BorrowID and BorrowDate column is in the Borrow Table, the FirstName column is in the Member Table, and Title Column is in the Book Info Table. They are data in separate tables, but we can retrieve these data without having to reorganize the tables in the database, because we are using relational database system and we can use SQL command to make such query to retrieve these data. Now, what we need to do to retrieve the data that we need is write this SQL command on the RDBMS:
SELECT Borrow.BorrowID, Member.FirstName, [Book Info].Title, Borrow.BorrowDate
FROM Borrow, Member, Book, [Book Info]
WHERE Borrow.MemberID = Member.MemberID
AND Borrow.BookID = Book.BookID
AND Book.TitleID = [Book Info].TitleID
AND BorrowDate BETWEEN #01-Feb-07# AND #31-Mar-07#;
and we will get this result:
|
Query1 |
|||
|
BorrowID |
FirstName |
Title |
BorrowDate |
| B0004 | Ryu | Chicken Soup for the Soul: Living Your Dreams |
14-Feb-07 |
| B0005 | Richard | Wheels on the Bus |
27-Feb-07 |
| B0006 | Rose | Marketing Research |
18-Mar-07 |
| B0007 | Link | World War II for Beginners |
25-Mar-07 |
The query result is a table with all information that we need, it got the BorrowID column, FirstName column, Title column, and BorrowDate column for the borrowing events that occur between February and March 2007. And the good news is that we don’t have to make any modification to the database’s tables at all. So that is the example of how the relational database gives us advantage of easier data access and reassembling of the data without having to reorganize the database’s tables.
F. Primary Keys
Primary key is an attribute/column in a table that contains unique value as an identifier for each row in the table. It can be formed by one column or combination of columns. The primary key column can’t contain duplicate data and cannot be null (no value), because the database use the primary key to distinct each row in the table, so every value in the primary key column must be unique.
Databasedev.co.uk suggests that there are two main function of primary key:
- Uniquely identifies the rows in the tables.
As the primary key store unique value for every rows in the table, so it can be used to identifies the rows.
- Provide ability to make relationships between tables in the database.
By the mean of primary key and foreign key, we can make relationship between the tables in a database. A primary key value in a table can appear as foreign key value in another table and make the two logically have a relationship.
For example of the use of primary key, look at the Member Table below:
|
Member |
||||||
|
MemberID |
FirstName |
LastName |
Gender |
DateOfBirth |
Phone |
|
| 1001 | Ryu | Ben | M |
10-Mar-84 |
6597740710 | ryub@hotmail.com |
| 1002 | Lou | Gandalf | M |
12-May-81 |
6597720520 | gadalou@yahoo.com |
| 1003 | Tana | Armstrong | F |
05-Dec-83 |
6281325590188 | cutie@yahoo.com |
| 1004 | Anthony | Bardack | M |
09-Aug-74 |
6587409230 | bardo98@gmail.com |
| 1005 | Richard | Leonheart | M |
05-May-70 |
6596836283 | leon@yahoo.com |
| 1006 | Melissa | Ciu | F |
01-Oct-80 |
6583729238 | meliciu@yahoo.com |
| 1007 | Gerween | Boween | F |
16-Nov-69 |
6587353626 | puccy2@yahoo.com |
| 1008 | Rose | Warden | F |
06-Sep-82 |
6597763532 | rosie82@yahoo.com |
| 1009 | Hanks | Power | M |
01-Dec-88 |
6285226818808 | hpower@hotmail.com |
| 1010 | Jin | Kazama | M |
07-Jul-77 |
6597732526 | jkzm@gmail.com |
| 1011 | Anna | Williams | F |
10-Aug-70 |
6586327134 | williams@hotmail.com |
| 1012 | Jackie | Chung | M |
11-Jun-85 |
6587888234 | jackcng@yahoo.com |
| 1013 | Mae | Queen | F |
29-Nov-79 |
6285225656888 | queen79@hotmail.com |
| 1014 | John | Bartel | M |
18-May-90 |
6595738292 | bart90@hotmail.com |
| 1015 | Sarah | Swan | F |
17-Oct-67 |
6588234622 | swan17@yahoo.com |
| 1016 | Michael | Carnoll | M |
31-Mar-88 |
6599236272 | carn_88@yahoo.com |
| 1017 | Mick | Connor | M |
14-Apr-80 |
6597321734 | mickey@hotmail.com |
| 1018 | Link | Gann | M |
02-Sep-75 |
6592719349 | zelda@hotmail.com |
| 1019 | Tirza | Eclia | F |
22-Aug-00 |
6593726622 | eclips@yahoo.com |
| 1020 | Windy | Zard | F |
30-Dec-79 |
6597777223 | zardia@gmail.com |
We can see that the Member Table has MemberID column, this is the table’s primary key. The MemberID column contains a unique value for each library’s member, so it makes sure that there are no duplicate rows in this table.
Now look at a part from Borrow Table below:
|
Borrow |
||||
|
BorrowID |
MemberID |
BookID |
BorrowDate |
ReturnDate |
| B0001 | 1003 | B0004 |
04-Jan-07 |
06-Feb-07 |
| B0002 | 1003 | B0007 |
07-Jan-07 |
07-Feb-07 |
| B0003 | 1002 | B0006 |
30-Jan-07 |
05-Feb-07 |
| B0004 | 1001 | B0040 |
14-Feb-07 |
20-Mar-07 |
| B0005 | 1005 | B0038 |
27-Feb-07 |
25-Mar-07 |
| B0006 | 1008 | B0036 |
18-Mar-07 |
01-Aug-07 |
| B0007 | 1018 | B0020 |
25-Mar-07 |
15-Apr-07 |
| B0008 | 1005 | B0012 |
01-Apr-07 |
29-Apr-07 |
| B0009 | 1005 | B0011 |
28-Apr-07 |
21-Jun-07 |
We can see that the Borrow Table also has the MemberID column, but in the Borrow Table, the MemberID column is not the primary key of the table as we can see that there are duplicate values in the MemberID column here. But, the value of MemberID column in the Borrow Table refers to the available value in MemberID column in the Member Table. So now we know that the Borrow Table has a relation with the Member Table by the mean of MemberID column that act as the primary key in the Member Table and as foreign key in the Borrow Table.
Another important thing about primary key is that we need to be careful in deciding which column will be the table’s primary key when we make a table. We have to make sure that the column we choose for the table’s primary key is unique. Some data shouldn’t be a primary key because they are not completely unique. For example, if we use the library member’s first name as the primary key for Member Table, there is always a possibility where the library will has two or more members with same first name, so this column shouldn’t be the table’s primary key.
Because there are many possibility that two or more person got a same particular data, there is a better way to make a primary key for a table, that is make use of a column that specially designed to make a unique value for the table’s rows. For example, in the Member Table, the MemberID doesn’t contain data that is natural information about a member, but this column is specially designed to assign a unique identifier for each member.
When we make a primary key column, we have some choice for data type of the column. We can choose a text or a number data type and write the primary key value manually every time we add a new record, or we can use the AutoNumber data type in Ms. Access 2003 so the software automatically assign a unique number for each record in the table.
If we choose the AutoNumber data type for our table’s primary key, we will have two options for the field size, long integer, or replication ID. The replication ID will assign a unique number in 16 bytes long format, while long integer will assign number in 4 bytes long. Although the replication ID option will give us a GUID or Globally Unique Identifier, but it’s better for us to choose the long integer. Because the replication ID will produce ID like {7EBE97A7-FFE1-481D-8ACD-F48CFD8CF918} or {8881E2DC-9121-4484-AB4E-66782E516664}, these ID will be greatly unique for primary key column, but it’s hard for us, humans, to distinct this kind of ID. If we use the long integer, it will produce normal number like 1 or 20 that is much easier for the user to understand. Beside that, the 4 bytes long, long integer will be processed faster by the computer rather than the 16 bytes long replication ID. (Databasedev.co.uk)
G. Composite Primary Keys
In case that we don’t make a column specially designed to contain a unique ID for each row in a table, we will use the object’s natural information. For example, for human object, we may use their first name, last name, birth date, phone number, etc. But as we know that two or more person possibly have same first name or same date of birth, so we can’t rely on one column that contain this data to be the primary key of the table that contain a unique data for the table’s rows. The solution of this problem is to use a Composite Primary Keys. With composite primary keys, we can use combination of columns rather than just one column as our table’s primary key. For example, we may want to uniquely identify each library’s member by their first name and birth date, so we will make a composite primary key using the FirstName column, and DateOfBirth column as the composite primary key that uniquely identifies each row in the Member Table.
H. Foreign Keys
Foreign key is a column in a table that matches with primary key column in another table. For example, look at the sample below:
|
Book Info |
|||||||
|
TitleID |
Title |
Author |
Publisher |
PubYear |
Edition |
Pages |
GenreID |
| BG001 | Goldwyn: A Biography | A. Scott Berg | Riverhead Trade |
1998 |
1 |
592 |
BGRP |
| BG002 | Sylvia Plath: A Biography | Linda Wagner-Martin | St. Martin’s Griffin |
1988 |
1 |
304 |
BGRP |
| BS001 | Marketing Research | David A. Aaker | John Wiley & Sons |
2005 |
1 |
686 |
BSN |
| BS002 | the e-business (r)evolution | Daniel Amor | Hewlett Packard co. |
2002 |
2 |
864 |
BSN |
| BS003 | E-Commerce | Kenneth C. Laudon | Addison Wesley Longman |
2004 |
2 |
936 |
BSN |
| CD001 | Wheels on the Bus | Jerry Smith | Grosset & Dunlap |
1991 |
1 |
18 |
CHD |
| CD002 | Duck’s Day Out | Jez Alborough | Collins |
2004 |
1 |
32 |
CHD |
| CP001 | Guide to Network Defense and Countermeasures | Greg Holden | Course Technology |
2003 |
1 |
527 |
CMP |
| CP002 | Macromedia Flash for Windows & Machintosh | Katherine Ulrich | Peachpit Press |
2002 |
1 |
613 |
CMP |
| CP003 | The Art of Programming | Steven C. Lawlor | PWS Publishing co. |
1996 |
1 |
567 |
CMP |
| CP004 | A Book on C | Al Kelley & Ira Pohl | Addison Wesley Longman |
1998 |
4 |
726 |
CMP |
| DC001 | World War II for Beginners | Erroll Selkirk | Writers & Readers Publishing |
1991 |
1 |
176 |
DCM |
| DC002 | The Irish Famine: A Documentary | Colm Toibin | Profile Books Limited |
2005 |
2 |
224 |
DCM |
| FC001 | Harry Potter and the Deathly Hallows | J. K. Rowling | Arthur A. Levine Books |
2007 |
1 |
784 |
FC |
| FC002 | The Great Eagle Spirit | Hal Granum | PublishAmerica |
2006 |
1 |
78 |
FC |
| FC003 | One Nation Under Par | Mark Nemcek | Publish To Go LLC |
2006 |
1 |
230 |
FC |
| FC004 | Snow Dog | Jim Kjelgaard | Yearling |
1983 |
1 |
176 |
FC |
|
Genre |
|
|
GenreID |
GenreDesc |
| BGRP | Biography |
| BSN | Business |
| CHD | Childrens |
| CMP | Computing |
| DCM | Documentary |
| FC | Fiction |
| HST | History |
| PSY | Physicology |
| Science Fiction | |
The sample shows a part of the Book info Table and Genre Table. We can see there that the Book Info Table has GenreID column that is a foreign key, because it match with the GenreID column in the Genre Table that act as the Genre Table’s primary key.
As we can see in the sample above, the existence of foreign key and primary key can form a logical relationship between two tables. The value in the foreign key column in a table must match with the available value of the primary key value in the other table where the foreign key refers to. Like in the example, if the Genre Table just has 9 kind of genre ID (BGRP, BSN, CHD, CMP, DCM, FC, HST, PSY, and SF), the GenreID column in the Book Info will just have that 9 kind of genre ID. Additionally, if primary key column can’t contain a duplicate data or null, a foreign key column can contain a null or duplicate data. Because the data in the foreign key column is not used as a unique identifier for the table’s rows, so it can contain duplicate data.
The relationship between primary key and foreign key often described as one to many relationships, which mean type once (in primary key) refer to many (in foreign key). Like in the example of Genre Table and Book Info Table above, we just need to type once for each kind of genre in the Genre Table, but it can be used for reference for many rows in the Book Info Table’s GenreID column.
The above picture shows us how one (1) to many (∞) relationship between the table will be displayed in Ms. Access 2003’s Table Relationships. The number 1 is written next to the table’s primary key column name, and then the infinity symbol is placed next to the foreign key column name that matches with the name of primary key column on the other end of the relationship’s line.
REFERENCES
Books Information for the Book Info Table in the Library database is retrieved on September 20, 2007, from http://www.amazon.com
Cheeseman, Aaron, 2001, AWD San Francisco database integration: What is a Database, anyway?, retrieved on September 18, 2007, from http://www.awdsf.com/courseware/database/database_explained.htm
Cl500.net, Basic Database Tutorial: Database-Advantages & Disadvantages, retrieved on September 15, 2007, from http://www.cl500.net/pros_cons.html
Databasedev.co.uk, Defining an Attribute Makeup, retrieved on September 18, 2007, from http://www.databasedev.co.uk/decomposing_attributes.html
Databasedev.co.uk, Relational Database Design-Primary Keys, retrieved on September 22, 2007, from http://www.databasedev.co.uk/primary_keys.html
Informatics Study Guide, 2006, C1025 Structured Query Language (Access 2003), Informatics Campus: Singapore.
SearchSQLServer.com Definitions, 2006, Relational Database, retrieved on September 21, 2007, from http://searchsqlserver.techtarget.com/sDefinition/0,,sid87_gci212885,00.html
Do you have any comment on this? maybe you have something to ask me or want to discuss with me about this?Or maybe you are IDIC student and taking this module also?
Feel free to use the comment box below (click here if you cannot see the comment box).
