C1025 SQL Term 3 2007 Assignment – part 2
II. Explanation on Database Terminologies
A. Database
Database is a set of related data organized in a manner that allow use, retrieval, access, and maintenance of the data. So, every table that contains the data in the database is related to the other table in the database, at least to one other table. There is no fully standalone table, if so, that table is actually not the part of the database, or actually we don’t need to store that table at all. For example, In Library.mdb all the tables there are related with the other table in order to store the information needed by the library. If I add a table that contain data about the kind of pet food in that database, this table’s data will not relevant and not related to any other table in the database, so I don’t have to store it there, because a database will only store data that are related with each other. Another example, a shop database may have data about the shop’s staff, the shop’s goods, and the goods’ supplier.
With the DBMS (Database Management System), software that allow a user manage a database, we can retrieve, use, edit, add, delete, create form, or make a report referring to the data in the database. For example, I can use the Library database that I have created to retrieve information about a library member, what book he borrow and when he borrow that book, and what’s the detail information about that book, or I can make a report about how many member borrow a book in a certain month, how many book borrowed in a month, and much more.
Ø Advantages and Disadvantages of Database
The use of database to store data will give us some advantages, they are:
a) Data Independence
The data in database is independent, the change or modification in the system or applications that use its data will not affect the data in the database. This mean low system or program maintenance.
b) Data consistency
There is no risk that a data is updated in one system and not in another system. Because the data is independent, so when an application make an update to the data in the database, the other application will see the same updated data because these applications refer to the same independent Database. This means that with the use of database to store data, we will get data consistency.
c) Controlled Redundancy
More advantage on using database is that we can avoid making unnecessary duplicate data. If we not using a database, each application that we use must have its own data files, and some application may have a set of the same data files. In a small number of applications, maybe this issue is not a big problem, but if we have a lot of applications with data files for each application, there will be so much space wasted to store copies of same data, and updating them will consume so much time. But thanks to the database, that we don’t have to get this problem.
d) Data Integrity
Using the DBMS, user can specify constraints on data, like determining that an entry to certain field is essential, the field can’t be empty, the field can contain duplicate data or not, etc. These give the user ability to keep the integrity of the data in the database.
e) Data Security
Because the database is stored in one place, so it is easier for the database administrator to control the right of the database user on how far the user may look or update the data in the database. Some users may be granted access to the entire database, while other users just can access a part of the database. For example, in the Library database (Library.mdb) the database administrator may grant the library member a right to access the data about the books but not the data about the other library member, while the librarian can have a full access to all the data in the library’s database and the administrator can give special password to access the database to ensure only authorized person can access the database. These mean that we can ensure the data security in a database.
Beside the advantages of database, according to cl500.net, there are some disadvantages of using database.
The disadvantages of using database are:
a) Complexity of database design
Designing a database is a bit complex because the database designer has to specify each field, table, form, and the other database element carefully to make sure that the database will be reliable and easy to use. In another words, we need a well trained and experienced person to do the complex and time consuming database designing process.
b) Expensive starting cost
To start using a database in an office or enterprise, we will need an expensive cost to provide the hardware (e.g. database server) and the software or the DBMS (e.g. Microsoft Access), where we need to buy an expensive license for the software.
c) High dependency on the database
Because all the critical data and important information is stored in one place, the database, so there is a high dependency on the database. Worse situation may take place if there is a serious damage or failure on the database, this situation will affect all applications and activities that depends on the database, make them failed to do their function too.
d) The need of trained user and administrator
In using the database, we will need a trained database administrator to maintain the database and trained user who understand at least how to operate the database, how to view the data that he/she need, and how to update certain data. In some case, an office or enterprise may need to hold training for its staffs to give them skills in using the database, that mean more expenses.
B. Tables
|
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 |
A relational database system has an object called table, it could has one or multiple tables. The table is used by the database to store the data. A table consists of several parts; they are the table name, columns, rows, and cells.
The example above shows the Member Table from Library.mdb. We can see that the Member Table has its name written on top of the table, that part is what we call the table name.
Beside the table name, Member Table also has some columns, and each column has its own name, there are MemberID, FirstName, LastName, Gender, DateOfBirth, Phone and e-mail column. Thus, we can set a certain data type for each column, which is just one data type for each column; additionally we can also set any other attributes for the column. For example, the DateOfBirth column has “Date/Time” as its data type, and the data format is medium date (dd-mmm-yy), so we can see that every data entered in this column should be a data about date and showed in medium date format. If we enter another data like numbers in this column, there will be an error because the data input is not match with the data type specified for the column.
Another part of a table is the table row. The row store the data for the column according to each column’s data type, and it form a record for a particular object. For example, in the Member Table showed above, a row formed the record for a library’s member, like the member with ID 1001 has his first name is Ryu, his last name is Ben, his gender is male (M), he was born at 10th March 1984, his phone number is 6597740710, and his e-mail address is ryub@hotmail.com.
The last part of a table is the cell. A cell is formed from column and row, the intersection between two vertical lines from a column and two horizontal lines from a row formed a space called a cell. While a row make a record for a particular object from several data, a cell just stores a single data (one value) where the data type depends on the data type of the column where the cell is placed.
That is all about the parts of a table. Another issue about database table according to Cheeseman (2001) is although a table used by a database to store data, each table should just deals with one sort of item, and we should store different kinds of items in different table. For example, in the Member Table we just store the data about certain item, which in this case is library’s member. We not store the data about what book borrowed by a member in this database, we store that data in another table. For the book’s data, we store it in Book Table, detail data for each book title in the library is stored in the Book Info Table, the list of available book genre in the library is stored in the Genre Table, and data for each book borrowing event is stored in the Borrow Table. So we have different table for different set of item.
For example, look at the Book Info table and Book Table 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 |
| HS001 | The Complete Book of World History | School Specialty Publishing | American Education Publishing |
2001 |
1 |
288 |
HST |
| HS002 | The Complete Book of U.S. History | School Specialty Publishing | American Education Publishing |
2001 |
1 |
352 |
HST |
| HS003 | Source Book of Medical History | Logan Clendening | Dover Publications |
1960 |
1 |
685 |
HST |
| PS001 | Your Ten- to Fourteen-Year-Old | Louise Bates Ames | Dell |
1989 |
1 |
368 |
PSY |
| PS002 | The Evolution of Love | Ada Lampert | Praeger Publishers |
1997 |
1 |
144 |
PSY |
| PS003 | Chicken Soup for the Soul: Living Your Dreams | Jack Canfield | HCI |
2003 |
10 |
384 |
PSY |
| SF001 | Gray/Guardians | Kathy Porter | BookSurge Publishing |
2007 |
1 |
368 |
SF |
| SF002 | The Other Side of Yore | J. Lyon Laiden | Booklocker, Inc. |
2007 |
1 |
156 |
SF |
| SF003 | The Time Travelers Academy | Reginald Williams | Lulu.com |
2006 |
1 |
228 |
SF |
|
Book |
||
|
BookID |
TitleID |
Reference |
| B0001 | BG001 |
Yes |
| B0002 | BG001 |
Yes |
| B0003 | BG002 |
No |
| B0004 | BG002 |
No |
| B0005 | BS001 |
No |
| B0006 | BS001 |
No |
| B0007 | BS001 |
No |
| B0008 | BS002 |
Yes |
| B0009 | BS002 |
Yes |
| B0010 | BS003 |
No |
| B0011 | BS003 |
No |
| B0012 | CD001 |
No |
| B0013 | CD001 |
No |
| B0014 | CD002 |
Yes |
| B0015 | CD002 |
Yes |
| B0016 | CP001 |
No |
| B0017 | CP002 |
Yes |
| B0018 | CP003 |
No |
| B0019 | CP004 |
No |
| B0020 | DC001 |
No |
| B0021 | DC001 |
No |
| B0022 | DC002 |
No |
| B0023 | FC001 |
No |
| B0024 | FC002 |
Yes |
| B0025 | FC003 |
No |
Above is Book Info Table, while on the left is a part from Book Table’s content. We need these two tables because there is a possibility where the library has some book with the same title. Look At the highlighted rows in the Book Table on the left, it shows that the library has three books with the same title ID (BS001). The Book Table just store the book’s title ID, while the title name and the other detail information of the book itself is stored in the Book Info Table.
If we don’t use the Book Info table to store the detail information for every book title, we will need to write each book’s detail information such as its author, publisher, publication year, etc. every time we enter a book record even the books has the same title, so if the library has three books with same title, we will need to write each book’s detail information for three times, and this is a waste of time and storage space. But if we use a separate table to store the detail information about each book’s title, we won’t need to do that. We just need to write the book’s title ID in Book Table, and when we need to see the book’s detail information, we can look for it in the Book Info Table according to the book’s title ID written in the Book Table, this will save our time and storage space because we don’t need to store unimportant duplicates of the same data. That’s why using table to store just one sort of item is important in the database system.
C. Attributes
In relational database system we can alternatively call a column as an attribute. An attribute holds one type of data for all the rows fall below this attribute/column. There are some points that we need to remember when creating attribute/column:
1. Attribute/column name: An attribute should have a name that represents what kind of data inside of it. For example, in the Member Table, the DateOfBirth column contain data about the member’s date of birth, the FirstName column contain the data about the member’s first name, and so on. By giving the proper name for each attribute/column that represents the data inside the attribute, it will increase the readability of the data in the database.
2. Attribute’s data type: An attribute shouldn’t have one type of data in one row and another type of data in the other row because each attribute just have one type of data for all the rows inside it. Therefore, it is also important to choose the right data type for an attribute/column. After choosing the most appropriate data type for a column, we can also set some parameters in the column’s properties like the field size for text data type or the display format for Date/Time data type.
3. Column/attribute’s order: Column/attribute’s order is not significant. For example we can place the FirstName column after the LastName column and vice versa. We just specify the attribute’s order to increase the readability of the data, like placing the column about name before the column about date of birth, etc.
4. Key value or non-key value: An attribute/column can contain a key value that store unique data for a table. There are two kind of key value, primary key and foreign key. If an attribute become the primary key of a table, it will contain data that uniquely define each record in the table. For example, the MemberID column in the Member Table is a primary key, it contain numbers that uniquely represent each member’s record. If an attribute is foreign key, it logically defines how the table is related with another table. But, if an attribute not contain a key value, it will contain one kind of data in the table.
5. Decomposing attribute: Decomposing attribute means that we need to store different data in separate attributes to ensure the data integrity and increase the database performance. For example, in the Member Table, we store the member’s first name, last name, and date of birth in three different attribute rather than store the three different data in one column. If we store those data in one column, they will have same data type, but if we decompose the attribute into three attribute, we can assign different methods to ensure data integrity for each attribute, like we can choose text data type for the first name and last name attribute, and we choose Date/Time for the data type of date of birth attribute. By doing this, we can also increase the database performance in data retrieval because it is easier when we use the SQL command to see just the first name of the patient or just his/her date of birth. (databasedev.co.uk)
To be continued in the next post….
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).
