304187: Database Management Lab 2021
Department of Electronics & Telecommunication
Engineering Academic Year: 2022-23
T.E. E&Tc (SEMESTER-V) TERM-I
SUBJECT: DATABASE MANAGEMENT (2019 PATTERN)
SUBJECT CODE: 304183
LAB MANUAL OF DATABASE MANAGEMENT
LAB SUBJECT CODE: 304187
EXPERIMENT ON E-R
MODEL
NAME OF STUDENT NUPUR CHANDANE
CLASS TE E&TC
ROLL NO 20ET008
DATE OF PERFORMANCE
DATE OF SUBMISSION
PREPARED BY : MISS V D NAGRALE, AP, ENTC, AISSMSCOE, PUNE-01Page 1
vdnagrale@aissmscoe.com
304187: Database Management Lab 2021
Entity Relationship (E R) Model
❖ The Entity Relationship (ER) model is one of several high-level, or
semantic, data models used in database design. The goal is to create
a simple description of the data that closely matches how users and
developers think of the data
A database can be modeled as : a collection of entities, relationship among
entities.
❖ An Entity is real-world object that exists and is distinguishable
from other objects.
❖ A relationship is an association among several (Two or
more) entities.
Entities are represented by means of their properties, called attributes.
An entity set is a set of entities of the same type that share the same
properties.
▪ Each entity set has a Key.
▪ Each Attribute has a Domain.
❖ Types of Attributes
▪ Simple attribute − Simple attributes are atomic values, which cannot
be divided further.
▪ For example, a Customer's ID number is an atomic value of
6 digits.
▪ Composite attribute − Composite attributes are made of more than
one simple attribute.
▪ For example, a customer's complete name may have first-name,
middle-initial and last-name.
▪ Single-value attribute − Single-value attributes contain single value.
▪ For example − Customer_ID, Social_Security_Number.
▪ Multi-value attribute − Multi-value attributes may contain more than
one values.
PREPARED BY : MISS V D NAGRALE, AP, ENTC, AISSMSCOE, PUNE-01Page 2
vdnagrale@aissmscoe.com
304187: Database Management Lab 2021
For example, a person can have more than one phone number,email_address,
etc.
▪ Derived attribute − Derived attributes are the attributes that do not
exist in the physical database, but their values are derived from other
attributes present in the database.
For example, age can be derived from date_of_birth.
PREPARED BY : MISS V D NAGRALE, AP, ENTC, AISSMSCOE, PUNE-01Page 3
vdnagrale@aissmscoe.com
304187: Database Management Lab 2021
PREPARED BY : MISS V D NAGRALE, AP, ENTC, AISSMSCOE, PUNE-01Page 4
vdnagrale@aissmscoe.com
304187: Database Management Lab 2021
PREPARED BY : MISS V D NAGRALE, AP, ENTC, AISSMSCOE, PUNE-01Page 5
vdnagrale@aissmscoe.com
304187: Database Management Lab 2021
PREPARED BY : MISS V D NAGRALE, AP, ENTC, AISSMSCOE, PUNE-01Page 6
vdnagrale@aissmscoe.com
304187: Database Management Lab 2021
Draw E-R diagram and convert entities and relationships to relation table for
a given scenario.
a. Two assignments shall be carried out i.e. consider two different
scenarios (eg. bank, college)
Consider following databases and draw ER diagram and convert entities and relationships to
relation table for a given scenario.
1. COLLEGE DATABASE:
STUDENT (USN, SName, Address, Phone, Gender)
SEMSEC (SSID, Sem, Sec)
CLASS (USN, SSID)
SUBJECT (Subcode, Title, Sem, Credits)
IAMARKS (USN, Subcode, SSID, Test1, Test2, Test3, FinalIA)
PREPARED BY : MISS V D NAGRALE, AP, ENTC, AISSMSCOE, PUNE-01Page 7
vdnagrale@aissmscoe.com
304187: Database Management Lab 2021
2. COMPANY DATABASE:
EMPLOYEE (SSN, Name, Address, Sex, Salary, SuperSSN, DNo)
DEPARTMENT (DNo, DName, MgrSSN, MgrStartDate)
DLOCATION (DNo,DLoc)
PROJECT (PNo, PName, PLocation, DNo)
WORKS_ON (SSN, PNo, Hours)
Use tools:
ER Diagram (ERD) Tool | Lucidchart
https://www.lucidchart.com/pages/examples/er-diagram-tool
ER Diagram Tool | Draw ER Diagram Online | Creately
https://creately.com/lp/er-diagram-tool-online
PREPARED BY : MISS V D NAGRALE, AP, ENTC, AISSMSCOE, PUNE-01Page 8
vdnagrale@aissmscoe.com
304187: Database Management Lab 2021
HTML5 Documentation
Complex databases need good documentation. Using DbSchema, you
can organize the tables in multiple layouts, add comments, and
generate HTML5 or PDF documentation. The documentation is using
an interactive diagram image, where you can read the comments as
mouse-over tooltips. The image below is such an example.
Name Price Link
DbSchema Free Trial + Paid Plan Learn More
Dbdiagram.io Free + Paid Plan Learn More
SqlDBM Free + Paid Plan Learn More
Dbdesigner.net Free + Paid Plan Learn More
Visual Paradigm 30-Days Free Trial + Paid Plan Learn More
PREPARED BY : MISS V D NAGRALE, AP, ENTC, AISSMSCOE, PUNE-01Page 9
vdnagrale@aissmscoe.com
304187: Database Management Lab 2021
PREPARED BY : MISS V D NAGRALE, AP, ENTC, AISSMSCOE, PUNE-01Page 10
vdnagrale@aissmscoe.com
304187: Database Management Lab 2021
PREPARED BY : MISS V D NAGRALE, AP, ENTC, AISSMSCOE, PUNE-01Page 11
vdnagrale@aissmscoe.com
304187: Database Management Lab 2021
PREPARED BY : MISS V D NAGRALE, AP, ENTC, AISSMSCOE, PUNE-01Page 12
vdnagrale@aissmscoe.com
304187: Database Management Lab 2021
Working with ER Diagram
Example: ER Diagram for Sailors DatabaseEntities:
1. Sailor
2. Boat
Relationship: Reserves
Primary Key Atributes:
1. SID (Sailor Entity)
2. BID (Boat Entity)
Following Tables (Relations) are considered for the lab purpose.
SAILORS (SID:INTEGER, SNAME:STRING, RATING:INTEGER, AGE:REAL)
BOATS (BID:INTEGER, BNAME:STRING, COLOR:STRING)
RESERVES (SID:INTEGER, BID:INTEGER, DAY:DATE)
PREPARED BY : MISS V D NAGRALE, AP, ENTC, AISSMSCOE, PUNE-01Page 13
vdnagrale@aissmscoe.com
304187: Database Management Lab 2021
PREPARED BY : MISS V D NAGRALE, AP, ENTC, AISSMSCOE, PUNE-01Page 14
vdnagrale@aissmscoe.com
304187: Database Management Lab 2021
PREPARED BY : MISS V D NAGRALE, AP, ENTC, AISSMSCOE, PUNE-01Page 15
vdnagrale@aissmscoe.com
304187: Database Management Lab 2021
“Roadway Travels” is in business since 1977 with several buses connecting
different places in
India. Its main office is located in Hyderabad.
The company wants to computerize its operations in the following areas:
Reservations Ticketing Cancellations
Reservations :
Reservations are directly handled by booking office. Reservations can be
made 60 days in advance in either cash or credit. In case the ticket is not
available, a wait listed ticket is issued to the customer. This ticket is
confirmed against the cancellation.
Cancellation and modification:
Cancellations are also directly handed at the booking office. Cancellation
charges will be charged.
Wait listed tickets that do not get confirmed are fully refunded
Analyze the problem and come with the entities in it. Identify what
Data has to be
persisted in the databases.
The Following are the entities:
1. Bus
2. Reservation
3. Ticket
4. Passenger
5. Cancellation
The attributes in the Entities:
Bus:( Entity)
PREPARED BY : MISS V D NAGRALE, AP, ENTC, AISSMSCOE, PUNE-01Page 16
vdnagrale@aissmscoe.com
304187: Database Management Lab 2021
PREPARED BY : MISS V D NAGRALE, AP, ENTC, AISSMSCOE, PUNE-01Page 17
vdnagrale@aissmscoe.com
304187: Database Management Lab 2021
PREPARED BY : MISS V D NAGRALE, AP, ENTC, AISSMSCOE, PUNE-01Page 18
vdnagrale@aissmscoe.com
304187: Database Management Lab 2021
PREPARED BY : MISS V D NAGRALE, AP, ENTC, AISSMSCOE, PUNE-01Page 19
vdnagrale@aissmscoe.com
