A

PROJECT REPORT ON
“E-R DIAGRAM AND NORMALIZATION ON ART
GALLERY ”

SUBMITTED UNDER DBMS TO THE M.S.B.T.E.,

MUMBAI IN PARTIAL FULFILLMENT OF THE
REQUIREMENTS FOR THE AWARD OF
DIPLOMA IN COMPUTER ENGINEERING
BY

MR.BADJATE VANSH NILESH (11) MR.BHATKUDAV BHAVESH VIJAY (17)

MR.CHUDIWAL SHREAYSH RAJENDRA (23)

Under the guidance of

Mr.M.V.Khasne.

DEPARTMENT OF COMPUTER ENGINEERING SANJIVANI RURAL

EDUCATION SOCIETY’S SANJIVANI K.B.P. POLYTECHNIC,
KOPARGAON-423603
2022-2023

Mr.M.V.Khasne Mr.G.N.Jorvekar Mr. A.R. Mirikar

(Subject Teacher) (H.O.D) (Principle)
 INDEX

Sr. Title Page

No. No.
1. INTRODUCTION TO E-R DIAGRAM 3.
2. INTRODUCTION TO NORMALIZATION 8.
3. CONCLUTION 14.
4. REFERANCE 15.
INTORDUCTION TO E-R DIAGRAM:\
ER Model is used to model the logical view of the system from a data perspective which consists of these
components:
• Entity
• Attributes
• Relationship
• Weak entity
• Strong entity
• Simple attribute
• Key attribute
• Composite attribute
• Derived attribute
• Multivalued attribute
•
Entity, Entity Type, Entity Set –
Entity: An Entity may be an object with a physical existence – a particular person, car, house, or employee
– or it may be an object with a conceptual existence – a company, a job, or a university course.
Entity Type: An Entity is an object of Entity Type and set of all entities is called as entity set. e.g.; E1 is an
entity having Entity Type Student and set of all students is called Entity Set. In ER diagram, Entity Type is
represented as:
Attribute(s):

Attributes are the properties that define the entity type. For example, Roll_No, Name, DOB, Age, Address,
Mobile_No are the attributes that define entity type Student. In ER diagram, the attribute is represented by
an oval
Key Attribute –
The attribute which uniquely identifies each entity in the entity set is called key attribute.For example,
Roll_No will be unique for each student. In ER diagram, key attribute is represented by an oval with
underlying lines.

Composite Attribute –
An attribute composed of many other attribute is called as composite attribute. For example, Address
attribute of student Entity type consists of Street, City, State, and Country. In ER diagram, composite
attribute is represented by an oval comprising of ovals.

Multivalued Attribute –
An attribute consisting more than one value for a given entity. For example, Phone_No (can be more than
one for a given student). In ER diagram, a multivalued attribute is represented by a double oval.

Derived Attribute –
An attribute that can be derived from other attributes of the entity type is known as a derived attribute. E.g.;
Age (can be derived from DOB). In ER diagram, the derived attribute is represented by a dashed oval.
The complete entity type Student with its attributes can be represented as:

Cardinality:
The number of times an entity of an entity set participates in a relationship set is known as cardinality.
Cardinality can be of different types:
One to one – When each entity in each entity set can take part only once in the relationship, the cardinality
is one to one. Let us assume that a male can marry to one female and a female can marry to one male. So the
relationship will be one to one.
Using Sets, it can be represented as:

Many to one – When entities in one entity set can take part only once in the relationship set and entities in
other entity set can take part more than once in the relationship set, cardinality is many to one. Let us assume
that a student can take only one course but one course can be taken by many students. So the cardinality will
be n to 1. It means that for one course there can be n students but for one student, there will be only one
course.
Using Sets, it can be represented as:

Many to many – When entities in all entity sets can take part more than once in the relationship cardinality
is many to many. Let us assume that a student can take more than one course and one course can be taken by
many students. So the relationship will be many to many.

Using sets, it can be represented as:

