CT042-3-1 Introduction to Databases

APU E-Bookstore

Part 1: Database Fundamentals, Business Rules, Normalization & ERD

Module: Introduction to Databases

Module Code: CT042-3-1-IDB

Intake: [Your Intake]

Lecturer: [Lecturer Name]

Group Members:

[Student 1 Name] - [TP Number]

[Student 2 Name] - [TP Number]

[Student 3 Name] - [TP Number]

[Student 4 Name] - [TP Number]

Submission Date: 30 November 2025

12
Entities
33
Business Rules
3NF
Normalization
13
Relationships

Table of Contents

Part 1(a): Database & DBMS

Understanding the fundamentals of database systems

1.1 Disadvantages of File-Based System

Before the implementation of database systems, organizations typically used file-based systems to store and manage data. In the context of APU E-Bookstore, using a file-based system would present several critical disadvantages:

🔄

Data Redundancy & Inconsistency

In a file-based system for APU E-Bookstore, the same book information might be stored in multiple files – one for inventory management, another for sales records. When the price of a book changes, it must be updated in all files. If any file is missed, the data becomes inconsistent, leading to billing errors.

📦

Data Isolation

Data is scattered across different files in various formats. Member information might be in one file format, book details in another. When the manager needs to generate a report showing which members purchased specific genres, correlating this data becomes extremely difficult.

🔍

Difficulty in Accessing Data

File-based systems require specific programs to access data. If a manager suddenly needs a list of all members who purchased books from a particular publisher, a new program would need to be written since such a query was not anticipated.

⚠️

Integrity Problems

Maintaining data integrity constraints is problematic. Business rules such as 'book quantity cannot be negative' or 'rating must be between 1-10' must be enforced through application code. Multiple applications must implement these constraints separately.

💥

Atomicity Problems

File-based systems cannot guarantee atomic transactions. If the system crashes after deducting payment but before recording the order, the member loses money without receiving books. There is no mechanism to rollback partial transactions.

👥

Concurrent Access Anomalies

When multiple users access files simultaneously, anomalies occur. If two customers try to purchase the last copy of a book at the same time, both might succeed because the file system cannot handle concurrent access properly.

🔒

Security Problems

File-based systems offer limited security options. It would be difficult to allow staff to view book inventory while restricting access to member payment details. File-level access control is too coarse-grained, either giving full access to a file or no access at all, making it impossible to implement role-based security effectively.

1.2 Advantages of Database and DBMS

A Database Management System (DBMS) offers significant advantages over file-based systems. For APU E-Bookstore, implementing a DBMS provides the following benefits:

🔗

Data Independence

DBMS provides both logical and physical data independence. If the database structure needs modification, applications using the database do not need to be rewritten. This separation allows the bookstore to evolve its data structure without disrupting operations.

📊

Minimal Data Redundancy

Book information is stored only once in the Book table. Publisher details are stored once in the Publisher table and referenced through foreign keys. A price change only needs to be made in one place to be reflected throughout the system.

✓

Improved Data Consistency

With minimal redundancy comes improved consistency. Since member information exists in one location, there cannot be conflicting versions of a member's address or contact details. All parts of the system access the same authoritative data source.

🤝

Improved Data Sharing

A DBMS allows multiple users and applications to share data simultaneously. The inventory management module, sales processing system, and reporting tools can all access the same database concurrently while accessing consistent information.

📏

Enforcement of Standards

DBMS enforces data standards centrally. APU E-Bookstore can define that all dates must follow a specific format, all prices must be in Malaysian Ringgit with two decimal places, and all ISBN numbers must conform to the standard format.

🛡️

Improved Data Integrity

Data integrity constraints are defined once and enforced automatically by the DBMS. Constraints such as 'rating must be between 1-10', 'book quantity must be non-negative' are enforced at the database level. No application can violate these rules.

🔐

Improved Security

DBMS provides fine-grained access control. Different user roles can have different permissions: the bookstore manager can access all data, staff can view and update inventory but not financial records, and members can only access their own order history.

