Answered step by step
Verified Expert Solution
Question
1 Approved Answer
In the development of an application for camera-based surveillance of elderly people at home, a database is to be used to manage the metadata
In the development of an application for camera-based surveillance of elderly people at home, a database is to be used to manage the metadata captured by the cameras. Two cameras are installed; one with view to the kitchen area and one with view to the living room. In the deployment of the system, the raw images are saved to a disk, it is sought to store in the database the following information: the people that can be recognised by the cameras, the instances when these people are seen on the cameras (specifying the time and location when a person is observed) and events of interest that are also detected (The predefined events of interest are: "preparing meal', 'eating', 'watching TV', 'sustaining a conversation', 'fallen to floor'). Detected Events should include the recognised event of interest, the location and the person associated to the event. An assumption is that an event of interest is associated only to a single person; but a single person can be involved on several events of interest. (a) Design an Entity Relationship diagram that follows the relational model principles that fulfil the requirements provided to deploy the database described to store and manage the camera meta-data. (10 marks) (b) Taking as an input the design made in (a), create some dummy data (5 events and 2 people observed in the cameras) and use this data to support why your design is normalised up to third normal form. (10 marks) 2. film code Ac01 Ac02 Co01 (a) SQL (Structured Query Language) is the tool used to interact with Relational Database Management Systems (RDBMS) and it is formed of four sub-languages. Give the name, objective, and a command example of each of these sub-languages. (8 marks) Co02 (b) A database has been set up to store a person's favourite films. Films are organised by genre; age categories have also been identified. The primary key for this table is (film_code, genre_code): genre code 01 01 02 02 film_name Indiana Jones Mission Impossible Two Weeks Notice Paddington Genre_name Action Action Comedy Comedy age_category PG 12 12 PG (i) Identify all partial dependencies in the above table. (3 marks) (ii) Through normalisation, remove any partial dependencies in the above table. (5 marks) order_num ord_amount ord_date cust_code 200100 1000 08/01/2019 C00015 200112 2000 30/05/2019 C00016 200114 3.500 15/08/2019 C00002 200118 500 20/07/2019 C00023 200130 2500 200105 2500 3000 1000 30/07/2019 C00025 18/07/2019 C00025 |15/07/2019|C00001 10/07/2019 C00020 20/06/2019 C00017 20/07/2019 C00024 20/07/2019 C00015 500 2500 2500 200101 200111 orders 200124 200129 200127 customer C00013 C00001 cust_code cust_name cust_city cust_country grade agent_code London UK C00020 Holmes Micheal Albert Ravindran Cook New York USA New York USA Bangalore India C00025 C00024 London UK C00015 Stuart London UK Bolt C00002 New York USA C00023 Karl London UK C00017 Srinivas Bangalore India C00016 Venkatpati Bangalore India 2 2 3 2 2 1 3 10 2 2 A003 A008 A008 A011 A006 A003 A008 A006 A007 A007 Create a SQL statement that generates a View called "london_orders". The view needs to be formed by the orders linked to customers located in London. Given that this view is for business users, the attributes need to be renamed to be more descriptive (order_number, amount, date, customer). SECTION B INFORMATION SECURITY 3. Many cars can be locked and unlocked remotely using the key fob. When the owner of a car presses a button on the key fob, it sends a radio signal to the car. The car has an embedded computer, which locks or unlocks the car's doors if it receives the correct signal. Joy Rider is a thief who operates in car parks. She uses a laptop computer connected to a radio transceiver to record the radio signals sent by key fobs. When a car is left by its owner in the car park, she replays the signal to unlock the car and steals any valuables left inside. (a) Joy is caught by the police and sent to prison. Give THREE examples of information the prison might store about her and her stay in prison, which either she or the prison governor would want to be kept confidential. For each, suggest a DISTINCT person, kind of person or organisation who might want this information and a DISTINCT reason why. (9 marks) (b) Rex Motors is a car company. Every car they manufacture has a unique serial number. The serial number is engraved or printed in a small font on many parts of the car, including the chassis, engine and windows. When Rex Motors sell a car at an official dealership, they record the serial number of the car and the contact details of the new owner in a database. If the car is later stolen, police can look up the serial number in the database to find the name and address of the original owner, so that they can return the car. Rex Motors want the police to be able to look up this information quickly, so they make the database available publicly online. Rex Motors are worried about thieves like Joy Rider. So, rather than programming their key fobs to send the car's serial number unencrypted in the radio signal, they program them to send the MD5 hash of the serial number. They also reduce the power of the radio signal, so that it has a range of only 30 metres. (b) A few months later, Luke notices some strange orders in the database. After checking the MariaDB logs, he finds some unusual queries, which he thinks it should not have been possible to execute through the Web application. He concludes that a security vulnerability on the Web server has been exploited by an attacker. (i) Why might an attacker want to delete logs on the server? (2 marks) (ii) Why might an attacker choose NOT to delete logs on the server? (2 marks) (iii) Could installing software updates fix this vulnerability? Explain your answer fully. (4 marks) 4. Three common security measures are: regularly installing software updates; setting up a network firewall; and encrypting network traffic using TLS. The Lamp Company is a small business that sells lamps through an online shop hosted on its Web server. Luke Smith is a system administrator who works for the company. He wrote the Web application that implements the shop, using the scripting language PHP and Web server software Apache. The application uses a MariaDB SQL database to store information about customers and their orders. (a) Before opening the online shop, Luke sets up a network firewall to protect the Web server. Will a firewall help to enforce the following information security properties for the online shop? Briefly justify your answers. (i) Confidentiality. (ii) Integrity. (iii) Availability. (b) A few months later, Luke notices some strange orders in the database. (2 marks) (2 marks) (2 marks) (c) of the three common security measures mentioned at the start of this question, write down the ONE measure that would be most likely to mitigate the risk present in each of the following situations. (i) Apache is written in C and reads HTTP requests into a fixedsize buffer. (2 marks) (ii) Luke is in a cafe with his laptop and uses the free wifi to login to the server and change some settings (2 marks) (iii) The default MariaDB installation has a blank password and listens for connections over the network on port 3306. (2 marks)
Step by Step Solution
★★★★★
3.40 Rating (166 Votes )
There are 3 Steps involved in it
Step: 1
The smart video surveillance system uses 3tier architecture that comprises of client side application server and a database server The application server consists of the server device which is equippe...Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started