Computer Science

Teacher: Maruf Ahmed

Syllabus content 8: Databases
Database:
- A database is a structured collection of data that allows people to extract information in a way that
meets their needs.
- The data in a database can include text, numbers, pictures; anything that can be stored and
manipulated in a computer
Why are databases useful?
Databases are very useful in preventing data problems occurring because:
- data is only stored once as a result no data duplication occurs
- if any changes or additions are made it only has to be done once. So, the data remains consistent
- the same data is used by everyone according to the need
File-based approach:
A file-based approach to data storage is a method where each application program maintains its own data
files, without a centralized database. Each department or application is responsible for storing, accessing,
and managing its own data.
Example: Imagine a company where the sales department manages its own customer data in a file, while the
accounting department manages its own customer data in a separate file. Both files might contain similar
information (like customer name, address, etc.), but they are not necessarily linked or synchronized.
Problem with file-based (flat database / flat-file) system:
• Flat-file has more data redundancy because the same data is stored many times
• There is program-data dependence with flat-files because any changes to the structure of the data
means the programs that access that data have to be re-written
• Flat-file systems have more data inconsistency and poorer data integrity because the same data may
be duplicated in multiple files, and when it is updated in one file, the change may not be made in the
others.
• It is not easy to perform complex searches /queries because a new program has to be written each
time
• Flat files could have a lack of privacy as user views cannot easily be implemented
Advantages
• Simple and inexpensive for small-scale applications.
• Each department can customize its files for its own needs.
Explanation of program-data dependence:
In a flat-file system, data and the programs that use it are tightly linked.
The program needs to know exactly how the data is structured (e.g., which column contains what, data
types, record format).
So, if you change the file structure (say, you add a new field or change its order), the program’s code that
reads or writes that data will no longer work correctly unless it’s updated.

Database Management System (DBMS): In order to manage our databases, we use a software application
called Database Management System or DBMS. We connect to a DBMS and give it instruction for querying
or modifying data. The DBMS will execute our instructions and sends results back. We have several
database management systems out there and these are classified into two categories; Relational and non-
relational. In relational databases we store data in tables that are linked to each other using relationships.
That’s why we call these databases as relational databases.

Page 1 of 21
Each table stores data about a specific type of object like customer, product, order or so on. SQL is the
language that we use to work with this relational database management system (RDBMS). Example of some
RDBMS are: MySQL, SQL Server by Microsoft and Oracle etc. Each different management system has a
different flavor of SQL but all these implementations are very similar and are based on the standard SQL
specification.
Advantages of using a relational database compared to a file-based approach.
• Reduces data redundancy:
o In a relational database, data is stored in linked tables. Each piece of data is stored only once,
even if multiple tables need to reference it.
o Example: Customer information is stored in one table and referenced by Sales, Accounting,
and HR tables.
• Reduces program-data dependency:
o The data structure is separate from programs. Changes to the data (like adding a new field) do
not require rewriting programs. This achieves program-data independence
• Reduces Data inconsistency:
o Updates are made in one place, and all linked tables automatically reflect the change.
• Improves data integrity:
o Ensures data is accurate, valid, and follows rules (constraints). Relationships, keys, and
constraints prevent invalid or contradictory data from being entered.
o Example: Cannot create an order for a non-existent customer (foreign key constraint).
• Complex queries are easier to run:
o Relational databases allow SQL queries to retrieve, filter, and join data across multiple tables.
• Different user views / Access Control:
o Users can be given different views of the database.
o Each user sees only the data relevant to them, improving security and usability.

Schema of a database:
• A database schema is the blueprint or logical design of a database.
• It defines how data is organised into tables, the fields (attributes) within those tables, and the
relationships between them.
• The schema describes the logical structure of the data — how different data elements are connected
— without describing how the data is physically stored on disk.
External schema:
- The individual’s view(s) of the database
- Each user or user group can have a custom view of the database.
- Individual users or groups of users can be given appropriate access rights to control what actions
are allowed for that view. For example, user may be allowed to read data but not to amend data.
Conceptual schema:
- Describes the data as seen by the applications making use of the DBMS
- Describes the ‘views’ which users of the database might have
- This is controlled by the database administrator (DBA) who has access to the DBMS
Logical schema:
- The logical schema provides a conceptual design of how the database is organised.
- It defines what data is stored and the relationships between data items, but not how the data is
physically stored.
- It gives an overview of the database structure, independent of any specific DBMS.
- It describes the tables (entities), fields (attributes), data types, primary keys, and foreign keys.

Page 2 of 21
 - It shows the relationships between entities or tables (e.g. one-to-many, many-to-many).
- It models the problem domain using tools such as Entity–Relationship (ER) diagrams or table
structures.
- It acts as a blueprint for creating the physical schema, which defines how the database is actually
implemented in a DBMS.
Physical / Internal schema:
- Describes how the data will be stored on the physical media
- The programmers who write the software are the only ones who know the structure for the storage of
the data on disk
- This is controlled by the database management system (DBMS) software.
Database administrator (DBA):
• A person who manages and maintains a database using a DBMS, ensuring it meets user and
programmer requirements
Responsibilities:
• Uses the DBMS to adapt the database to suit the needs of users and programmers.
• Assigns permissions to individuals or groups to control who can view, edit, or delete data.
• Ensures that data is regularly backed up and can be restored in case of system failure or data loss.

