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
Question 4
4.1 One of your friends suggests that you hire a database administrator to help you create the proposed relational database.
4.1.1 List THREE duties that are carrie... show full transcript
Worked Solution & Example Answer: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
Step 1
List THREE duties that are carried out by a database administrator.
96%
114 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
Management: Overseeing the overall database environment to ensure it runs effectively.
Maintenance: Performing routine checks and updates to keep the database functioning smoothly.
Security access rights: Managing who has access to the database and ensuring data security.
Step 2
What is a relational database?
99%
104 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
A relational database is a collection of tables that are linked together through defined relationships. This structure allows for efficient data management and retrieval.
Step 3
List TWO possible sources of data for your database.
96%
101 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
Existing documents: Such as invoices or sales records that can provide valuable data.
Existing workflows: Current business processes that can highlight necessary data points.
Step 4
What is involved in data maintenance?
98%
120 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
Data maintenance involves keeping information up to date to ensure the reliability and accuracy of the data stored in the database.
Step 5
What is the general aim of normalization?
97%
117 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
Normalization aims to organize a database into separate tables to reduce data redundancy and avoid update anomalies.
Step 6
Which field would you regard as redundant? Motivate your answer.
97%
121 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
The Owner's Name field is redundant because it can be derived from the Owner_ID field, which references the owners table.
Step 7
Using SQL, split the database into TWO tables. Clearly indicate the table names, primary and foreign keys as well as the relationship between the tables.
96%
114 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
Owners Table:
Primary Key: Owner_ID
Fields: Owner_ID, Name, Contact
Clothes Table:
Primary Key: Clothes_ID
Foreign Key: Owner_ID
Fields: Clothes_ID, Item, Basket, Category, MarkUp, Total
Relationship: Owners Table (1) to Clothes Table (N) indicating that one owner can have multiple items.
Step 8
Write a statement to show all the information from the table arranged in ascending order according to the price.
99%
104 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
SELECT * FROM Stock_DetailsTB ORDER BY TotalValue;
Step 9
Write a statement that will delete the item in the old basket called Dress.
96%
101 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
DELETE FROM Stock_DetailsTB WHERE Item='Dress';
Step 10
Malcolm Peter's phone number has changed. Please update it to 0715624117.
98%
120 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
UPDATE Stock_DetailsTB SET Contact = '0715624117' WHERE Owner's Name = 'Malcolm Peter';
