Dbms Notes Lecture 8 : E R Diagram question with solutions

E R diagram university questions with solutions

1. UNIT 1
Lecture 8
E R Model

2. Question 2
•ConstructanE-Rdiagramforacar-insurance
companywhosecustomersownoneormore
carseach.Eachcarhasassociatedwithitzeroto
anynumberofrecordedaccidents.
Dinesh Kumar Bhawnani, BIT DURG

3. Step 1 : Identify the entity sets
•From the given question the entity sets identified are
1.PERSON
2.CAR
3.ACCIDENT
Dinesh Kumar Bhawnani, BIT DURG

4. Step 2 : Identify the relevant attributes
•The relevant attributes of PERSON entity set are
•Person_Id
•Person_Name
•Address
•The relevant attributes of CAR entity set are
•Engine_No
•Model
•Year
•The relevant attributes of ACCIDENT entity set are
•Report_No
•Location
•Acc_Date
Dinesh Kumar Bhawnani, BIT DURG

5. Step 3 : Identify the prime attributes
•The prime attribute of PERSON entity set is
•Person_Id
•The prime attribute of CAR entity set is
•Engine_No
•The prime attribute of ACCIDENT entity set is
•Report_No
Dinesh Kumar Bhawnani, BIT DURG

6. Step 4 : Identify the relationships
•Customersownoneormorecarseach.
Dinesh Kumar Bhawnani, BIT DURG
PERSON CARowns

7. Step 4 : Identify the relationships
•Eachcarisassociatedwithitzerotoanynumber
ofrecordedaccidents.
Dinesh Kumar Bhawnani, BIT DURG

8. Step 4 : Identify the relationships
•Eachcarisassociatedwithitzerotoanynumber
ofrecordedaccidents.
Dinesh Kumar Bhawnani, BIT DURG
PERSON CAR
participated
ACCIDENT

9. Step 5 : Complete E R Diagram
Dinesh Kumar Bhawnani, BIT DURG
PERSON CAR
participated
ACCIDENT
owns
Person_Id
Person_Name
Address
Model
Year
Engine_No
Report_No Location
Acc_Date
Damage_Amount

10. Question 3
•DesignanE-Rdiagramforkeepingtrackoftheexploits
ofyourfavoritesportsteam.Youshouldstorethe
matchesplayed,thescoresineachmatch,theplayers
ineachmatchandindividualplayerstatisticsforeach
match.Summarystatisticsshouldbemodeledas
derivedattributes.
Dinesh Kumar Bhawnani, BIT DURG

11. Step 1 : Identify the entity sets
•From the given question the entity sets identified are
1.MATCH
2.PLAYER
Dinesh Kumar Bhawnani, BIT DURG

12. Step 2 : Identify the relevant attributes
•The relevant attributes of MATCH entity set are
•Match_Id
•Stadium
•Date
•Opponent
•Own_Score
•Opp_Score
•The relevant attributes of PLAYER entity set are
•Player_Id
•Player_Name
•Summary_Score
Dinesh Kumar Bhawnani, BIT DURG

13. Step 3 : Identify the prime attributes
•The prime attribute of MATCH entity set is
•Match_Id
•The prime attribute of PLAYER entity set is
•Player_Id
Dinesh Kumar Bhawnani, BIT DURG

14. Step 4 : Identify the relationships
Dinesh Kumar Bhawnani, BIT DURG
MATCH PLAYERplayed

15. Step 5 : Complete E R Diagram
Dinesh Kumar Bhawnani, BIT DURG
MATCH PLAYERplayed
Match_Id
Opponent
Stadium
Own_Score Opp_Score
Date
Score
Summary_Score
Player_Id
Player_Name

