CCNC/CCNC Module 5/The database application/Database Concepts/How a Database is Organized

From WikiEducator
Jump to: navigation, search
Tutorial.png Database Concepts 

What is a Database | How a Database is Organized | Data Types | Normalization | Self Assessment | Summary & FAQs


How a Database is Organized

Icon objectives.jpg

Outcomes

Upon completion of this tutorial the learner will:
  • Understand how a database is organized in terms of tables, records and fields
  • Identify entities and attributes for a database design


Tables Records and Fields

A database is a collection or tables. The tables contain records that are organized into fields. Or using database terminology; A database is a collection of entities. The tables contain data that are organized into attributes. The use of these two terminologies is frequent when working with databases; become familiar with the terms table and entity, data and record and field and attribute being used interchangeably. Another important concept is the idea of a design view and a query view. Consider the two tables below, one being design view the second being query view;

A design view is a look at the structure of a table as it is being designed.

Students (in Design View)
field name data type length allow nulls
first_name Text 32 No
last_name Text 64 No
birth_date Date 10 No
gender Char 1 Yes
Icon present.gif
Tip: Basic data types
  • field name is the name given to the field as it will be stored in the database.
  • data type is the type of data stored in the database. Usually; Numeric, Text, Date or Char.
  • length is the number of characters long the field will be.
  • allow nulls indicates if the field can be left blank or empty



A query view is a look at the data in rows and columns. Notice the field names from design view are the column headers in query view.

Students (in Query View)
first_name last_name birth_date gender
Peter Rawsthorne 28/12/1963 M
David Rawsthorne 26/10/1961
Lisa Rawsthorne 11/03/1965 F
Malcolm Rawsthorne 03/05/1987 M
Hannah Rawsthorne 23/09/1989 F
Note: it is common for field names to not contain spaces. There are a number of different conventions
for naming fields which can change depending on the standards defined by the organization you are
working. The convention used in this course is to be clear with your field names and use underscores
for spaces. (i.e. 'first name' becomes first_name)



Icon activity.jpg
Activity
Design an address entity;
  • Using a pencil and paper write down all the fields for an address entity
  • List the fields in an order as they would happen on a form. It is important to keep fields names and order as they occur in the 'real' world.
  • Assess the fields and give each field a data type and length. Ask yourself if the field can be left blank.




Identifying entities and attributes

This is where we start to use the small school scenario introduced at the beginning of this module. From now on all the analysis and design we do will be based upon this scenario.

Identifying entities and attributes could be considered both and art and a science. It is an art for you need to bring together and analyze information from many different sources. The ability to understand a subject domain and identify the entities with their attributes challenges you to see something virtual from many perspectives and groupings, this can be a very creative process. It is also a science for we use proven analysis processes based upon relational algebra, this process refines how our database is structured into entities and attributes. Analyzing a subject domain for the purpose of designing a database means you identify three things; the entities, which are objects you seek information, the attributes, which are the data collected for an entity and the relationships among entities.


This simple diagram shows the relationship between the students and addresses entities.

CCNC-5.1.1.2-StudentsAddresses.jpg


And once we begin to add attributes to the entities, the diagram will look like this;

CCNC-5.1.1.2-StudentAddressAttributed.jpg


Entity diagram symbols

The symbols used to create these entity relationship diagrams are as follows;

CCNC-5.1.1.2-EntityRelationshipAttribute.jpg



Icon activity.jpg
Activity
Read over the small school scenario and using a paper and pencil identify all the entities described.
  1. Keep it simple, just identify an entity, and write down a word that describes the entity
  2. Draw a box around each entity
  3. Try and group related entities




Test your knowledge



Icon qmark.gif

How is a database organized

 

1. A table can also be known as?

(a) An attribute.
Incorrect, click here to review the topic.
(b) A data type.
Incorrect, click here to review the topic.
(c) An entity.
Absolutely Correct!
(d) None of the above.
Incorrect, click here to review the topic.

2. What symbol is used for the attribute in an entity relationship diagram?

(a) Circle.
Incorrect, click here to review the topic.
(b) Oval.
Absolutely correct!
(c) Diamond.
Incorrect, click here to review the topic.
(d) Rectangle.
Incorrect, click here to review the topic.

Your score is 0 / 0