week - 1 Date 04/04/22
EXPERIMENT- 1
CONCEPT DESIGN WITH E-R MODEL
AIM: To Relate the entities appropriately. Apply cardinalities for each relationship. Identify
strong and weak entities. Indicate the type of relationships (total/partial). Incorporate
generalization, aggregation and specialization etc wherever required.
E-R Model
1. Draw ER- diagrams for college management system.
Entities Attributes
Student Student_id, Name( First_Name, Last_Name ), DOB, Age, Phone_no,Address
(city,state,pincode)
Faculty Faculty_id, Name(FirstName,LastName), Department, Phone_no, salary
Course Course_id, Course_Name, Department.
Department Department_Id, Department_Name.
Subjects Subject_Id, Subject_Name.
Exam Exam_code, Room_No, Time, Date.
Hostel Hostel_Id,Hostel_Name,No_of_seats.
Relationship
Entity Relation Entity
Student Living in Hostel
Student Enrolls Course
Faculty Teaches Student
Faculty Takes Subject
Faculty Belongs Department
Department Handles Course
Department Conducts Exam
Student (Entity)
Last_Name
First_Name
State
Student id City
Name
Pincode
Address
Student
Phone no Age
DOB
Faculty : (Entity)
Last_Name
First_Name
Faculty id
Name
Department
Faculty
Phone no
Salary
Course : (Entity)
Course_Nam
Course_id
e
Department
Course
Department : (Entity)
Department_id
Department_Name
Department
Subjects : (Entity)
Subject_id
Subject_Name
Subjects
Exams : (Entity)
Exam_code
Room_no
Time
Date
Exams
Hostel : (Entity)
No_of_seats
Hostel_name
Hostel_id
Hostel
CONCEPT DESIGN WITH E-R MODEL
2. Draw ER- diagrams for Bus Ticket reservation system.
Entities Attributes
Bus BusNo, Source, Destination, Coachtype
Ticket TicketNo, DOJ, Address, ContactNo, BusNo, SeatNo, Source, Destination
Passenger PassportID,TicketNo,Name,ContactNo,Age,Sex,Address
Reaervation PNRNo,DOJ,No_of_seats,Address,ContactNo,BusNo,SeatNo
Cancellation PNRNo,DOJ,SeatNo,ContactNo,Status
Relationship
Entity Relation Entity
Bus Has a Reservation
Bus Has a Cancellation
Passenger Asks for Reservation
Bus User ask for Ticket
Bus
BusNo
Source
Destination
CoachType
SCHEMA
Bus: Bus(BusNo :String ,Source : String, Destination: String, Coach Type: String)
Ticket
TicketNo
DOJ
Address
ContactNo
BusNo
SeatNo
Source
Destination
SCHEMA
Ticket (TicketNo: string, DOJ: date, Address: string, ContactNo : string, BusNo:String
SeatNo : Integer, Source: String, Destination: String)
Passenger
PassportID
TicketNo
Name
ContactNo
Age
Sex
Address
SCHEMA
Passenger (PassportID: String, TicketNo :string, Name: String, ContactNo: string, Age:
integer, Sex: character, Address: String)
Reservation
PNRNo
DOJ
No_of_seats
Address
ContactNo
BusNo
SeatNo
SCHEMA
Reservation(PNRNo: String, DOJ: Date, NoofSeats: integer , Address: String ,ContactNo:
String, ,BusNo: String,SeatNo:Integer)
Cancellation
PNRNo
DOJ
SeatNo
ContactNo
Status
SCHEMA
Cancellation (PNRNo: String, DOJ: Date, SeatNo: integer, ContactNo: String, Status:
String)
CONCEPT DESIGN WITH E-R MODEL
[Link]-Diagram for Banking System,
Entities Attributes
Bank Name, Code, Address.
Branch Branch_id, Name, Address
Loan Loan_id, Loan_type, Amount
Account Account_NO, Account_type, Balance
Customer Customer_id, Name, Phone, Address.
Relationship
Entity Relation Entity
Bank Has Branch
Bank Maintain Account
Bank Offer Loan
Loan Availed by Customer
Account Hold by Customer
ER- Diagrams
Bank:
Name
Code
Address
SCHEMA
Bank(Name String, Code Integer, Address String)
Code Address
Name
BANK
Branch:
Branch_Id
Name
Address
SCHEMA
Branch(Branch_id Integer, Name String, Address String)
Name Address
Branch_Id
BRANCH
Loan:
Loan_Id
Loan_type
Amount
SCHEMA
Loan(Loan_Id Integer, Loan_type string, Amount Integer)
Loan_Type Amount
Loan_Id
e
BANK
Account:
Account_No
Account_type
Balance
SCHEMA
Account(Account_No Integer, Account_Type String, Balance Integer)
Account_type Balance
Account_No
ACCOUNT
Customer:
Customer_Id
Name
Phone
Address
SCHEMA
Customer(Customer_id Integer, Name String, Phone String, Address string)
Name Phone Address
Customer_Id
CUSTOMER
CONCEPT DESIGN WITH E-R MODEL
VIVA QUESTIONS:
[Link] is database?
A database is organized collection of related data of an organization stored in
formatted way which
is shared by multiple users.
The main feature of data in a database are:
1. It must be well organized
2. It is related
3. It is accessible in a logical order without any difficulty
4. It is stored only once
[Link] is DBMS?
A database management system consists of collection of related data and refers
to a set of programs for defining, creation, maintenance and manipulation of a
database
[Link] is a database system?
The database and DBMS software together is called as database system
[Link] is E-R model?
A graphical technique for understanding and organizing the data independent of
the actual database implementation
[Link] is an Entity?
An entity is a “thing” or “object” in the real world that is distinguishable from all
other objects
[Link] is an Entity set?
An entity set is a set of entities of the same type that share the same
properties, or attributes
[Link] is weak Entity set?
An entity which depends on other entity for its existence and doesn't have any
key attribute of its own is a weak entity.
[Link] is an attribute?different types of attribute?
Properties/characteristics which describe entities are called attributes
Key attribute
Simple attribute
Composite attribute
Single valued Attributes
Stored Attribute
Derived Attribute
[Link] is Relationship ? what is digree of a Relationship?
[Link] between entities are called relationships
b. Degree of a relationship is the number of entity types involved. The n-ary
relationship is the general form for degree n. Special cases are unary, binary, and
ternary ,where the degree is 1, 2, and 3, respectively
[Link] is Cardinality of a Relationship?
Relationship cardinalities specify how many of each entity type is allowed. Relationships can have
four possible connectivity's as given below.
1. One to one (1:1) relationship
2. One to many (1:N) relationship
3. Many to one (M:1) relationship
4. Many to many (M:N) relationship
The minimum and maximum values of this connectivity is called the cardinality of the relationship