16. Question 4
•DesignanE-Rdiagramforkeepingtrackoftheexploitsofyour
favoritesportsteam.Youshouldstorethematchesplayed,thescores
ineachmatch,theplayersineachmatchandindividualplayer
statisticsforeachmatch.Summarystatisticsshouldbemodeledas
derivedattributes.
•ExtendtheE-Rdiagramofthepreviousquestiontotrackthesame
informationforallteamsinaleague.
Dinesh Kumar Bhawnani, BIT DURG

17. Complete E R Diagram
Dinesh Kumar Bhawnani, BIT DURG
MATCH PLAYERplayed
Match_Id
Opponent
Stadium
Date
Score
Summary_Score
Player_Id
Player_Name
TEAM
team_played player_of
Name Ranking
Result
Team_Score

18. Question 4
Auniversityregistrar’sofficemaintainsdataaboutthefollowing
entities:(a)courses,includingnumber,title,credits,syllabus,and
prerequisites;(b)courseofferings,includingcoursenumber,year,
semester,sectionnumber,instructor(s),timings,andclassroom;(c)
students,includingstudent-id,name,andprogram;and(d)instructors,
includingidentificationnumber,name,department,andtitle.Further,
theenrollmentofstudentsincoursesandgradesawardedtostudents
ineachcoursetheyareenrolledformustbeappropriatelymodeled.
ConstructanE-Rdiagramfortheregistrar’soffice.Documentall
assumptionsthatyoumakeaboutthemappingconstraints.
Dinesh Kumar Bhawnani, BIT DURG

19. Step 1 : Identify the entity sets
•From the given question the entity sets identified are
1.COURSE
2.COURSE-OFFERINGS
3.STUDENT
4.INSTRUCTOR
Dinesh Kumar Bhawnani, BIT DURG

20. Step 2 : Identify the relevant attributes
•TherelevantattributesofCOURSEentitysetare
•Course_No
•C_Tile
•Credits
•Syllabus
•TherelevantattributesofCOURSE-OFFERINGSentitysetare
•Year
•Time
•Secno
•Room
•Semester
•TherelevantattributesofSTUDENTentitysetare
•S_Id
•S_Name
•Program
•TherelevantattributesofINSTRUCTORentitysetare
•I_id
•I_Name
•I_Title
•Dept
Dinesh Kumar Bhawnani, BIT DURG

21. Step 3 : Identify the prime attributes
•TheprimeattributeofCOURSEentitysetare
•Course_No
•ThereisnoprimeattributeinCOURSE-OFFERINGSentityset,
soitisanweakentityset,howeverthepartialkey
(discriminator)is
•Year
•TheprimeattributeofSTUDENTentitysetis
•S_Id
•TheprimeattributeofINSTRUCTORentitysetis
•I_id
Dinesh Kumar Bhawnani, BIT DURG

22. Step 4 : Identify the relationship
Dinesh Kumar Bhawnani, BIT DURG
COURSErequires
pre-requisite
main-course

23. Step 4 : Identify the relationship
Dinesh Kumar Bhawnani, BIT DURG
COURSE
COURSE-
OFFERINGS
Is_offered

24. Step 4 : Identify the relationship
Dinesh Kumar Bhawnani, BIT DURG
STUDENT enrolls
COURSE-
OFFERINGS

25. Step 4 : Identify the relationship
Dinesh Kumar Bhawnani, BIT DURG
INSTRUCTORteaches
COURSE-
OFFERINGS

26. Step 5 : Complete E R Diagram
Dinesh Kumar Bhawnani, BIT DURG
STUDENT INSTRUCTOR
COURSE
enrolls teaches
requires
COURSE-
OFFERINGS
Is_offered
I_id I_Name
Dept I_Title
Course_No
Credits
C_Title
Syllabus
Program
S_Id Grade
S_Name
Time Secno Room
Year
Semester
pre-requisite
main-course

27. For Video lecture on this topic please subscribe to my youtubechannel.
The link for my youtubechannel is
https://www.youtube.com/channel/UCRWGtE76JlTp1iim6aOTRuw?sub
_confirmation=1
Dinesh Kumar Bhawnani, BIT DURG