The facilities provided by a DBMS:

Developer Interface:
• A software tool within a DBMS that allows developers to create, modify, and manage database
objects and build user-friendly features for database users
Functions:
• Create, modify, and delete database objects
o Tables, queries, forms, reports, and other database items.
• Design user-friendly input features
o Forms to allow users to enter new data easily, e.g., booking forms.
• Generate outputs
o Reports summarising data, e.g., bookings on a given date.
• Build interactive features
o Buttons, menus, and other elements to allow users to perform actions or run queries without
needing to write code.
Query processor:
• The Query Processor is a software component of a DBMS that processes and executes SQL queries
and returns results to the user.
Functions:
• Create SQL queries
o Allows users to write queries to retrieve or manipulate data.
• Enter criteria
o Enables filtering of data using conditions (e.g., WHERE clauses).
• Search and extract data
o Finds data that meets the entered criteria.
• Organise results
o Displays query results in a structured format (e.g., sorted or grouped).
• Perform calculations on data
o Supports functions like SUM, AVG, COUNT, etc., on extracted data.
• Execute queries
Page 3 of 21
 o Processes the SQL statements and interacts with the database to retrieve or modify data.

A DBMS uses a data dictionary. Discuss about it.

Data dictionary:
- A Data Dictionary is a repository that stores metadata — that is, information about the data in a
database and the structure of the database itself.
- It identifies and defines the characteristics of the data that will be stored, such as data types, field
sizes, valid values, and relationships.
Metadata - a set of data that describes and gives information about other data.
Data dictionary normally holds information about the following:
• field / attribute names: to store the names of fields so they cannot be repeated in a table
• table names: to identify the tables
• validation rules: to ensure that data entered is sensible
• primary keys: to uniquely identify each record in a table
• foreign keys: to link tables // to create relationships between tables
• relationships: to identify the relationships between tables
• data types: to prevent the wrong data type being entered in a field
• passwords: to allow access only to authorised personnel
• access rights: for different levels of access

Feature Logical Schema Data Dictionary

Definition The logical structure that shows how data A reference file that stores metadata —
is organised and related. information about the data and database structure.
Focus Database design and relationships between Field-level details and data constraints.
entities.
Used for Designing how the database will be Managing and maintaining consistency and
structured. integrity of data.
Contains Tables, fields, keys, relationships. Field names, data types, sizes, validation rules,
and constraints.
Users Database designers. Database designers, administrators, and the
DBMS itself.
Example Customer(CustomerID, Name, Address) CustomerID: Integer (5 digits, PK)Name:
Text(30)Address: Text(50)

Some terms associated with database:

