Scribd Logo
Upload

Welcome to Scribd!

  • 77 views

    Database Systems (Tut-6) : BITS Pilani

    Uploaded by

    Aditya Chopra
    The document describes three exercises related to entity relationship diagrams (ERD) for database design. The first exercise provides a description of a university research project system and asks to draw the ERD. The second exercise provides a description of a banking system and asks to draw the ERD. The third exercise provides a sample database schema and asks to write relational algebra and SQL queries.

    Copyright:

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

    Database Systems (Tut-6) : BITS Pilani

    Uploaded by

    Aditya Chopra
    77 views6 pages
    The document describes three exercises related to entity relationship diagrams (ERD) for database design. The first exercise provides a description of a university research project system and asks to draw the ERD. The second exercise provides a description of a banking system and asks to draw the ERD. The third exercise provides a sample database schema and asks to write relational algebra and SQL queries.

    Original Description:

    Database Systems Tutorial

    Original Title

    DBS_Tut-6

    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 three exercises related to entity relationship diagrams (ERD) for database design. The first exercise provides a description of a university research project system and asks to draw the ERD. The second exercise provides a description of a banking system and asks to draw the ERD. The third exercise provides a sample database schema and asks to write relational algebra and SQL queries.

    Copyright:

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

    Database Systems (Tut-6) : BITS Pilani

    Uploaded by

    Aditya Chopra
    The document describes three exercises related to entity relationship diagrams (ERD) for database design. The first exercise provides a description of a university research project system and asks to draw the ERD. The second exercise provides a description of a banking system and asks to draw the ERD. The third exercise provides a sample database schema and asks to write relational algebra and SQL queries.

    Copyright:

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

    CS F212

    Database Systems
    (Tut-6)
    Prof.R.Gururaj
    BITS Pilani CS&IS Dept.
    Hyderabad Campus
    Exercise-1
    Look at the following description related to Research and Sponsored Projects in an
    Educational Institution.
    Institute has some sponsored Projects. Projects will have one Faculty as Principal
    Investigator (PI), and can have one or more faculty member as Co-PIs. Some
    projects will not have any Co-PI. A Project has ProjectID (unique), Project name,
    Budget, and Duration as attributes. Faculty are identified by FacultyID (unique)
    and have Name, Dept , Designation as other attributes. A Project is funded by only
    one Funding agency (like UGC, DIT, DST etc.) which has- Agency name (unique),
    Head, Location (with street, city, and state as sub components). A faculty, as a PI
    can have zero to any number of projects. Similarly a faculty as a Co-PI can have
    zero to any number of projects. A funding agency might have funded one or more
    projects.
    Draw an ER diagram for the above requirement. Assume necessary data which is
    missing in the question. The model should include- Entity types, relationships, min-
    max, cardinality, participation, and other relevant constraints.

    Prof.R.Gururaj CSF 212 Database Systems Tutorial BITS Pilani, Hyderabad Campus
    Prof.R.Gururaj CSF 212 Database Systems Tutorial BITS Pilani, Hyderabad Campus
    Exercise-2
    Assume that we need to capture the data about Banking system described below.

    i) We capture details of banks; like- name (unique) (Ex. SBI, Canara bank etc.) ,
    category (private or public sector) , headquarters (city) , start year, and CEO.
    ii) A bank (like SBI, Canara Bank, Union bank etc.), will have branches identified by
    branch code. Branch codes are unique within a bank, but may repeat for different banks.
    (Ex. SBI and Union Bank both may have branches with code ‘1126’. But no bank can
    have two branches with same code.
    iii) Each branch will have street, city, manager (name, Designation, contact as sub-
    components), turnover as other attributes apart from branch code.
    iv) Banks have accounts with unique account number (with in the bank) and customer
    name, start_date, cust_address, cust_contact (multiple contacts possible), type, balance
    as other attributes. Accounts are attached to banks, but not to branches. There is no
    concept of joint account. Every Bank will have at least 1000 accounts. Different banks
    can have accounts with same account number. But no bank can have two accounts with
    same account number. (Ex. Acct# 11267 can be there in SBI and Corporation bank as
    well.). One account is associated with only one bank .
    Now, draw the ER diagram for the above description. Indicate- cardinality, keys,
    attributes, min-max, and participation constraints for entity-types involved in the
    relationships. If found missing, assume necessary data.

    Prof.R.Gururaj CSF 212 Database Systems Totorial BITS Pilani, Hyderabad Campus
    Prof.R.Gururaj CSF 212 Database Systems Tutorial BITS Pilani, Hyderabad Campus
    Exercise:3 (if time permits)
    Look at the following Database schema.
    Student(sid, sname, sbranch)
    Course(cid, cname, credits)
    Student_Course(sid, cid, grade)

    Now, write Relational Algebra and SQL (both) queries for the following.

    1.Get the sid and the sname for those students who have got A grade in
    ’Networks’ course.
    2.Get the cid and cname for those courses registered by all students from
    CIVIL branch.
    3. Get the sid and sname for those from CSE and also have registered for
    at least 4 courses

    Note: No need to rename attributes in results. Do not define VIEWS. Do not


    use Outer joins.

    Prof.R.Gururaj CSF 212 Database Systems Tutorial BITS Pilani, Hyderabad Campus

    You might also like