代写ITS D005 Database Management and Security 2024代做留学生Matlab编程

ITS D005 Database Management and Security

25 April 2024

SECTION A

Question 1

(a)       Name TWO (2) benefits of storing data in a database, as opposed to a spreadsheet like Microsoft Excel or Google Sheets, noting that in today’s digital age, it is possible to share spreadsheets on cloud services like Google Drive to bypass the data consistency issues they traditionally create.          (6 marks)

(b)       What is the difference between data and information? Why is it important for a business to generate information from their data?                 (10 marks)

(c)       Demonstrate how the relationship between TEACHER and STUDENT maybe M:N.

Draw an ER diagram using Crow’s Feet notation to express this relationship. You may leave out the associative entity and entity attributes if you wish.   (9 marks)

Question 2

(a)       What are the TWO (2) conditions for a strong relationship?           (6 marks)

(b)       In a database storing employee data, is it recommended to use an employee’s mobile phone number, e.g. +65 91234567, as the primary key? Explain your answers. (10 marks)

(c)       Identify all of the steps to convert a table from 0NF to 2NF.            (9 marks)

(a)       The following is a snapshot of a database’s AUTHORS table for a famous news site.

Author_ID	Full Name	Author_Description
2	Leon DeCaprious	Cakes go bad after 25.
3	Vill Smith	Cuckoos are my favourite animal.
4	Ronor MacGregor	Secretly a leprechaun.

(i)        Describe the problem you will encounter if you were to attempt to sort the results by first name or last name.  (5 marks)

(ii)       How would you solve this problem by altering the table structure?  (5 marks)

(iii)      How would the new table structure help to produce the author listing by first

or last name? Suggest a way to optimise the table for searching by first and last name as well.   (5 marks)

(b)       The table below stores records of different authors, as well as the details of each of their articles:

Author _ID	Name	Email address	Post_ID	Post_Title
2	Leon DeCaprious	leondecaprious25@hotmail. com	8	Why cakes must be fresh
3	Vill Smith	coppercarbonpotassium@g mail.com	10	How to punch a Rock
4	Ronor MacGregor	[email protected]	12	Life of a champ- champ
4	Ronor MacGregor	[email protected]	13	Owning multiple cakes
3	Vill Smith	coppercarbonpotassium@g mail.com	14	Life being bald

(i)        Identify the columns in the table which have redundancy.    (5 marks)

(ii)       How would you suggest reorganising the table structure to remove the redundancy?   (5 marks)

(a)       You are working for a company that wants to launch a blog on its website. They have hired a developer to create a custom blogging web application, and you are to create an ER diagram to tell the developer how you want the database to be like. Here are the business rules your boss has given you:

The blog will have multiple AUTHORS.

Each AUTHOR will be able to write many ARTICLES on the blog.

Each ARTICLE can later be assigned multiple AUTHORS.

Each ARTICLE can take multiple COMMENTS posted by readers.

Each COMMENT can only belong to a single POST.

Draw the ER diagram of this database. You NEED NOT list the attributes of each table, but you will NEED to use associative entities wherever applicable. (Hint: Remember to show the relationship  strength, cardinality and connectivity too, wherever appropriate).   (13 marks)

(b)       Your boss has requested for you to populate the AUTHORS table with the following attributes:

First name of the author.

Last name of the author.

Date the author joined.

Profile description of the author.

Draw an ER diagram of the AUTHORS table with attributes that are able to store the attributes above. Include any primary keys and foreign keys that are required for the table to work, but you DO NOT need to include other tables, connections to the other tables, or the types of the attributes (e.g. INTEGER, VARCHAR, etc.).   (12 marks)