Basic Database Terminologies

By
Irfan Abdullah
Database
• A database is a named collection of tables. A database can also
contain views, indexes, sequences, data types, operators, and
functions. Other relational database products use the term
catalog.
Table (relation, file, class)
• A table is a collection of rows. A
table usually has a name, although
some tables are temporary and
exist only to carry out a command.
All the rows in a table have the
same shape (in other words, every
row in a table contains the same
set of columns). In other database
systems, you may see the terms
relation, file, or even class all these
are equivalent to a table.
Column/Field/Attribute
• A column is the smallest unit of
storage in a relational database.
A column represents one piece
of information about an object.
Every column has a name and a
data type. Columns are grouped
into rows, and rows are grouped
into tables. In Figure, the
shaded area depicts a single
column.
• The term field and attribute
have the same meaning.
Row /Record/Tuple
• A row is a collection of
column values. Every row in a
table has the same shape (in
other words, every row is
composed of the same set of
columns). In Figure, the
shaded area depicts a row.
• The terms record or tuple are
equivalent to a row.
View
• A view is an alternative way to
present a table (or tables). You
might think of a view as a "virtual"
table.
• A view is (usually) defined in terms
of one or more tables. When you
create a view, you are not storing
more data, you are instead creating
a different way of looking at
existing data.
• A view is a useful way to give a
name to a complex query that you
may have to use repeatedly.
Data Type & Key
Data Type
• Data types define what type of data a column can contain.

Key
• A DBMS key is an attribute or set of an attribute which helps
you to identify a row(tuple) in a relation(table). They allow you
to find the relation between two tables. Keys help you uniquely
identify a row in a table by a combination of one or more
columns in that table.
Data Types Available in a
Relational Database
Data Types
Character
• Character data types to deal with printable and displayable
characters. Char holds a single character whereas String contains an
indefinite number of characters.
Integer
• Integer data types hold numbers that are whole, or without a decimal
point.
• Example
• 234
• 45678
• 1
• 67
Data Types
Real Number
• These data types have a precision, or a positive integer that defines the number of
significant digits. This type of data representation is commonly called floating-
point representation.
• Example
• 2.2 ● 34.56 ● 1.1

Boolean Data
• The Boolean data type is a data type that has one of two possible values (usually
denoted true and false).

Date and Time

• The DATETIME type is used for values that contain both date and time parts.
Field Data Types In MS-Access
Short Text A String 0 to 255 characters long.

Long Text A String up to 64,000 characters long.

Number A Number holds numbers. You will be safe using Long and Single. There are six types: byte,
integer, decimal, long, single and double.
Byte, integer and long cannot hold fractions.
Date/Time Holds dates and times.

Currency A number field designed for Currencies and floating point precision numbers.

AutoNumber A special Long number. Access can change this field automatically and is designed to be used as
a Primary Key field.
Yes/No A Boolean field and may be Yes/No, On/Off or True/False

OLE Object For string BLOBs – binary large objects.

Field Data Types In MS-Access
Hyperlink Contains hyperlink text. It has the additional functionality that being clicked will activate the
link within.

Attachment May contain 0, 1 or many files. This provides a convenient and more efficient way to store
OLE Objects.

Calculated Field Results of a calculation. The calculation must refer to other fields in the same table. You
would use the Expression Builder to create the calculation. Note, Calculated fields were first
introduced in Access 2010.
Lookup Displays either a list of values that is retrieved from a table or query, or a set of values that you
specified when you created the field. The Lookup Wizard starts and you can create a Lookup
field. The data type of a Lookup field is either Text or Number, depending on the choices that
you make in the wizard.
Lookup fields have an additional set of field properties, which are located on the Lookup tab
in the Field Properties pane.
Introduction to Database Keys
SLO # 7.2.3
Database Keys
• Keys are very important part of
Relational database model.
They are used to establish and
identify relationships between
tables and also to uniquely
identify any record or row of
data inside a table.
• A Key can be a single attribute
or a group of attributes, where
the combination may act as a
key.
Why we need a Key?
• In real world applications, number of tables required for storing the data is
huge, and the different tables are related to each other as well.

