[Link] is the purpose of data base system?

The primary goal of a DBMS is to provide an environment that is both convenient and
efficient for people to storing and retrieving information.

[Link] the three levels of data abstraction?

There are three levels of abstraction:

Physical level

Logical level

View level

[Link] is a schema?

The structure of a database system, described in a formal language supported by the

database management system (DBMS), In a relational database, the schema defines the
tables, the fields in each table, and the relationships between fields and tables. Schemas
are generally stored in a data dictionary.

[Link] do you mean by functional dependencies?

Let R be a relation variable and let X and Y be arbitrary subsets of the set of attributes of
R. then we say that Y is functionally dependent on-in symbols,

X->Y

(read ―X functionally determines Y,‖ or simply ― X arrow Y ‖)- if and only if in every
possible legal value of R, each X Value has associated with it precisely one Y value.

5. What is entity? And relationship set?

It is a 'thing' in the real world with an independent existence.

The collection (or set) of similar relationships.

PART-B

Explain the system structure of a database system with neat block diagram
A Database Management System (DBMS) is software that allows users to define, store,
maintain, and manage data in a structured and efficient manner. It acts as an intermediary
between data and users, allowing disparate data from different applications to be
managed. A DBMS simplifies the complexity of data processing by providing tools to
organize data, ensure its integrity, and prevent unauthorized access or loss of data.
In today's data-driven world, DBMS are essential for applications such as banking
systems, e-commerce platforms, education, and medical systems. They not only store and
manage large amounts of data, but also provide functionality that provides performance,
security, and scalability for multiple users with multiple access levels.

Components of a Database System

Query Processor, Storage Manager, and Disk Storage. These are explained as following
below.

1. Query Processor
It interprets the requests (queries) received from end user via an application program into
instructions. It also executes the user request which is received from the DML compiler.
Query Processor contains the following components -
 DML Compiler: It processes the DML statements into low level instruction (machine
language), so that they can be executed.
 DDL Interpreter: It processes the DDL statements into a set of table containing meta
data (data about data).
 Embedded DML Pre-compiler: It processes DML statements embedded in an
application program into procedural calls.
 Query Optimizer: The Query Optimizer executes instructions generated by the DML
Compiler and improves query execution efficiency by choosing the best query plan,
considering factors such as indexing, join order, and available system resources. For
instance, if a query involves joining two large tables, the optimizer will select the best
join order to minimize query execution time.
2. Storage Manager
Storage Manager is an interface between the data stored in the database and the queries
received. It is also known as Database Control System. It maintains the consistency and
integrity of the database by applying the constraints and executing the DCL statements. It
is responsible for updating, storing, deleting, and retrieving data in the database. It
contains the following components:
 Authorization Manager: It ensures role-based access control, i.e,. checks whether
the particular person is privileged to perform the requested operation or not.

 Integrity Manager: It checks the integrity constraints when the database is

modified.

 Transaction Manager: It controls concurrent access by performing the operations in

a scheduled way that it receives the transaction. Thus, it ensures that the database
remains in the consistent state before and after the execution of a transaction.

 File Manager: It manages the file space and the data structure used to represent
information in the database.

 Buffer Manager: It is responsible for cache memory and the transfer of data
between the secondary storage and main memory.

3. Disk Storage
It contains the following essential components:
 Data Files: It stores the actual data in the database.
 Data Dictionary: It contains the information about the structure of database
objects such as tables, constraints, and relationships. It is the repository of
information that governs the metadata.
 Indices: Provides faster data retrieval by allowing the DBMS to find records
quickly, improving query performance.
Levels of DBMS Architecture
The structure of a Database Management System (DBMS) can be divided into three main
components: the Internal Level, the Conceptual Level, and the External Level.

1. Internal Level

This level represents the physical storage of data in the database. It is responsible for
storing and retrieving data from the storage devices, such as hard drives or solid-state
drives. It deals with low-level implementation details such as data compression,
indexing, and storage allocation.

