Databases

(Chapter 8)
Database Types:
1) Flat File
2) Relational

1) Flat File:
A flat-file database is a database stored in a file.
Records follow a uniform format, and there are no structures for indexing or recognizing
relationships between records.
The file is simple.
A flat file can be a plain text file (e.g. csv, txt), or a binary file.

2) Relational Database:
A relational database is a type of database that stores and provides access to data points
that are related to one another.
Relational databases are based on the relational model, an intuitive, straightforward way of
representing data in tables.
What is the difference between a flat file and relational database?
A flat file is usually a table with rows of information, whereas a relational database contains
many tables with rows and columns that recognize relationships between different pieces of
information.
Keys are used for linking related data in different tables.
Flat files are usually used for simpler applications with less data than relational databases.
What are the advantages of using a relational database over a flat file database?
Some advantages of relational databases over flat files include lot more data can be
handled, increased security, many people can work at the same time, people can work from
many different locations, easier data entry and maintenance, and increased speed and
performance.
It is, however, important to notice that the advantages do not apply when things are so
simple that a relational database is not the right tool at all.
When would you use a flat file database?
Flat files are usually used for simple applications with a small amount of data that can be
stored in one table.
A commonly used example of a flat file is the contacts list on a phone.

1
Limitations of File Based System:

1) SEPARATION & ISOLATION OF DATA:

It is difficult to access the data which is isolated and stored in separate files. Imagine we have to
generate a single report of student, who is studying in particular class, his study report, his library
book details, and hostel information. All these information’s are stored in different files. How do
we get all these details in one report?

2) DUPLICATION OF DATA (REDUNDANCY):

There is uncontrolled duplication of data in the file-based approach. Duplication is waste of space
and it costs time and money to enter the data more than once. Additional space requirements
leads to additional costs. Duplication of data means that the data is no longer consistent which
leads to Loss of Data Integrity.

3) LOSS OF DATA INTEGRITY:

Imagine Student Details and Student_ReportCard files have student’s address in it, and there was a
change request for one particular student’s address.
The program searched only Student Details file for the address and it updated it correctly.
There is another program which prints the student’s report and mails it to the address mentioned
in the Student_ReportCard file.
What happens to the report of a student whose address is being changed? There is a mismatch in
the actual address and his report is sent to his old address. This mismatch in different copies of
same data is called data inconsistency (loss of data integrity).

4) DATA DEPENDENCE:
The data stored in file depends upon the application program through which the file was created.
It means that the structure of data files is coupled with application program.

5) INCOMPATIBLE FILE FORMATS:

The structure of files are dependent on the application programming languages. The structure of a
file generated by a PYTHON program may be different from the structure of a file generated by a
‘C’ program. The incompatibility of such files makes them difficult to process jointly.

6) FIXED QUERIES OF PROGRAMS:

File-based systems are very dependent upon the application developer, who has to write any
queries or reports that are required. There was no facility for asking unplanned queries about the
data or about the type of data available.

2
Exam Style Questions:
Question 1:

Answer:

3
Question 2:

Answer:

4
Database Management System (DBMS):
DBMS Tools:
1) Developer Interface:
It is used to create user friendly features e.g. forms to enter the new booking in a hotel.
It is used to create outputs e.g. report of bookings on a given date in a hotel.
It is used to create interactive features e.g. buttons or menus.
Tasks performed by Developer Interface:
1. Create a table
2. Set up relationships between tables
3. Create a form
4. Create a report
5. Create a query
2) Query Processer:
It is used to create SQL queries.
It is used to search for data that meets set criteria e.g. all bookings for next week in a hotel.
It is used to perform calculation of extracted data e.g. number of empty rooms in a hotel.
It is used to organize the results to be displayed.
Purpose of Data Dictionary in DBMS:
It stores all the information about the database.
For example, fields, datatypes, keys etc.
How DBMS Software is used to ensure the security of the data:
1) Issue usernames and passwords:
It stops unauthorized access to the data.
The strong passwords must be used.
2) Access rights:
It is used so that only certain usernames can read certain part of the data.
It can be read only or full access.
For example, only finance department can edit/read the data related to finance.
3) Create regular backups:
In case of loss/damage to the live data, a copy is available.
For example, backup at the end of each day.
4) Encryption of data:
If there is unauthorized access to the data, it cannot be understood.