Entity: Anything that data can be stored about // Any object, person or thing about which it is possible to
store data // An object that data is stored about // A real-life object that is represented as a table
Table: A table is a collection of related data organised in rows (records) and columns (fields). Each row
represents one record, and each column represents one attribute or data item.
Relation: the special type of table which is used in a relational database (A relation is the theoretical
concept in relational database theory that a table represents. It is a set of tuples (records) that share the
same set of attributes (fields).
Every relation can be implemented as a table, but not every table strictly qualifies as a relation unless it
follows all the rules of the relational model.
Fields / Attributes: A field (also called an attribute) is a column in a table that stores a single item of data
about an entity. Each attribute represents one property or characteristic of the entity.
Record / Tuple: A record (also called a tuple) is a single row in a table that stores all the data about one
instance of an entity.

Page 4 of 21
Candidate key: Any attribute or combination of attributes that can qualify as a primary key // A field that
could be a primary key but is not
Primary key: A primary key is an attribute (or a combination of attributes) that uniquely identifies each
record (tuple) in a table. No two records can have the same primary key value. A primary key cannot be
NULL.
Composite / Compound primary key: A composite (or compound) primary key is a primary key made up
of two or more attributes that together uniquely identify each record (tuple) in a table. Individually, the
attributes may not be unique, but combined, they ensure uniqueness. Used when no single attribute can act
as a primary key.
Secondary key: A secondary key is an attribute (or combination of attributes) that can uniquely identify
records but is not chosen as the primary key. An attribute that is a candidate key but is not the primary key //
An additional/alternative key used as well as the primary key to locate specific data // An attribute that can
be indexed for faster searching
Purpose of secondary key: Secondary keys are useful for searching, indexing, or accessing data efficiently.
Foreign Key: A foreign key is a field (or combination of fields) in one table that refers to the primary key in
another table. It is used to establish and enforce a link (relationship) between two tables. A foreign key
ensures referential integrity, meaning every value in the foreign key field must match a value in the
referenced primary key.
Index: An index in a database is like a lookup list that helps the DBMS find records faster, just like an index
in a book helps you find topics quickly. Instead of scanning every row in a table, the database can use an
index to jump directly to the correct location.
An index is a secondary table that stores key values and pointers (addresses) to the corresponding records in
the main table. It is used to speed up data retrieval without scanning the entire file. An index can be created
on the primary key or on a secondary key (a candidate key not chosen as the primary key). Both the primary
key and the secondary key are unique, so the index entries are also unique.

However, you can create an index on non-unique fields (like Name or City) to speed up searching.
However, this version of “secondary index” is not part of the 9618 syllabuses.
Data modelling:
• Data modelling is a tool used to represent the structure of data in a database and the relationships
between data items.
• Helps design the logical schema, serving as a blueprint for creating tables, fields, primary/foreign
keys, and relationships.
• An Entity-Relationship (ER) diagram is a common example of a data model.

Entity Relationship Diagram (ERD):

• An Entity–Relationship (ER) model describes the structure of a database using a diagram, called an
Entity-Relationship Diagram (ER Diagram).
• It is a design or blueprint that shows how data is organised and related, which can later be
implemented as a database.
Main components of ERD:
• Entity Set (Entity)
o Represents a real-world object or concept (e.g., Customer, Product, Order).
o Usually drawn as a rectangle in ER diagrams.
• Relationship Set (Relationship)
o Represents a connection between two or more entities (e.g., Customer places Order).
• Attributes

Page 5 of 21
 o Properties of entities or relationships (e.g., CustomerID, Name, OrderDate).
Steps to create an ER Diagram:
• Choose the entities
o Identify the key objects or concepts in the problem domain.
• Identify the relationships
o Determine how entities are connected (e.g., Customer places Order).
• Decide the cardinalities of the relationships
o Specify one-to-one, one-to-many, or many-to-many.
An example of an ERD:
A shop sells pens to customers. Customers place an order with the shop and collect the items the next day.
The shop uses a database to store the information about the orders.
The database contains the following tables:

The entity-relationship diagram for the database:

N.B. The crow foot represents many side

Relationships in database: Database relationships are the associations between tables that allow you to
connect and query related data within a relational database. These relationships are crucial for data integrity,
efficient data retrieval, and complex data analysis. They are established using primary and foreign keys and
are fundamental to the structure and functionality of relational databases.
Types of Database Relationships:
One-to-One (1:1): Each record in one table is related to only one record in another table.
Implementation methods:
Foreign Key + Unique Constraint:
• Place a foreign key in one table referencing the primary key of the other table.
• Apply a unique constraint on the foreign key column to ensure only one row in the child table
references each parent row.

Page 6 of 21
 • Ensures that each record in one table is associated with at most one record in the other table, and vice
versa.
Primary Key + Primary Key (PK + PK):
• Make the primary key of the child table also a foreign key referencing the primary key of the parent
table.
• This automatically enforces the 1:1 relationship without needing a separate unique constraint.
• Useful when the child table contains optional or additional attributes related to the parent.
Example: Consider a Countries table and a Capitals table. The Countries table has a primary key
Country_id. The Capitals table might have Capital_id (primary key) and Country_id (foreign key)
referencing the Countries table. Applying a unique constraint on Capitals.Country_id ensures that each
country has only one capital.
One-to-Many (1:M): One record in a table can be related to multiple records in another table, but each
record in the second table is related to only one record in the first. This is often represented by a foreign key
in the "many" table that references the primary key of the "one" table.
Example: One customer can place multiple orders, but each order is associated with only one customer.
Consider a database for a bookstore:
Table: Customers (One side)
Table: Orders (Many side)
In this scenario, a single customer can place many orders. The Orders table would have a foreign key
(Customer_id) that references the primary key (Customer_id) in the Customers table. This ensures that each
order is associated with a specific customer.
Many-to-one (M:1): The transpose or inverse of a one-to-many relationship is a many-to-one relationship.
In essence, they represent the same relationship but viewed from different perspectives.
Many-to-Many (M:M): Multiple records in one table can be related to multiple records in another table.
For example, a many-to-many relationship exists between customers and products: customers can purchase
various products, and products can be purchased by many customers. To implement many-to-many
relationships in a relational database, a third table (often called a junction or linking table) is used to connect
the two tables.
A typical example of a many-to many relationships is one between students and classes. A student can
register for many classes, and a class can include many students.
The following example includes a Students table, which contains a record for each student, and a Classes
table, which contains a record for each class. A join table, Enrollments, creates two one-to-many
relationships—one between each of the two tables.

The primary key Student ID uniquely identifies each student in the Students table. The primary key Class ID
uniquely identifies each class in the Classes table. The Enrollments table contains the foreign keys Student
ID and Class ID.

Page 7 of 21
Scenario based question:
Bobby has a shop that sells products to customers. His database will store data about his customers, their
payment details, orders and the products he sells. Customers will have login details to access their accounts.
The database will update customers’ payment and login details without keeping any historical records

Give one example of each of the following relationships from Bobby’s database
one-to-one: e.g., customer to payment details // customer to login details
one-to-many: e.g., customer to order
many-to-many: e.g., order to product // customer to product
Scenario based question:
Relationships are created between tables using primary and foreign keys.
Describe the role of a primary and a foreign key in database relationships
• Primary key uniquely identifies each tuple // Each tuple in the table is unique
• Primary key can be used as a foreign key in another table to form a link/relationship between the
tables

Data Integrity: Data integrity ensures that the data in the database is accurate, correct, and valid.
Explain how data integrity is implemented in a database.
• Maintaining data integrity by enforcing referential integrity: foreign keys ensure that all references to
primary keys are valid, and cascading updates/deletes automatically preserve consistency when
primary records are modified or removed.
• Maintaining data integrity by applying validation and verification rules: ensuring that all data entered
is accurate, sensible, and follows defined formats or constraints.
• Maintaining data integrity by using a data dictionary: ensuring that all field names, data types, and
relationships are defined consistently, so that data is accurate and used correctly throughout the
database.
• Maintaining data integrity by normalizing the database: organizing data into related tables to reduce
redundancy and prevent inconsistencies, ensuring that each piece of data is stored accurately and
updated correctly.
Referential integrity:
- Referential integrity ensures that relationships between tables remain consistent — that is, all foreign
keys in a table correctly reference existing primary keys in another table.
- Rule enforcement: A record cannot be added to a foreign table unless the corresponding primary key
exists in the related primary table.
- Cascading deletes - If a record is deleted in the primary table, all corresponding records in the
foreign table are also deleted automatically to maintain consistency.
- Cascading update - If a primary key value is modified in the primary table, the corresponding foreign
keys in related tables are updated automatically.
Explain the reasons why referential integrity is important in a database.
• Referential Integrity makes sure data is consistent
• Referential Integrity makes sure all data is up-to-date
• Referential integrity ensures that every foreign key has a corresponding primary key
• Referential Integrity prevents records from being added / deleted / modified incorrectly
• Referential Integrity makes sure that if data is changed in one place the change is reflected in all
related records
• Referential Integrity makes sure any queries return accurate and complete results

Page 8 of 21
Data consistency:
• Data consistency ensures that all related data across the database agrees and follows defined rules.
What this means
In a relational database, data is often stored in multiple tables to reduce redundancy. Related data in different
tables must match correctly to make sense. Consistency is about relationships between tables, not just
individual records.
Consistency ensures that changes in one table do not conflict with data in another table. It ensures that
foreign keys match primary keys, totals add up correctly, and data does not conflict across tables.
Often implemented using:
• Foreign key constraints: enforce valid relationships
• Cascading updates/deletes: automatically propagate changes
• Transactions: Ensure all related operations succeed or fail together to maintain consistency.
A transaction is a sequence of database operations (e.g., insert, update, delete) that are treated as a single
unit.
Purpose: Ensure that all operations succeed or fail together, so the database remains consistent.
Security provided by a DBMS: A DBMS helps to provide data security to prevent the unwanted alteration,
corruption, deletion or sharing of data with others that have no right to access it.
Security measures taken by a DBMS can include:
• using usernames and passwords to prevent unauthorised access to the database
• using access rights to manage the actions authorised users can take, for example, users could
read/write/delete, or read only, or append only
• using access rights to manage the parts of the database they have access to, for example, the
provisions of different views of the data for different users
• users to allow only certain users access to some tables
• automatic creation and scheduling of regular back-ups
• encryption of the data stored
• automatic creation of an audit trail or activity log to record the actions taken by users of the database.

Normalization of Database:
Database normalization is a systematic technique for organizing data in a database to reduce redundancy and
prevent undesirable anomalies during insertion, update, or deletion. It involves decomposing large tables
into smaller, related tables and ensuring that each table stores only one type of data. Normalization improves
data integrity by eliminating duplicate data and ensures consistency by maintaining clear relationships
between tables.
Normalization is used mainly for two purposes:
• Eliminating redundant data: to remove repeated or unnecessary data, which reduces storage and
prevents update, insertion, and deletion anomalies.
• Ensuring logical data dependencies: to organize data so that it is stored logically, with clear
relationships between tables, improving integrity and consistency.
Problems without Normalization
If a table is not properly normalized and have data redundancy then it will not only eat up extra storage
space but will also make it difficult to handle and update the database without facing data loss. Insertion,
Update and Deletion anomalies are very frequent if database is not normalized. To understand these
anomalies let us take an example of a Student table.

Page 9 of 21
In the table above, we have data of four Computer Science students. As we can see, data for the fields
branch, hod (Head of Department) and office_tel is repeated for the students who are in the same branch in
the college, this is Data Redundancy.
Insertion Anomaly:
Suppose for a new admission, until and unless a student opts for a branch, data of the student cannot be
inserted, or else we will have to set the branch information as NULL (keep it as blank).
Also, if we have to insert data of 100 students of same branch, then the branch information will be repeated
for all those 100 students.
These scenarios are nothing but Insertion anomalies.
Update Anomaly:
What if Mr. X leaves the college? or is no longer the HOD of Computer Science department? In that case all
the student records will have to be updated, and if by mistake we miss any record, it will lead to data
inconsistency. This is Update anomaly.
Deletion Anomaly:
In our Student table, two different information are kept together, Student information and Branch
information. Hence, at the end of the academic year, if student records are deleted, we will also lose the
branch information. This is Deletion anomaly.
Normalization Rule: Normalization rules are divided into the following normal forms:
• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)
Un-Normalised Form (UNF):
It’s the very first stage of a database before any normalization is applied. It represents all the data from a
source such as a form, report, invoice, or document in a single table. It contains both actual data (from the
source) and modelled data (extended or derived from the source).
Essentially, it’s a table that may have:
• Repeating groups – multiple values in a single column.
• Non-atomic attributes – a cell may store more than one value.
• Redundancy – the same data may appear multiple times.
• No clear primary key – it may not uniquely identify rows.
At this stage, the table may have redundancy and repeated data, but it provides a structured starting point for
normalization. Each subsequent stage depends on the UNF being correctly prepared, with appropriate
domains and well-named columns.
To create a UNF table:
• Identify all data items from the source: Examine the raw data and list every individual piece of
information that needs to be stored.
• Assign a column for each data item: Give each item a meaningful column heading that represents the
type of data it will hold (its normalization domain).

