Scribd Logo
Upload

Welcome to Scribd!

  • 2K views

    Third Assignment

    Uploaded by

    Suhas Pandit
    This document contains instructions for Assignment 3 in an ME Comp class. It includes: 1) Extending existing schema to allow teaching assistants per course section and students to have multiple advisors. 2) Writing SQL queries on modified schema including queries to find students with more than 3 advisors or co-advised by specific professors. 3) Creating tables for phones, networks, and deals between phones and networks with constraints. 4) Providing sample queries as solutions for a previous assignment.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOC, PDF, TXT or read online from Scribd

    Third Assignment

    Uploaded by

    Suhas Pandit
    2K views3 pages
    This document contains instructions for Assignment 3 in an ME Comp class. It includes: 1) Extending existing schema to allow teaching assistants per course section and students to have multiple advisors. 2) Writing SQL queries on modified schema including queries to find students with more than 3 advisors or co-advised by specific professors. 3) Creating tables for phones, networks, and deals between phones and networks with constraints. 4) Providing sample queries as solutions for a previous assignment.

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    DOC, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document contains instructions for Assignment 3 in an ME Comp class. It includes: 1) Extending existing schema to allow teaching assistants per course section and students to have multiple advisors. 2) Writing SQL queries on modified schema including queries to find students with more than 3 advisors or co-advised by specific professors. 3) Creating tables for phones, networks, and deals between phones and networks with constraints. 4) Providing sample queries as solutions for a previous assignment.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    2K views3 pages

    Third Assignment

    Uploaded by

    Suhas Pandit
    This document contains instructions for Assignment 3 in an ME Comp class. It includes: 1) Extending existing schema to allow teaching assistants per course section and students to have multiple advisors. 2) Writing SQL queries on modified schema including queries to find students with more than 3 advisors or co-advised by specific professors. 3) Creating tables for phones, networks, and deals between phones and networks with constraints. 4) Providing sample queries as solutions for a previous assignment.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    You are on page 1of 3

    Assignment 3

    Class: ME Comp Date: 10/02/2011

    1. Each offering of a course (i.e. a section) can have many Teaching


    assistants; each teaching assistant is a student. Extend the existing
    schema by adding or altering tables to accommodate this requirement.

    2. According to the existing schema, one student can have only one advisor.

    1. Alter the schema to allow a student to have multiple


    advisors and make sure that you are able to insert multiple
    advisors for a student.
    2. Write SQL queries on the modified schema. You will need
    to insert data to ensure the query results are not empty.
    1. Find all students who have more than 3 advisors
    2. Find all students who are co-advised by Prof.
    Srinivas and Prof. Ashok.
    3. Find students advised by instructors from different
    departments. etc.
    2. Write SQL queries for the following:
    1. Delete all information in the database which is more than
    10 years old. Add data as necessary to verify your query.
    2. Delete the course CS 101. All courses which have this as a
    prereq should remove this from its prereq set. Create a
    cascade constraint and verify.

    3. Write the create table SQL commands to create the following


    tables:
    1. phone(phone_id, brand, model, price)

     This table represents a mobile phone.


     brand is some string like 'Nokia', 'Samsung' etc.
     model is a string such as 'N95', 'Galaxy3' etc.
    2. network(network_id, name, type)
     This table represents mobile phone networks such as
    'Airtel', 'BSNL' etc.
    3. deal(id, phone_id, network_id, deal_price, start_date,
    end_date)
     Deal is an offer for a phone when bought along with
    a network
     The deal price is valid only between start_date and
    end_date
     The phone_id and network_id attributes are foreign
    keys to the phone and the network table respectively
     Create constraints to check the following:
    1. The start_date of a deal should always be <= its
    end_date.
    2. The network type should be one of the following values:
    'GSM', 'CDMA', 'BOTH'
     Modify the create table script and add a cascade constraint so
    that, when a phone is deleted (i.e., no longer being manufactured),
    all deals for that phone are deleted.
     Try to insert inconsistent data and verify the constraints. Provide
    insert statements that fail, along with the error message.

    Few Solutions for second assignment:

    1. Find the number of instructors who have never taught any course. If the
    result of your query is 0, add appropriate data (and include corresponding
    insert statements) to ensure the result is not 0.

    Query:

    select count(*) from instructor


    where ID not in (select ID from teaches);

    2. Find the total capacity of each of the buildings in the university

    Query:

    select building, sum(capacity) from classroom group by building;

    3. For each student, compute the total credits they have successfully
    completed, i.e. total credits of courses they have taken, for which they have
    a non-null grade other than 'F'. Do NOT use the tot_creds attribute of
    student.

    Query:

    select student.ID, name, sum(course.credits)


    from takes, student, course
    where grade <> 'F'
    and grade is not null
    and takes.course_id = course.course_id
    and student.ID = takes.ID
    group by student.ID, name;
    4. Find the number of students who have been taught (at any time) by an
    instructor named 'Srinivasan'. Make sure you count a student only once even
    if the student has taken more than one course from Srinivasan.

    Query:

    select count(distinct takes.ID)


    from instructor, takes, teaches
    where takes.course_id = teaches.course_id
    and takes.sec_id = teaches.sec_id
    and takes.semester = teaches.semester
    and takes.year = teaches.year
    and teaches.ID=instructor.ID
    and instructor.name='Srinivasan';

    I hope rest u will try and submit the assignment on or before: 15th Feb 2011.

    You might also like