5
Advantages of DBMS:

1) CONTROL OF DATA REDUNDANCY:

DBMS tries to eliminate the redundancy by integrating the files so that multiple copies of the same
data are not stored. DBMS does not remove the duplication entirely but can control the amount of
duplication. Sometimes it is important to duplicate key data to model relationships, improve
performance.

2) DATA CONSISTENCY:
By eliminating or controlling redundancy, we reduce the risk of inconsistencies occurring. If a data
item is stored only once in the database, any update to its value has to be per- formed only once
and the new value is available immediately to all users. If a data item is stored more than once and
the system is aware of this, the system can ensure that all copies of the item are kept consistent.

3) SHARING OF DATA:
Database is a shared resource which can be shared with different authorized departments. New
applications can build on the existing data in the database and add only data that is not currently
stored, rather than having to define all data requirements again.

4) IMPROVED DATA INTEGRITY:

Database integrity refers to the validity and consistency of stored data. Integrity is usually expressed
in terms of constraints, which are consistency rules that the database is not permitted to violate.

5) IMPROVED SECURITY:
DBMS protects the database from the unauthorized users. This may take the form of user names
and passwords to identify people authorized to use the database. The access that an authorized user
is allowed on the data may be restricted by the operation type (retrieval, insert, update, delete).

6) INCREASED CONCURRENCY:
DBMS ensures concurrent data access so that there is no loss of information. DBMS provide access
to multiple users to access the database at the same time.

7) IMPROVED BACKUP & RECOVERY:

Backup and recovery in general refers to the various strategies and operations involved in protecting
your database against data loss and reconstructing the data should that loss occur.

8) DATA INDEPENDENCE:
DBMS separates the data descriptions from the applications, thereby making applications immune
to changes in the data descriptions. This is known as data independence.
It refers to the immunity of user applications to changes made in the definition and organization of
data.

6
Disadvantages of DBMS:

1) COMPLEXITY:
The provision of the functionality we expect of a good DBMS makes the DBMS an extremely
complex piece of software.

2) SIZE:
DBMS is a large software, occupying many megabytes of disk space and requiring substantial
amounts of memory to run efficiently.

3) COSTS:
Cost of DBMS Software, Additional Hardware Costs and Cost of Conversion.

7
Exam Style Questions:
Question 1:

8
Answer:

9
Question 2:

10
Answer:

11
Question 3:

Answer:

12
Question 4:

Answer:

13
Question 5:

Answer:

14
Question 6:

Answer:

15
Database Terms:
Database – a structured collection of items of data that can be accessed by different applications
programs.
Relational database – a database where the data items are linked by internal pointers.
Table – a group of similar data, in a database, with rows for each instance of an entity and
columns for each attribute.
Record – a row in a table in a database.
Field – a column in a table in a database.
Tuple – one instance of an entity, which is represented by a row in a table.
Entity – anything that can have data stored about it, for example, a person, place, event, thing.
Attribute – an individual data item stored for an entity, for example, for a person, attributes could
include name, address, date of birth.
Candidate key – an attribute or smallest set of attributes in a table where no tuple has the same
value.
Primary key – a unique identifier for a table. It is a special case of a candidate key.
Secondary key – a candidate key that is an alternative to the primary key.
Foreign key – a set of attributes in one table that refer to the primary key in another table.
Relationship – situation in which one table in a database has a foreign key that refers to a primary
key in another table in the database.
Referential integrity – property of a database that does not contain any values of a foreign key
that are not matched to the corresponding primary key.
Index – a data structure built from one or more columns in a database table to speed up
searching for data.
Entity-relationship (E-R) model or E-R diagram – a graphical representation of a database and
the relationships between the entities.
Normalisation – the process of organizing data to be stored in a database into two or more tables
and relationships between the tables, so that data redundancy is minimized.
First normal form (1NF) – the status of a relational database in which entities do not contain
repeated groups of attributes.
Second normal form (2NF) – the status of a relational database in which entities are in 1NF and
any non-key attributes depend upon the primary key.
Third normal form (3NF) – the status of a relational database in which entities are in 2NF and all
non-key attributes are independent.
Composite key – a set of attributes that form a primary key to provide a unique identifier for a
table.

