Scribd Logo
Upload

Welcome to Scribd!

  • 14 views

    DBMS Lec 8

    Uploaded by

    J40 Sarthak Pandey
    The document describes an Entity-Relationship (E-R) diagram for a university registrar's office. It identifies the key entities as courses, course offerings, students, and instructors. Relationships include courses requiring or being prerequisites for other courses, course offerings being offered from courses, students enrolling in course offerings, and instructors teaching course offerings. Attributes are identified for each entity like course number and title, student ID, instructor name. The E-R diagram maps out these entities, relationships, and attributes.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    DBMS Lec 8

    Uploaded by

    J40 Sarthak Pandey
    14 views27 pages
    The document describes an Entity-Relationship (E-R) diagram for a university registrar's office. It identifies the key entities as courses, course offerings, students, and instructors. Relationships include courses requiring or being prerequisites for other courses, course offerings being offered from courses, students enrolling in course offerings, and instructors teaching course offerings. Attributes are identified for each entity like course number and title, student ID, instructor name. The E-R diagram maps out these entities, relationships, and attributes.

    Original Description:

    dbms ch 2

    Original Title

    DBMS lec 8

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document describes an Entity-Relationship (E-R) diagram for a university registrar's office. It identifies the key entities as courses, course offerings, students, and instructors. Relationships include courses requiring or being prerequisites for other courses, course offerings being offered from courses, students enrolling in course offerings, and instructors teaching course offerings. Attributes are identified for each entity like course number and title, student ID, instructor name. The E-R diagram maps out these entities, relationships, and attributes.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    14 views27 pages

    DBMS Lec 8

    Uploaded by

    J40 Sarthak Pandey
    The document describes an Entity-Relationship (E-R) diagram for a university registrar's office. It identifies the key entities as courses, course offerings, students, and instructors. Relationships include courses requiring or being prerequisites for other courses, course offerings being offered from courses, students enrolling in course offerings, and instructors teaching course offerings. Attributes are identified for each entity like course number and title, student ID, instructor name. The E-R diagram maps out these entities, relationships, and attributes.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 27

    UNIT 1

    Lecture 8
    E R Model
    Question 2
    • Construct an E-R diagram for a car-insurance
    company whose customers own one or more
    cars each. Each car has associated with it zero to
    any number of recorded accidents.

    Dinesh Kumar Bhawnani, BIT DURG


    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


    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
    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


    Step 4 : Identify the relationships
    • Customers own one or more cars each.

    PERSON owns CAR

    Dinesh Kumar Bhawnani, BIT DURG


    Step 4 : Identify the relationships
    • Each car is associated with it zero to any number
    of recorded accidents.

    Dinesh Kumar Bhawnani, BIT DURG


    Step 4 : Identify the relationships
    • Each car is associated with it zero to any number
    of recorded accidents.
    PERSON CAR

    participated

    ACCIDENT

    Dinesh Kumar Bhawnani, BIT DURG


    Step 5 : Complete E R Diagram
    Person_Name
    Model
    Person_Id
    Engine_No
    Address Year

    PERSON owns CAR

    Report_No Location

    participated
    Acc_Date
    ACCIDENT

    Damage_Amount

    Dinesh Kumar Bhawnani, BIT DURG


    Question 3
    • Design an E-R diagram for keeping track of the exploits
    of your favorite sports team. You should store the
    matches played, the scores in each match, the players
    in each match and individual player statistics for each
    match. Summary statistics should be modeled as
    derived attributes.

    Dinesh Kumar Bhawnani, BIT DURG


    Step 1 : Identify the entity sets
    • From the given question the entity sets identified are
    1. MATCH
    2. PLAYER

    Dinesh Kumar Bhawnani, BIT DURG


    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
    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


    Step 4 : Identify the relationships

    MATCH played PLAYER

    Dinesh Kumar Bhawnani, BIT DURG


    Step 5 : Complete E R Diagram
    Match_Id

    Stadium Player_Id Player_Name


    Opponent

    Date MATCH played PLAYER

    Own_Score Opp_Score Score Summary_Score

    Dinesh Kumar Bhawnani, BIT DURG


    Question 4
    • Design an E-R diagram for keeping track of the exploits of your
    favorite sports team. You should store the matches played, the scores
    in each match, the players in each match and individual player
    statistics for each match. Summary statistics should be modeled as
    derived attributes.
    • Extend the E-R diagram of the previous question to track the same
    information for all teams in a league.

    Dinesh Kumar Bhawnani, BIT DURG


    Complete E R Diagram
    Match_Id
    Score
    Stadium Player_Id Player_Name
    Opponent

    Date MATCH played PLAYER

    Team_Score Summary_Score

    team_played player_of

    Result
    TEAM

    Name Ranking

    Dinesh Kumar Bhawnani, BIT DURG


    Question 4
    A university registrar’s office maintains data about the following
    entities: (a) courses, including number, title, credits, syllabus, and
    prerequisites; (b) course offerings, including course number, year,
    semester, section number, instructor(s), timings, and classroom; (c)
    students, including student-id, name, and program; and (d) instructors,
    including identification number, name, department, and title. Further,
    the enrollment of students in courses and grades awarded to students
    in each course they are enrolled for must be appropriately modeled.
    Construct an E-R diagram for the registrar’s office. Document all
    assumptions that you make about the mapping constraints.

    Dinesh Kumar Bhawnani, BIT DURG


    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


    Step 2 : Identify the relevant attributes
    • The relevant attributes of COURSE entity set are
    • Course_No
    • C_Tile
    • Credits
    • Syllabus
    • The relevant attributes of COURSE-OFFERINGS entity set are
    • Year
    • Time
    • Secno
    • Room
    • Semester
    • The relevant attributes of STUDENT entity set are
    • S_Id
    • S_Name
    • Program
    • The relevant attributes of INSTRUCTOR entity set are
    • I_id
    • I_Name
    • I_Title
    • Dept
    Dinesh Kumar Bhawnani, BIT DURG
    Step 3 : Identify the prime attributes
    • The prime attribute of COURSE entity set are
    • Course_No
    • There is no prime attribute in COURSE-OFFERINGS entity set,
    so it is an weak entity set, however the partial key
    (discriminator) is
    • Year
    • The prime attribute of STUDENT entity set is
    • S_Id
    • The prime attribute of INSTRUCTOR entity set is
    • I_id
    Dinesh Kumar Bhawnani, BIT DURG
    Step 4 : Identify the relationship

    pre-requisite

    requires COURSE
    main-course

    Dinesh Kumar Bhawnani, BIT DURG


    Step 4 : Identify the relationship

    COURSE-
    OFFERINGS

    Is_offered

    COURSE

    Dinesh Kumar Bhawnani, BIT DURG


    Step 4 : Identify the relationship

    COURSE-
    STUDENT enrolls
    OFFERINGS

    Dinesh Kumar Bhawnani, BIT DURG


    Step 4 : Identify the relationship

    COURSE-
    teaches INSTRUCTOR
    OFFERINGS

    Dinesh Kumar Bhawnani, BIT DURG


    S_Name
    Step 5 : Complete E R Diagram
    S_Id Grade Time Secno Room I_id I_Name

    COURSE-
    STUDENT enrolls teaches INSTRUCTOR
    OFFERINGS

    Year Semester
    Program Dept I_Title

    Is_offered

    Course_No
    C_Title
    pre-requisite

    requires COURSE Credits

    main-course Syllabus
    Dinesh Kumar Bhawnani, BIT DURG
    For Video lecture on this topic please subscribe to my youtube channel.

    The link for my youtube channel is

    https://www.youtube.com/channel/UCRWGtE76JlTp1iim6aOTRuw?sub
    _confirmation=1

    Dinesh Kumar Bhawnani, BIT DURG

    You might also like