4.1 One of your friends suggests that you hire a database administrator to help you create the proposed relational database - NSC Information Technology - Question 4 - 2017 - Paper 2

A relational database is a type of database that stores data in structured tables that are linked together by relationships. Each table consists of rows and columns, where rows represent records and columns represent attributes of the data. This structure allows for efficient data retrieval and management through the use of SQL (Structured Query Language).

Two potential sources of data for your database include:

1. Existing documents: These can contain valuable information relevant to your business operations.
2. Business plan: This document outlines your business objectives and strategies, and can provide essential data for the database.

Data maintenance is the process of keeping information up-to-date and ensuring the integrity and security of data within the database. This includes tasks such as regular backups, updating records, cleaning obsolete data, and monitoring the performance of the database to identify and resolve any issues.

The general aim of normalization is to organize data within a database in such a way as to minimize redundancy and dependency. This process involves dividing large tables into smaller, interconnected tables, ensuring that each piece of data is stored only once, thereby reducing the likelihood of anomalies in data modification.

The field Owner's Name could be regarded as redundant because it can be derived from the Owner_ID field. Since the Owner_ID uniquely identifies the owner, having their name in the same table leads to unnecessary duplication of information.

The database can be split into two tables as follows:

1. Owners Table:

   • Primary Key: Owner_ID
   • Fields: Owner_ID, Name, Contact

2. Clothes Table:

   • Primary Key: Clothes_ID
   • Foreign Key: Owner_ID
   • Fields: Clothes_ID, Item, Basket, Category, Owner_ID, MarkUp, Total

Relationship: The Owners Table and Clothes Table are related through Owner_ID, linking each item to its respective owner.

The SQL statement to retrieve all information from the table sorted by price in ascending order is:

SELECT * FROM Stock_DetailsTB ORDER BY TotalValue ASC;

The SQL statement to delete the item named 'Dress' from the Stock_DetailsTB is:

DELETE FROM Stock_DetailsTB WHERE Item='Dress';

The SQL statement to update Malcolm Peter's phone number is:

UPDATE Stock_DetailsTB SET Contact='0715624117' WHERE Owner's Name = 'Malcolm Peter';