2. Conceptual Level

This level represents the logical view of the database. It deals with the overall
organization of data in the database and the relationships between them. It defines the
data schema, which includes tables, attributes, and their relationships. The conceptual
level is independent of any specific DBMS and can be implemented using different
DBMSs.

3. External Level

This level represents the user's view of the database. It deals with how users access the
data in the database. It allows users to view data in a way that makes sense to them,
without worrying about the underlying implementation details. The external level
provides a set of views or interfaces to the database, which are tailored to meet the needs
of specific user groups.

Schema Mapping in DBMS

The three levels are connected via schema mapping, ensuring that changes at one level
(e.g., the conceptual level) are accurately reflected in the others. This process
maintains data independence, allowing changes in physical storage (internal level)
without affecting the logical or user views.
Role of Database Administrator (DBA)
In addition to these three levels, a DBMS also includes a Database
Administrator (DBA) component, which is responsible for managing the database
system. The DBA performs critical tasks such as:
 Database design and architecture.
 Security management: Implementing role-based access control (RBAC), encryption,
and ensuring strong authentication measures such as multi-factor authentication
(MFA).
 Backup and recovery: Regularly creating backups and preparing recovery plans in
case of data loss.
 Performance tuning: Optimizing database performance, including query
optimization, indexing, and resource management to ensure the DBMS runs
efficiently.

Explain the following

i. Data Definition Language.

ii. Data Manipulation Language.
iii. Data control Language.
iv. Views

SQL commands are fundamental building blocks used to perform given operations
on database. The operations include queries of data. creating a table, adding data
to tables, dropping the table, modifying the table and set permission for users.
SQL Commands are mainly categorized into five categories:

DDL - Data Definition Language

DDL (Data Definition Language) consists of SQL commands that can be used for
defining, altering and deleting database structures such as tables, indexes and schemas. It
simply deals with descriptions of the database schema and is used to create and modify
the structure of database objects in the database
 Command Description Syntax

Create database or its objects CREATE TABLE table_name

CREATE (table, index, function, views, (column1 data_type, column2
store procedure and triggers) data_type, ...);

Delete objects from the

DROP DROP TABLE table_name;
database

ALTER TABLE table_name

Alter the structure of the
ALTER ADD COLUMN column_name
database
data_type;

Remove all records from a

table, including all spaces TRUNCATE TABLE
TRUNCATE
allocated for the records are table_name;
removed

Add comments to the data COMMENT ON TABLE

COMMENT
dictionary table_name IS 'comment_text';

RENAME TABLE
Rename an object existing in
RENAME old_table_name TO
the database
new_table_name;

Example:

CREATE TABLE employees (

employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
hire_date DATE
);
In this example, a new table called employees is created with columns for employee ID,
first name, last name and hire date.

3. DML - Data Manipulation Language

DML commands are used to manipulate the data stored in database tables. With DML,
you can insert new records, update existing ones, delete unwanted data or retrieve
information.

Command Description Syntax

INSERT INTO table_name (column1,

INSERT Insert data into a table
column2, ...) VALUES (value1, value2, ...);

UPDAT Update existing data UPDATE table_name SET column1 = value1,

E within a table column2 = value2 WHERE condition;

Delete records from a DELETE FROM table_name WHERE

DELETE
database table condition;

Example:

INSERT INTO employees (first_name, last_name, department)

VALUES ('Jane', 'Smith', 'HR');
This query inserts a new record into employees table with first name 'Jane', last name
'Smith' and department 'HR'.

DCL - Data Control Language

DCL (Data Control Language) includes commands such as GRANT and REVOKE
which mainly deal with the rights, permissions and other controls of the database
system. These commands are used to control access to data in the database by granting or
revoking permissions.
 Command Description Syntax

Assigns new privileges to a GRANT privilege_type

