23-Sep-22
Basic Database
Terminologies
Define the following terms related to relational
database:
a. field/ attribute/ column
b. record/ tuple/ row
c. table
d. file
e. view
f. data type
g. key
SLO # 7.2.1
1
23-Sep-22
Database Terminologies
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. In Figure 1.1, the shaded
area depicts a single column.
The terms field and attribute have similar Figure 1.1. A column (highlighted).
meanings.
Database Terminologies
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 1.2, the shaded area depicts a
row.
The terms record or tuple are equivalent to a
row. Figure 1.2. A row (highlighted).
2
23-Sep-22
Class Assignment
Q1- Differentiate between rows and columns?
(Any two)
Database Terminologies
Table (relation, file)
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. The terms relation and file are all equivalent to a table.
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.
3
23-Sep-22
Class Assignment
Q2- Write two differences between table and views?
(Any two)
Database Terminologies
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.
4
23-Sep-22
Define the data types available in a
relational database; i.e. character,
integer, real number, Boolean data,
date and time;
SLO # 7.2.2
Data types in relational database
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
5
23-Sep-22
Data types in relational database
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).
Data types in relational database
Date and time
The DATETIME type is used for values that contain both date and time parts.
6
23-Sep-22
Class Assignment
Q3- Identify the data type that can be used in the
following table.
Column Pid Full Name BirthDate GrossSalary Address
Names
Data type
Data 12 John 12/12/1996 40000 Karachi
Differentiate among primary key,
candidate key, alternate key,
secondary key, foreign key;
SLO # 7.2.3
7
23-Sep-22
Keys in relational database
What is a Primary Key?
A column in a table which helps us to uniquely identifies every row in that table is
called a primary key. This DBMS can't be a duplicate. The same value can't appear
more than once in the table.
Rules for defining Primary key:
Two rows can't have the same primary key value
It must for every row to have a primary key value.
The primary key field cannot be null.
The value in a primary key column can never be modified or updated if any foreign
key refers to that primary key.
Keys in relational database
What is a Candidate Key?
A super key with no values is called candidate key.
Rules for defining Candidate key:
It must contain unique values
Candidate key may have multiple attributes
Must not contain null values
It should contain minimum fields to ensure uniqueness
Uniquely identify each record in a table
8
23-Sep-22
Class Assignment
Q4- Write any two purposes of using primary key.
Q5- Write two difference between primary key and
candidate key.
(Any two)
Keys in relational database
What is a 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.
9
23-Sep-22
Keys in relational database
What is a 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.
Keys in relational database
What is a Foreign Key?
A foreign key is a column which is added to create a relationship with another table.
Foreign keys help us to maintain data integrity and also allows navigation between two
different instances of an entity. Every relationship in the model needs to be supported
by a foreign key. This concept is also known as Referential Integrity.
Customer id (PK) Customer name Customer phone numer
Order ID (PK) Customer id (FK) Product name
10
23-Sep-22
Summary of Keys
Key: Any column to identify record.
1- Primary key: Any unique value that cannot be modified. Each table can have
one.
2- Candidate key: contains unique value. Each table can have more than one.
3- Alternative key: Any key except primary. Each table can have more than one.
4- Secondary key: Any key that could be chosen as primary key.
5- Foreign key: Any primary key in other table. Can be repeated.
Class Assignment
Q6- While having the same data type, why foreign key
is different from primary key. Support your answer
with suitable statement
11
23-Sep-22
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.
• Primary Key never accept null values. • A foreign key may accept multiple null
• You can have the single Primary key in values.
a table. • You can have multiple foreign keys in a
table.
12