🚀

Improved Data Accessibility

Using SQL, users can easily query the database for any information needed. Managers can quickly generate reports such as 'bestselling books this month' or 'members who haven't purchased in 30 days' without requiring new programs.

1.3 Functions of DBMS

A DBMS provides several essential functions that support APU E-Bookstore's operations:

Function Description & Application to APU E-Bookstore
1. Data Definition The DBMS allows definition of database structure through Data Definition Language (DDL). For APU E-Bookstore, this means creating tables for Members, Books, Publishers, Orders, Order_Items, Feedback, and other entities. The DBMS stores this structural information in the data dictionary.
2. Data Manipulation The DBMS provides Data Manipulation Language (DML) for inserting, updating, deleting, and retrieving data. APU E-Bookstore uses these capabilities to add new books, update member information, record orders, and generate various reports.
3. Data Security & Integrity The DBMS enforces security rules and integrity constraints. It ensures only authorized personnel can modify prices, prevents negative inventory quantities, enforces referential integrity between orders and members, and protects sensitive payment information.
4. Backup and Recovery The DBMS provides mechanisms for database backup and recovery. If APU E-Bookstore's database suffers a failure, the DBMS can restore data to a consistent state using backup files and transaction logs, protecting against data loss.
5. Concurrency Control The DBMS manages simultaneous access by multiple users. When multiple customers attempt to purchase the same book, the DBMS ensures proper coordination through locking mechanisms, preventing overselling and maintaining data consistency.
6. Transaction Management The DBMS ensures transactions follow ACID properties (Atomicity, Consistency, Isolation, Durability). When a customer completes a purchase, the entire transaction either completes fully or is rolled back entirely, preventing partial updates.

Part 1(b): Business Rules & Normalization

Defining rules and normalizing the database structure

2.1 Business Rules

Based on the APU E-Bookstore case study, the following business rules have been identified and categorized:

ENTITY RULES
  1. Each MEMBER must have a unique Member_ID, name, email, password, contact number, address, and gender.
  2. Each BOOK must have a unique Book_ID, title, ISBN, price, quantity, description, and date added.
  3. Each PUBLISHER must have a unique Publisher_ID, name, contact person, email, phone, and address.
  4. Each GENRE must have a unique Genre_ID and genre name.
  5. Each ORDER must have a unique Order_ID, order date, and status.
  6. Each ORDER_ITEM must record quantity and unit price for each book in an order.
  7. Each FEEDBACK must have a unique Feedback_ID, rating (1-10), optional comment, and feedback date.
  8. Each PAYMENT must have a unique Payment_ID, payment method, payment date, amount, and status.
  9. Each DELIVERY must have a unique Delivery_ID, delivery address, expected delivery date, and status.
  10. Each STORE_ORDER (order to publisher) must have a unique Store_Order_ID, order date, and status.
RELATIONSHIP RULES
  1. A MEMBER can place zero or many ORDERs; each ORDER must be placed by exactly one MEMBER.
  2. An ORDER can contain one or many BOOKs; a BOOK can appear in zero or many ORDERs (via ORDER_ITEM).
  3. A MEMBER can provide zero or many FEEDBACKs; each FEEDBACK must be provided by exactly one MEMBER.
  4. A BOOK can receive zero or many FEEDBACKs; each FEEDBACK must be for exactly one BOOK.
  5. A MEMBER can only provide one FEEDBACK per BOOK (unique constraint on Member_ID + Book_ID).
  6. A PUBLISHER can publish one or many BOOKs; each BOOK must be published by exactly one PUBLISHER.
  7. An ORDER must have exactly one PAYMENT; each PAYMENT belongs to exactly one ORDER.
  8. An ORDER must have exactly one DELIVERY; each DELIVERY belongs to exactly one ORDER.
  9. A BOOK belongs to one or many GENREs; a GENRE can contain zero or many BOOKs (via BOOK_GENRE).
  10. A STORE_ORDER is sent to exactly one PUBLISHER; a PUBLISHER can receive zero or many STORE_ORDERs.
  11. A STORE_ORDER can request one or many BOOKs (via STORE_ORDER_ITEM).