user account, allowing access [(column_list)] ON [object_type]
GRANT
to specific database objects, object_name TO user [WITH
actions or functions. GRANT OPTION];

Removes previously granted REVOKE [GRANT OPTION

privileges from a user FOR] privilege_type
REVOKE account, taking away their [(column_list)] ON [object_type]
access to certain database object_name FROM user
objects or actions. [CASCADE];

Example:

GRANT SELECT, UPDATE ON employees TO user_name;

This command grants the user user_name the permissions to select and update records in
the employees table.

DCL - Data Control Language

DCL (Data Control Language) includes commands such as GRANT and REVOKE
which mainly deal with the rights, permissions and other controls of the database
system. These commands are used to control access to data in the database by granting or
revoking permissions.

Command Description Syntax

Assigns new privileges to a GRANT privilege_type

user account, allowing access [(column_list)] ON [object_type]
GRANT
to specific database objects, object_name TO user [WITH
actions or functions. GRANT OPTION];
 Command Description Syntax

Removes previously granted REVOKE [GRANT OPTION

privileges from a user FOR] privilege_type
REVOKE account, taking away their [(column_list)] ON [object_type]
access to certain database object_name FROM user
objects or actions. [CASCADE];

Example:

GRANT SELECT, UPDATE ON employees TO user_name;

This command grants the user user_name the permissions to select and update records in
the employees table.
A view in a Database Management System (DBMS) is a virtual table based on the
result-set of an SQL statement. It does not store data physically but dynamically retrieves
data from one or more underlying tables when accessed. Views are used to simplify
complex queries, enhance security by restricting access, and present data in a structured,
intuitive format.
Key Characteristics of Views

 Virtual: Views do not store physical data; only their definition (the SELECT query) is
stored in the database.

 Dynamic: The data in a view is always up-to-date because it is computed at the time of
the query from the current data in the base tables.

 Security: Views can restrict access to sensitive information by allowing users to see only
specific rows and columns of a table, rather than the entire base table.

 Simplicity: Views can hide the complexity of data by joining multiple tables or using
aggregate functions, making interactions simpler for the user.

Example: Creating a View

Consider a database with two tables: Employees and Departments.

Employees Table:
EmployeeID FirstName LastName DepartmentID Salary

1 John Doe 101 60000

2 Jane Smith 102 75000

3 Michael Johnson 101 55000

A Departments table exists with DepartmentID and DepartmentName. An example view

combining data from Employees and Departments can be created using a CREATE
VIEW statement. This view can be queried using a standard SELECT statement. Views
can be modified with CREATE OR REPLACE VIEW or removed using DROP VIEW.
DML operations on simple views are possible, but generally not supported for complex
views based on multiple tables.

[Link] and explain the ER Diagram for banking Systems. (Home loan applications)
ER diagram is known as Entity-Relationship diagram. It is used to analyze to structure of
the Database. It shows relationships between entities and their attributes. An ER model
provides a means of communication.

ER diagram of Bank has the following description :

 Bank have Customer.

 Banks are identified by a name, code, address of main office.
 Banks have branches.
 Branches are identified by a branch_no., branch_name, address.
 Customers are identified by name, cust-id, phone number, address.
 Customer can have one or more accounts.
 Accounts are identified by account_no., acc_type, balance.
 Customer can avail loans.
 Loans are identified by loan_id, loan_type and amount.
 Account and loans are related to bank's branch.

This bank ER diagram illustrates key information about bank, including entities such as
branches, customers, accounts, and loans. It allows us to understand the relationships
between entities.

Entities and their Attributes are :

 Bank Entity : Attributes of Bank Entity are Bank Name, Code and Address.
Code is Primary Key for Bank Entity.
 Customer Entity : Attributes of Customer Entity are Customer_id, Name, Phone
Number and Address.
Customer_id is Primary Key for Customer Entity.
 Branch Entity : Attributes of Branch Entity are Branch_id, Name and Address.
