Ask a question from expert
Data Schema (Note that primary keys are boldfaced).
2 Pages490 Words633 Views
Added on 2019-09-16
Data Schema (Note that primary keys are boldfaced).
Added on 2019-09-16
BookmarkShareRelated Documents
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 tie break, 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 title in the output.
End of preview
Want to access all the pages? Upload your documents or become a member.