Data Schema (Note that primary keys are boldfaced).

Added on - 16 Sep 2019

  • Dissertation

    type

  • 2

    pages

  • 490

    words

  • 370

    views

  • 0

    downloads

Showing pages 1 to 1 of 2 pages
Data Schema(Note that primary keys are boldfaced)// userType to indicate whether the user is an administrator, a student, or a teacherUser(userID, password, email, userType)Course(courseID, courseTitle)// course rooms that users can access; note that users include all sorts of users such as teachers// and administratorsEnroll(userID, courseID)// Teacher’s userID is recorded when a piece of learning material is uploaded to a particular course room// so that update right to the material can be confined to the concerned teacher. For simplicity,// materials are assumed to be presented in text no larger than 64KB.Material(materialID, materialText, userID, courseID)// Teacher’s userID is recorded when an assignment is uploaded to a particular course room so that// update right to the assignment can be confined to the concerned teacher. For simplicity, assignments// are assumed to be presented in text no larger than 64KB.Assignment(assignmentID, assignmentText, submissionDeadline, userID, courseID)// For ease of processing, groups are still created for individual assignments. In such cases, each group is// composed of one member only.GroupMember(groupID, userID)// For each assignment, one or more groups are created.Group(groupID, assignmentID)// Each submission is associated with an assignment and submitted by a student group member. Once a// submission is made, any change to the submission is restricted to that group member. If a submission// is removed, any group member can upload a new submission.Submission(submissionID, assignmentID, groupID, submissionDate, userID)// Each submission is composed of one or more files.SubmittedFile(fileID, filePath, filename, submissionID)SQL QueriesYou are asked to implement SQL queries to address the following needs.1.Find three teachers who have access to most number of course rooms. Should there be a tiebreak, choose the ones with smaller user IDs. List the user ID and email in the output.(2 marks)2.Find the courses that assess student performance purely by examination. List the course titlein the output.
desklib-logo
You’re reading a preview
card-image

To View Complete Document

Become a Desklib Library Member.
Subscribe to our plans

Unlock This Document