Instructions
Individual Assignment#3A
Description:
Create Database and load Data - create the physical database and load it with "real" or "realistic" data.
Instructions:
a. Correct relational model as per feedback provided for individual assignment#2A.
b. Generate the DDL code from the relational model of your individual project.
c. Create tables along with column comments, Primary Keys, and foreign keys on Oracle Live SQL.
d. Populate all tables with appropriate data - must have adequate data to support the problem / opportunity of your business case (7 to 10 records for Parent tables, 10 to 15 records for Child tables, 15 to 20 records for intersect tables)
e. Run following dictionary queries and capture the screenshots.
REM: List of Tables
select table_name
from user_tables;
REM: List of Table Columns
select table_name, column_name,column_id
from user_tab_columns
order by table_name,column_id;
REM: List of Table Column Constraints
select table_name,constraint_name,constraint_type,search_condition,index_name,r_constraint_name,delete_rule
from user_constraints
order by table_name;
REM: List of Table Column Comments
select table_name,column_name,comments
from user_col_comments
order by table_name;
f. For EACH tables of your database schema run following to show data. Replace with actual table name.
select count(*) from ;
For example, SELECT count (*) FROM AP_PAYMENT;
Submission:
Submit following in a single PDF document (your initial_IAS3A.pdf with screen shots copied to the same document. Each team member needs to submit the assignment.
1. Title page
2. Executive Summary
3. Logical Model
4. Relational Model
5. DDL script. generated from Relational model
6. Resulting screen shots from the dictionary queries in instructions "e" above
7. Resulting screen shots from the queries in instructions "f" above
Sumit your assignment with file name format "asp13_ISA3A.pdf" (replace asp13 with your net id)
Submission Feedback
Overall Feedback
Please make following changes before the next assignment.
Address is the composite attribute and needs to be resolved Name is the composite attribute and needs to be resolved. 1:1 relationship should be resolved in relational model FK column names should be renamed to match PK column names of their parent tables
In logical model, attributes should not be multiple words
Feedback：
ISA2A
Executive summary
This meticulously designed and comprehensive database serves as the backbone of Tesla's operational infrastructure, playing a critical role in enhancing various aspects of the company's operations. Primarily, it focuses on bolstering operational efficiency, streamlining customer relationship management, optimizing inventory control, and ensuring consistently high service quality standards. The database architecture is intentionally crafted to be scalable, allowing for seamless integration of new product lines or business models as Tesla continues to expand its offerings and market reach.
One of the key features of this database is its intricate network of relationships between different entities, which provides a holistic view of Tesla's operations. From the manufacturing processes to post-sale services, every aspect of Tesla's business is interconnected within the database. This interconnectedness facilitates the smooth flow of data across departments, enabling informed decision-making at various levels of the organization. For example, data from production can inform. inventory management, which in turn influences customer service strategies, ensuring a cohesive and efficient operation.
Moreover, the database serves as a repository of invaluable insights derived from extensive data analysis and modeling. By leveraging advanced analytics tools, Tesla gains deeper understanding into market trends, consumer behavior. patterns, and operational performance metrics. These insights are instrumental in guiding strategic decision-making processes, driving product innovation, and optimizing resource allocation. For instance, data on customer preferences can inform. the development of new vehicle features, while insights into production efficiency can lead to improvements in manufacturing processes.
In addition to its role in supporting day-to-day operations, the database also plays a crucial role in ensuring data security and compliance with regulatory standards. Advanced encryption techniques and robust access controls are implemented to safeguard sensitive information and protect against potential cyber threats. Furthermore, the database architecture is designed to facilitate seamless integration with third-party systems and applications, enabling collaboration with suppliers, partners, and other stakeholders within the automotive ecosystem.
Overall, this comprehensive database represents a strategic investment in Tesla's technological infrastructure, underpinning its mission to accelerate the world's transition to sustainable energy. By harnessing the power of data-driven insights and fostering a culture of innovation, Tesla remains at the forefront of the automotive industry, continuously pushing boundaries and shaping the future of mobility.
Logical Model
Relational model
Assumption
- Customers to Orders: One-to-many relationship. A single customer can place multiple orders.
- Orders to Vehicles: Many-to-one relationship. Multiple vehicles can be associated with a single order, especially in cases of fleet sales or multiple purchases by a single customer.
- Vehicles to Production: One-to-one relationship. Each vehicle has a unique production record detailing its manufacturing process.
- Vehicles to Maintenance Records: One-to-many relationship. A vehicle can have multiple maintenance records from various service visits.
- Service Centers to Maintenance Records: One-to-many relationship. A service center can perform. maintenance on multiple vehicles, generating several maintenance records.
- Parts to Part Inventories: One-to-many relationship. A single part type can have multiple inventory records across different locations or suppliers.
- Suppliers to Part Inventories: One-to-many relationship. A supplier can supply multiple types of parts, each with its own inventory records.
- Maintenance Records to Parts: Many-to-many relationship (realized through an associative entity, not listed as a core entity here). A single maintenance activity can require multiple parts, and a part can be used in multiple maintenance activities.
DDL code
-- Generated by Oracle SQL Developer Data Modeler 23.1.0.087.0806
-- at: 2024-03-26 13:10:34 EDT
-- site: Oracle Database 21c
-- type: Oracle Database 21c
-- predefined type, no DDL - MDSYS.SDO_GEOMETRY
-- predefined type, no DDL - XMLTYPE
CREATE TABLE bookingorders (
order_id NUMBER(1, 1) NOT NULL,
order_date DATE NOT NULL,
total_price NUMBER(2, 2) NOT NULL,
customers_customer_id NUMBER NOT NULL
);
ALTER TABLE bookingorders ADD CONSTRAINT bookingorders_pk PRIMARY KEY ( order_id );
CREATE TABLE customers (
customer_id NUMBER NOT NULL,
name VARCHAR2(20) NOT NULL,
email_address VARCHAR2(30) NOT NULL,
phone_number VARCHAR2(30) NOT NULL,
address VARCHAR2(30) NOT NULL
);
ALTER TABLE customers ADD CONSTRAINT customers_pk PRIMARY KEY ( customer_id );
CREATE TABLE maintenance_records (
maintenance_record_id NUMBER NOT NULL,
maintenance_date DATE NOT NULL,
details VARCHAR2(200),
teslavehicles_vehicle_id NUMBER NOT NULL,
-- ERROR: Column name length exceeds maximum allowed length(30)
service_centers_service_center_id NUMBER NOT NULL
);
ALTER TABLE maintenance_records ADD CONSTRAINT maintenance_records_pk PRIMARY KEY ( maintenance_record_id );
CREATE TABLE part_inventories (
inventory_id NUMBER NOT NULL,
quantity VARCHAR2(50) NOT NULL,
location VARCHAR2(50),
parts_part_id NUMBER NOT NULL,
suppliers_suppliers NUMBER NOT NULL
);
ALTER TABLE part_inventories ADD CONSTRAINT part_inventories_pk PRIMARY KEY ( inventory_id );
CREATE TABLE parts (
part_id NUMBER NOT NULL,
parts_name VARCHAR2(50) NOT NULL,
parts_type VARCHAR2(50) NOT NULL,
price NUMBER(2, 2) NOT NULL,
-- ERROR: Column name length exceeds maximum allowed length(30)
maintenance_records_maintenance_record_id NUMBER
);
ALTER TABLE parts ADD CONSTRAINT parts_pk PRIMARY KEY ( part_id );
CREATE TABLE production (
production_id NUMBER(1) NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
status VARCHAR2(50) NOT NULL,
teslavehicles_vehicle_id NUMBER NOT NULL
);
CREATE UNIQUE INDEX production__idx ON
production (
teslavehicles_vehicle_id
ASC );
ALTER TABLE production ADD CONSTRAINT production_pk PRIMARY KEY ( production_id );
CREATE TABLE service_centers (
service_center_id NUMBER NOT NULL,
service_name VARCHAR2(20) NOT NULL,
address VARCHAR2(30),
service_contact_number VARCHAR2(30) NOT NULL
);
ALTER TABLE service_centers ADD CONSTRAINT service_centers_pk PRIMARY KEY ( service_center_id );
CREATE TABLE suppliers (
suppliers NUMBER NOT NULL,
suppliername VARCHAR2(50) NOT NULL,
address VARCHAR2(50),
contact_number VARCHAR2(20) NOT NULL,
main_products VARCHAR2(50) NOT NULL
);
ALTER TABLE suppliers ADD CONSTRAINT suppliers_pk PRIMARY KEY ( suppliers );
CREATE TABLE teslavehicles (
vehicle_id NUMBER NOT NULL,
vehiclesmodel VARCHAR2
-- ERROR: VARCHAR2 size not specified
NOT NULL,
production_date DATE NOT NULL,
color VARCHAR2
-- ERROR: VARCHAR2 size not specified
,
battery_range VARCHAR2
-- ERROR: VARCHAR2 size not specified
NOT NULL,
bookingorders_order_id NUMBER(1, 1),
production_production_id NUMBER(1) NOT NULL
);
CREATE UNIQUE INDEX teslavehicles__idx ON
teslavehicles (
production_production_id
ASC );
ALTER TABLE teslavehicles ADD CONSTRAINT teslavehicles_pk PRIMARY KEY ( vehicle_id );
ALTER TABLE bookingorders
ADD CONSTRAINT bookingorders_customers_fk FOREIGN KEY ( customers_customer_id )
REFERENCES customers ( customer_id );
-- ERROR: FK name length exceeds maximum allowed length(30)
ALTER TABLE maintenance_records
ADD CONSTRAINT maintenance_records_service_centers_fk FOREIGN KEY ( service_centers_service_center_id )
REFERENCES service_centers ( service_center_id );
-- ERROR: FK name length exceeds maximum allowed length(30)
ALTER TABLE maintenance_records
ADD CONSTRAINT maintenance_records_teslavehicles_fk FOREIGN KEY ( teslavehicles_vehicle_id )
REFERENCES teslavehicles ( vehicle_id );
ALTER TABLE part_inventories
ADD CONSTRAINT part_inventories_parts_fk FOREIGN KEY ( parts_part_id )
REFERENCES parts ( part_id );
ALTER TABLE part_inventories
ADD CONSTRAINT part_inventories_suppliers_fk FOREIGN KEY ( suppliers_suppliers )
REFERENCES suppliers ( suppliers );
ALTER TABLE parts
ADD CONSTRAINT parts_maintenance_records_fk FOREIGN KEY ( maintenance_records_maintenance_record_id )
REFERENCES maintenance_records ( maintenance_record_id );
ALTER TABLE production
ADD CONSTRAINT production_teslavehicles_fk FOREIGN KEY ( teslavehicles_vehicle_id )
REFERENCES teslavehicles ( vehicle_id );
ALTER TABLE teslavehicles
ADD CONSTRAINT teslavehicles_bookingorders_fk FOREIGN KEY ( bookingorders_order_id )
REFERENCES bookingorders ( order_id );
ALTER TABLE teslavehicles
ADD CONSTRAINT teslavehicles_production_fk FOREIGN KEY ( production_production_id )
REFERENCES production ( production_id );
-- Oracle SQL Developer Data Modeler Summary Report:
--
-- CREATE TABLE 9
-- CREATE INDEX 2
-- ALTER TABLE 18
-- CREATE VIEW 0
-- ALTER VIEW 0
-- CREATE PACKAGE 0
-- CREATE PACKAGE BODY 0
-- CREATE PROCEDURE 0
-- CREATE FUNCTION 0
-- CREATE TRIGGER 0
-- ALTER TRIGGER 0
-- CREATE COLLECTION TYPE 0
-- CREATE STRUCTURED TYPE 0
-- CREATE STRUCTURED TYPE BODY 0
-- CREATE CLUSTER 0
-- CREATE CONTEXT 0
-- CREATE DATABASE 0
-- CREATE DIMENSION 0
-- CREATE DIRECTORY 0
-- CREATE DISK GROUP 0
-- CREATE ROLE 0
-- CREATE ROLLBACK SEGMENT 0
-- CREATE SEQUENCE 0
-- CREATE MATERIALIZED VIEW 0
-- CREATE MATERIALIZED VIEW LOG 0
-- CREATE SYNONYM 0
-- CREATE TABLESPACE 0
-- CREATE USER 0
--
-- DROP TABLESPACE 0
-- DROP DATABASE 0
--
-- REDACTION POLICY 0
--
-- ORDS DROP SCHEMA 0
-- ORDS ENABLE SCHEMA 0
-- ORDS ENABLE OBJECT 0
--
-- ERRORS 0
-- WARNINGS 0