Fig. E-R Diagram On Art Gallery
INTORDUCTION TO NORMALIZATION:
Normalization is the process of the eliminating the data repetitions, undesirable characteristics like Insertion,
Update and Deletion anomalies, and making sure the data logically stored that can disrupt the integrity of the
database. Problems, which can appear without normalization are extra memory space and hard to handle and
update the database without data loss. During normalization the database will be changed according to the
rules, which called normal forms. In this example, the first three normal forms (1NF, 2NF, 3NF) will be
considered (but there are more than three normal forms: BCNF and 4NF).
All relations are in the first Normal Form (1NF) if each column contains one value, tables does not
have repeating groups of related data, the order in which data stored does not matter, values have to belong
to one domain. For example, if the table has whole address (number of the building, street, apartment, zip
code, state, city) in one line it will violate the first normal form, because each column (each attribute) has to
contain only one value. Another example of violating the first normal form would be having similar names
of the columns, such as Art_name_1, Art_name_2. Each column has to have unique name. After 1NF, there
is possibility of increased redundancy but each row will be unique.

In Our Microproject We are going to Normalize the Art Gallery Database .

The First Relationship Is:

Artist Table:
ArtistID Fname Lname Birthdate Birthplace City State Zip Phone
1122 Ajay Rao 23-01-98 Nashik Nashik MH 2201 1234
1133 Mukesh Varma 31-08-78 Pune Pune MH 2103 9876

The primary key is Artist_ID. Functional Dependency (FD) is the relation of one attribute to another
attribute in a database management system (DBMS) system and denoted by the arrow →.
FUNCTIONAL DEPENDENCY 1:
Artist_ID → FirstName, LastName, BirthDate, BirthPlace, City, State, Zip, Phone_Number, Style, Country.
FUNCTIONAL DEPENDANCY 2:
Zip → City, State, Country.
1NF: Meets the definition of a relation
2NF: No partial Key dependencies
3NF: this is not in 3N due to the existence of the transitive dependency.
Artist_ID → FirstName, LastName, BirthDate, BirthPlace,Zip, Phone_Number, Style and Zip → City,
State, Country.

Solution:
Split the relation into two new relations named Artist_Address, Artist_Info:
ARTIST_ADDRESS:

City State Zip

Nasik MH 2201
Pune MH 2103
ARTIST_INFORMARTION:

ArtistId Fname Lname Birthdate Zip

1122 Ajay Rao 23-01-98 2201
1133 Mukesh Varma 31-08-78 2103

FUNCTIONAL DEPENDANCY 1 :
Artist_ID → FirstName, LastName, BirthDate, BirthPlace, Zip, PhoneNumber, Style.
1NF: Meets the definition of a relation
2NF: No partial Key dependencies
3NF: No transitive dependency.
Customer(Customer_ID (key), FirstName, LastName, PhoneNumber, Address, City, State, Zip, Country)
CUSTOMER TABLE:

C_id Fname Lname Address City State Zip Country

1111 Ashok Jain Goa Goa MH 2103 INDIA
2222 Chaitanya Ahuja Mumbai Mumbai Mh 2155 INDIA

Key: Customer_ID
Functional DEPENDANCY 1:
Customer_ID → FirstName, LastName, PhoneNumber, Address, City, State, Zip, Country
Functional DEPENDANCY 2 :
Zip → City, State, Country
1NF: Meets the definition of a relation
2NF: No partial Key dependencies
3NF: this is not in 3NF due to existence of transitive dependency
Customer_ID → FirstName, LastName, PhoneNumber, Address, Zip, Country and Zip → City, State,
Country

Solution:
Split User relation into two new relations named Customer_Address, Customer_Info
CUSTOMER ADDRESS:

City State Zip Country

Goa MH 2103 INDIA
Mumbai Mh 2155 INDIA
Functional DEPENDANCY 1:
Zip → City, State, Country
1NF: Meets the definition of a relation
2NF: No partial Key dependencies
3NF: No transitive dependency
CUSTOMER INFORMATION:

C_id Fname Lname Address Zip

1111 Ashok Jain Goa 2103
2222 Chaitanya Ahuja Mumbai 2155

Functional dependency 1:
Customer_ID → FirstName, LastName, PhoneNumber, Address, Zip)
1NF: Meets the definition of a relation
2NF: No partial Key dependencies
3NF: No transitive dependency
EXHIBITION:

Ex_id Gall_N S_Date E_Date Country City State Zip

7771 Nature 23-1-22 30-1-22 INDIA Mumbai MH 2210
8882 Mountain 23-2-22 30-2-22 AMERICA London Satel 2120

