Scribd Logo
Upload

Welcome to Scribd!

  • 96 views

    Fake Solutions

    Uploaded by

    nacem09
    The document contains example solutions to problems involving relational databases and queries. It includes entity relationship diagrams and relational algebra/SQL queries for different scenarios involving suppliers, parts, and catalogs. The problems involve finding suppliers based on the types of parts they supply, as well as suppliers that supply all or specific combinations of part colors.

    Copyright:

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

    Fake Solutions

    Uploaded by

    nacem09
    96 views4 pages
    The document contains example solutions to problems involving relational databases and queries. It includes entity relationship diagrams and relational algebra/SQL queries for different scenarios involving suppliers, parts, and catalogs. The problems involve finding suppliers based on the types of parts they supply, as well as suppliers that supply all or specific combinations of part colors.

    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 contains example solutions to problems involving relational databases and queries. It includes entity relationship diagrams and relational algebra/SQL queries for different scenarios involving suppliers, parts, and catalogs. The problems involve finding suppliers based on the types of parts they supply, as well as suppliers that supply all or specific combinations of part colors.

    Copyright:

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

    Fake Solutions

    Uploaded by

    nacem09
    The document contains example solutions to problems involving relational databases and queries. It includes entity relationship diagrams and relational algebra/SQL queries for different scenarios involving suppliers, parts, and catalogs. The problems involve finding suppliers based on the types of parts they supply, as well as suppliers that supply all or specific combinations of part colors.

    Copyright:

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

    Cs166 Fake Solutions

    1) A university database contains information about professors (identified by social


    security number, or SSN), courses (identified by courseid), and semesters (identified by
    semid). Professors teach courses during semesters, each of the following situations
    concerns the Teaches relationship set. For each situation, draw an ER diagram that
    describes it (assuming no further constraints hold).
    a) Professors can teach the same course in several semesters, and each offering
    must be recorded.

    semid

    Semesters
    ssn courseid
    N

    Professors M teaches 1 Courses

    b) Professors can teach the same course in several semesters, and only the most
    recent such offering needs to be recorded. (Assume this condition applies in all
    subsequent questions.)

    semid

    Semesters
    ssn courseid
    1

    Professors M teaches 1 Courses


    c) Every professor teaches exactly one course (no more, no less).

    semid

    Semesters
    ssn courseid
    1

    Professors M teaches 1 Courses

    d) Every professor teaches exactly one course (no more, no less), and every
    course must be taught by some professor.

    semid

    Semesters
    ssn courseid
    1

    Professors M teaches 1 Courses

    2) Consider the following schema:

    Suppliers( sid: integer, sname: string, address: string )


    Parts( pid: integer, pname: string, color: string )
    Catalog( sid: integer, pid: integer, cost: real )

    Write the following queries in relational algebra.


    Assume >< is the join symbol.
    a) Find the names of suppliers who supply some red part.

    ( ( )
    π sname π sid (π pidσ color =' red ' Parts ) >< Catalog >< Suppliers )
    b) Find the sids of suppliers who supply some red or green part.

    (
    π sid π pid (σ color =' red 'or color =' green ' Parts ) >< Catalog )
    c) Find the sids of suppliers who supply some red and some green part.

    ( (
    ρ R1, π sid (π pid σ color =' red ' Parts ) >< Catalog ))
    ρ ( R2, π ( (π sid pid σ color =' green ' Parts ) >< Catalog ) )
    R1 ∩ R2

    d) Find the sids of suppliers who supply every part.

    (π sid , pid Catalog ) / (π pid Parts )

    e) Find the sids of suppliers who supply every red or green part.

    (π sid , pid Catalog ) / (π pid σ color =' red ' or color =' green ' Parts )

    3) Consider the schema presented in problem 2. Write the following queries in SQL.
    a) Find the name of every part.

    SELECT P.pname
    FROM Parts P

    b) Find the pname and cost of all parts supplied by “BMI Supply”.

    SELECT P.pname, C.cost


    FROM Parts P, Catalog C, Suppliers S
    WHERE S.sname=’BMI Supply’ and P.pid=C.pid and S.sid=C.sid

    c) Find the sids of suppliers who supply some red and some green part.

    SELECT C.sid
    FROM Parts P, Catalog C
    WHERE P.color=’red’ and P.pid=C.pid
    and EXISTS ( SELECT P2.pid
    FROM Parts P2, Catalog C2
    WHERE P2.color=’green’ and C2.sid=C.sid
    and P2.pid=C2.pid )
    d) Find the sids of suppliers who only supply blue parts.

    ( ( SELECT C.sid
    FROM Catalog C, Parts P
    WHERE C.pid=P.pid and P.color=’blue’ )
    EXCEPT
    ( SELECT C2.sid
    FROM Catalog C2, Parts.P2
    WHERE C2.pid=P.pid and P.color <> ‘blue’ ) )

    e) Find the sids of suppliers who supply every part.

    SELECT C.sid
    FROM Catalog C
    WHERE NOT EXISTS ( SELECT P.pid
    FROM Parts P
    WHERE NOT EXISTS ( SELECT C1.sid
    FROM Catalog C1
    WHERE C1.sid=C.sid
    and C1.pid=P.pid ) )
    OR

    SELECT S.sid
    FROM Suppliers S
    WHERE NOT EXISTS ( ( SELECT P.pid
    FROM Parts P )
    EXCEPT
    ( SELECT C.pid
    FROM Catalog C
    WHERE C.sid=S.sid ) )

    You might also like