Final assessment (60%)
The individual assessment caries 60% weighting. This assessment replaces the final exam. Prepare a report which responds to all the assessment questions. Please divide your report into 3 sections (Task A, B, C and D). Please use each question as heading in the report while answering the questions. Students are required to complete all questions.
Students must complete all questions Task A – ER Diagram
Swinburne University of Technology (Sydney campus) is planning to implement a new student portal which requires a database system to help improve the learning experience of students. Data needs to be maintained for each student, including many aspects of the learning experience, for example the enrolment, assessments, lecture evaluation and graduation. Data also needs to be stored for the student services to track their study progression.
According to the information above, draw a corresponding ER diagram (at least 7 entities included). Please note:
A1. You may decide the names for entities, attributes and relations, but you should use UPPERCASE to name your entities and use Capitalisation (of first letter) to name attributes of entities. (5 marks)
A2. Underline all primary key attributes and mention all the foreign key attributes.
A3. Clearly indicate participation and cardinalities between entities. Also specify the relationship verbs. (5 marks)
Task B – Normalization
Consider the following relation for student records:
TRANSCRIPT REPORT(SID, StudentName, Major, Address, SubjectID, SubjectName, SubjectCreditPoints, YearofStudy, SubjectLecturerID, LecturerID, LecturerName, LecturerOffice, Grade, StudentResult)
Given that the following functional dependencies exist: SID -> StudentName, Major, Address
SubjectID-> SubjectTitle, SubjectCreditPoints, SubjectLecturerID StudentResult -> SubjectID, YearofStudy, Grade
Lecturer -> LecturerID, LecturerName, LecturerOffice
B1. Why is the relation in 1NF? State the reason(s). (5 marks)
B2. Is the relation in 2NF? State the reason for your answer with example. (5 marks)
B3. Decompose the relation to 3NF. State the reason(s) why the decomposed relation is in 3NF. (5 marks)
Task C – SQL
Consider the following relational schema (the primary keys are underlined and foreign keys are italic)
STUDENT(SID, sname, addressline1,addressline2, city, state,postcode, age) ENROLLMENT(EID, year, description)
COURSE(CID, Lecturername, cname)
Write the SQL statements for the following queries:
C1. List all cnames which containing “C++”. (5 marks)
C2. Find the names of students who are enrolled in C++ in the year of 2020. (5 marks)
C3. What is the youngest student’s name who lives Melbourne? (5 marks)
C4. Find the SID’s of students who either above 20 years old or who are in NSW. (5 marks)
Task D- NoSQL
D1 .Consider Task A, if the database is applied with the concept of Big data, in which way you would like to create the database (4 marks).
D2. Please provide the sample code including: insert document(s) into a collection (2 marks), retrieve document(s) from a collection (1 marks), query an array of Embedded documents (1 marks), query for null or missing field and update documents in collection (2 marks).
For solution, connect with our online professionals.