National College of Ireland

Higher Diploma in Science in Computing

HDSDEV_JAN_HDAIML_JANOL, CIC_FEBOL, HDSDEV_SEPOL_YR1,

HDCYB_JANOL_HDWD_JANOL, HDCSDEV_INTJAN22

Semester I, May

Release Date: May 15, 2022 at 09:00am

 Submission Deadline: May 21, 2022by 11:55pm

______________________________________________________________________

Introduction to Databases

Anu Sahni

Dr David Hamill

Eugene McLaughlin

Hicham Rifai

This TABA will cover the following learning outcomes

LO1 Analyse and evaluate current and future trends in database technologies

LO2 Construct and evaluate data models based on analysis of data requirements

LO3 Comprehend and describe the relational database model

LO4 Design, implement and evaluate a relational database system with an appropriate database package

LO5 Formulate and assess advanced SQL queries and commands 

Exam Instructions

Please read carefully all instructions before starting the exam

Submission: Two links are available on Moodle for the submission of TABA. Link 1 is mandatory to upload TABA report.

Link 1 (Mandatory): Upload Single file (MS Word/ pdf) report submission including SQL code, illustrations, images, snaps or handwritten work (Turnitin link). Please, use the format below for the filename. 

[LastName]_[FirstName]_[Program]_IDB_TABA

Link 2: Upload zip folder that may contain any supporting material (SQL code files or any images or snaps, for the justification of your answers in the submitted report at link 1) 

[LastName]_[FirstName]_[Program]_IDB_TABA_SUP.zip

            Turnitin database will be used to check the plagiarism of submitted report at link 1.

Citrix Tools available: MySQL Workbench, Microsoft Visio and MS Office are available to complete this TABA.  Include reference to any material you have used to answer the questions.

Note: This is an individual personal assignment, co-operation or collaboration among students is strictly not allowed and may result in disqualification. Students may be asked to outline/explain in person the reason for any approach taken or solution provided. 

Attempt all questions and each question worth 20 marks in total. Choose the appropriate option for Q3 and Q4.

Carefully examine the following description and Entity Relationship Diagram and answer Question 1, and 2

A hospital management system wants to ensures that information regarding health care is managed properly. The database designers of the hospital came up with the following ER diagram with four entities and three relationship types (read left to right).

• A large number of healthcare providers/doctors work in a hospital.
• There is an influx of patients in every hospital. It is difficult to maintain such a large database. Thus, ER diagrams help in analyzing such a large database with ease.
• Since every patient has a medical record, it is very costly to store these records in hard copy. So, it is better for the records to be stored in soft copy.

1. Transform the conceptual design (ER diagram) into a physical design by converting the entities and relationships into their appropriate tables. Show if your tables are normalized using 1^st, 2^nd, and 3^rd normal form. [20 Marks]

• Create a database called HMS and convert all the resulting logical tables from question 1 into a physical database design using DDL. Choose the appropriate datatype, primary and foreign

keys for the attributes.  Fill your table with some data of your choice(you can use Mockaroo to create the data).Provide detailed assumptions for any of your design decisions. [20 Marks]

• Using your database created in question 2 answer the following question using SQL statements:
  • Insert a new Doctor with all the relevant information   (2 marks)
  • Increases the salary of Doctor by 10%   (2 marks)
  • Display the details of all hospitals based in Dublin   (3 marks)
  • List the name patients, problems that they have and the date of examination.                                                                      (4 marks)
  • List the name of doctors and the name of hospital they are working at if they have a salary bigger than 100000   (4 marks)
  • Create a view that shows the name of patients and the hospital that they reside in.                                                                            (5 marks)

• Discuss the BASE characteristics of non-relational databases by choosing one of the contexts below. Discuss the four types of non-relational databases along with the scenarios suitable to use and avoid them. [20 Marks, Maximum 600 Words]
  • Context 1: Social media
  • Context 2: Big data from IoT devices
  • Context 3: Data-driven and Semantic Web.
  • Context 4: Airbnb record management system

If your NCI ID ends with

0,1 or 2 use context 1

3,4 or 5 use context 2

6,7 use context 3

8,9 use context 4

• Differentiate between Authorisation and Authentication with a suitable example to elaborate. With the help of a suitable diagram, explain the various Database Security Levels and how Discretionary Access Control in SQL maintains security. Discuss why Availability is an important aspect of secure DBMS and how it can be ensured. [20 Marks, Maximum 600 Words]