Copyright

Designing a Relational Database: Tutorial & Overview

Instructor: Temitayo Odugbesan

Temitayo has 11+ years Industrial Experience in Information Technology and has a master's degree in Computer Science.

This lesson is designed to teach the basic steps in relational database design using a library as an illustrative example. Basic relational database terminology is defined and explained with illustrations.

A database is a collection of information. This information is stored independently in a container of columns and rows called a table.

A mobile device store, which for example, has on display a number of smartphones and other mobile devices in their inventory for sale. These devices on display are not connected together informationally, rather, they are displayed with their respective information independent of the other.

A relational database organizes information in related tables. Related tables are tables that are connected, or linked to each other by some common attribute. Related tables are dependent on other tables for certain pieces of information. Your bank teller's computer is connected to the banks network (interconnected to other computers in the bank) without which your bank balance cannot be accessed.

Consider a Video Rental database:

relatedtables

There is an established relationship between the tables in the Video Zone database. The Loan Table displays the respective videos loaned to members of the Video Zone. Through the link it obtains the member's name (Member ID) from the Members Table and the video name loaned (Video ID) from the Video Table.

Relational Database Design Steps

Library Database

Step 1: What is the Purpose of the Database?

What are you collecting data about? A library database will be our illustrative example, therefore we will be collecting data on everything about the books and periodicals in the library, including their handling and movements.

Step 2: Data Collection

With our library database, data collection details would include books, authors, publishers, genres, shelf location, book status (book on shelf, out on loan or overdue), members, and membership categories.

In database design, we have listed the different entities that are going to comprise the database. An entity is anything (person, place or thing) about which data can be collected (attribute). Consider your national identity card (entity). It contains data and descriptive information (attributes) about you.

EntitiesAttributes

Entities are the basic building blocks of a database.

Step 3: Creating Tables and Identifying Primary Keys

Once our entities have been identified, their attributes (columns) are populated into individual tables.

Tables in the Library database will include:

  • BOOK Table
  • PUBLISHER Table
  • AUTHOR Table
  • GENRE Table
  • MEMBER Table
  • SHELF LOCATION Table

Let's consider the Book Table.

With so many books in our library, the Book Table will have numerous records of all of the different books (rows) and their attributes (columns) the library holds.

A snippet of our Book Table will look like this:

The Book Table.

Tables

The fundamental function of any database is to efficiently search through a bunch of data and extract meaningful information.

Searching always involves sifting through a bunch of similar items and if the items cannot be differentiated one from the other, then no meaningful information can be extracted. Consider 64 student assignments with no names written on them. The marked assignments are of no use to the academic board responsible for compiling student results. The students' scores are not identifiable.

Database tables are always going to hold multiple records (rows) which need to be differentiated one from another. Identifying a particular attribute (column) that uniquely differentiates one row in the table from the next is a key element. This attribute is known as a primary key.

In our Book Table, book title has been identified as the table's primary key: Book title*. Neither the dates in ''year published'' nor the numbers in ''edition'' could be used as unique identifiers. The table shows two books being published in 1729 and three books in their 9th edition. Therefore, we will not be able to distinguish one book from the other by either searching through the year published or the edition.

Step 4: Creating Table Relationships

The word relationship always brings to mind our sense of humanity. We will use human relationships to illustrate the dynamics of relationships between tables.

One to One relationship: 1 - 1

Monogamous Relationship

  • Person A can only be in a relationship with person B.

The reverse:

  • Person B can only be in a relationship with person A.

onetoone

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 160 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create An Account
Support