Question 1

Consider a website that partners with universities and organizations that are industry leaders to provide courses to learners. Each university/organization has a unique name, a description, and an email address. In addition, each organization has a focus area.

Each course is offered by a partner and is listed with a unique course code, a course title, a synopsis, the duration of the course (in terms of weeks), the number of hours of material per week, whether it is learner-paced or instructor-led, the number of graded assignments, a subject category such as computer science, math, business, engineering, etc and a level category: beginner, intermediate, etc.

Each course has a date by which learners must enroll and an end date after which the course is unavailable. When the course becomes available again, it will have a new enroll-by date and end date. The assignments for a course are weighted and are identified with a running number generated for each course. Courses offered in different periods may differ in course fee, instruction mode, assignments and their weights, duration of the course, and hours of material per week.

Learners choose to either pay for a course or audit for free. Course materials are available to learners for download if they pay for the course, and these learners are awarded a certificate if they submit assignments and obtain an overall pass grade for the course. The overall grade is computed using the weights for the assignments and the corresponding grades obtained. This, each submission should include a submission date and a grade after it has been graded.

A learner who audits for a course may subsequently decide to pay and submit assignments to earn certification. Once the course fee is paid, no refund is possible. The enrolment date for the course is recorded for each learner, and if the course is paid, the payment date and payment reference are recorded as well. The course fee varies from course to course.

Courses that are offered by universities may be standalone or may be taken as part of a program. A learner who enrolls in a program will earn a specialist certification in addition to the certification for individual courses he selects from the set of courses for a program. Each program will require a learner to pass a certain number of courses offered at specified levels.

For example, a program for Data Science A offered by university X may require 3 courses at the beginner level and 2 courses at an intermediate level. If a course has prerequisites, the learner must take the pre-requisite courses first. The date a learner enrolls into a program must also be recorded, and if he completes the program, the date of completion is recorded. There is no time limit to when a program must be completed by.

A learner may re-enroll for a course if he does not clear it, regardless of whether the course is taken standalone or as part of a program. To re-enroll, he needs to pay the prevailing course fee when the course becomes available and submits assignments accordingly. Note that two learners may also enroll in the same program or the same course on the same date and complete the same program on the same date.

The website offers two types of accounts: a business account and an individual account. Each account has an email and a name. The gender, full name, and highest education qualification are recorded for an individual account. All learners must have an individual account through which they enroll in the program and/or enroll for courses.

A business account is a paid account renewable on a per-year basis, and the yearly amount paid is dependent on the number of individual accounts it can create and remove from the business account. Individual accounts created through a business account are eligible for a discount on course fees if the duration of the course falls within the paid period. Individual accounts that are removed from a business account function as standalone individual accounts and thereafter will pay the full course fee if a learner is not auditing.

Question 2

A course may be taught by more than one instructor and an instructor may teach more than one course. A student may enroll in more than one course, and a course may have more than one student enrolled. date Enrolled In Course is the date a student currently enrolls for a particular course. A course has many assignments, each identified by the course code and a running number generated for each course. assignment score is the score a student is awarded for a particular assignment, and assignment weightage is the weightage that an assignment contributes to the overall course grade.

State your assumption(s) for only data requirements that are not specified.

Formulate and list the functional and multi-valued dependencies.
Propose candidate key(s) for the table. Show composite keys in brackets.
(i) For each MVD in answer to Q2(a), explain why it is subsumed or not subsumed.
(ii) Normalise the relation to BCNF and 4NF by applying the BCNF approach
covered in the course text.

Question 3

Demonstrate the basic techniques of concurrency control and their application.

Suppose the table Course in Q3 includes an additional column capacity. A student may enroll for a course if the capacity for the course is not exceeded. Assume the following stored procedures may run concurrently:

i) Store procedure A allows the school administrator to update the capacity of
courses. The capacity may be increased or decreased if students who have
successfully enrolled in courses prior to the update are not impacted.
ii) Store procedure B allows students to enroll for courses if the capacity for the courses is not exceeded

Discuss what problem(s) if stored procedures A and B run without locks, and then recommend the lowest transaction isolations for running stored procedures A and B to avoid the problem(s). The transaction isolation levels for running stored procedures A and B need not be the same. Note that your answer must be specific to the specifications of the stored procedures.

Describe ONE (1) permission that should be given to students to allow them to enroll for courses and submit assignments. Describe ONE (1) permission that should not be given to students. Limit your answers to permissions on the tables in Q3.
Given Figure Q4(c), explain what the database recovery procedure will do for the transactions T1 to T5. Explain also how the transaction log helps ensure the ACID properties of T1 to T5.

The post ICT330: Consider a website that partners with universities and organizations that are industry leaders to provide courses to learners: Database Management Systems Assignment, SUSS appeared first on Singapore Assignment Help.