Question

Design normalised tables in 3NF for the ER diagram drawn in part (c), with the required integrity constraints

13 Sep 2024
Answer :
Word Count : 982

To design normalized tables in 3NF (Third Normal Form) based on an ER diagram, the steps involved include identifying the entities, relationships, and attributes, and then applying the normalization rules progressively from 1NF (First Normal Form) through 2NF (Second Normal Form) and ultimately to 3NF. Below, we will discuss how to achieve the normalized form based on an ER diagram without seeing the actual diagram. For this example, let’s assume a simple ER diagram with entities like `Employee`, `Department`, and `Project`, with attributes associated with each and relationships between them.

### Step 1: Identify Entities, Attributes, and Relationships
Assuming the following entities and relationships in the ER diagram:

1. Employee:
   - Attributes: `EmpID` (Primary Key), `EmpName`, `EmpSalary`, `DeptID` (Foreign Key referencing Department)
   
2. Department:
   - Attributes: `DeptID` (Primary Key), `DeptName`, `DeptLocation`
   
3. Project:
   - Attributes: `ProjID` (Primary Key), `ProjName`, `EmpID` (Foreign Key referencing Employee)
   
4. Employee_Project (Many-to-Many relationship between Employee and Project):
   - Attributes: `EmpID` (Foreign Key), `ProjID` (Foreign Key), `Role`, `HoursWorked`
   
### Step 2: Convert the ER diagram into Unnormalized _________ ___ ____ _________ _______ ____ __________ _______.
_________ ______ ___ __________ ____ ___ ________ ____ __________ _____.
_______ _____ __________ ___ ________ ______ __________.
______ ______ ___ ____ ____.
______ __________ _______ ______ ________ ______ _______ ____.
___ _________ ______ ____ ______ _____ _____ ____.
__________ ___ _______ ___ ___ _________ ________ _______ _______.
_______ __________ _________ _______ _____ ______ ________.
__________ ________ __________ __________ _______ ______.
____ _____ ___ _____ ______ _________ ________ ____ ______ _________.
__________ ________ _______ __________ ______.
__________ ____ _________ _________ ________ _________ ____ _____ _______ ____ __________ ____.
________ _______ __________ _______ ______ ____ ______ _______ ______ __________ _________ ______.
________ __________ _____ ____ ___ _____ _____.
_____ _______ ___ _____ ________ _______ _________ _______ ____.
_____ _________ ______ _________ __________ ____ ________.
____ ________ __________ _________ _______.
_________ _____ __________ _________ ___ __________ _______.
___ _________ __________ ______ _____ _________ _________ ___ ________ ______ _________.
_____ _______ ________ ______ ___ _______ _______ _______ _____ __________.
__________ ____ ____ ___ _____ __________ _________ ___.
________ ___ _________ __________ __________ _________ __________.
________ _______ ___ ___ ______ _________ ________ _____ ________ ___.
___ __________ ____ __________ ____ ______ __________ ____ _________ _________ _____ _________.
__________ _______ ________ ____ ________ _________.
______ _________ ________ __________ _____ _____ ________ ______ ________ _______ ______.
_______ ________ _____ _________ ________ ______ ____ __________ ______ __________ _________ _______.
___ ________ ___ _____ _____ __________ _______.
_____ __________ ___ _________ ______ ___.
_______ _____ ______ _____ ___ _______.
_________ ___ _______ ____ ___ ____ _____ ___ ______ ___ ___ _______.
______ ________ __________ __________ _________ __________ ___ _____ _________ ______ ___ ___.
__________ ___ _______ _____ _____ _________ ______ ________.
_____ _______ __________ _______ ____ _________ _________ ___.
_________ ____ __________ ___ __________ ____ _______ _____ _________ _____ _________ _________.
______ ________ ____ ________ ______.
___ _________ ___ ____ __________ __________.
_______ _________ ____ _____ ________ _____.
_______ _____ ___ _____ ___ ____ _________ ________.
____ _________ __________ ____ _______ ______ __________.
________ ___ ________ _________ _____ _______ ____ ___ ___.
__________ ______ ____ __________ _______ _____ _____ _____ _____ _____ ________ _______.
______ ___ ____ _________ ________ ________ _______ ___ ___ _______ ___ __________.
____ _____ ____ __________ ___ ___ ____.
__________ _____ _____ _______ _________ ______ ________ _________ ___ ____.
_______ _______ ____ __________ ___ __________ _______ __________ ____.
__________ _________ ______ __________ _____ _________ ___ ________.
_______ ______ ___ ___ ______ _____ __________ _______ _______ _________ ______ _________.
_________ _____ ______ _________ _____ ____ _____ ____.
____ ______ _________ _________ __________ _____ ________ _____ _______ ____ __________ _______.
__________ ____ ________ ________ _________.
____ _______ ________ ____ _________ ______ _________ ________ __________ _____ ____.
_______ ____ _______ ______ _______.
______ ___ _____ ____ _____ _______ __________ ____ _____ ___ ____.
_______ ___ ____ _______ _______ __________.
_______ _______ ________ _____ __________ _________.
___ ______ ____ _____ ________ _________ __________ _____ ________ _____ _________ _____.
_______ _________ _______ _________ ________ ____ __________ _____ ______.
_________ ________ ________ ______ ____ _______ ______ _______ ________ __________.
_____ ____ __________ ____ ____ ___ ______.
__________ _________ _______ ________ ____ ______ ______ __________ ______ _____ ____ ___.
_______ _______ ____ _______ ________ ______ _____ __________ __________ ______ ___.
___ _______ __________ __________ __________.
____ ________ ______ _________ ______ _________.
_________ ___ ____ ___ __________ _____.
___ _____ ______ _________ __________.
_____ ______ _________ ____ ____ ________.
____ ________ _____ _______ _______ ________ _______ ______ _________ _____ _______ __________.
________ ________ ___ ______ ________ _______ ___ _____ _____.
_________ ________ _______ _____ ____ ______ ___ _____ ______ __________.
__________ ________ ________ _____ ____.
__________ ____ _________ ______ ___ _____ _______ __________ ______ ___ __________ _______.
________ ____ ________ __________ ______ _____.
________ _____ _____ _____ _____ __________ _______.
____ _______ ________ _________ ____ ______ _______ ____ ___ _______ ____.
______ ________ _____ ____ _________ _______ _______ __________ __________ _____.
________ __________ ___ _____ ____ ____ ____.
____ ______ ____ ____ ____ __________ _____ __________ _________.
_________ _____ __________ __________ ____.
_______ ______ _____ _________ ________ _________ ____ __________ ___.
______ ______ _________ _________ __________ _____ ____ _______ _______.
________ ____ _____ ________ ____ ____ ________ ________ _________ _____ ____ ______.
________ ________ ______ _____ ___ ________ _________ ___ __________ ______ ______.
_________ _________ __________ ________ _______ _______ ____ _____ _____.
___ _________ ___ ____ __________ __________ ______ _________.
__________ ________ __________ ______ _____ _______ __________ ______ _______ _________ __________.
_______ ____ _____ _______ _______ ______ __________ ________ ______.
_____ ____ __________ __________ ____ _______ ________ _________ ________ ______ __________ ______.
_______ _________ ________ ________ _______.
________ ______ ______ _______ __________ ________ _____ ____.
__________ _________ _______ _____ ________ ___ ___ _________ ______.
___ _____ _______ _____ ____ _________.
__________.
Get Full Answer on WhatsApp