CONSTRAINT RULES
  1. Book quantity must be greater than or equal to zero.
  2. Book price must be greater than zero.
  3. Feedback rating must be between 1 and 10 (inclusive), where 1 = terrible and 10 = masterpiece.
  4. Member gender must be 'Male', 'Female', or 'Other'.
  5. Order status must be 'Pending', 'Confirmed', 'Shipped', 'Delivered', or 'Cancelled'.
  6. Payment status must be 'Pending', 'Completed', 'Failed', or 'Refunded'.
  7. Delivery status must be 'Pending', 'Shipped', 'In Transit', 'Delivered', or 'Failed'.
  8. Order_Item quantity must be greater than zero.
  9. Feedback comment is optional but if provided, must not exceed 500 characters.
  10. Delivery must be completed within 7 working days of order confirmation.
  11. Member email must be unique in the system.
  12. Book ISBN must be unique in the system.

2.2 Normalization Process (UNF to 3NF)

Normalization is the process of organizing data to minimize redundancy and dependency. Below is the step-by-step normalization process for APU E-Bookstore:

Step 1: Unnormalized Form (UNF)

The UNF represents raw data collected from the APU E-Bookstore operations with repeating groups and no structure:

Table Attributes (UNF)
ORDER_DATA Order_ID, Order_Date, Member_ID, Member_Name, Member_Email, Member_Phone, Member_Address, Member_Gender, {Book_ID, Book_Title, Book_ISBN, Book_Price, Publisher_ID, Publisher_Name, Publisher_Contact, Publisher_Email, Quantity_Ordered, {Genre_ID, Genre_Name}}, Order_Total, Payment_ID, Payment_Method, Payment_Date, Payment_Status, Delivery_ID, Delivery_Address, Delivery_Date, Delivery_Status, {Feedback_ID, Rating, Comment}
⚠️ Problems in UNF:
  • Repeating groups exist (multiple books per order, multiple genres per book) - shown in red
  • Data redundancy (member and publisher info repeated for each order)
  • No primary key structure defined

Step 2: First Normal Form (1NF)

📋 Rules for 1NF:
  1. Eliminate repeating groups
  2. Create separate tables for related data
  3. Identify primary key for each table
  4. All attributes must be atomic (single value)
Table Attributes (1NF)
MEMBER Member_ID (PK), Member_Name, Member_Email, Member_Phone, Member_Address, Member_Gender
PUBLISHER Publisher_ID (PK), Publisher_Name, Publisher_Contact, Publisher_Email, Publisher_Phone, Publisher_Address
GENRE Genre_ID (PK), Genre_Name
BOOK Book_ID (PK), Book_Title, ISBN, Price, Quantity, Description, Date_Added, Publisher_ID (FK), Publisher_Name, Publisher_Contact, Publisher_Email, Publisher_Phone, Publisher_Address
BOOK_GENRE Book_ID (PK, FK), Genre_ID (PK, FK)
ORDER Order_ID (PK), Order_Date, Order_Total, Order_Status, Member_ID (FK), Member_Name, Member_Email, Member_Phone, Member_Address, Member_Gender
ORDER_ITEM Order_ID (PK, FK), Book_ID (PK, FK), Quantity, Unit_Price, Subtotal
PAYMENT Payment_ID (PK), Order_ID (FK), Payment_Method, Payment_Date, Amount, Payment_Status
DELIVERY Delivery_ID (PK), Order_ID (FK), Delivery_Address, Delivery_Date, Delivery_Status
FEEDBACK Feedback_ID (PK), Member_ID (FK), Book_ID (FK), Rating, Comment, Feedback_Date
📊 Analysis: Tables are now in 1NF - no repeating groups, each cell contains atomic values, and primary keys are identified. However, partial dependencies still exist (highlighted rows show redundant data).