Page 10 of 21
 • Include only actual data, not calculated fields: Avoid derived or computed values; each column
should represent a real, stored data item.

Student Registration Form (Source)

Field Label Example Input
Name Alice
Age 16
Subjects Enrolled Math, Physics
Address (Street + City) 12 Oak Street, CityA
Advisor Dr. Smith

From this form, we directly create a table, without changing anything:

Table name: Students
Name Age SubjectsEnrolled Address Advisor
Alice 16 Math, Physics 12 Oak Street, CityA Dr. Smith
Bob 17 Chemistry 45 Pine Road, CityB Dr. Johnson
Carol 16 Biology, Chemistry 78 Maple Ave, CityC Dr. Lee

Issues in UNF:
SubjectsEnrolled → repeating group (multiple values in one cell)
Address → non-atomic (street + city combined)
No primary key

First-Normal Form (1NF):

A table is in First Normal Form (1NF) if it satisfies the following conditions:
• No repeating groups of attributes: each column should store only one type of data, not multiple
values or lists.
• All attributes are atomic (indivisible): each field contains a single, simple value.
• No duplicate rows: each record in the table is unique.
Repeating group: A repeating group is a set of attributes in a table that contains repeated or multiple values
for a single record. Repeating groups can lead to data redundancy and inconsistencies.
To remove a repeating group:
• Create a separate table for the repeating data.
• Link the new table to the original table using a foreign key.
• Ensure each record in the new table is atomic and uniquely identified.
Meaning of atomic: An atomic attribute is a field (cell) in a table that holds only a single value, not
multiple or composite values. Each cell must contain a single, indivisible piece of data.