16
Exam Style Questions:
Question 1:

17
Answer:

18
Question 2:

Answer:

Question 3:

19
Answer:

Question 4:

20
Answer:

21
Question 5:

22
Answer:

23
Question 6:

24
Answer:

25
Entity-Relationship (ER) Diagram:
An E-R diagram can be used to document the design of a database. This provides an easily
understandable visual representation of how the entities in a database are related.

The relationships may be mandatory or optional.

For example:
In a workroom with desks, each employee has one desk, but there could be spare desks.
The relationship between desk and employee is zero or one, so this relationship is optional.
The relationship between mother and child is mandatory because every mother must have at
least one child, so the relationship is one or many.
The type of relationship and whether it is mandatory or optional gives the cardinality of the
relationship. The cardinality of relationships is shown in following diagram:

26
Exam Style Questions:
Question 1:

Answer:

27
Question 2:

Answer:

28
Question 3:

Answer:

29
Question 4:

30
Answer:

31
Question 5:

Answer:

32
Question 6:

33
Answer:

Question 7:

Answer:

34
Normalization:
Database Normalization is a technique of organizing the data in the database.
Normalization is a systematic approach of decomposing tables to eliminate data redundancy
(repetition) and undesirable characteristics like Insertion, Update and Deletion Anomalies.
It is a multi-step process that puts data into tabular form, removing duplicated data from the
relation tables.
Normalization is used for mainly two purposes:
1) Eliminating redundant (useless) data.
2) Ensuring data dependencies make sense i.e. data is logically stored.
Normalization Rule:
Normalization rules are divided into the following normal forms:
1) First Normal Form
2) Second Normal Form
3) Third Normal Form
First Normal Form (1NF):
For a table to be in the First Normal Form, it should follow the following 4 rules:
1) It should only have single(atomic) valued attributes/columns.
2) Values stored in a column should be of the same domain
3) All the columns in a table should have unique names.
4) And the order in which data is stored, does not matter.
Second Normal Form (2NF):
For a table to be in the Second Normal Form,
1) It should be in the First Normal form.
2) It should not have Partial Dependency.
Partial Dependency:
It is when a field in a table isn’t dependent on primary key but is dependent on a composite
key.
Composite Key:
A set of fields that form a primary key to provide a unique identifier for a table.
Third Normal Form (3NF):
A table is said to be in the Third Normal Form when,
1) It is in the Second Normal form.
2) It doesn't have Transitive Dependency.

35
Transitive Dependency:
It is when a field in a table isn’t dependent on primary key or on composite key, but is
dependent on a local attribute/key within a table.

First Normal Form (1NF):

When a database conforms to first normal form, it contains no repeating attributes. The database’s
data can be referred to as atomic (meaning that no single column contains more than one value).
This table contains repeating attributes so is not normalized to first normal form.

Splitting the repeating attributes means that this database is now in first normal form.

36
Second Normal Form (2NF):
In order to meet second normal form, a database must also satisfy first normal form. In second
normal form, partial key dependencies are removed. A partial key dependency occurs in databases
with composite primary keys when a non-key attribute doesn’t depend on the whole of the
composite key. In our example, the primary key is composite.

Staff (Name, Department, Subject, DepartmentHead)

Because the attribute DepartmentHead depends only on the attribute Department and
Department depends only on the attribute Subject, the tables must be modified to meet second
normal form.

Creating the two tables SubjectDepartments and HeadsOfDepartment has ensured that the
database now conforms to second normal form as the partial key dependencies of Department
and DepartmentHead have been removed from the Staff table.

37
Third Normal Form (3NF):
In order to meet third normal form, in addition to conforming to second normal form, a database
must have no non-key dependencies. A database that meets third normal form can be described
as follows: All non-key attributes depend on the key, the whole key and nothing but the key Our
example meets third normal form as none of the attributes that do not form the key (or part of a
composite key) depend on the anything other than the whole key.