Branch_id is Primary Key for Branch Entity.
 Account Entity : Attributes of Account Entity are Account_number, Account_Type
and Balance.
Account_number is Primary Key for Account Entity.
 Loan Entity : Attributes of Loan Entity are Loan_id, Loan_Type and Amount.
Loan_id is Primary Key for Loan Entity.
Relationships are :

 Bank has Branches => 1 : N

One Bank can have many Branches but one Branch can not belong to many Banks, so
the relationship between Bank and Branch is one to many relationship.
 Branch maintain Accounts => 1 : N
One Branch can have many Accounts but one Account can not belong to many
Branches, so the relationship between Branch and Account is one to many
relationship.

 Branch offer Loans => 1 : N

One Branch can have many Loans but one Loan can not belong to many Branches, so
the relationship between Branch and Loan is one to many relationship.

 Account held by Customers => M : N

One Customer can have more than one Accounts and also One Account can be held
by one or more Customers, so the relationship between Account and Customers is
many to many relationship.

 Loan availed by Customer => M : N

(Assume loan can be jointly held by many Customers).
One Customer can have more than one Loans and also One Loan can be availed by
one or more Customers, so the relationship between Loan and Customers is many to
many relationship.

Consider the following relations for a company database Application.

Employee (Eno, Name, Sex, DOB, Doj, Designation, Basic_Pay, Dept_No)
Department (DeptNo,Name)
Project (ProjNo, Name, Dept_No)
Works for (Eno,ProjNo,Date, Hours)
The attributes specified for each relation is self-explanatory. However the business rules
are stated as follows. A department can control a project. An employee can work on any
number of projects on a day. However an employee cannot work more than once on a
project he/she worked on that day. The primary key are underlined.
(i) Develop an SQL query to list the department number and the number of Employee in
each department.
(ii) Develop a View that will keep track of the department number, the number of
employees in the department, and the total basic pay expenditure for each department.

Explain All Normalization form in DBMS.

Database normalization is an important process used to organize and structure relational

databases. This process ensures that data is stored in a way that minimizes redundancy,
simplifies querying, and improves data integrity.

Different Types of Database Normalization

So far, we have looked at what normalization in SQL is, why normalization in SQL is
important, and what causes the need for normalization. Database normalization comes
in different forms, each with increasing levels of data organization.

In this section, we will briefly discuss the different normalization levels and then explore
them deeper in the next section.

First Normal Form (1NF)

This normalization level ensures that each column in your data contains only atomic
values. Atomic values in this context means that each entry in a column is indivisible. It
is like saying that each cell in a spreadsheet should hold just one piece of information.
1NF ensures atomicity of data, with each column cell containing only a single value and
each column having unique names.

Second Normal Form (2NF)

Eliminates partial dependencies by ensuring that non-key attributes depend only on the
primary key. What this means, in essence, is that there should be a direct relationship
between each column and the primary key, and not between other columns.
Third Normal Form (3NF)

Removes transitive dependencies by ensuring that non-key attributes depend only on the
primary key. This level of normalization builds on 2NF.

Boyce-Codd Normal Form (BCNF)

This is a more strict version of 3NF that addresses additional anomalies. At this
normalization level, every determinant is a candidate key.

Fourth Normal Form (4NF)

This is a normalization level that builds on BCNF by dealing with multi-valued

dependencies.

Fifth Normal Form (5NF)

5NF is the highest normalization level that addresses join dependencies. It is used in
specific scenarios to further minimize redundancy by breaking a table into smaller tables.

Database Normalization With Real-World Examples

We have already highlighted all the data normalization levels. Let’s further explore each
of them in more depth with examples and explanations.

First Normal Form (1NF) Normalization

1NF ensures that each column cell contains only atomic values. Imagine a library
database with a table storing book information (title, author, genre, and borrowed_by). If
the table is not normalized, borrowed_by could contain a list of borrower names
separated by commas. This violates 1NF, as a single cell holds multiple values. The table
below is a good representation of a table that violates 1NF, as described earlier.

