软件工程代写

程序代写 CS代考

CSIE4100 Software Engineering

CSIE4105 Database Systems Homework # 3

Due on 12/05/2018

  1. (55%) Consider the following database schema.
    STUDENT(StudentNumber, Name, Class, Major) COURSE(CourseNumber, CourseName, CreditHour, Department) SECTION(SectionNumber, CourseNumber, Semester, Year, Instructor) GRADE_REPORT(StudentNumber, SectionNumber, Grade) PREREQUISITE(CourseNumber, PrerequisiteCourseNumber) Specify the following queries in SQL.
    1. (a)  (5%) Change the credit hours of the course ‘Database Systems’ for the ‘CSIE’ department to 3.
    2. (b)  (5%) Delete the record for the student whose name is ‘Edward’ and whose student number is ‘001’.
    3. (c)  (5%) Retrieve the names of all courses taught by Professor ‘Chen’ in years 2017 and 2018.
    4. (d)  (5%) For each section taught by Professor ‘Chen’, retrieve the course number, semester, year, and the number of students who took the section.
    5. (e)  (5%) Retrieve the prerequisite course number and name for the course ‘Programming II’ offered by the ‘CSIE’ department.
    6. (f)  (5%) Retrieve the name and transcript of each junior student (Class=3) majoring in ‘CSIE’. A transcript includes course number, course name, credit hours, semester, year, and grade for each course completed by the student.
    7. (g)  (5%) Retrieve the names of students who have a grade greater than or equal to 80 in all of their courses.
    8. (h)  (5%) Retrieve the names and major departments of all students who do not have a grade below 60 in any of their courses.
    9. (i)  (5%) Retrieve the names and majors of all students who have a grade below 60 in any of their courses, order alphabetically by the student number.
    10. (j)  (5%) Retrieve the names and their average grades for the students who have an average grade greater than 80.0 in year 2018.
    11. (k)  (5%) For each department (i.e., student major), list the number of students whose average grade is below 60.0.
  2. (45%) Choose a DBMS (e.g., SQL Server, Oracle or MySQL) to create and populate the tables in problem 1.
    1. (a)  (15%) Show your SQL statements and the execution results (screen snapshots) for creating the tables. The SQL statements need to specify the key and referential constraints (i.e., primary key and foreign key) for each table.
    2. (b)  (10%) Populate the tables so that each table contains at least 5 tuples. Show your SQL insertion statements and the execution results (screen snapshots) for populating the tables.
    3. (c)  (20%) Perform the SQL queries in problem 1 in your DBMS. Show your SQL query statements and the execution results (screen snapshots). Note that your SQL query results MUST NOT contain no tuples.

Related Posts