Scenario:
In this assignment you are asked to design and implement a database app for managing and analyzing University course experiences. Let's call it the Course Experience App (CEA). The CEA database stores information about courses offered by the University, some basic information about instructors and students, and student course experiences. Information about courses consists of the following facts: course number, the name and the code of the offering department, the general area of study (e.g. humanities, sciences, arts, business, engineering), a list of topics covered in the course, and a list of skills acquired upon successful completion of this course. We also store information about prerequisite and exclusion courses for any given course.
Note that a course number is unique only within a specific department. For each instructor, we store a name, gender, age, and areas of expertise (if applicable). An instructor can be permanent faculty or a freelancer. If an instructor is faculty, we store the year when they started their career as a faculty, and whether they are teaching or research faculty. If an instructor is research faculty, we record a list of their research interests. For each student - identified by a unique user name - we store basic demographic data such as year and month of birth, gender, and country of birth. In addition, we store the year and month when this student begun his study at university. Additional information about students include their professional history outside academia.
For each significant job that the student held in the past or is holding now, we record the job title, Company name (or self- employed), Company expertise (can be more than one), start and end date of the employment, and a list of skills used during or acquired on the job. For each skill, we record a level of expertise on a scale of beginner-intermediate-advanced-professional (2-5). Each time the course is offered we call it an ‘edition' of the course. For each edition we record the start and end date, the time of day the course was offered (morning, day, evening), an approximate overall number of students enrolled in this course, and the name of the course instructor. Each edition of a course can be co-taught by more than one instructor.
For the main part of this database app, we record all course editions taken by a particular student, together with the final grade (in numeric format - percentage). The student records their experiences after enrolling in an edition of the course. The experiences consist of the following facts:
- Overall satisfaction with the course on a scale from 1 to 5 (5 is the highest).
- An overall ranking of the course instructor(s) (5 is the highest).
- The list of skills that the student has acquired from this course. For each skill we record 2 self-assessment values: the rank of a skill before the course and the rank of the same skill upon the course completion - on a scale 1-zero knowledge, 2-beginner, 3- intermediate, 4-advanced, 5-professional.
- Overall interest in the course topics - also 2 values on a 5 point scale - one specifies the interest before taking the course, and the second - the interest after its completion.
Learning Outcomes
1. Create and maintain a database server
2. Design an enterprise data model that incorporates all of the data used by an organisation's business processes
3. Devise and implement database security policies
Analyze Scenario given below and answer the following questions:
Task 1:
Submit a work proposal for assignment
which must include:
- Understanding of deliverables - a detail description of deliverables
- General overview of task 2 and 3 - initial understanding of solution to tasks
The work proposal must be submitted in a word file through the link available in Moodle.
Task 2:
a. Assume that you are responsible for installing a new DBMS product for the scenario given below. To undertake this exercise, you must have clear understanding of instance. Discuss the various modes to shut down and startup the database server.
b. As a database administrator perform the following tasks: [Lab demo is required. There will be no marks without a lab demo. The lab demo schedule will be announce in week #9 ]
i. Create separate physical structures for the new schema as per OFA guidelines
ii. Create a Schema with appropriate privileges
iii. Analyse the following scenario and create database tables with correct relationships
iv. Create at least two complex Views for the scenario give below.
c. Discuss cloud database management system.
Task 3:
Select any oraganisation where the data is sensitive. Discuss the concept of authentication and authorization in the database. You are required to provide the examples with the appropriate justification.