• Also, tables store a lot of data in them. Tables generally extends to

thousands of records stored in them, unsorted and unorganised.

• Now to fetch any particular record from such dataset, you will have to
apply some conditions, but what if there is duplicate data present and
every time you try to fetch some data by applying certain condition, you get
the wrong data. How many trials before you get the right data?

• To avoid all this, Keys are defined to easily identify any row of data in a
table.
Types of Database Keys
• The database key types can be broadly grouped into two groups. The first group of keys include
unique keys and the second group is non-unique keys.
• A database key is said be unique when it has a unique value for each row ( that is tuple / record /
instance ).
• A database key is said be non-unique when a unique key of one table is used in another table with
non-unique value for the purpose of defining a relationship between the two tables.
• The different types of database keys used in relational model include :
• Super Key.
• Candidate Key.
• Primary Key.
• Alternate Key.
• Secondary Key.
• Foreign Key.
• Compound Key.
• Composite Key.
• Surrogate Key.
Super Key
• The term super key is defined as
either a single attribute or group of
attribute that can be used to
uniquely identify each row in a
table.
• The super key is the most general
form of unique key. The table can
have many super keys. A set of
super keys is usually a superset for
other keys.
• In the context of database design,
the database designers will first
identify number of super keys in a
relation for a given functional
dependencies.
Candidate Key
• Candidate keys are defined as the minimal set of fields which
can uniquely identify each record in a table. It is an attribute or
a set of attributes that can act as a Primary Key for a table to
uniquely identify each record in that table. There can be more
than one candidate key.
• A candiate key can never be NULL or empty and its value should
be unique.
• There can be more than one candidate keys for a table.
• A candidate key can be a combination of more than one
columns(attributes).
Primary Key
• Primary key is a candidate key that is most appropriate to
become the main key for any table. It is a key that can uniquely
identify each record in a table.
Alternate Key
• All the keys which are not primary key are called an alternate
key. It is a candidate key which is currently not the primary key.
However, A table may have single or multiple choices for the
primary key.
Secondary Key
• An entity may have one or more choices for the primary key.
Collectively these are known as candidate keys. One is selected
as the primary key. Those not selected are known as secondary
keys.
Foreign Key
• Foreign key is a column that
creates a relationship between
two tables. The purpose of
Foreign keys is to maintain data
integrity and allow navigation
between two different instances
of an entity. It acts as a cross-
reference between two tables as
it references the primary key of
another table. Every
relationship in the model needs
to be supported by a foreign key.
This concept is also known as
Referential Integrity.
Compound Key
• Compound key has two or more attributes that allow you to
uniquely recognize a specific record. It is possible that each
column may not be unique by itself within the database.
However, when combined with the other column or columns the
combination of composite keys become unique. The purpose of
the compound key in database is to uniquely identify each
record in the table.
Composite Key
• Key that consists of two or more
attributes that uniquely identify
any record in a table is called
Composite key. But the
attributes which together form
the Composite key are not a key
independently or individually.
• The difference between
compound and the composite
key is that any part of the
compound key can be a foreign
key, but the composite key may
or maybe not a part of the
foreign key.
Surrogate Key
• Surrogate KEYS is An artificial key which aims to uniquely
identify each record is called a surrogate key. This kind of
partial key in DBMS is unique because it is created when you
don’t have any natural primary key. They do not lend any
meaning to the data in the table. Surrogate key in DBMS is
usually an integer. A surrogate key is a value generated right
before the record is inserted into a table.
Difference between Primary and Foreign Key

Primary Key Foreign key

• Helps you to uniquely identify a • It is a field in the table that is the
record in the table. primary key of another table.
• A foreign key may accept multiple
• Primary Key never accept null null values.
values. • A foreign key cannot automatically
• Primary key is a clustered index create an index, clustered or non-
and data in the DBMS table are clustered. However, you can
manually create an index on the
physically organized in the foreign key.
sequence of the clustered index. • You can have multiple foreign keys
• You can have the single Primary in a table.
key in a table.