title author genre borrowed_by

John Doe, Jane Doe, James

To Kill a Mockingbird Harper Lee Fiction
Brown

J. R. R. Fantas
The Lord of the Rings Emily Garcia, David Lee
Tolkien y
 Harry Potter and the Sorcerer’s Fantas
J.K. Rowling Michael Chen
Stone y

The solution?

In 1NF, we create a separate table for borrowers and link them to the book table. These
tables can either be linked using the foreign key in the borrower table or a separate
linking table. The foreign key in the borrowers table approach involves adding a foreign
key column to the borrowers table that references the primary key of the books table.
This will enforce a relationship between the tables, ensuring data consistency.

You can find a representation of this below:

Books table

book_id (PK) title author genre

1 To Kill a Mockingbird Harper Lee Fiction

2 The Lord of the Rings J. R. R. Tolkien Fantasy

3 Harry Potter and the Sorcerer’s Stone J.K. Rowling Fantasy

Borrowers table

borrower_id (PK) name book_id (FK)

1 John Doe 1

2 Jane Doe 1

3 James Brown 1

4 Emily Garcia 2

5 David Lee 2
 6 Michael Chen 3

Second Normal Form (2NF)

This level of normalization, as already described, builds upon 1NF by ensuring there are
no partial dependencies on the primary key. In simpler terms, all non-key attributes must
depend on the entire primary key and not just part of it.

From the 1NF that was implemented, we already have two separate tables (you can check
the 1NF section).

Now, let’s say we want to link these tables to record borrowings. The initial approach
might be to simply add a borrower_id column to the books table, as shown below:

book_id borrower_id
title author genre
(PK) (FK)

1 To Kill a Mockingbird Harper Lee Fiction 1

J. R. R.
2 The Lord of the Rings Fantasy NULL
Tolkien

Harry Potter and the

3 J.K. Rowling Fantasy 6
Sorcerer’s Stone

This might look like a solution, but it violates 2NF simply because the borrower_id only
partially depends on the book_id. A book can have multiple borrowers, but a single
borrower_id can only be linked to one book in this structure. This creates a partial
dependency.

The solution?

We need to achieve the many-to-many relationship between books and borrowers to

achieve 2NF. This can be done by introducing a separate table:

Book_borrowings table
 borrowing_id (PK) book_id (FK) borrower_id (FK) borrowed_date

1 1 1 2024-05-04

2 2 4 2024-05-04

3 3 6 2024-05-04

This table establishes a clear relationship between books and borrowers. The book_id and
borrower_id act as foreign keys, referencing the primary keys in their respective tables.
This approach ensures that borrower_id depends on the entire primary key (book_id) of
the books table, complying with 2NF.

Third Normal Form (3NF)

3NF builds on 2NF by eliminating transitive dependencies. A transitive dependency

occurs when a non-key attribute depends on another non-key attribute, which in turn
depends on the primary key. It basically takes its meaning from the transitive law.

From the 2NF we already implemented, there are three tables in our library database:

Books table

book_id (PK) title author genre

1 To Kill a Mockingbird Harper Lee Fiction

2 The Lord of the Rings J. R. R. Tolkien Fantasy

3 Harry Potter and the Sorcerer’s Stone J.K. Rowling Fantasy

Borrowers table
 borrower_id (PK) name book_id (FK)

1 John Doe 1

2 Jane Doe 1

3 James Brown 1

4 Emily Garcia 2

5 David Lee 2

6 Michael Chen 3

Book_borrowings table

borrowing_id (PK) book_id (FK) borrower_id (FK) borrowed_date

1 1 1 2024-05-04

2 2 4 2024-05-04

3 3 6 2024-05-04

The 2NF structure looks efficient, but there might be a hidden dependency. Imagine we
add a due_date column to the books table. This might seem logical at first sight, but it’s
going to create a transitive dependency where:

 The due_date column depends on the borrowing_id (a non-key attribute) from the