Step 3: Second Normal Form (2NF)

📋 Rules for 2NF:
  1. Must be in 1NF
  2. Remove partial dependencies (non-key attributes must depend on the entire primary key)

Partial Dependencies Identified:

  • In ORDER table: Member_Name, Member_Email, Member_Phone, Member_Address, Member_Gender depend only on Member_ID, not Order_ID
  • In BOOK table: Publisher_Name, Publisher_Contact, Publisher_Email, Publisher_Phone, Publisher_Address depend only on Publisher_ID, not Book_ID
Table Attributes (2NF)
MEMBER Member_ID (PK), Member_Name, Member_Email, Member_Phone, Member_Address, Member_Gender
PUBLISHER Publisher_ID (PK), Publisher_Name, Publisher_Contact, Publisher_Email, Publisher_Phone, Publisher_Address
GENRE Genre_ID (PK), Genre_Name
BOOK ✓ Book_ID (PK), Book_Title, ISBN, Price, Quantity, Description, Date_Added, Publisher_ID (FK)
BOOK_GENRE Book_ID (PK, FK), Genre_ID (PK, FK)
ORDER ✓ Order_ID (PK), Order_Date, Order_Total, Order_Status, Member_ID (FK)
ORDER_ITEM Order_ID (PK, FK), Book_ID (PK, FK), Quantity, Unit_Price, Subtotal
PAYMENT Payment_ID (PK), Order_ID (FK), Payment_Method, Payment_Date, Amount, Payment_Status
DELIVERY Delivery_ID (PK), Order_ID (FK), Delivery_Address, Delivery_Date, Delivery_Status
FEEDBACK Feedback_ID (PK), Member_ID (FK), Book_ID (FK), Rating, Comment, Feedback_Date
✅ Analysis: Partial dependencies removed. Publisher attributes removed from BOOK (reference Publisher via FK), Member attributes removed from ORDER (reference Member via FK). Highlighted rows (✓) show the changes.

Step 4: Third Normal Form (3NF) - FINAL

📋 Rules for 3NF:
  1. Must be in 2NF
  2. Remove transitive dependencies (non-key attributes must not depend on other non-key attributes)

Transitive Dependencies Identified:

  • In ORDER_ITEM: Subtotal depends on Quantity and Unit_Price (Subtotal = Quantity × Unit_Price). This is a derived/calculated attribute.
  • In ORDER: Order_Total depends on the sum of all ORDER_ITEM subtotals. This is also a derived attribute.
Table Attributes (3NF - FINAL)
MEMBER Member_ID (PK), Member_Name, Member_Email, Member_Password, Member_Phone, Member_Address, Member_Gender, Registration_Date
PUBLISHER Publisher_ID (PK), Publisher_Name, Contact_Person, Publisher_Email, Publisher_Phone, Publisher_Address
GENRE Genre_ID (PK), Genre_Name
BOOK Book_ID (PK), Book_Title, ISBN, Price, Quantity, Description, Date_Added, Publisher_ID (FK)
BOOK_GENRE Book_ID (PK, FK), Genre_ID (PK, FK)
ORDER ✓ Order_ID (PK), Order_Date, Order_Status, Member_ID (FK)
ORDER_ITEM ✓ Order_ID (PK, FK), Book_ID (PK, FK), Quantity, Unit_Price
PAYMENT Payment_ID (PK), Order_ID (FK, UK), Payment_Method, Payment_Date, Amount, Payment_Status
DELIVERY Delivery_ID (PK), Order_ID (FK, UK), Delivery_Address, Delivery_Date, Delivery_Status
FEEDBACK Feedback_ID (PK), Member_ID (FK), Book_ID (FK), Rating, Comment, Feedback_Date, UNIQUE(Member_ID, Book_ID)
STORE_ORDER Store_Order_ID (PK), Publisher_ID (FK), Order_Date, Status
STORE_ORDER_ITEM Store_Order_ID (PK, FK), Book_ID (PK, FK), Quantity_Ordered, Quantity_Received
✅ FINAL ANALYSIS - All 12 tables are now in 3NF:
  • No partial dependencies - all non-key attributes depend on the entire primary key
  • No transitive dependencies - no non-key attribute depends on another non-key attribute
  • Subtotal removed from ORDER_ITEM (calculated as Quantity × Unit_Price)
  • Order_Total removed from ORDER (calculated as SUM of all ORDER_ITEM subtotals)
  • STORE_ORDER and STORE_ORDER_ITEM added to track orders from publishers
  • UNIQUE constraint on (Member_ID, Book_ID) in FEEDBACK ensures one feedback per member per book

