DBMS Practical Slip2

1. Introduction

Database design is a core topic in DBMS (Database Management System) and is frequently asked in university theory and practical exams. Questions based on entity relationships, normalization, and constraints help students understand how real-world data is stored efficiently.

In this blog post, we will solve Slip No. 2 – Question A, where we are asked to design a Relational Database (RDB) in Third Normal Form (3NF) using given entities and constraints.
The explanation is written in simple English, making it ideal for students and beginners.

---

2. Problem Statement

Slip No. 2 – Consider the following entities and their relationships. Create a

 RDB in 3 NF with appropriate data types and Constraints. [15 Marks]

Sales_order (ordNo, ordDate)

Client (clientNo, ClientName, addr)

The relationship between Client & Sales_order is one-to-many. 

Constraints: – Primary Key, ordDate should not be NULL

Consider the following entities and their relationships:

Entities:

Sales_order

• ordNo
• ordDate

Client

• clientNo
• clientName
• addr

Relationship:

• The relationship between Client and Sales_order is One-to-Many
  • One client can place many sales orders
  • Each sales order belongs to one client

Constraints:

• Primary Key must be defined
• ordDate should NOT be NULL

3. Source Code

Step 1: Database Design Logic

• Each entity is converted into a separate table
• Primary keys uniquely identify records
• Foreign key is used to represent one-to-many relationship
• No partial or transitive dependency exists
• Tables follow Third Normal Form (3NF) rules

---

SQL Source Code

-- Create Client Table

CREATE TABLE Client (clientNo INT PRIMARY KEY, clientName VARCHAR(50),addr VARCHAR(100));

-- Create Sales Order Table

CREATE TABLE Sales_order (

    ordNo INT PRIMARY KEY,

    ordDate DATE NOT NULL,

    clientNo INT,

    CONSTRAINT fk_client

        FOREIGN KEY (clientNo)

        REFERENCES Client(clientNo)

);

Explanation:

• clientNo is the Primary Key in Client table
• ordNo is the Primary Key in Sales_order table
• ordDate NOT NULL ensures order date is mandatory
• clientNo in Sales_order acts as a Foreign Key
• One-to-many relationship is properly implemented

4. FAQ Section

Q1. What does one-to-many relationship mean in DBMS?

Answer:
One-to-many means one record in a table can be associated with multiple records in another table.
Example: One client can place many sales orders.

---

Q2. Why is clientNo a foreign key in Sales_order?

Answer:
It connects each sales order to a specific client and maintains the one-to-many relationship between Client and Sales_order.

Q3. Why is ordDate marked as NOT NULL?

Answer:
Every sales order must have a date. The NOT NULL constraint ensures that no order is stored without an order date.

Q4. Is this database design in Third Normal Form?

Answer:
Yes.

• Each table represents a single entity
• No redundant data
• All non-key attributes depend only on the primary key

Q5. Is this question important for DBMS exams?

Answer:
Yes. Similar questions are commonly asked in DBMS theory papers, practical exams, and viva voce.