Car Sales Database System Technology: SQL DDL Script & Structure

This assignment presents a database system technology solution, focusing on the creation of a car sales database using SQL Data Definition Language (DDL) scripts. The solution includes the creation of tables such as tbl_buyer, tbl_car, tbl_car_owner, tbl_sales_invoice, and tbl_seller, each with specific attributes and data types. Primary keys and foreign key constraints are defined to establish relationships between the tables, ensuring data integrity. The document also provides screenshots of the successful DDL script execution and the resulting database structure. A bibliography is included, referencing relevant academic sources on SQL queries and database management.

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

1DATABASE SYSTEM TECHNOLOGY
Database Structure:
CREATE TABLE tbl_buyer (
buyerID number(10) NOT NULL,
first_name varchar2(150) NOT NULL,
last_name varchar2(150) NOT NULL,
address varchar2(150) NOT NULL,
contact_number varchar2(150) NOT NULL
);
CREATE TABLE tbl_car (
car_number number(10) NOT NULL,
make varchar2(150) NOT NULL,
model varchar2(150) NOT NULL,
color varchar2(150) NOT NULL,
price number(10,2) NOT NULL,
year number(4) NOT NULL,
mileage number(10) NOT NULL,
body_type varchar2(150) NOT NULL,
fuel_type varchar2(150) NOT NULL,
engine_size varchar2(150) NOT NULL,
fuel_consumption varchar2(150) NOT NULL,
CO2_emission varchar2(150) NOT NULL
);
CREATE TABLE tbl_car_owner (
car_number number(10) NOT NULL,
buyer_id number(10) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL
);
CREATE TABLE tbl_sales_invoice (

2DATABASE SYSTEM TECHNOLOGY
sales_number number(10) NOT NULL,
car_number number(10) NOT NULL,
seller_id number(10) NOT NULL,
buyer_id number(10) NOT NULL,
purchase_date date NOT NULL,
sold_date date NOT NULL,
sold_price number(10,2) NOT NULL
);
CREATE TABLE tbl_seller (
seller_id number(10) NOT NULL,
last_name varchar2(150) NOT NULL,
first_name varchar2(150) NOT NULL,
address varchar2(150) NOT NULL,
contact_number varchar2(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);
ALTER TABLE tbl_sales_invoice
ADD PRIMARY KEY (sales_number);
ALTER TABLE tbl_seller
ADD PRIMARY KEY (seller_id);

3DATABASE SYSTEM TECHNOLOGY
ALTER TABLE tbl_car_owner
ADD CONSTRAINT tbl_car_owner_ibfk_1 FOREIGN KEY (buyer_id) REFERENCES
tbl_buyer (buyerID);
ALTER TABLE tbl_car_owner
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);
ALTER TABLE tbl_sales_invoice
ADD CONSTRAINT tbl_sales_invoice_ibfk_2 FOREIGN KEY (car_number) REFERENCES
tbl_car (car_number);
ALTER TABLE tbl_sales_invoice
ADD CONSTRAINT tbl_sales_invoice_ibfk_3 FOREIGN KEY (seller_id) REFERENCES
tbl_seller (seller_id);
Figure 1: DDL Script Execution Successful
(Source: Created by Author)

4DATABASE SYSTEM TECHNOLOGY
Figure 2: Created Database Structure using DDL Script
(Source: Created by Author)

5DATABASE SYSTEM TECHNOLOGY
Bibliography:
Guagliardo, P., & Libkin, L. (2017). Correctness of SQL queries on databases with nulls. ACM
SIGMOD Record, 46(3), 5-16.
Chandra, B., Chawda, B., Kar, B., Reddy, K. V., Shah, S., & Sudarshan, S. (2015). Data
generation for testing and grading SQL queries. The VLDB Journal—The International
Journal on Very Large Data Bases, 24(6), 731-755.
Guagliardo, P., & Libkin, L. (2016, June). Making SQL queries correct on incomplete databases:
A feasibility study. In Proceedings of the 35th ACM SIGMOD-SIGACT-SIGAI
Symposium on Principles of Database Systems (pp. 211-223). ACM.
Kamara, S., & Moataz, T. (2018, December). SQL on structurally-encrypted databases.
In International Conference on the Theory and Application of Cryptology and
Information Security (pp. 149-180). Springer, Cham.