Now that you have learnt about the various types of constraints that are used to restrict the values in a database, the precursor to designing a database is drawing a well-defined ERD.
An Entity-Relationship Diagram, or ERD,can be thought of as a map of the database schema. We can visualise the structure of the entire schema and answer the following questions just by looking at the ERD:
- What are the tables that it contains?
- What are the columns that each table contains?
- What is/are the data types and constraint/s (if any) for each column?
- What are the relationships between the various tables?
So, as you can see, ERDs are extremely useful to get an overall idea of a database in very less time. Now, in the upcoming video, you will learn more about them from our expert.
So, as you learnt in the video, the constituents of an ERD are as follows:
- Entity Type/Entity: It is nothing but a table in the schema. For example, ‘orders’ and ‘payments’ are both entity types.
- Attribute: It is a column in an entity type. For example, ‘orderNumber’ is an attribute in the ‘orders’ entity type.
- Relationship Types: They are the lines between the tables. They define the relationships among the tables. These can be of various types based on their cardinalities, i.e., one-to-one, one-to-many, many-to-many, etc.
In the next video, let’s understand the self-referential relationship.
So, as you learnt in the video, there is another type of relationship, called self-referential relationship. Here, the table refers to itself.
Take a look at the table given below.
Team India
| Player Name | Player Role | Captain | Vice-Captain |
| K. L. Rahul | Batsman | Kohli | Sharma |
| Rohit Sharma | Batsman | Kohli | Sharma |
| Virat Kohli | Batsman | Kohli | Sharma |
| Shreyas Iyer | Batsman | Kohli | Sharma |
| Rishabh Pant | Batsman | Kohli | Sharma |
| Ravindra Jadeja | All-rounder | Kohli | Sharma |
| R. Ashwin | All-rounder | Kohli | Sharma |
| Hardik Pandya | All-rounder | Kohli | Sharma |
| Bhuvneshwar Kumar | Bowler | Kohli | Sharma |
| Mohammed Shami | Bowler | Kohli | Sharma |
| Jasprit Bumrah | Bowler | Kohli | Sharma |
The table given above has a self-referential relationship because Virat Kohli is the captain of every player, but he is a player himself. The same applies to Rohit Sharma, who is the vice-captain of the team.
Comprehension
Suppose you are given a data set of a college. This data set mainly includes the following four tables
- Student: This table contains information about the students, such as student id, name, range of marks scored in the exam, year of graduation and branch id.
| Student |
| student_id |
| student_name |
| mark_range |
| year |
| branch_id |
- Branch: This table includes the different branches present in the college, such as Electrical, Mechanical, Civil, Computer Science, Chemical, along with branch ids.
| Branch |
| branch_id |
| branch_name |
- HoD: This table contains information about the HoDs, i.e., Head of the Departments of the different branches. This information includes HoD name, duration of service as HoD, branch id of the branch in which a particular person is/was HoD and their contact information.
| HoD |
| branch_HoD_neme |
| year_of_service_as_HoD |
| branch_id |
| HoD_contact |
- Marks: This table contains the mapping of the range of marks with the grades awarded.
| Marks |
| marks_range |
| grades |
- You can consider the following entries as examples of each of the table formats above.
Student
Answer the following questions based on the information in the tables given above.
In the next segment, you will learn about the demonstration of the star schema.