Final Assessment (Individual Work) – 50%. Time allowed 2 hours
The Final Assessment submission box will only be available for the tme period window
June 2020
MITS4003
Database SystemsSkills Assessment
Final Assessment
This study source was downloaded by 100000852006834 from CourseHero.com on 05-30-2023 05:32:30 GMT -05:00
https://www.coursehero.com/file/85167005/MITS4003-Final-Assessmentdocx/
MITS4003 Database Systems
beginning from when this document was made available for the length of tme specifed
above. This assessment must be completed and uploaded to the Final Assessment
submission box within this tme period. The submission box will not remain open beyond
this tme period. If you are late in downloading this document, you will not have the same
length of tme to work on the assessment. The submission box will stll close at the same
tme for everyone.
The submission box will be linked to Turnitn, and similarity scores WILL BE CHECKED and
assessments indicatng high similarity will be penalized as per all other assessments
throughout the semester.
The assessment must be submited in a Word or PDF fle, with your student ID, name, unit
code and unit name. You can submit this any tme within the specifed tme frame (2 hours),
DO NOT leave this tll the fnal minute as when the submission closes you won’t be able to
submit your work for marking.
ENTER YOUR NAME AND ID BELOW
Student ID. Name
Exam contnues NEXT PAGE
This study source was downloaded by 100000852006834 from CourseHero.com on 05-30-2023 05:32:30 GMT -05:00
https://www.coursehero.com/file/85167005/MITS4003-Final-Assessmentdocx/
MITS4003 Database Systems
Queston 1: (Total 10 marks)
VIT offers many course streams (e.g. MITS, BITS, MBA and many short courses) and each
course has a course coordinator who belongs to faculty. Course stream has specifc
course code, course name, fee and student belong to that course. VIT students can enrol
only in one course at a tme and each course can have one or no students.
VIT need to record student id, name, phone number, address, gender and date of birth
for each student. Students study different units offered by VIT. Each unit is taught by a
faculty member. Faculty member id, name, gender, address, date of birth and salary has
been recorded. Every semester each student can enrol in more than one unit and
faculty member can teach more than one unit. A faculty member can teach in multple
course streams. Also, a unit can be taught by many faculty members or one.
Faculty members may work on multple projects. For each projects the name, project id,
area, duraton and associated faculty member ids are maintained.
For the above given case study identfy all enttes, atributes for each entty, primary
keys, foreign keys, relatonship between enttes and cardinalites. To answer assume
you identfy an entty student and course then it should be writen as
Entty, atributes and keys: Student (StudentId(PK), Name,………..,CourseCode(FK) )
Relatonship and cardinalites: Student(0,M) —<belongs to>— (1,1)Course
Answer:
We have following Entty,
Course, Student, Faculty, Project, Unit
The atributes of enttes and their keys:
Course (Course Code (PK), Course Name, Fee, Student ID(FK), Faculty ID(FK)
Student (Student ID(PK), Name, mobile Number, Address, Gender, DOB)
Faculty (Faculty ID(PK), Name, Gender, Address, DOB, Salary)
Project (Project ID(PK), Name, Area, Duraton, Faculty ID(FK), Student ID(FK)
Unit (Unit ID(PK), Name, Duraton, Faculty ID(FK), Student ID(FK)
Relatonship and cardinalites:
Student(0,M) ——<belongs to>—– (M,1)Course
Student(1,M) ——<enroll in>—– (M,1)Unit
This study source was downloaded by 100000852006834 from CourseHero.com on 05-30-2023 05:32:30 GMT -05:00
https://www.coursehero.com/file/85167005/MITS4003-Final-Assessmentdocx/
MITS4003 Database Systems
Faculty(1,M) ——<teach>—– (M,1)Unit
Faculty(1,M) ——<belongs to>—– (M,1)Course
Faculty(1,M) ——<works on >—– (M,1)project
This study source was downloaded by 100000852006834 from CourseHero.com on 05-30-2023 05:32:30 GMT -05:00
https://www.coursehero.com/file/85167005/MITS4003-Final-Assessmentdocx/
MITS4003 Database Systems
Queston 2: (Total 3+3+4=10 marks)
VIT wants to store the record of its research projects. For each project, VIT records
informaton of projects, (Project Code, Project Title, Project Manager, Project Budget)
and faculty (Faculty ID, Name, Hourly Rate for project) and Course (Course Code, Course
Name) to which faculty belonged. Below is a sample of data of a project.
Project
Code:
P-2000 Project Manager: Sara
Project Title: Student at risk
identfcaton
system
Project Budget: $20,000
Facutly ID Faculty Name Course
Code
Department Name Project Hourly
Rate
F100 Roger MITS Master of IT and System $45.00
F125 Gerson BITS Bachelor of IT and System $32.00
F234 Aaron MITS Master of IT and System $50.00
F111 Jones BITS Bachelor of IT and System $39.00
The un-normalised table (called Project) that corresponds to the above format is as
follows:
Project(ProjectCode, ProjectTitle, ProjectBudget, ProjectManager, FacultyID,
FacultyName, Course Code, Course Name, Project HourlyRate)
Where required, you may make assumptons. However, your assumptons should not
contradict the above situaton and all assumptons should be stated in your answer.
a) Identfy the repeatng group of atributes and transform the above unnormalised table into tables that are in 1st Normal Form.
b) Identfy any partal dependencies and transform into tables that are in 2nd
Normal Form.
c) Identfy any transitve dependencies and transform into tables that are in 3rd
Normal Form.
Answer:
1NF: The provided Project details are repeatng for all the employees and departments
working on the project, so we separate it from the other table.
Project (Project_Code, Project_Title, Project_Budget, Project_Manager)
Project_Details (Project_Code, Faculty_ID, Faculty_Name, Course_Code,
Department_name, Project_Hourly_Rate)
This study source was downloaded by 100000852006834 from CourseHero.com on 05-30-2023 05:32:30 GMT -05:00
https://www.coursehero.com/file/85167005/MITS4003-Final-Assessmentdocx/
MITS4003 Database Systems
b) 2NF: we have two Primary keys in the Project_Details Table which is
Project_code and Faculty_ID. While, Faculty_Name is partally dependent on the
primary key that is the Faculty_ID, therefore, we create different table for faculty.
Project (Project_Code, Project_budget, Project_ttle, Project_Manager)
Project_Details (Project_Code, Faculty_ID, Course_Code, Department_name,
Project_Hourly_Rate)
Faculty (Faculty_ID, Faculty_Name)
c) 3NF:
Project (Project_Code, Project_Title, Project_manager, Project_budget)
Project_Details (Project_Code, Faculty_ID, Course_Code, Project_Hourly_Rate)
Faculty (Faculty_ID, Faculty_Name)
Department (Course_Code, Department_Name)
Queston 3: (Total 3×4=12 marks)
Consider the tables below and then atempt the queries that follow
Employee Table
Employee_id First_name Last_name City
E2001 Roger Clarke Melbourne
E2002 Michael Martn Melbourne
E1002 Roy Lee Sydney
E1004 Tom White Sydney
E785 Jerry Wilson Brisbane
E879 Sean Martn Brisbane
Department
This study source was downloaded by 100000852006834 from CourseHero.com on 05-30-2023 05:32:30 GMT -05:00
https://www.coursehero.com/file/85167005/MITS4003-Final-Assessmentdocx/
MITS4003 Database Systems
Department_id Department_name
D1002 Finance
D2001 Marketng
D2002 Administraton
Works Table
Employee_id Department_id Designaton Yearly_Salary
E2001 D2001 Loan Ofcer $70,000
E2002 D2002 Adviser $45,000
E1002 D2001 Internal Auditor $75,000
E1004 D2002 Manager $95,000
E785 D1002 Administratve Assistant $65,000
E879 D1002 Manager $90,000
a) Write down a query to display the employee name and city for all employees who
work for ‘Marketng’ and earn more than $74,000, and give output based on the
sample data in the above tables.
b) Write down a query to display informaton of employees and its department who are
receiving salary more than $50,000, and give output based on the sample data in
above tables.
c) Write down an insert statement to add a record for “Insurance” department in
department table and then write down a query to update the department of “Roger
Clarke” to the “Insurance” department.
d) Write a query to restrict user employee from updatng and insertng data into the
above table. However, they are allowed to read the informaton from the
database.
Answer:
This study source was downloaded by 100000852006834 from CourseHero.com on 05-30-2023 05:32:30 GMT -05:00
https://www.coursehero.com/file/85167005/MITS4003-Final-Assessmentdocx/
MITS4003 Database Systems
Queston 4: (Total 10 marks)
In the modern era, informaton becomes an integral part of our daily life. All public and
private sector business and companies store huge amount of data in databases and
apply data analysis/data mining techniques to fnd a hidden patern in that data and
make critcal decisions. Consider a current scenario of COVID-19 and assume you have
informaton about all the patents, their symptoms, treatment, current conditon
(critcal, recovered or deceased) and their travel informaton. In such situaton discuss
the importance of database management system, data analysis and data mining
techniques and suggest a technique you may use to identfy new potental patents.
Answer:
There is no doubt that data is increasing day by day, therefore organisatons cannot use
this data as it is, they must fnd the valuable informaton from this huge data. So, it is
not that much easy to store and manage big data.as a soluton we have database
management system, by using this system we can store and manage the big data
according to organizaton’s needs.
Data analysis, this is characterized as a procedure of cleaning altering and transmitng
informaton to fnd helpful data from informaton and taking choices dependent on the
informaton examinaton.
Data mining: this is process that helps in fnding different paterns in huge sets of data
involving techniques. The patern should have meaning that we can get some beneft.,
economic beneft should be prioritsed
According to the situatons of covid 19, the frst technique that is associaton technique
that we use to fnd the relaton for example we can say that if the frst person have virus
then ofcurse the second person got virus from the frst person. The second patern
which is classifcaton is used to fnd that person who is infected from the virus or who
is not. For example, we can say that the informaton that we have about the patent
such as symptoms treatment current conditon and the travel history.
I think second patern is beter to fnd the new patent as the provided informaton such
as symptoms treatment current conditon and the travel history. Helps to test the
infected person or not infected.
This study source was downloaded by 100000852006834 from CourseHero.com on 05-30-2023 05:32:30 GMT -05:00
https://www.coursehero.com/file/85167005/MITS4003-Final-Assessmentdocx/
MITS4003 Database Systems
Queston 5: (Total 8 marks)
Consider a scenario, where you are hired to improve the performance of database
searches. When you analysed the records stored in the database you found the records
are unordered and a linear search is used to retrieve a record. Discuss the techniques
you would use to reduce the search tme of such database. Justfy your answer using
one example.
Answer:
As afer analysing the scenario binary search is suitable for odering the data, as well as
it is suitable the for reducing tme for fnding the informaton from huge data as well as
improving performance of organisaton.
GOOD LUCK