Database System Technology: Prototype, Test Plan & DML Codes

This project provides a comprehensive overview of database system technology, including a complete prototype database designed with one-to-one, one-to-many, and many-to-one relationships to reflect real-world scenarios, adhering to normalization principles up to the third normal form to prevent data anomalies and ensure data integrity. The project incorporates a detailed test plan to validate the database structure, data types, and security measures, including check constraints for data validity. It also includes DBMS DML codes for inserting data into various tables such as tbl_buyer, tbl_car, tbl_car_owner, tbl_seller, and tbl_sales_invoice, demonstrating practical implementation and data management techniques. Desklib offers this and similar solved assignments to aid students in their studies.

Running head: DATABASE SYSTEM TECHNOLOGY
Database System Technology
Name of the Student
Name of the University
Author’s note:

1DATABASE SYSTEM TECHNOLOGY
Table of Contents
Complete Prototype Database:........................................................................................................2
Complete Test Plan Document:.......................................................................................................3
DBMS DML Codes:........................................................................................................................4
Bibliography:...................................................................................................................................6

2DATABASE SYSTEM TECHNOLOGY
Complete Prototype Database:

3DATABASE SYSTEM TECHNOLOGY
Complete Test Plan Document:
In terms of structure, the database should have all the relationships in the form of one-to-
one, one-to-many or many-to-one. This will allow the database to be implemented according to
the real life scenario. The many-to-many relationships are not capable of meeting the

4DATABASE SYSTEM TECHNOLOGY
requirements of normalization. The second, things is normalization. This process allows the
database to prevent data anomalies to happen. The normalization should be done up to third
normal form. This makes sure that database has no transitive dependency. Each of the attributes
of the database must have a valid and relevant data type and data value. Such as, an attribute that
stores date should have only date related data type.
In terms of security, the database should accept only valid data. This can be implemented
using the programming language used for developing the actual system. There are some ways to
implement it into the database too. The check constraint can allow the database to check the
validity of the data entered into the system.
DBMS DML Codes:
INSERT INTO tbl_buyer (buyerID, first_name, last_name, address, contact_number)
VALUES
(101, 'Ethan', 'Umbagai', '81 Darwinia Loop NULLAGINE WA 6758', '[phone]'),
(201, 'Tristan', 'Waddell', '22 Friar John Way, MYARA WA 6207', '[phone]'),
(301, 'Lincoln', 'Jerome', '20 Wagga Road, SAN ISIDORE NSW 2650', '[phone]');
INSERT INTO tbl_car (car_number, make, model, color, price, year, mileage,
body_type, fuel_type, engine_size, fuel_consumption, CO2_emission) VALUES
(101, 'alfa-romero', 'Sports', 'red', '150000.00', 2018, 10, 'convertible', 'gas', '80 ',
'mpfi', '.3'),
(201, 'mercedes-benz', 's102', 'royal blue', '250000.00', 2017, 7, 'wagon', 'diesel', '183',
'idi', '.25'),
(301, 'alfa-romero', 'SUV', 'red', '150000.00', 2016, 12, 'convertible', 'Diesel', '90 ',
'mpfi', '.5');
INSERT INTO tbl_car_owner (car_number, buyer_id, from_date, to_date) VALUES
(101, 201, '2015-07-22', '2015-08-15'),
(201, 101, '2015-07-22', '2015-07-22'),

5DATABASE SYSTEM TECHNOLOGY
(301, 301, '2015-07-22', '2015-08-16');
INSERT INTO tbl_seller (seller_id, last_name, first_name, address, contact_number)
VALUES
(101, 'Mary', 'Barak', '83 Cherry Grove LILEAH TAS 7330', '[phone]'),
(201, 'Georgia', 'Kavel', '96 Jones Road KENMORE DC QLD 4069', '96 Jones Road
KENMORE DC QLD 4069'),
(301, 'Sophia', 'Fergusson', '42 Weigall Avenue CAVENAGH SA 5422', '(08) 8745
0115');
INSERT INTO tbl_sales_invoice (sales_number, car_number, seller_id, buyer_id,
purchase_date, sold_date, sold_price) VALUES
(101, 101, 201, 201, '2015-08-15', '2015-08-15', '1200.00'),
(201, 201, 101, 101, '2015-07-22', '2015-07-22', '2200.00'),
(301, 301, 301, 301, '2015-08-16', '2015-08-16', '3200.00');

6DATABASE SYSTEM TECHNOLOGY
Bibliography:
Aspin, A. (2016). Pro Power BI Desktop. Apress.
Dedic, N., & Stanier, C. (2016, December). Measuring the success of changes to existing
business intelligence solutions to improve business intelligence reporting.
In International Conference on Research and Practical Issues of Enterprise Information
Systems (pp. 225-236). Springer, Cham.
Ferrari, A., & Russo, M. (2017). Analyzing Data with Power BI and Power Pivot for Excel.
Microsoft Press.
Ferrari, A., & Russo, M. (2017). Analyzing Data with Power BI and Power Pivot for Excel.
Microsoft Press.