Related Questions

Consider the following relational database:

Customer (custId, custName, custAddress, custPhone)
Account (AccountNumber, custId, TypeOfAccount, Balance)
Transaction (DateTimeofTransaction, AccountNumber, DebitORCredit, Amount)

The underlined attribute(s) in the relation forms the primary key. In relation Customer, the custID is
the unique identifier of a customer. The purposes of other attributes in Customer relation are self
explanatory. You may define the domain of different attributes. The TypeOfAccount attribute can
take the value (“Saving”, “Current”, “Salary”, “Other”). Please note that at a specific time of a date
only one transaction can be performed from an account. Please also note that the Account relation
has foreign key custID and Transaction relation has foreign key AccountNumber. Write and run the
following SQL queries on the database:
(i) Create the tables with the primary and foreign key constraints.
(ii) Insert at least 5 records in the first 2 tables and 20 records in the 3rd table.
(iii) List “Saving” Account details showing the AccountNumber, custName, custPhone,
Balance of all the accounts. These records should be shown in the order of custName.
(iv) Find all the transactions made by customer whose custID is “240002”.
(v) Find the list of those customers who have more than one account.
(vi) Find the total of Debit transactions made for Account Number “A0054”
(vii) Find the total of balance of all the accounts of a customer.
(viii) Find the list of customers whose name start with an alphabet “B”
(ix) List the pair of customers who share the same phone number.
(x) Find the list of the customers, who have not made any credit transaction since 1st January,
2023.

Consider a Relation: Student (EnrolNo, StudentName, ProgrammeCode, ProgrammeName,
CourseCode, CourseName, Grade). Some of the constraints on the relation Student are:
 A student is assigned one unique Enrolment Number (EnrolNo).
 Two students may have same name.
 A student can register in one Programme only.
6
 A Programme consists of several compulsory courses.
 Grades can be “A”, “B”, “C”, or “D” and only one grade value is recorded for a student
in a course.
Perform the following task for the relation given above:
(i) What is the key to the relation?
(ii) Identify and list the functional dependencies in the relation.
(iii)Make an instance of this relation showing possible redundancies.
(iv) Decompose R into 2NF and 3NF relations.

Design normalised tables in 3NF for the ER diagram drawn in part (c), with the required integrity constraints

) Explain the Log based Recovery with the help of an example. What are Redo and Undo operations. Why do you need checkpoint? Explain the process of recovery with check points with the help of an example Explain with the help of an example, how recovery is performed when Deferred database modification scheme is used.

What are the problems that can be encountered, if the three transactions (given in Figure 1) are run concurrently? Explain with the help of different transaction schedules of these transactions

 Write short notes on the following database technologies: (i) Distributed databases (ii) Block chain databases

Explain the following terms with the help of an example of each – Assertion, Cursor, Stored Procedure, Triggers.

IGNOU Solved Assignments Assignments Ignou Projects Project Previous Year Solved Exam Papers Notes Notes Sample Question Papers Sample Question Papers
Login Cart
IGNOU NEWS
Assignment Submission Last Date Extended Till 30 June 2026 Click Here★★★IGNOU June 2026 TEE Date Sheet Released Click Here★★★
Top
📞
Call Support Instant phone assistance Design normalised tables in 3NF for the ER diagram drawn in part (c),
→
🟢
WhatsApp Chat Fast live messaging
→
Email Us Business enquiries & support
→