book_borrowings table.
 The borrowing_id in turn depends on book_id (the primary key) of the books table.
The implication of this is that due_date relies on an intermediate non-key attribute
(borrowing_id) instead of directly depending on the primary key (book_id). This violates
3NF.
The solution?

We can move the due_date column to the most appropriate table by updating the
book_borrowings table to include the due_date and returned_date columns.

Below is the updated table:

borrowing_id (PK) book_id (FK) borrower_id (FK) borrowed_date due_date

1 1 1 2024-05-04 2024-05-20

2 2 4 2024-05-04 2024-05-18

3 3 6 2024-05-04 2024-05-10

By placing the due_date column in the book_borrowing table, we have successfully

eliminated the transitive dependency.

What this means is that due_date now directly depends on the combined relationship
between book_id and borrower_id. In this context, book_id and borrower_id are acting as
a composite foreign key, which together form the primary key of the book_borrowings
table.

Boyce-Codd Normal Form (BCNF)

BCNF is based on functional dependencies that consider all candidate keys in a

relationship.

Functional dependencies (FD) define relationships between attributes within a relational

database. An FD states that the value of one column determines the value of another
related column. FDs are very important because they guide the process of normalization
by identifying dependencies and ensuring data is appropriately distributed across tables.

BCNF is a stricter version of 3NF. It ensures that every determinant (a set of attributes
that uniquely identify a row) in a table is a candidate key (a minimal set of attributes that
uniquely identify a row). The whole essence of this is that all determinants should be able
to serve as primary keys.

It ensures that every functional dependency (FD) has a superkey as its determinant. In
other words, if X —> Y (X determines Y) holds, X must be a candidate key (superkey) of
the relation. Please note that X and Y are columns in a data table.
As a build-up from the 3NF, we have three tables:

Books table

book_id (PK) title author genre

1 To Kill a Mockingbird Harper Lee Fiction

2 The Lord of the Rings J. R. R. Tolkien Fantasy

3 Harry Potter and the Sorcerer’s Stone J.K. Rowling Fantasy

Borrowers table

borrower_id (PK) name book_id (FK)

1 John Doe 1

2 Jane Doe 1

3 James Brown 1

4 Emily Garcia 2

5 David Lee 2

6 Michael Chen 3

Book_borrowings table

borrowing_id (PK) book_id (FK) borrower_id (FK) borrowed_date due_date

1 1 1 2024-05-04 2024-05-20
 2 2 4 2024-05-04 2024-05-18

3 3 6 2024-05-04 2024-05-10

While the 3NF structure is good, there might be a hidden determinant in the
book_borrowings table. Assuming one borrower cannot borrow the same book twice
simultaneously, the combination of book_id and borrower_id together uniquely identifies
a borrowing record.

This structure violates BCNF since the combined set (book_id and borrower_id) is not
the primary key of the table (which is just borrowing_id).

The solution?

To achieve BCNF, we can either decompose the book_borrowings table into two separate
tables or make the combined attribute set the primary key.

1. Approach 1 (decompose the table): In this approach, we will be decomposing the

book_borrowings table into separate tables:


 A table with borrowing_id as the primary key, borrowed_date, due_date, and

returned_date.
 Another separate table to link books and borrowers, with book_id as a foreign key,
borrower_id as a foreign key, and potentially additional attributes specific to the
borrowing event.
2. Approach 2 (make the combined attribute set the primary key): We can consider making
book_id and borrower_id a composite primary key for uniquely identifying borrowing
records. The problem with this approach is that it won’t serve its purpose if a borrower
can borrow the same book multiple times.
In the end, your choice between these options depends on your specific data needs and
how you want to model borrowing relationships.

Fourth Normal Form (4NF)

4NF deals with multi-valued dependencies. A multi-valued dependency exists when one
attribute can have multiple dependent attributes, and these dependent attributes are
independent of the primary key. It’s quite complex, but we will be exploring it deeper
using an example.
 The library example we’ve been using throughout these explanations is not applicable at
