EX NO : 1
ENTITY RELATIONSHIP DIAGRAM
DATE :
AIM:
To construct an entity relationship diagram for the given scenario.
Question 1:
Suppose that you are designing a schema to record information about reality shows on TV.
Yourdatabase needs to record the following information:
• For each reality show, its name, genre, basic_info and participants name. Any reality show
has atleast two or more participants.
• For each producer, the company name, company country. A show is produced by exactly one
producer. And one producer produces exactly one show.
• For each television, its name, start year, head office. A television may broadcasts multiple
shows.
• Each show is broadcasted by exactly onetelevision.
• For each user, his/her username, password, and age. A user may rate multipleshows, and a
show may be rated by multiple users. Each rating has a score of 0to 10.
Draw an entity relationship diagram for this database.
• Identify the Entities
• List the attribute and its types.
• Identify the key attribute.
• Identify the cardinalities.
• Identify the relationships present in the ER model.
ER DIAGRAM:
717823D106-AKHILESH.R 23CSR302-DATABASE MANAGEMENT SYSTEM
Question 2:
Draw an ER diagram to capture the requirement as stated below:
In a hospital there are different Departments2. Patients are treated in these Departments2 by the
doctor assigned to patient.Usually each patient is treated by a single doctor,but in rare case they will
have two orthree.Healthcare assistant will also attend patients;every department has many healthcare
assistant.
Identify the Entities:
• List the attribute and its types.
• Identify the key attribute.
• Identify the cardinalities.
• Identify the relationships present in the ER model.
ER DIAGRAM:
717823D106-AKHILESH.R 23CSR302-DATABASE MANAGEMENT SYSTEM
Question 3:
The flight database stores details about an airline’s fleet, flights, and seat bookings. Again, it’s a
hugely simplified version of what a real airline would use, but the principles are the same. Consider
the following requirements list:
• The airline has one or more airplanes.
• An airplane has a model number, a unique registration number, and the capacity to take one
or morepassengers.
• An airplane flight has a unique flight number, a departure airport, a destination airport, a
departure dateand time, and an arrival date and time.
• Each flight is carried out by a single airplane.
• A passenger has given names, a surname, and a unique email address.
• A passenger can book a seat on a flight.
ER DIAGRAM:
717823D106-AKHILESH.R 23CSR302-DATABASE MANAGEMENT SYSTEM
Question 4:
A bank has many branches, and a large number of customers. A customer can open many
different kinds of accounts with the bank. The bank keeps track of the customer with his SSN, name,
address, and phone number. Age is a factor to check whether he is a major. There are different types
of loans, each identified by a loan number. Customer can take out more than one type of loan, and all
branches can give loans. Loans have a duration and interest rate. The account holder can enquire
about the balance in his account. Draw an ER Diagram for the bank. Make suitable assumptions and
use them in showing maximum and minimum cardinality ratios.
• Identify the Entities
• List the attribute and its types.
• Identify the key attribute.
• Identify the cardinalities.
• Identify the relationships present in the ER model.
ER DIAGRAM:
Result:
Thus, the ER Diagram for the given Scenario was successfully constructed.
717823D106-AKHILESH.R 23CSR302-DATABASE MANAGEMENT SYSTEM