Functional DEPENDANCY 1:
Exhibition_ID → Name, StartDate, EndDate, Country, City, State, Zip
Functional DEPENDANCY 2:
Zip → Country, City, State
1NF: Meets the definition of a relation
2NF: No partial Key dependencies
3NF: this is not in 3N due to existence of transitive dependency
Exhibition_ID → Name, Start_Date, End_Date, Zip and Zip → Country, City, State

Solution:
Split User relation into two new relations named Exhibition_Address, Exhibition_Info
EXHIBITION_ADDRESS:

Country City State Zip

INDIA Mumbai MH 2210
AMERICA London Satel 2120

FUNCTIONAL DEPENDANCY 1:
Zip → Country, City, State
1NF: Meets the definition of a relation
2NF: No partial Key dependencies
3NF: No transitive dependency
EXIBITION INFORMATION:

Ex_id Gall_N S_Date E_Date ZIP

7771 Nature 23-1-22 30-1-22 2210
8882 Mountain 23-2-22 30-2-22 2120

Functional DEPENDANCY 1 :
Exhibition_ID → Name, Start_Date, End_Date, Zip
1NF: Meets the definition of a relation
2NF: No partial Key dependencies
3NF: No transitive dependency

ART EXHIBITION

EXIBITION_id Art_id
7771 1122
8882 1133

ORDER_TO_PURCHASE:

Order_id Order_Pri Order_Date Art_id C_id

0001 12000 23-6-22 1122 1111
0005 1500 25-9-22 1133 2222
FUNCTIONAL DEPENDANCY 1:

OrderID → Order_Price, OrderDate, Art_ID, Customer_ID

1NF: Meets the definition of a relation

2NF: No partial Key dependencies

3NF: No transitive dependency

RENTAL:

R_ID R_START_D R_END_D R_PRICE ART_ID C_ID

1221 1-1-21 1-1-22 15000 1122 1111
1331 2-2-21 2-2-22 1700 1133 2222

FUNCTIONAL DEPENDANCY 1:
RentalID → Rental_Start_Date, Rental_End_Date, Rental_Price, Art_ID, Customer_ID
1NF: Meets the definition of a relation
2NF: No partial Key dependencies
3NF: No transitive dependency
ARTWORK:-

ART_ID Year Title Price Type Country

1122 2021 National 17000 Online India

1331 2022 International 20000 Offline AMERICA

FUNCTIONAL DEPENDANCY 1:
Art_ID → Year, Title, Price, Description, Type, Artist_ID
1NF: Meets the definition of a relation
2NF: No partial Key dependencies
3NF: No transitive dependency

Final relations normalized to Third Normal Form:

Artist_Address(Zip(key), City, State, Country)
Artist_Info(Artist_ID(key), First_Name, Last_Name, Birth_Date, Birth_Place, Zip(fk), Address,
Phone_Number, Style)
Customer_Address(Zip(key), City, State, Country)
Customer_Info(Customer_ID(key), First_Name, Last_Name, Phone_Number, Address, Zip(fk))
Exhibition_Address(Zip(key), Country, City, State)
Exhibition_Info(Exhibition_ID(key), Name, Start_Date, End_Date, Zip(fk))
Art_Exhibition( Exhibition_ID (fk)(key), Art_ID (fk)(key))
Order_To_Purchase(Order_ID (key), Order_Price, Order_Date, Art_ID (fk), Customer_ID (fk))
Rental(Rental_ID (key), Rental_Start_Date, Rental_End_Date, Rental_Price, Art_ID (fk), Customer_ID
(fk))
Artwork(Art_ID (key), Year, Title, Price, Description, Type, Artist_ID (fk), Order_ID (fk), Rental_ID (fk))
 CONCLUSION
ER Diagram in DBMS is widely used to describe the conceptual design of databases. It helps both users and
database developers to preview the structure of the database before implementing the database.
Through the process of database normalization we bring our schema’s tables into conformance with
progressive normal forms. As a result our tables each represent a single entity (a ART, an Artist, a
Exhibition, etc) and we benefit from decreased redundancy, fewer anomalies and improved efficiency.
To protect the data and to make the database more flexible by eliminating redundancy and inconsistent
dependency.
We Normalized the Art Gallery ER Diagram And Removed Redundancy and Inconsistency.
 REFERENCE
Website Used
WWW ORACLE.COM
Preferred Books
NIRALI PRAKASHAN