this normalization level. 4NF typically applies to situations where a single attribute might
have multiple dependent attributes that don’t directly relate to the primary key.

Let’s use another scenario. Imagine a database that stores information about publications.
We will be considering a “Publications” table with columns, title, author,
publication_year, and keywords.

publication_id
title author publication_year keywords
(PK)

To Kill a Harper Coming-of-Age,

1 1960
Mockingbird Lee Legal

The Lord of the J. R. R. Fantasy, Epic,

2 1954
Rings Tolkien Adventure

Pride and Jane Romance, Social

3 1813
Prejudice Austen Commentary

The table structure above is violating 4NF because:

 The keywords column has a multi-valued dependency on the primary key publication_id.
What this means is that a publication can have multiple keywords, and these keywords
are independent of the publication’s unique identifier.
The solution?

We can create a separate table.

Publication_keywords table

publication_id (FK) keyword

1 Coming-of-Age

1 Legal
 2 Fantasy

2 Epic

2 Adventure

3 Romance

3 Social Commentary

The newly created table (Publication_keywords) establishes a many-to-many relationship

between publication and keywords. Each publication can have multiple keywords linked
through the publication_id, which is a foreign key, and each keyword can be associated
with multiple publications.

With this, we have successfully eliminated the multi-valued dependency and achieved
4NF.

Fifth Normal Form (5NF)

5NF is the most complex form of normalization that eliminates join dependencies. This is
a situation where data needs to be joined from multiple tables to answer a specific query,
even when those tables are already in 4NF.

In simpler terms, 5NF ensures that no additional information can be derived by joining
the tables together that wasn’t already available in the separate tables.

Join dependencies are less likely to occur when tables are already normalized (in 3NF or
4NF), hence the difficulty in creating a clear and straightforward example for 5NF.

However, let’s take a look at this scenario where 5NF might be relevant:

Imagine a university database with normalized tables for “Courses” and “Enrollments.”

Courses table

course_id (PK) course_name department

101 Introduction to Programming Computer Science

 202 Data Structures and Algorithms Computer Science

301 Web Development I Computer Science

401 Artificial Intelligence Computer Science

Enrollments table

enrollment_id (PK) student_id (FK) course_id (FK) grade

1 12345 101 A

2 12345 202 B

3 56789 301 A-

4 56789 401 B+

Assuming these tables are already in 3NF or 4NF, a join dependency might exist
depending on how data is stored. For instance, a course has a prerequisite requirement
stored within the “Courses” table as the “prerequisite_course_id” column.

This might seem efficient at first glance. However, consider a query that needs to retrieve
a student’s enrolled courses and their respective prerequisites. In this scenario, you would
need to join the “Courses” and “Enrollments” tables, then potentially join the “Courses”
table to retrieve prerequisite information.

The Solution?

To potentially eliminate the join dependency and achieve 5NF, we could introduce a
separate “Course Prerequisites” table:

Course_prerequisite table

course_id (FK) prerequisite_course_id (FK)

202 101
 301 NULL

401 202

This approach separates prerequisite information and allows efficient retrieval of enrolled
courses and their prerequisites in a single join between the “Enrollments” and
“Course_prerequisites” tables.

Note: We are assuming a student can only have one prerequisite per course.

5NF is a very complex and rare type of normalization, so as someone just starting their
learning journey in data, you might not find an application. However, it’s going to be
added knowledge and will make you prepared when you stumble on complex databases.

Develop an Entity Relationship model for a library management system. Clearly State the
problem Definition, Description, Business Rule and any assumption you make
 Problem Definition

The objective is to design a database system for managing the core functions of a library.
The system needs to efficiently track the library's collection of books, manage member
information, record lending transactions, and handle reservation requests. This ER model
will serve as the blueprint for the database schema, ensuring data integrity and supporting
key business processes.
Description