With the un-normalised relation complete, the normalization process begins. The First Normal Form (1NF)
is the most important step because it eliminates repeating groups and ensures all attributes are atomic. This
step breaks the data into related groups, creating a structured foundation. Subsequent normal forms (2NF,
3NF) fine-tune the relationships within and between these groups to reduce redundancy and maintain
integrity and consistency.
With First Normal Form we are looking to remove repeating groups. A repeating group is a domain or set of
domains, directly relating to the key, that repeat data across tuples in order to cater for other domains where
the data is different for each tuple.
So, the steps from UNF to 1NF are:

Page 11 of 21
 • Identify repeating groups of data. Make sure your model data is of good quality to help identify the
repeating groups and don’t be afraid to move the domains around to help with the process.
• Remove the domains of the repeating groups to a new relation leaving a copy of the primary key
with the relation that is left.
• If the original primary key is no longer unique in the new table, create a new primary key by
combining the original key with one or more additional attributes to form a composite key.
Taking our original example once we have followed these simple steps, we have relations that looks like
this:
Students:
StudentID Name Age Subject Street City Advisor
1 Alice 16 Math 12 Oak Street CityA Dr. Smith
1 Alice 16 Physics 12 Oak Street CityA Dr. Smith
2 Bob 17 Chemistry 45 Pine Road CityB Dr. Johnson
3 Carol 16 Biology 78 Maple Ave CityC Dr. Lee
3 Carol 16 Chemistry 78 Maple Ave CityC Dr. Lee
Primary key consideration:
Surrogate key: A surrogate key is an artificial key added to a table to uniquely identify each row, instead of
using a natural attribute from the data. StudentID was introduced as a unique identifier because the original
data (Name, etc.) could not guarantee uniqueness after splitting repeating groups.
If you assign StudentID alone as primary key, it will not be unique in this table (because students appear
multiple times for multiple subjects).
To make the primary key unique for this table, you need a composite key (StudentID + Subject).
Second Normal Form (2NF):
• Entities are in 1NF and all attributes must be fully dependent on the composite primary key // No
partial dependencies. // Entities are in 1NF and any non-key attributes depend upon the primary key.
There are no partial dependencies.
A partial dependency occurs when a non-key attribute depends on only part of a composite primary key, not
the whole key.
Identify partial dependencies: Name, Age, Street, City, Advisor → depend only on StudentID, not on the
whole composite key (StudentID + Subject). Therefore, partial dependencies exist, and the table is not yet in
2NF.
So, we have to remove partial dependencies. To achieve 2NF, we must remove these dependencies by
splitting the table.
Move all attributes that depend only on StudentID into the table called Students.
Students:
StudentID Name Age Street City Advisor
1 Alice 16 12 Oak Street CityA Dr. Smith
2 Bob 17 45 Pine Road CityB Dr. Johnson
3 Carol 16 78 Maple Ave CityC Dr. Lee
Primary Key: StudentID for Students table

Keep the StudentID and Subject in the new table called Subjects table.
Subjects:
StudentID Subject
1 Math
1 Physics
2 Chemistry
3 Biology
3 Chemistry
Page 12 of 21
Primary Key for Subjects table: (StudentID + Subject)
Foreign Key: StudentID of Subjects references StudentID of Students table

Now all non-key attributes are fully dependent on their respective primary keys.
This sets up a one-to-many relationship between Students and Subjects.

Third Normal Form (3NF):

• Entities are in 2NF and all attributes must be fully dependent on the primary key and no other non-
key attributes // no non-key dependencies // no transitive dependencies

