Thomaset2000’s Room

Hi! I want to share another assignment that I get during my International Diploma in Computing course from Informatics Computer School Singapore. This assignment is from C1025 Structured Query Language Module.. The main objective is that we have to make a Library database and then use that database as a reference to explain common terms in Relational Database System.

The Question:
Write detail notes to explain the following terminologies.
(a)    Database            [15]
(b)    Tables                [20]
(c)    Attributes            [10]
(d)    Tuples                [15]
(e)    Relational Database        [15]
(f)    Primary Keys            [10]
(g)    Composite Primary Key    [5]
(h)    Foreign Keys            [10]

Requirements for this paper are as follow:
•    Design and draw a database of 3 to 5 tables, to be used to explain the above terminologies. These tables must be realistic and related to indicate your understanding of RDB.
•    Your mock LIBRARY database must show the fields with realistic data.
•    You must display your in-depth understanding of the terminologies by their characteristic, purpose, advantage and disadvantages of their use (if any).
•    Use the mark allocation to serve as a guide for the volume and quality of your answer

My Answer:

My Library Database Introduction

In this part, I will show the screenshots from the database that I make for this assignment. The database name is Library.mdb. It was made using Microsoft Access 2003. The function of this database is to manage data for a library. This database contains the data needed by a library. So this database has some tables related to its function, there are Member Table that contain the library’s member data, Book Table that contain the ID for each book in the library and its status as a reference book or not, Book Info Table that contain the data about each book title in the library including its bibliography, Genre Table that contain the data about the kind of book genre available in the library, and Borrow Table that contain information about every book borrowing activity done by the library’s members.

The screenshots here will show each table with its design view and the relation between the tables.

List of tables

1. Book
2. Book Info
3. Borrow
4. Genre
5. Member

a) Member table

i. Design View

The screenshot shows the design view of Member Table. It has seven fields:

1.      MemberID : Store the member’s ID; this is the primary key of the table that uniquely identifies each member. The data type is text, field size is 6.

2.      FirstName : Member’s first name, data type is text, field size is 50.

3.      LastName : Member’s last name, data type is text, field size is 50.

4.      Gender : Member’s gender, with lookup value M (male) or F (female).

5.      DateOfBirth: Member’s date of birth. The data type is Date/Time, with medium date format (dd-mmm-yy).

6.      Phone: Member’s phone number. Data type is text, field size is 20.

7.   e-mail: Member’s e-mail address. Data type is text, field size is 50.

ii.      Content

Member
MemberID	FirstName	LastName	Gender	DateOfBirth	Phone	e-mail
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

This screenshot shows the content of Member Table.

b) Book Table

i. Design View

This is the screenshot of Book Table’s design view. This table has some fields:

1.      BookID : Unique ID for each book. We need this field because a library can has some books with the same title, but each book need its own ID. This field is the table’s primary key. The data type is text and the field size is 5.

2.      TitleID : The book’s title ID, refer to the available titleID in the Book Info Table.

3.      Reference : Contain data about whether a book is a reference book that can’t be checked out from the library or not. The data type is yes/no with display control as a checkbox.

The screenshot above shows the content of Book Table.

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
B0026	FC004	No
B0027	HS001	No
B0028	HS002	Yes
B0029	HS003	No
B0030	PS001	Yes
B0031	SF002	No
B0032	PS003	No
B0033	PS002	No
B0034	SF001	Yes
B0035	SF003	No
B0036	BS001	No
B0037	CP001	No
B0038	CD001	No
B0039	DC002	Yes
B0040	PS003	No

We can see here that the library has some books with same title, but with this table, each book has its own unique ID even if their title is the same.

c) Book Info Table

i. Design view

Image above is the screenshot of Book Info Table’s design view. This table contains the detail data about each book title available in the library. The fields are:

1.      TitleID: Uniquely define a book title. This is the table’s primary key. The data type is text, the field size is 5.

2.      Title: The book title. Data type is text, field size is 50.

3.      Author: The book’s author. Data type is text, field size is 50

4.      Publisher: The publisher of the book. Data type is text, field size is 50.

5.      PubYear: The book’s publication year. Data type is number.

6.      Edition: The book’s edition. Data type is number.

7.      Pages: The number of pages in the book. Data type is number.

8.      GenreID: The book’s genre ID, data type is text with lookup value refer to the available genre ID on the Genre Table.

ii. Content

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

The screenshot shows the content of Book Info Table.

d) Genre Table

i. Design view

This is the screenshot of the Genre Table. The fields are:

1.      GenreID : Uniquely define each kind of genre. This is the table’s primary key. Data type is text, and field size is 5.

2.      GenreDesc: Description of each genre ID. Data type is text, and field size is 50.

ii. Content

Genre
GenreID	GenreDesc
BGRP	Biography
BSN	Business
CHD	Childrens
CMP	Computing
DCM	Documentary
FC	Fiction
HST	History
PSY	Physicology
SF	Science Fiction

This is the screenshot of the Genre Table’s content.

e) Borrow Table

i. Design view

The Borrow Table has some fields, they are:

1.      BorrowID: Uniquely define a borrowing event. This is the primary key of the table. The data type is text, field size is 5.

2.      MemberID: Store the data about the ID of the member who borrow the book. The data type is text with lookup value to the available MemberID in the Member Table.

3.      BookID: Store the data about the ID of the borrowed book. The data type is text with lookup value to the available BookID in the Book Table.

4.      BorrowDate: Store the data about when the book is borrowed. The data type is Date/Time with medium date format (dd-mmm-yyyy).

5.      ReturnDate: Store the data about when is the date of the borrowed book returned. The data type is Date/Time, with medium date format (dd-mmm-yy).

ii. Content

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
B0010	1003	B0010	29-Apr-07	19-May-07
B0011	1018	B0004	03-May-07	21-Jun-07
B0012	1004	B0006	17-May-07	03-Jul-07
B0013	1010	B0026	30-May-07	30-Jul-07
B0014	1013	B0025	20-Jun-07	19-Jun-07
B0015	1011	B0027	24-Jun-07	01-Jun-07
B0016	1012	B0037	19-Jul-07	20-Jul-07
B0017	1015	B0035	26-Jul-07	06-Dec-07
B0018	1007	B0038	31-Jul-07	03-Aug-07
B0019	1017	B0036	31-Aug-07	15-Sep-07
B0020	1016	B0040	01-Sep-07	05-Oct-07
B0021	1016	B0007	24-Sep-07	25-Sep-07
B0022	1020	B0012	17-Oct-07	19-Oct-07

This screenshot shows the content of the Borrow Table.

f) Table Relationships

The above screenshot shows the relationship between the tables in the database. We can see that each table has a relation with another table by the mean of the table’s primary key and foreign key. The term primary key and foreign key will be explained in the second part of this assignment.

This is the end of the first part of this assignment. In this part I have explained about the Library.mdb which is the database I make for this assignment. I have given the database’s screenshots and explanation for every screenshot. Later in the second part of this assigment, I will use this database to help me to answer the assignment’s questions that ask me to explain some database terminologies.

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).

admin Education, IDIC, Informatics C1025, database, library database, sql, table, tuple