The library management system must manage various entities and their relationships:
 Books and Authors: A library holds many books. A single book can be written by
multiple authors, and an author can write multiple books. Books have key attributes like
ISBN, title, publication date, and genre.
 Library Members: Individuals who can borrow books are registered as members. Each
member has a unique ID, name, contact details (phone, email), and registration date.

 Lending and Returns: Members borrow specific book copies. The system must record
who borrowed which book copy, the date borrowed, and the expected return date. It also
needs to record the actual return date and track any fines incurred.

 Inventory: The library may have multiple physical copies of the same book title. Each
copy has a unique identifier (e.g., accession number) and its current status (e.g.,
'available', 'on loan', 'reserved').

 Reservations: Members can reserve book titles that are currently unavailable.

Business Rules

The following rules govern the system's operations and data integrity:
1. Unique Identifiers: Every entity instance must have a unique identifier (e.g., ISBN for a
book title, Member ID for a member, Copy ID for a book copy, Author ID for an author).

2. Borrowing Limit: A member may borrow a maximum of 5 book copies at any given
time.

3. Loan Duration: The standard loan period for any book copy is 14 days.

4. Fines: Fines are calculated for overdue books at a rate of $0.50 per day overdue.

5. Reservations: A member can only reserve a book title if all copies of that title are
currently loaned out.

6. Data Integrity: All book copies must be associated with an existing book title and a
library branch (if a multi-branch system is assumed).

7. Membership Status: Only active members can borrow or reserve books.

Assumptions

 The system operates within a single library branch (to simplify branch management; can
be extended later).

 Fines are calculated upon return of the book.

 A 'Book Title' entity is distinct from a 'Book Copy' entity to manage inventory
effectively.
 A book must have at least one author, and an author must have authored at least one book
in the system.

 A simple reservation system is assumed, where the first person in the queue gets the next
available copy.

Entity-Relationship (ER) Diagram Model

This model illustrates the entities (tables), their attributes (columns), and the relationships
between them.

Entity Attributes Primary

Key
(PK)

MEMBE Member_ID (PK), First_Name, Last_Name, Address, Phone, Ema Member_

R il, Date_Registered, Status ID

AUTHO Author_ID (PK), First_Name, Last_Name, Biography Author_I

R D

BOOK_ ISBN (PK), Title, Publication_Date, Genre, Publisher ISBN

TITLE

BOOK_ Copy_ID (PK), ISBN (FK), Status (Available, On Copy_ID

COPY Loan, Reserved), Location_Shelf

LOAN Loan_ID (PK), Copy_ID (FK), Member_ID (FK), Date_Borrowe Loan_ID

d, Due_Date, Date_Returned, Fine_Amount

RESERV Reservation_ID (PK), ISBN (FK), Member_ID (FK), Date_Reser Reservati

ATION ved, Notification_Sent_Date on_ID
 Relationships

Relationship Entities Involved Type Description

Authorship AUTHOR, Many- Authors write Book Titles, Book

BOOK_TITLE to-Many Titles have Authors. Requires an
(M:N) associative entity BOOK_AUTHOR.

Inventory Of BOOK_TITLE, One-to- One Book Title can have many Book
BOOK_COPY Many Copies.
(1:N)

Borrowed MEMBER, Many- A Member borrows a specific Book

By BOOK_COPY, to-Many Copy. Requires the associative
LOAN (M:N) entity LOAN.

Requested MEMBER, Many- A Member reserves a Book Title.

By BOOK_TITLE, to-Many Requires the associative
RESERVATION (M:N) entity RESERVATION.

Associative Entities:

 BOOK_AUTHOR: Links AUTHOR and BOOK_TITLE.

Attributes: Author_ID (FK), ISBN (FK), Author_Order (optional, for sequence).

 LOAN: Details the specific instances of borrowing.

 RESERVATION: Details the specific instances of reserving.