Non-key attribute: A non-key attribute is an attribute that is not part of the primary key (or composite key,
if the primary key is made up of multiple attributes).
For each table, do the following: Take each non-key attribute in turn and ask
“Does this attribute depend only on the primary key/composite primary key?”
“Or does it depend on another non-key attribute?”
If it depends on another non-key attribute → transitive dependency exists → violates 3NF.
If it depends only on the primary key → 3NF condition satisfied for that attribute.
Identify Transitive Dependencies
In Students table, check non-key attributes: Name, Age, Street, City, Advisor
City depends on Street, not on StudentID → transitive dependency because the city can be determined by
the street (each street belongs to one city), we have a transitive dependency:
All other attributes depend directly on StudentID → OK

Street → City
Each street is located in exactly one city.
So, knowing the street uniquely determines the city.
Makes sense: “12 Oak Street” is always in CityA.

City → Street
This would mean that knowing the city uniquely determines the street.
Not true in real life: a city has many streets.
Example: CityA may have “12 Oak Street” and “14 Pine Street.”

Students Table (Primary Key = StudentID)

StudentID Name Age Street Advisor
1 Alice 16 12 Oak Street Dr. Smith
2 Bob 17 45 Pine Road Dr. Johnson
3 Carol 16 78 Maple Ave Dr. Lee

Remove Transitive Dependency: Create a new table for the dependent attribute (City) along with the
attribute it depends on (Street)
Address Table (Primary Key = Street)
Street City
12 Oak Street CityA
45 Pine Road CityB
78 Maple Ave CityC
Foreign Key: Street in the Students table references the Street attribute in the Address table.
Street of Students table now becomes a foreign key referencing Street of Address table

Page 13 of 21
So, for Students table
Primary Key: StudentID
Foreign Key: Street → references Address table
This sets up a one-to-many relationship between Street and Students.
Check for Subjects table also.
Composite primary key: (StudentID, Subject)
There are no non-key attributes here — only the key itself.
There cannot be any transitive dependency, because there are no other attributes that could depend on a non-
key field. Subjects table is already in 3NF.
So, finally we have three tables: Students, Subjects and Address

To become a fully normalized database the following four conditions must be met:
• There cannot be any repeating groups of attributes which makes it 1NF
• There cannot be any partial dependencies which makes it 2NF
• There cannot be any no non-key dependencies which makes it 3NF// There are no transitive
dependencies (3NF)
• There cannot be many-to-many relationships between tables. (Such relationships must be resolved by
creating a link (junction) table containing the primary keys from both related tables.)

SQL (Structured Query Language)

SQL is a programming language used to manage and manipulate data stored in relational databases. It
operates through simple, declarative statements, allowing users to specify what data they want rather than
how to get it.
SQL helps to maintain data accuracy, security, and integrity, regardless of the size or complexity of the
database.
Structured Query Language (SQL):
• A standardized language used for interacting with a Relational Database Management System
(RDBMS).
• Used to perform C.R.U.D. operations — Create, Read, Update, Delete — as well as administrative
tasks such as user management, security, and backups.
• Used to define database structures (tables, relationships, constraints, etc.).
• Not fully portable across all RDBMSs — SQL code may require modification to run on different
database systems.

SQL Commands:
SQL commands are instructions written in SQL statements that allow users to communicate with a database
to perform specific tasks, functions, and queries on data.
They are used not only for searching and retrieving data, but also for performing other operations such as:
• Creating tables and database objects
• Inserting, updating, or deleting data
• Modifying database structures
• Dropping (deleting) tables or databases
• Managing user permissions and security
SQL commands are grouped into TWO major categories depending on their functionality:
Data Definition Language (DDL) – Data Definition Language (DDL) is used to define, create, and modify
the structure of a database and its objects.
Using DDL commands, you can create or change:
Page 14 of 21
 • Databases
• Tables and their fields/attributes
• Indexes
• Users and permissions
• Primary and foreign keys
• Relationships
• Views
Common DDL commands include:
CREATE – to create a new database object (e.g., database, table, view)
ALTER – to modify an existing database table
Data Manipulation Language (DML) - Data Manipulation Language (DML) commands are used to store,
retrieve, modify, and delete data within database tables.
Common DML commands include:
SELECT – retrieves data from one or more tables
INSERT – adds new records to a table
UPDATE – modifies existing records in a table
DELETE – removes records from a table

Key difference between DDL and DML:

DDL works with the structure of the database (tables, fields, etc.)
DML works with the data stored inside those structures

