Database System Technology

This document explores the world of Database System Technology. It includes BI reports, DBMS codes, live demo of database and bibliography. The document covers topics like creating tables, inserting data, primary keys, foreign keys and more.

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
Database BI Reports:.......................................................................................................................2
DBMS Codes:..................................................................................................................................3
Live Demo of Database:..................................................................................................................7
Bibliography:...................................................................................................................................9

2DATABASE SYSTEM TECHNOLOGY
Database BI Reports:
Figure 1: Total Purchase Amount of Individual Buyer
(Source: Created by Author)

3DATABASE SYSTEM TECHNOLOGY
Figure 2: Total Amount of Car Sold Against Months
(Source: Created by Author)
DBMS Codes:
CREATE DATABASE IF NOT EXISTS autoseller;
USE autoseller;
CREATE TABLE tbl_buyer (
buyerID int(11) NOT NULL,
first_name varchar(150) NOT NULL,
last_name varchar(150) NOT NULL,
address varchar(150) NOT NULL,
contact_number varchar(150) NOT NULL
);
CREATE TABLE tbl_car (
car_number int(11) NOT NULL,
make varchar(150) NOT NULL,

4DATABASE SYSTEM TECHNOLOGY
model varchar(150) NOT NULL,
color varchar(150) NOT NULL,
price decimal(10,2) NOT NULL,
year year(4) NOT NULL,
mileage int(11) NOT NULL,
body_type varchar(150) NOT NULL,
fuel_type varchar(150) NOT NULL,
engine_size varchar(150) NOT NULL,
fuel_consumption varchar(150) NOT NULL,
CO2_emission varchar(150) NOT NULL
);
CREATE TABLE tbl_car_owner (
car_number int(11) NOT NULL,
buyer_id int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL
);
CREATE TABLE tbl_sales_invoice (
sales_number int(11) NOT NULL,
car_number int(11) NOT NULL,
seller_id int(11) NOT NULL,
buyer_id int(11) NOT NULL,
purchase_date date NOT NULL,
sold_date date NOT NULL,
sold_price decimal(10,2) NOT NULL
);
CREATE TABLE tbl_seller (
seller_id int(11) NOT NULL,
last_name varchar(150) NOT NULL,
first_name varchar(150) NOT NULL,

5DATABASE SYSTEM TECHNOLOGY
address varchar(150) NOT NULL,
contact_number varchar(150) NOT NULL
);
ALTER TABLE tbl_buyer
ADD PRIMARY KEY (buyerID);
ALTER TABLE tbl_car
ADD PRIMARY KEY (car_number);
ALTER TABLE tbl_car_owner
ADD PRIMARY KEY (car_number,buyer_id),
ADD KEY buyer_id (buyer_id);
ALTER TABLE tbl_sales_invoice
ADD PRIMARY KEY (sales_number),
ADD KEY buyer_id (buyer_id),
ADD KEY car_number (car_number),
ADD KEY seller_id (seller_id);
ALTER TABLE tbl_seller
ADD PRIMARY KEY (seller_id);
ALTER TABLE tbl_car_owner
ADD CONSTRAINT tbl_car_owner_ibfk_1 FOREIGN KEY (buyer_id)
REFERENCES tbl_buyer (buyerID),
ADD CONSTRAINT tbl_car_owner_ibfk_2 FOREIGN KEY (car_number)
REFERENCES tbl_car (car_number);
ALTER TABLE tbl_sales_invoice
ADD CONSTRAINT tbl_sales_invoice_ibfk_1 FOREIGN KEY (buyer_id)
REFERENCES tbl_buyer (buyerID),

6DATABASE SYSTEM TECHNOLOGY
ADD CONSTRAINT tbl_sales_invoice_ibfk_2 FOREIGN KEY (car_number)
REFERENCES tbl_car (car_number),
ADD CONSTRAINT tbl_sales_invoice_ibfk_3 FOREIGN KEY (seller_id)
REFERENCES tbl_seller (seller_id);
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'),
(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');

7DATABASE SYSTEM TECHNOLOGY
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');
Live Demo of Database:

8DATABASE SYSTEM TECHNOLOGY

9DATABASE 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.