Part 1(c): Entity Relationship Diagram

Visual representation of the database structure

3.1 Entities and Attributes

Based on the normalization process, the following 12 entities and their attributes have been identified for APU E-Bookstore:

Entity Attributes Primary Key
MEMBER Member_ID, Member_Name, Member_Email, Member_Password, Member_Phone, Member_Address, Member_Gender, Registration_Date Member_ID
PUBLISHER Publisher_ID, Publisher_Name, Contact_Person, Publisher_Email, Publisher_Phone, Publisher_Address Publisher_ID
GENRE Genre_ID, Genre_Name Genre_ID
BOOK Book_ID, Book_Title, ISBN, Price, Quantity, Description, Date_Added, Publisher_ID (FK) Book_ID
BOOK_GENRE Book_ID (FK), Genre_ID (FK) Book_ID, Genre_ID
ORDER Order_ID, Order_Date, Order_Status, Member_ID (FK) Order_ID
ORDER_ITEM Order_ID (FK), Book_ID (FK), Quantity, Unit_Price Order_ID, Book_ID
PAYMENT Payment_ID, Order_ID (FK), Payment_Method, Payment_Date, Amount, Payment_Status Payment_ID
DELIVERY Delivery_ID, Order_ID (FK), Delivery_Address, Delivery_Date, Delivery_Status Delivery_ID
FEEDBACK Feedback_ID, Member_ID (FK), Book_ID (FK), Rating, Comment, Feedback_Date Feedback_ID
STORE_ORDER Store_Order_ID, Publisher_ID (FK), Order_Date, Status Store_Order_ID
STORE_ORDER_ITEM Store_Order_ID (FK), Book_ID (FK), Quantity_Ordered, Quantity_Received Store_Order_ID, Book_ID

Relationship Summary

Relationship Cardinality Description
MEMBER → ORDER 1:M One member can place many orders; each order belongs to one member
MEMBER → FEEDBACK 1:M One member can provide many feedbacks; each feedback is from one member
ORDER → ORDER_ITEM 1:M One order contains many items; each item belongs to one order
ORDER → PAYMENT 1:1 One order has exactly one payment; each payment is for one order
ORDER → DELIVERY 1:1 One order has exactly one delivery; each delivery is for one order
PUBLISHER → BOOK 1:M One publisher publishes many books; each book has one publisher
BOOK → ORDER_ITEM 1:M One book can be in many order items; each item is for one book
BOOK → FEEDBACK 1:M One book can receive many feedbacks; each feedback is for one book
BOOK ↔ GENRE M:N Many-to-many via BOOK_GENRE junction table
PUBLISHER → STORE_ORDER 1:M One publisher receives many store orders
STORE_ORDER → STORE_ORDER_ITEM 1:M One store order contains many items
BOOK → STORE_ORDER_ITEM 1:M One book can be in many store order items