Data types:
- Each field will require a data type to be selected.
- A data type classifies how the data is stored, displayed and the operations that can be performed on
the stored value.
- For example, a field with an integer data type is stored and displayed as a whole number and the
value stored can be used in calculations.
Data types in SQL:
Data Type Description Literal Representation Example
INTEGER Stores whole numbers (no decimal Without any quotation 100
places)
REAL Stores numbers with decimal places Without any quotation 10.25
CHARACTER (n) Fixed-length text of length n; padded With double quotes "Male"
with spaces if shorter
VARCHAR (n) Variable-length text up to n characters With double quotes "Bangladesh"
DATE Stores calendar dates, usually formatted With hash signs (#) #2023-11-25#
as YYYY-MM-DD
TIME Stores time of day, usually formatted as With hash signs (#) #13:20:21#
HH:MM:SS
BOOLEAN Stores logical values: TRUE or FALSE Without any quotation TRUE
Some of the most important SQL Commands:
CREATE DATABASE - creates a new database (Example of DDL)
CREATE TABLE - creates a new table (Example of DDL)
ALTER TABLE- modifies an attribute/s in a table (Example of DDL)
SELECT - extracts data from a database (Example of DML)
INSERT INTO - inserts new data into a database (Example of DML)
DELETE - deletes data from a database (Example of DML)
UPDATE - updates data in a database (Example of DML)
Page 15 of 21
How to create a database:
CREATE DATABASE database name
e.g., CREATE DATABASE Student;

How to create a table:

CREATE TABLE table name (column name1 datatype, column name2 datatype)
e.g., CREATE TABLE Parent (
ParentID CHARACTER (8) NOT NULL,
Name VARCHAR (40),
EmailAddress VARCHAR (40),
Dob DATE,
PRIMARY KEY (ParentID)
);
The table may be created in the following way also:
e.g., CREATE TABLE Parent (
ParentID CHARACTER (8) NOT NULL PRIMARY KEY,
Name VARCHAR (40),
EmailAddress VARCHAR (40),
Dob DATE
);
CREATE TABLE STUDENT_TEST (
StudentId INTEGER,
TestID VARCHAR (10),
Mark INTEGER,
PRIMARY KEY(StudentID, TestID), //Referring to composite primary key
FOREIGN KEY(TestID) REFERENCES TEST(TestID),
FOREIGN KEY(StudentID) REFERENCES STUDENT(StudentID)
);

ALTER TABLE example:

ALTER TABLE USER
ADD COUNTRY VARCHAR(20); //Adding a new field in the table

ALTER TABLE FILMS

ADD PRIMARY KEY (FilmID); //Making FilmID as the primary key

ALTER TABLE CAMERA_DATA

ADD NumberStored INTEGER,
LastUsed DATE; //Adding two attributes in the table

ALTER TABLE Student

ADD FOREIGN KEY ClassID REFERENCES Class (ClassID); //Adding a foreign key

Inserting data in a table:

INSERT INTO Student
VALUES (“S1301”, “Peter”, “Probert”, #06/06/2011#, “7A”) //When all the attribute values will be inserted
// in the order then attribute names can be omitted
INSERT INTO SHOP-SUPPLIER (ShopID, SupplierID)
Page 16 of 21
VALUES (8765, “SUP89”); //If the values for all the columns are not known, then the table columns need to
// be specified before the values are inserted:

UPDATE data in a table:

UPDATE DOCTOR
SET DoctorID = “017”
WHERE DoctorID = “117”; //Updating DoctorID data

DELETE a record from existing table

DELETE FROM DOCTOR
WHERE DoctorID = “117”; //Deleting every record whose DoctorID = “117”

Joining between two tables: A query can be based on a ‘join condition’ between data in two tables. The
most frequently used is an inner join.
INNER JOIN: Combines rows from different tables if the join condition is true
Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the
columns. If there are records in the "Orders" table that do not have matches in "Customers", these orders will
not be shown!
The INNER JOIN keyword selects records that have matching values in both tables.
N.B. When an inner join command is performed between two tables then table name followed by dot
(.) followed by field name has to be given. For example, [Link]

SELECT [Link], [Link]

FROM Orders
INNER JOIN Customers
ON [Link] = [Link];

Could be written in the following way also:

SELECT [Link], [Link]
FROM Customers
INNER JOIN Orders
ON [Link] = [Link] ;

This could be written in the following way also without using INNER JOIN keywords:
SELECT [Link], [Link]
FROM Orders, Customers
WHERE [Link] = [Link];

Another example:
SELECT [Link], [Link]
FROM Band-Booking
INNER JOIN Band
ON [Link] = [Link]
AND [Link] = ‘ComputerKidz’;

SELECT [Link], [Link]

FROM Booking, Band-Booking
WHERE [Link] = [Link]
Page 17 of 21
AND [Link] = ‘ComputerKidz’;

SELECT [Link], [Link]

FROM Teacher
INNER JOIN Subject
ON [Link] = [Link]

SELECT Title, Rating

FROM MOVIE, MOVIESCHEDULE
WHERE [Link] = [Link]
AND [Link] = 3;

The upper one could be written the following way also

SELECT Title, Rating
FROM MOVIE
INNER JOIN MOVIESCHEDULE
ON [Link] = [Link]
WHERE [Link] = 3;

Aggregate functions in SQL: An aggregate function in SQL performs a calculation on multiple values
from a column and returns a single summarised value. SQL provides many aggregate functions that include
count, sum, avg etc. Aggregate functions are used with SELECT command. In your syllabus aggregate
functions are limited to SUM, COUNT and AVG.
SUM: Returns the sum of numerical values in a field (column).
COUNT: Counts the number of records (rows) where the field (column) matches a specified condition.
AVG: Returns the average of numerical values in a field
Use of different operators in a condition: To compare values, conditions are needed with WHERE clause
and for the conditions the following logical operators and Boolean operators may be used.
Comparison operators: Greater than (>), Less than (<), Greater than or equal to (>=), Less than or equal to
(<=), Is equal to (=), Not equal to (< >) are known as logical operators.
Logical operators: NOT, AND and OR and are known as Logical operators.
Operator Comparison Example in WHERE clause
= equal to Salary = 10000
> Greater than Salary > 10000
< Less than Salary < 10000
>= Greater than or equal to Salary >= 10000
<= Less than or equal to Salary <= 10000
<> Not equal to Salary < > 10000
() Used to make group. Parenthesis ( ), used
to change the order of operation by
putting the rules in group. No other
brackets can be used
BETWEEN Search for a specified range of values Salary BETWEEN 10000 AND 20000
where both part of the range is inclusive
LIKE The % operator in SQL is used as a Name LIKE “A%” (Searches for names in
wildcard character in the LIKE operator Name field which starts with A followed by
for pattern matching. any number of characters.

Page 18 of 21
 The LIKE operator is used in a WHERE Name LIKE “ABC_” (Searches for only the
clause to search for a specified pattern in fourth character which could be any character
a column. There are two wildcards often but the first 3 characters must be ABC.
used in conjunction with the LIKE
operator: The percent sign (%) represents
zero, one, or multiple characters. The
underscore (_) is another wildcard used
with the LIKE operator, but represents
exactly one character in a string when
using LIKE.
IN to check if a value is in a set of values for Department IN (10, 20, 30)
a particular field
AND specify multiple conditions that must all Salary >= 10000 AND Department <= 10
be true
OR specify multiple conditions where one or Salary > 15000 OR (Department >= 10 AND
more conditions must be true Department <= 20)
NOT specify a condition that must be FALSE NOT (Salary >= 10000 AND Salary <=
for the expression to be TRUE 20000)
NOT (Salary BETWEEN 10000 AND 20000)

A SQL statement should be terminated with semicolon (;)

* (asterisk) is used to retrieve all the columns from a table

Example use of LIKE operator:

SELECT COUNT (TelescopeID)
FROM TELESCOPE
WHERE CompanyID LIKE “HW%”;

ORDER BY
The ORDER BY clause is used to sort the result set of a query in either ascending or descending order based
on one or more columns.
For example:
SELECT Name, Grade, Marks
FROM Student
ORDER BY Grade ASC, Marks DESC;
Sorted first by Grade ascending, then by Marks descending within each grade.

GROUP BY
The GROUP BY clause groups rows that have the same values in one or more columns. It is commonly used
with aggregate functions to summarize data for each group.
How it works:
• SQL groups all rows with the same value(s) in the specified column(s).
• Aggregate functions like COUNT(), SUM(), AVG() can then be applied to each group.

Key Points
• All columns in the SELECT clause that are not aggregated must appear in the GROUP BY clause.
• Aggregate functions such as COUNT, SUM, AVG do not need to appear in GROUP BY.

Page 19 of 21
 • You can group by one or multiple columns.
For example:
Display the total number of employees per department from the Employee table.
SELECT Department, COUNT(EmployeeID) AS TotalEmployees
FROM Employee
GROUP BY Department;
Explanation:
• Groups all rows (employees) that belong to the same department into one group.
• Counts how many employees (rows) are in each department group.
• Gives a meaningful temporary name (alias) to the calculated column.
Use of AS keyword in SQL
• Provides a temporary, readable name for a column or table in the query result.
• Improves clarity, especially when using aggregate functions or calculations.
Write an SQL query to display each department’s total number of employees and average salary.
SELECT Department, COUNT(EmployeeID) AS TotalEmployees, AVG(Salary) AS AvgSalary
FROM Employee
GROUP BY Department;
Explanation:
• Groups all employees by their department
• Counts how many employees are in each department
• Calculates the average salary for each department

N.B. In a query with GROUP BY, every column in the SELECT list must either:
• appear in the GROUP BY clause, or
• be used inside an aggregate function (COUNT, SUM, AVG, etc.)

Table: Sales
SaleID Region Product Quantity Revenue
1 East A 20 800
2 East B 10 400
3 West A 50 2100
4 East A 30 1200
5 West B 40 1600
6 East B 70 2800
7 West A 100 4100

Write an SQL query to calculate the total quantity and total revenue for each combination of Region and
Product.
SELECT Region, Product, SUM(Quantity) AS TotalQuantity, SUM(Revenue) AS TotalRevenue
FROM Sales
GROUP BY Region, Product;

The answer would be:

Region Product TotalQuantity TotalRevenue
East A 50 2000
East B 80 3200
West A 150 6200
West B 40 1600

Page 20 of 21
Explanation:
• GROUP BY Region, Product groups rows by both columns, so each unique combination of Region
and Product becomes a group.
• SUM(Quantity) calculates total quantity for each group.
• SUM(Revenue) calculates total revenue for each group.
• The result shows one row per Region–Product combination with summarized values.

List the number of customers in each country, sorted from the country with the highest number of customers
to the lowest.
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC; //ORDER BY should be used last if used with GROUP BY

Example:
SELECT BIRD_TYPE.Size, COUNT(BIRD_TYPE.BirdID) AS NumberOfBirds // Give the counted
// column a name by using the AS keyword.
FROM BIRD_TYPE, BIRD_SEEN
WHERE BIRD_SEEN.PersonID = "J_123"
AND BIRD_TYPE.BirdID = BIRD_SEEN.BirdID
GROUP BY BIRD_TYPE.Size;

Page 21 of 21