Create Entity Relationship Diagram (ERD) and Normalization
An Entity Relationship (ER) Diagram is a type of flowchart, which shows how entities (people, objects) relate to each other within a system. Flowchart is a type of diagram that represents the process. ERD uses a defined set of symbols such as rectangles, diamonds, ovals and lines to depict interconnectedness of entities, relationships, and their attributes.
ER Diagrams are composed of entities, relationships, and attributes. And depict cardinality, which defines relationships in terms of numbers.
Steps to draw an ER Diagram:
1.Define the purpose and scope of what is analyzed or modeled. For example, to model Art Gallery database, which will keep track of the Artists’ Artworks.
2.Identify the entities that are involved, labeling as nouns. Entity is a thing — such as a person, object, concept or event — that have data stored about it.
Entity can be weak and strong. The weak entity has no primary keys, has no meaning without its parent entity (strong entity). Strong entities, for example, are ArtWorks and Exhibition, and weak entity will be Art_Exhibition event in this case, because the event will not happen if there are no artworks in the exhibition. Tables are another way representing entities.
3. Determine how the entities are all related, using verbs. Relationships — how entities are associated with each other. For example, entities Artist and Artwork, and the relationship depicted is the act of creating, connects the entities in that way. Relationship’s symbol is diamond.
4. Add attributes. Attributes — characteristics of an entity, and has an oval symbol. For example, entity Artist has FirstName, LastName, BirthDate, etc. attributes. Attributes represent the columns in the table entity.
5. Show cardinality. Cardinality — defines the numerical attributes of the relationship between two entities. The three main cardinal relationships are one-to-one, one-to-many, and many-many. For example, Artist creates 0..* ArtWorks but ArtWork created by 1..1 Artist.
Sentences of relationships to the Art Gallery Diagram with Cardinality:
One artist MAY BE creating ONE or MORE pieces of artwork.
One piece of artwork MUST BE created by ONE and ONLY ONE artist.
One piece of artwork MUST BE present at ONE and ONLY ONE art exhibition.
One art exhibition MAY BE presenting ONE or MORE pieces of artwork.
One exhibition MUST BE putting on ONE and ONLY ONE art exhibition.
One art exhibition MAY BE put on by ONE or MORE exhibitions.
One piece of artwork MAY BE rented via ONE or MORE rentals.
One rental MUST BE agreed upon to rent ONE and ONLY ONE piece of artwork.
One piece of artwork MAY BE ordered via ONE or MORE orders to buy.
One order to buy MUST BE agreed upon to buy ONE and ONLY ONE piece of artwork.
One rental MUST BE a rental agreement for ONE and ONLY ONE customer.
One customer MAY BE renting ONE or MORE rental.
One customer MAY BE placing ONE or MORE order to buy.
One order to buy MUST BE placed by ONE and ONLY ONE customer.
There are three levels (stages) of details of ER models: Conceptual, Logical, and Physical. Conceptual ER model is highly abstract. Conceptual ER model contains only entities. Logical ER model has attributes for each entity, key attributes, and non-key attributes, primary key-foreign key relationships. Physical ER diagram is the most granular level of ER diagram. Physical ER show all table structures (entities referred to as tables), such as column name, primary keys, foreign keys, relationships between tables, cardinalities, indexes, constrains, data types.
Relational Model:
Keys serve integral part in the normalization process. There are two types of keys: primary key (key) and foreign key (fk). The primary key is a unique identifier of each value in the table, with that said it cannot be changed and cannot be NULL. The good example of the primary key can be U.S. Social Security number. In many cases, these primary keys need to be created. The created primary keys are artificial keys. The foreign key is the key in the table B, which is primary key in the different table A. The foreign keys let us to link together two tables. The keys are defined in the Relational Model. Relational Model represents the collection of relations.
In Art Gallery relational model, the table Artist has primary key Artist_ID, which was created (artificial key):
Artist(Artist_ID (key), FirstName, LastName, BirthDate, BirthPlace, City, State, Zip, Phone_Number, Style)
The table Customer has artificially created primary key Customer_ID:
Customer(Customer_ID (key), FirstName, LastName, PhoneNumber, Address, City, State, Zip, Country)
The table Exhibition has artificially created primary key Exhibition_ID:
Exhibition( Exhibition_ID (key), Name, StartDate, EndDate, Country, City, State, Zip )
The table Order_To_Buy has created primary key OrderID and foreign key Art_ID (table B) which is primary key in table ArtWorks (table A), linking Order_To_Buy and ArtWorks tables together. The table Order_To_Buy has foreign key Customer_ID (table B) which is primary key in table Customer (table A), linking Order_To_Buy and Customer tables together.
Order_To_Buy(OrderID (key), Order_Price, OrderDate, Art_ID (fk), Customer_ID (fk))
The table Rental has created primary key RentalID and foreign key Art_ID (table B) which is primary key in table ArtWorks (table A), linking Rental and ArtWorks tables together. The table Rental has foreign key Customer_ID (table B) which is primary key in table Customer (table A), linking Rental and Customer tables together.
Rental(RentalID (key), Rental_Start_Date, Rental_End_Date, Rental_Price, Art_ID (fk), Customer_ID (fk))
The table Artwork has created primary key and foreign key Artist_ID (table B) which is primary key in table Artist (table A), linking Artwork and Artist tables together.
Artwork(Art_ID (key), Year, Title, Price, Description, Type, Artist_ID (fk), RentalID (fk)?, OrderID (fk)?)
The table Art_Exhibition is the weak entity. Weak entities don’t have primary keys. Art_Id and Exhibition_Id are foreign keys.
Art_Exhibition(Art_Id (fk), Exhibition_Id (fk))
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.
If a relation is in 2NF it means that the database is already in the 1NF and all non-key attributes are dependent upon all key attributes (primary key).
If a relation is in 3NF, it means that the database is in the 2NF and there are no transitive dependencies. A transitive dependency is an indirect relationship between attributes in the same table that causes a functional dependency. Boyce and Codd Normal Form is a improved version of the Third Normal form, when for each functional dependency ( x → y ), a should be a super Key. In 4NF, the relation doesn’t have Multi-Valued Dependency.
In Art Gallery Database, the first relationship is
Artist(Artist_ID (key), FirstName, LastName, BirthDate, BirthPlace, City, State, Zip, Phone_Number, Style)
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 →.
FD1: Artist_ID → FirstName, LastName, BirthDate, BirthPlace, City, State, Zip, Phone_Number, Style, Country
FD2: 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(Zip, City, State)
FD1: Zip → City, State
1NF: Meets the definition of a relation
2NF: No partial Key dependencies
3NF: No transitive dependency
Artist_Info(Artist_ID, FirstName, LastName, BirthDate, BirthPlace, Zip, PhoneNumber, Style)
FD1: 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)
Key: Customer_ID
FD1: Customer_ID → FirstName, LastName, PhoneNumber, Address, City, State, Zip, Country
FD2: Zip → City, State, Country
1NF: Meets the definition of a relation
2NF: No partial Key dependencies
3NF: this is not in 3N 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(Zip, City, State, Country)
FD1: Zip → City, State, Country
1NF: Meets the definition of a relation
2NF: No partial Key dependencies
3NF: No transitive dependency
Customer_Info(Customer_ID, FirstName, LastName, PhoneNumber, Address, Zip)
FD1: Customer_ID → FirstName, LastName, PhoneNumber, Address, Zip)
1NF: Meets the definition of a relation
2NF: No partial Key dependencies
3NF: No transitive dependency
Exhibition(Exhibition_ID (key), Name, StartDate, EndDate, Country, City, State, Zip )
Key: Exhibition_ID
FD1: Exhibition_ID → Name, StartDate, EndDate, Country, City, State, Zip
FD2: 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(Zip(key), Country, City, State)
FD1: Zip → Country, City, State
1NF: Meets the definition of a relation
2NF: No partial Key dependencies
3NF: No transitive dependency
Exhibition_Info(Exhibition_ID(key), Name, StartDate, EndDate, Zip(fk))
FD1: 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(Exhibition_ID (fk), Art_ID (fk))
Order_To_Buy(OrderID (key), Order_Price, OrderDate, Art_ID (fk), Customer_ID (fk))
Key: OrderID
FD1: 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(RentalID (key), Rental_Start_Date, Rental_End_Date, Rental_Price, Art_ID (fk), Customer_ID (fk))
Key: RentalID
FD1: 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 (key), Year, Title, Price, Description, Type, Artist_ID (fk))
Key: Art_ID
FD1: 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_Buy(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))