3.2 Entity Relationship Diagram (Crow's Foot Notation)

The following ERD illustrates the complete database design for APU E-Bookstore using Crow's Foot notation. The diagram shows all 12 entities, their attributes, primary keys (PK), foreign keys (FK), and the relationships between entities with appropriate cardinality.

Interactive Diagram: Use Scroll Wheel to Zoom, Drag to Pan.

APU E-Bookstore - Entity Relationship Diagram
erDiagram MEMBER { int Member_ID PK string Member_Name string Member_Email string Member_Password string Member_Phone string Member_Address string Member_Gender date Registration_Date } PUBLISHER { int Publisher_ID PK string Publisher_Name string Contact_Person string Publisher_Email string Publisher_Phone string Publisher_Address } GENRE { int Genre_ID PK string Genre_Name } BOOK { int Book_ID PK string Book_Title string ISBN decimal Price int Quantity text Description date Date_Added int Publisher_ID FK } BOOK_GENRE { int Book_ID PK int Genre_ID PK } CUST_ORDER { int Order_ID PK date Order_Date string Order_Status int Member_ID FK } ORDER_ITEM { int Order_ID PK int Book_ID PK int Quantity decimal Unit_Price } PAYMENT { int Payment_ID PK int Order_ID FK string Payment_Method date Payment_Date decimal Amount string Payment_Status } DELIVERY { int Delivery_ID PK int Order_ID FK string Delivery_Address date Delivery_Date string Delivery_Status } FEEDBACK { int Feedback_ID PK int Member_ID FK int Book_ID FK int Rating text Comment date Feedback_Date } STORE_ORDER { int Store_Order_ID PK int Publisher_ID FK date Order_Date string Status } STORE_ORDER_ITEM { int Store_Order_ID PK int Book_ID PK int Quantity_Ordered int Quantity_Received } MEMBER ||--o{ CUST_ORDER : "places" MEMBER ||--o{ FEEDBACK : "provides" CUST_ORDER ||--|{ ORDER_ITEM : "contains" CUST_ORDER ||--|| PAYMENT : "has" CUST_ORDER ||--|| DELIVERY : "has" BOOK ||--o{ ORDER_ITEM : "included-in" BOOK ||--o{ FEEDBACK : "receives" BOOK ||--o{ BOOK_GENRE : "belongs-to" BOOK ||--o{ STORE_ORDER_ITEM : "ordered-in" PUBLISHER ||--o{ BOOK : "publishes" PUBLISHER ||--o{ STORE_ORDER : "receives" GENRE ||--o{ BOOK_GENRE : "categorizes" STORE_ORDER ||--|{ STORE_ORDER_ITEM : "contains"

Crow's Foot Notation Legend

||──|| One and only one (1:1 mandatory)
||──o| Zero or one (1:1 optional)
||──|{ One or many (1:M mandatory)
||──o{ Zero or many (1:M optional)
PK Primary Key - uniquely identifies each record
FK Foreign Key - references another table's PK
UK Unique Key - must be unique in the table
PK-FK Composite PK that is also a Foreign Key
📊 ERD Summary:
  • Core Entities (4): MEMBER, BOOK, PUBLISHER, GENRE
  • Transaction Entities (4): CUST_ORDER, ORDER_ITEM, PAYMENT, DELIVERY
  • Junction Tables (2): BOOK_GENRE (M:N between Book and Genre), STORE_ORDER_ITEM
  • Feedback Entity (1): FEEDBACK (with unique constraint on Member_ID + Book_ID)
  • Store Operations (2): STORE_ORDER, STORE_ORDER_ITEM (for orders to publishers)

References

Academic sources used in this report

Connolly, T. and Begg, C. (2015) Database Systems: A Practical Approach to Design, Implementation, and Management. 6th edn. Boston: Pearson.

Coronel, C. and Morris, S. (2018) Database Systems: Design, Implementation, & Management. 13th edn. Boston: Cengage Learning.

Date, C.J. (2019) An Introduction to Database Systems. 8th edn. Boston: Pearson.

Elmasri, R. and Navathe, S.B. (2016) Fundamentals of Database Systems. 7th edn. Boston: Pearson.

Silberschatz, A., Korth, H.F. and Sudarshan, S. (2019) Database System Concepts. 7th edn. New York: McGraw-Hill Education.