38
Exam Style Questions:
Question 1:

Answer:

39
Question 2:

40
Answer:

Question 3:

41
Answer:

42
Question 4:

Answer:

43
SQL (Structured Query Language):
1) DDL (Data Definition Language)
It is a language for describing data and its relationships in a database.
2) DML (Data Manipulation Language)
Represents a collection of programming languages explicitly used to make changes to the
database.

1) DDL Commands:

SQL (DDL) Command Description

CREATE DATABASE Creates a database

CREATE TABLE Creates a table definition

ALTER TABLE Changes the definition of a table

PRIMARY KEY Adds a primary key to a table

FOREIGN KEY … REFERENCES … Adds a foreign key to a table

Data Types:

Data Types for Attributes Description

CHARACTER Fixed length text

VARCHAR(n) Variable length text

BOOLEAN True or False; SQL uses the integers 1 and 0

INTEGER Whole number

REAL Number with decimal places

DATE A date usually formatted as YYYY-MM-DD

TIME A time usually formatted as HH:MM:SS

44
2) DML Commands:

SQL (DML) Command Description

SELECT FROM Fetches data from a database. Queries always begin with SELECT.

WHERE Includes only rows in a query that match a given condition

Sorts the results from a query by a given column either

ORDER BY
alphabetically or numerically

GROUP BY Arranges data into groups

INNER JOIN Combines rows from different tables if the join condition is true

SUM Returns the sum of all the values in the column

COUNT Counts the number of rows where the column is not NUL

AVG Returns the average value for a column with a numeric data type

DML Maintenance Commands:

SQL (DML) Maintenance Commands Description

INSERT INTO Adds new row(s) to a table

DELETE FROM Removes row(s) from a table

UPDATE Edits row(s) in a table

45
Syntax of Commands:
1) DDL Commands:
Creating a database:
CREATE DATABASE <database-name>
Creating a table:
CREATE TABLE <table-name>
Changing a table:
ALTER TABLE <table-name>
Adding a primary key:
PRIMARY KEY (field)
ADD <field-name>:<data-type>
Adding a foreign key:
FOREIGN KEY (field) REFERENCES <table>(field)

Examples:

1) CREATE DATABASE ‘Personnel’

2) CREATE TABLE Training
3) (EmpID INT NOT NULL,
4) CourseTitle VARCHAR(30) NOT NULL,
5) CourseDate Date NOT NULL,
6) PRIMARY KEY (EmpID, CourseDate),
7) FOREIGN KEY (EmpID) REFERENCES Employee(EmpID));

46
2) DML Commands:
Creating a query:
SELECT <field-name>
FROM <table-name>
WHERE <search-condition>
Sort into order:
ORDER BY <field-name> ASC/DEC
Arrange identical data into groups:
GROUP BY <field-name>
Joining together fields of different tables:
INNER JOIN2

DML Data Maintenance Commands:

Adding data to table:
INSERT INTO <table-name>(field1, field2, field3)
VALUES (value1, value2, value3)
Deleting a record:
DELETE FROM <table-name>
WHERE <condition>
Updating a field in a table:
UPDATE <table-name>
SET <field-name> = <value>
WHERE <condition>

47
Using the COUNT, AVG, and SUM of (SQL) DML Commands:
DATABASE ‘PRODUCTS’

SQL statement to find the number of products:

SELECT COUNT (ProductID)

FROM PRODUCTS;

SQL statement to find the average price of all products:

SELECT AVG (Price)

FROM PRODUCTS;

SQL statement to calculate the sum of all prices:

SELECT SUM (Price)

FROM PRODUCTS;

SQL Operators:

= Equals to

> Greater than

< Less than

>= Greater than or equal to

<= Less than or equal to

<> Not equal to

IS NULL Check for null values

48
Exam Style Questions:
Question 1:

49
Answer:

Question 2:

50
Answer:

51
Question 3:

52
Answer:

53
Question 4:

54
Answer:

55
Question 5:

56
Answer:

57
Question 6:

58
59
Answer:

60